"Deano" <de***@mailinator.comschreef in bericht news:5d*************@mid.individual.net...
Let me know more!
OK, I wil give an example with only two search-controls. You can adapt or expand that idea.
We are going to search Students here on Surname and City.
We need a Mainform (FrmSearch) with the two search-controls (strCity and strSurname, unbound)
We need a subform that will show the results.
Subform (Subform and control is called FrmSearchSub) might have an initial recordsource like:
Select * FROM TblStudents WHERE
Surname = Like "*" & Forms!FrmSearch!strSurname & "*" AND
City=Like "*" & Forms!FrmSearch!strCity & "*"
==The form will give you all the students when opened.
Make sure this works !!
You will then need to change the recordsource for the subform while typing in a search-string in a search-control like:
Private Sub strSurName_Enter()
Dim strSQL as string
strSQL="Select * FROM TblStudents WHERE"
strSQL=strSQL & " Surname = Like "*" & Forms!FrmSearch!strSurname.Text & "*" AND
strSQL=strSQL & " City=Like "*" & Forms!FrmSearch!strCity & "*"
Me.FrmSearchSub.Form.RecordSource = strSQL
End Sub
==The main point here is that we need to use the .Text property of the search-control in the recordsource.
Similar for strCity:
Private Sub strCity_Enter()
Dim strSQL as string
strSQL="Select * FROM TblStudents WHERE "
strSQL=strSQL & " Surname = Like "*" & Forms!FrmSearch!strSurname & "*" AND
strSQL=strSQL & " City=Like "*" & Forms!FrmSearch!strCity.Text & "*"
Me.FrmSearchSub.Form.RecordSource = strSQL
End Sub
You need a bit more code for the search-controls:
Private Sub strSurName_Change()
Me!FrmSearchSub.Requery
End Sub
Also for the second control:
Private Sub strCity_Change()
Me!FrmSearchSub.Requery
End Sub
==The requery will force the subform to show a limited set of records for each character that you type.
Suppose your Student is found, (or your Student is one of the xxx left...)
Now your sub can easily be coded to open the form FrmStudents with the record you want:
Let's suppose your PK is StudentNr.
Private Sub StudentNr_Click()
Dim lngStudent As Long
lngStudent = Me!StudentNr
DoCmd.Close acForm, Me.Parent.Name 'Closing the searchform will also close the subform
DoCmd.OpenForm "FrmStudents", , , "StudentNr = " & lngStudent
End Sub
I hope this makes sense to you.
Also hope that I did not make too much typo's here (translated the code somewhat) ;-)
Arno R