Finding records in a form

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

I've got a form and a subform and within the subform I can
search for records using the find command button I set up
from the wizard. My question is how could I be able to
search for a record within the subform and it look at all
the records in the table not just the records in the
subform I'm currently in?

Ex: Say I'm in Company1 form and within this form is the
subform that has all the records for Company1. Currently,
I can search records within that subform and find the info
I entered in the "Find What" field pertaining to Company1
only. I'd like to be able to search all records and it
search throughout the entire subform table for whatever
my "Find What" criteria is and it change the form and
subform to that specific company and subform record.

Does this make sense? If so, I'd appreciate any
assistance.
 
You will need to search the subform data source outside the subform.
Assuming that subform and form are linked on a CompanyID and the field
in the subform you search is called Criteria, create a text box on the
main form called txtCriteria. In its AfterUpdate, enter:

Dim Rst as ADODB.Recordset
Set Rst = New ADODB.Recordest
Rst.Open "SELECT CompanyID FROM SubformDataTable WHERE Criteria = " &
txtCriteria.Value, _
CurrentProject.Connection
If Rst.RecordCount <> 0 Then
Rst.MoveFirst
Me.Recordset.FindFirst "CompanyID = " Rst.Fields(1)
end if
Rst.Close
Set Rst = Nothing

Pavel
 
Back
Top