Searching in a subform

[nlc]

Hello -
I'm setting up a database where the main form contains the company information. Since we frequently have multiple customers at one company, I've made a subform for contact information. Now for the problem. We want to be able to do a search on the information in the subform to find the contact name and bring up the correct record in the main form. The search function will currently only search in the contacts subform for the current record. Is there a way to make it search all the contact records?
Thank you,
nlc

 

[WildHare]

The combo box on your subform will need to use a query that pulls ALL of the contact records, and also the Foreign key back up to the COMPANY table. After you select a contact, you'll need to do a FIND/SEEK whatever, on the COMPANY recordset, to match the COMPANY FK from the combo box.

If you need more details, let us know.

Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[PMrider]

PUT THIS IN YOUR CODE BEHIND THE FORM

Private Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)

' Create criteria for WHERE clause.
If FieldValue <> &quot;&quot; Then
' Add &quot;and&quot; if other criterion exists.
If ArgCount > 0 Then
MyCriteria = MyCriteria & &quot; and &quot;
End If

' Append criterion to existing criteria.
' Enclose FieldValue and asterisk in quotation marks.
MyCriteria = (MyCriteria & FieldName & &quot; Like &quot; & Chr(39) & FieldValue & Chr(42) & Chr(39))

' Increase argument count.
ArgCount = ArgCount + 1
End If

End Sub


THEN USE A COMBOBOX ON YOUR MAIN FORM FOR THE FIELD YOU WANT TO USE THEN PUT THIS ON THE &quot;ON CLICK&quot; OF THE COMBOBOX


' Create a WHERE clause using search criteria entered by user and
' set RecordSource property of MCR_Info Subform.

Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
Dim Tmp As Variant

' Initialize argument count.
ArgCount = 0

' Initialize SELECT statement.
MySQL = &quot;SELECT * FROM YOURQUERY WHERE &quot;
MyCriteria = &quot;&quot;

' Use values entered in text boxes in form header to create criteria for WHERE clause.
AddToWhere [COMBOBOXNAME], &quot;[YOURFIELDNAME]&quot;, MyCriteria, ArgCount

' If no criterion specifed, return all records.
If MyCriteria = &quot;&quot; Then
MyCriteria = &quot;True&quot;
End If

' Create SELECT statement.
MyRecordSource = MySQL & MyCriteria

' Set RecordSource property of your Subform.
Me.RecordSource = MyRecordSource

' If no records match criteria, display message.
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox &quot;No records match the criteria you entered.&quot;, 48, &quot;No Records Found&quot;

End If


Hope this is what you want

Alan