Hi there,
I am building a database that requires cascading lists on a form.
I currently have (I may be adding more later) 3 combo boxes on my form
- Department, Surname and Forename.
The user chooses the department they want and then the corresponding
surnames from that department can be chosen from the Surname box and
then the Forename depending on which Surname they chose.
I then have a command button which produces the results of the
selection in a query (basically running a query 'on the fly').
The database is a training database so it is likely that there will be
more than 1 record of training for each member of staff.
The following is the code I have used for the combo boxes:
Private Sub ComboDept_AfterUpdate()
ComboSurname.RowSource = "Select Distinct Sheet1.Surname " & _
"FROM Sheet1 " & _
"WHERE Sheet1.Dept = '" & ComboDept.Value & "' " & _
"ORDER BY Sheet1.Surname;"
End Sub
Private Sub ComboSurname_AfterUpdate()
ComboForename.RowSource = "Select Distinct Sheet1.Forename " & _
"FROM Sheet1 " & _
"WHERE Sheet1.Surname = '" & ComboSurname.Value & "' " & _
"ORDER BY Sheet1.Forename;"
End Sub
'Sheet 1' is the table it reads from.
And this is the code in my command button:
Private Sub Command5_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strDept As String
Dim strSurname As String
Dim strForename As String
Dim strTeam As String
Dim strAttending As String
Dim strTraining As String
Set db = CurrentDb
Set qdf = db.QueryDefs("TestQuery")
If IsNull(Me.ComboDept.Value) Then
strDept = " Like '*' "
Else
strDept = "='" & Me.ComboDept.Value & "' "
End If
If IsNull(Me.ComboSurname.Value) Then
strSurname = " Like '*' "
Else
strSurname = "='" & Me.ComboSurname.Value & "' "
End If
If IsNull(Me.ComboForename.Value) Then
strForename = " Like '*' "
Else
strForename = "='" & Me.ComboForename.Value & "' "
End If
If IsNull(Me.ComboTeam.Value) Then
strTeam = " Like '*' "
Else
strTeam = "='" & Me.ComboTeam.Value & "' "
End If
If IsNull(Me.ComboAttending.Value) Then
strAttending = " Like '*' "
Else
strAttending = "='" & Me.ComboAttending.Value & "' "
End If
If IsNull(Me.ComboTraining.Value) Then
strTraining = " Like '*' "
Else
strTraining = "='" & Me.ComboTraining.Value & "' "
End If
strSQL = "SELECT StaffTeamQuery.* " & _
"FROM StaffTeamQuery " & _
"WHERE StaffTeamQuery.Dept" & strDept & _
"AND StaffTeamQuery.Surname" & strSurname & _
"AND StaffTeamQuery.Forename" & strForename & _
"AND StaffTeamQuery.Team" & strTeam & _
"AND StaffTeamQuery.Attending" & strAttending & _
"AND StaffTeamQuery.Training" & strTraining
qdf.SQL = strSQL
DoCmd.OpenQuery "TestQuery"
Set qdf = Nothing
Set db = Nothing
End Sub
The problem I have is, for certain members of staff, where there are 2
entries, it'll only display 1 entry and not the other and for some
members of staff it will not display any information at all.
Also, sometimes, if a persons Surname and Forename are left in the
combo boxes from the previous open of the form, they're department is
changed automatically in my table when a department is selected when
the form is re-opened and a new search started.
Sorry for the long winded post but I wanted to make it as detailed as
possible as I am exhausted of ideas!
Many Thanks!
R.
