search subform values 1

[NRK]

I have created a search allows users to select a value from either a combo box or a list box. If the user selects a value from the combo box (Category), then they can find all records that match the selected Category or they can select a specific record from the list box (contains all records that correspond to Category).

This search method works great when I have just one form...but I am trying to move it to a form that has a subform. I can get the Search form to find the Category, but I cannot get it to find the specific record. I believe I am able to do the former because the Parent/Child link for this main/sub form is my Category field.

Can anyone think of a way in which I can resolve this issue?

Thank you, in advance.

 

[NRK]

I was wondering if anyone had any advice on this topic? I have made some progress but cannot seem to get it.

 

[LittleSmudge]

I think the problem is that your challenge isn't clear.

Are you saying that the combo box selection finds the record(s) that relate to the MainForm or do they relate to a specific record on the subform?

Example
I've recently been working on a database that contains a table of "ThirdParty" companies. Linked to it is a table of "Contacts" - being people who work for the ThirdParty Cos. This is a One to Many relationship.
The main form is the ThirdPartyCompany
On a Single Form view subform all the Contact people that work for that ThirdParty appear and I can cycle through them one at a time

In the main form header I have a combo box that lists all the Contact people by name. If I pick a person by name the main form goes to the record for their employing company and then the subform goes to the particular individual.


Is this the sort of thing that you are trying to do?

G LS

 

[NRK]

Yes, what you describe is more of what I am trying to do. Sorry I didn't make it clear.

The subform and the main form are pulling from the same query - if it matters.

If it helps, here is the code that I am using:
Private Sub Form_Activate()
Dim rst As DAO.Recordset
Dim strID As String
Dim strCat As String
Dim strVar As String
Dim strLinkCriteria As String

Set rst = Me.RecordsetClone
If IsNull(txtSrchCat) Then
'text field is on main form
Exit Sub
Else
rst.FindFirst "tcID='" & Me.txtID & "'"
Me.Bookmark = rst.Bookmark
cboCategory = Me!txtSrchCat
End If


Set rst = Nothing

If you see anything, I would appreciate your feedback. I have been experimenting with calling the subform control (i.e. DatasheetView.Form.Bookmark = rst.Bookmark instead of Me.bookmark)

 

[LittleSmudge]

Okay then this is how I did it
ThirdParty info in tblThirdParty
ThirdPartyId is PrimeKey

Contacts data is in tblContacts
ContactsId is PrimeKey
ThirdPartyRef is Foreign Key pointing to tblThirdParty

Combo box
RowSource = "SELECT ContactsId, ContactsName, ThirdPartyRef FROM tblContacts ORDER BY ContactsName"

Bound Column = 1
Column Widths = 0cm; 3cm; 0cm so that the ID and Ref fields do not appear when the combo drops down.

Combo_AfterUpdate goes like this
Dim TargetThirdParty As Long
Dim TargetContact As Long
TargetThirdParty = Combo.Column(2) ' Rem Column is Zero based index
TargetContact = Combo
Echo Off ' Stop User seeing what comes next
ThirdPartyIdControl.Visible = True ' Make ThirdPartyIdControl visible
ThirdPartyIdControl.SetFocus ' Set focus to Id control on main form
DoCmd.FindRecord = TargetThirdParty ' Go to the TargetThirdParty
Forms!ContactSubForm.Form!ContactIdField.Visible = True ' Make ContactIdContol on SubForm visible
Forms!ContactSubForm.Form!ContactIdField.SetFocus ' Go to the newly visible control
DoCmd.FindRecord = TargetContact ' Find the target Contact
SomeOtherControl.SetFocus ' Move away from the ContactIDControl

' Make the two controls NotVisible again
Forms!ContactSubForm.Form!ContactIdField.Visible = False
ThirdPartyIdControl.Visible = False
Echo True ' Reveal all to the user


I hope you can extract from that what you can use for your application.

G LS