Trying to search the subform from Main form

[drblackard]

I have a database built for my work. I was trying to track training hours and jobs by using a subform within a mainform searched by employees names. I am trying to use a combo box with the employees names in the main form to search the query in the subform. Can anyone help me with this. I can't get the search box to communicate with the subform. I am a newbee to access and am not too familiar with codes. Please help me if you can...here are my columns in my forms:

Subform contains:

employee name, date trained, duration of training, job of training, trainee, and comments...

Thanks in advance...

 

[Remou]

Hi
A few ideas.
You could search like this:

Dim rs As DAO.Recordset
Set rs = Me.[i]My_subform[/i].Form.RecordsetClone
rs.FindFirst "LastName=" & Chr(34) & Me.[i]cboSearch[/i] & Chr(34)
If rs.NoMatch Then
    MsgBox "Not found: " & Me.[i]cboSearch[/i]
Else
    Me.[i]My_subform[/i].Form.Bookmark = rs.Bookmark
End If

Or you could change the query the subform is based on:

Me.[i]My_subform[/i].Form.RecordSource = "SELECT [employee name], [date trained], [duration of training], [job of training], [trainee], [comments] FROM [i]tblEmplyees[/i] WHERE [i][LastName][/i]=" & Chr(34) & [Forms]![i][frmMain].cboSearch[/i] & Chr(34)

Or maybe a filter:

Me.[i]My_subform[/i].Form.Filter = "[i][LastName][/i]=" & Chr(34) & Me.[i]cboSearch[/i] & Chr(34)
Me.[i]My_subform[/i].Form.FilterOn = True

You may wish to have an All to reset, if you change the query or use a filter, in which case this should be useful:
How do I add an <All> selection to my combo box drop-down?
faq702-4538

 

[drblackard]

I'm not using the lastname as a field, would that make a difference...I was wanting to use a drop down list combo box to search with on the main form and just select the names from this and it would bring up the appropriate records ... is this possible or will it work with what you have given me here... Thanks

 

[drblackard]

I thought this would eliminate the error of mispelling the names....

 

[Remou]

Well, if you want to use something like the first code sample above, you will need a name field in the query the subform is based on, but not necessarily on the subform. The idea behind the first code sample is that when the search combo box (I have called it cboSearch for the purposes of testing) is updated, the subform is searched for the chosen name using the subform's RecordsetClone. The record is then selected using the Bookmark property.

The other two snippets also depend on a name field being available in the table being filtered or queried.

I do not quite see how you are not using a name field, if you need to search on such a field, so perhaps I am missing some point of what you wish to achieve.

 

[drblackard]

Maybe I should have stated before that i am using the whole name in a combo list box... this way no mispelling is allow...just select the name and BOOM! it's there...I'm sorry for the big mess up on my part...Thanks though for your assistance.

 

[Remou]

No mess up, you did mention the name in the combo box (can we call it cboSearch or have you a name for this combo?).
Can I take it that this lists the field names?
employee name, date trained, duration of training, job of training, trainee, and comments...
To search the subform (let's call it My_Subform for now) based on the name selected in cboSearch, try adding the code below to the After Update event of cboSearch, substituting appropriate names for those in italics:

Dim rs As DAO.Recordset
Set rs = Me.[i]My_subform[/i].Form.RecordsetClone
rs.FindFirst "[employee name]=" & Chr(34) & Me.[i]cboSearch[/i] & Chr(34)
If rs.NoMatch Then
    MsgBox "Not found: " & Me.[i]cboSearch[/i]
Else
    Me.[i]My_subform[/i].Form.Bookmark = rs.Bookmark
End If

 

[drblackard]

I tried the code but I keep getting an error...

Dim rs As DAO.Recordset keeps turning yellow and stops the code from working...

any suggestions...
Thanks

 

[Remou]

It sounds like you need a reference to the Microsoft DAO 3.x Object Library. (References can be found under Tools on the Menubar for the code window.) This reference has not been included as a default since Access 2000, I think.