|
Subject:
|
Linked Subforms and Search Function
|
|
Posted By:
|
Jae8201
|
Post Date:
|
12/11/2005 5:36:49 AM
|
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 By:
|
mmcdonal
|
Reply Date:
|
12/12/2005 7:44:02 AM
|
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 By:
|
kindler
|
Reply Date:
|
12/12/2005 4:54:01 PM
|
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 By:
|
Jae8201
|
Reply Date:
|
12/12/2005 10:17:27 PM
|
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 By:
|
Jae8201
|
Reply Date:
|
12/13/2005 3:23:41 AM
|
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
|
