Hallo,
That's made it clearer.
Would you prefer to have a single 'Find' button, rather than two? I would suggest it would be better as the layout of your form does not easily distinguish which fields are used in conjunction with each button.
A single find button would generally be more elegant, more obvious and more flexible. Of course there may be specific reasons why you want it done this way, and that's fine too.
You could then get rid of your Module Training date find fields.
As indicated earlier, a good way of implementing a Filter, is to use code to build up an SQL Query and set the subforms recordsource when the Find button is clicked.
Before I go any further I'll suggest that you use the standard-ish MS Access naming conventions, where you add a type prefix to all your controls, fields and variables.
ie. Training becomes tblTraining, Name becomes strName, MedChart datMedChart, Role intRole (or lngRole, dblRole etc.) and remove the spaces from your field names, Pt Lists becomes datPtLists, Password Allocated become ysnPasswordAllocated. This makes it much easier to see what's going on (when you get used to it) particularly when you come back to change your db in a years time.
Code:
Private Sub btnFind_Click()
Dim strSQLQuery as String
Dim strField as string
strSQLQuery = ""
if len(nz(me!txtName,""))>0 then strSQLQuery =strSQLQuery & " AND strName Like """ & me!txtName & "*"""
if len(nz(me!txtGroup,""))>0 then strSQLQuery =strSQLQuery & " AND strGroup Like """ & me!txtGroup & "*"""
Select Case me!fraModule
case me!optModule_First.OptionValue
strField ="datColumn1"
case me!optModule_Second.OptionValue
strField ="datColumn2"
case me!optModule_Last.OptionValue
strField ="datColumn3"
case else
strField =""
end select
if strField <>"" then
If isdate(me!txtStartDate) then
if isdate(me!txtEndDate) then
strSQLQuery =strSQLQuery & " AND " & strField & ">=#" & me!txtStartDate & "# AND " & strField & "<=#" & me!txtEndDate & "#"
else 'Start date only specified
strSQLQuery =strSQLQuery & " AND " & strField & ">=#" & me!txtStartDate & "#"
endif
else 'End date only specified
if isdate(me!txtEndDate) then
strSQLQuery =strSQLQuery & " AND " & strField & "<=#" & me!txtEndDate & "#"
endif
endif
endif
if strSQLQuery like " AND *" then
strSQLQuery="WHERE " & mid$(strSQLQuery,5)
else
strSQLQuery=""
endif
forms!frmData.Recordsource="SELECT * FROM tblTraining " & strSQLQuery
lblExit:
Exit Sub
lblErr:
Msgbox Err.Description,vbExclamation,"Error in frmFind.btnFind"
Resume lblExit
End Sub
Other things you might like to consider is putting the Find button at the end of the Find fields, as people (in the West) generally work from top to bottom, so you get the form title, form fields, find button, results as you look down the page. I'd keep Add User at the top as it is not part of the find, although it might be better on nother form? Close could go at the top or bottom, up to you.
You could also implement a field colour coding scheme, with a different colour background for writeable, read-only and Find fields, to aid the user.
One last thing, Radio buttons (the round ones) are generally used in an option group to indicate you can only select one of the options. Checkbox buttons (square) are used where more than one option can be selected.
Just some comments, take them or leave them. You know your users better than I do. With forms, I think looking at MS products is a good way to start. See how they do things in Wizards etc. They're not perfect, but they are usually well done and people are used to them.
Hope the code works,
- Frink
