Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 11th, 2005, 06:36 AM
Registered User
 
Join Date: Dec 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Linked Subforms and Search Function

Hi,

There are two sections to the form that I am creating. In the top section, I have the fields that I want the users to be able to update. In the bottom section (subform), I have all the records listed in the datasheet view. These records are locked so that users can't manipulate them at all, only view them.

1.) Since the record source in both the main form and subform are the same, I know I can link them using any of the fields. If a user accesses one of the records in the main form, is there a way to show that record and all the records before and after in the subform Datasheet view in the bottom half of my form?

2.) I want to create a search function for my users because there will be approximately 40,000 records in this database. I have figured out how to create a query that takes input from the user and produces the results. How can I take that query and apply it to the record source for my form so that when I search for *bank*, my form will populate with all records that contain the word bank in them?

If anyone has insight on these two problems, I would greatly appreciate it. Please let me know if anything is unclear and I can clarify. Thanks for all the help.

Reply With Quote
  #2 (permalink)  
Old December 12th, 2005, 08:44 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

It sounds as if you are saying that you have a parent form and a child form, and both forms are displaying the same data. Why would you do this?

In the second issue, are you trying to do an English search on all fields in the table? Or just some fields?


mmcdonal
Reply With Quote
  #3 (permalink)  
Old December 12th, 2005, 05:54 PM
Friend of Wrox
 
Join Date: Dec 2005
Location: , , .
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You should be able to do it by setting the "On Current" event for both to display the same record in the other form. As for your search example, set whatever you saved your query as in VB to be the RecordSource of the form. That query should also be used for any record set you want to display on that form, be it all records or just the ones searched for.

Reply With Quote
  #4 (permalink)  
Old December 12th, 2005, 11:17 PM
Registered User
 
Join Date: Dec 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the tips.

mmcdonal:
I do have the same data displayed twice. In the top form, I want the users to be able to edit the data using the look of a form. In the bottom subform, I want the users to be able to view all records that come before and after the current record that is displayed in the top form. The bottom form is a datasheet view and the top form is a form view. There is going to be something like 40,000 records in the DB, and users have expressed that they would like to see other records around the current record. It may be best to just do one form in the datasheet view so that they can see all records that way, and then just lock down the fields I don't want users to edit.


kindler:
I figured out how to set the recordsource equal to the search. Everything works fine when I search for something that is in the database. If I search for something that can't be found, my page disappears and I have to refresh it to get the form back. My code is below. I'm thinking I could create a temporary search and run a statement that said If tempsearch is null then cancel action, but I am not sure how to code that. I have never worked with VB before so I am trying to pick it up as I go. Any thoughts if this would work and how something like this would be structured?

Private Sub cmdSearch_Click()
Dim varWhere As Variant

If IsNull([txtSearchBox]) Then
    Ok = General.Err_msg("The search feild is mandatory." & Chr(10) & Chr(10) & "Please enter a search item.")

Else

    Me.RecordSource = _
    "SELECT * FROM [tbl11I_BANK_BRANCH_ALL] " & "WHERE [11I_BANK_NAME] Like Forms![frm11I_BANK_BRANCH_ALL].[txtSearchBox]"

End If

End Sub


Reply With Quote
  #5 (permalink)  
Old December 13th, 2005, 04:23 AM
Registered User
 
Join Date: Dec 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I figured it out. Here is the code in case anyone is interested.

Private Sub cmdSearch_Click()
Dim varBankSearch

If IsNull([txtSearchBox]) Then
    Ok = General.Err_msg("The search feild is mandatory." & Chr(10) & Chr(10) & "Please enter a search item.")

Else

varBankSearch = DLookup("[11I_BANK_NAME]", "[tbl11I_BANK_BRANCH_ALL]", "[11I_BANK_NAME] Like Forms![frm11I_BANK_BRANCH_ALL].[txtSearchBox]")

    If IsNull(varBankSearch) Then
    Ok = General.Err_msg("The search returned no results. Please try another search.")

    Else
    Me.RecordSource = _
    "SELECT * FROM [tbl11I_BANK_BRANCH_ALL] " & "WHERE [11I_BANK_NAME] Like Forms![frm11I_BANK_BRANCH_ALL].[txtSearchBox]"
    End If

End If

End Sub

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Subforms and Search Function Jae8201 Access VBA 4 June 22nd, 2006 10:46 AM
Search function talktome Excel VBA 1 September 6th, 2005 12:47 AM
search function elania MySQL 1 February 6th, 2005 07:00 PM
Search function Urban_Roughneck Beginning PHP 9 January 21st, 2005 08:31 AM
function in linked js file 'is not defined' John K. King Javascript 4 May 7th, 2004 09:04 PM



All times are GMT -4. The time now is 06:49 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.