Quote:
>>>visionstate@googlemail.com wrote:
>>>
>>>>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.
>>>>
>>>
>>>-----BEGIN PGP SIGNED MESSAGE-----
>>>Hash: SHA1
>>>
>>>You're doing too much. You can reduce the amount of VBA code like this:
>>>
>>>Set the RowSource property of the ComboSurname to this (all one line):
>>>
>> SELECT Distinct Surname
>> FROM Sheet1
>> WHERE Dept = Form.ComboDept
>> ORDER BY Surname
>>>
>>>Set the RowSource property of the ComboForename to this (all on line):
>>>
>> SELECT Distinct Forename
>> FROM Sheet1
>> WHERE Surname = Form.ComboSurname
>> ORDER BY Forename
>>>
>>>The Form.ControlName allows the RowSource query to read the named
>>>control on the same form as the ComboBox.
>>>
>>>Then all you need in the ComboBoxes AfterUpdate events is a Requery:
>>>
>>>Private Sub ComboDept_AfterUpdate()
>>>
>> Me!ComboSurname.Requery
>> Me!ComboForename.Requery
>>>
>>>End Sub
>>>
>>>Private Sub ComboSurname_AfterUpdate()
>>>
>> Me!ComboForename.Requery
>>>
>>>End Sub
>>>
>>>Since you are using these ComboBoxes as search criteria you should not
>>>bind them to a query or table (IOW, don't put anything in their
>>>ControlSource properties).
>>>
>>>After the Department, Surname, Forename have been selected then run the
>>>CommandButton "Command5" click event. BTW, you really have to change
>>>the name of that CommandButton to something more descriptive - it will
>>>make your future maintenance work a lot easier.
>>>
>>>You can reduce the If...Then statements to something like the following,
>>>which will allow the users to use wildcard characters.
>>>
>> strSurname = " LIKE '*" & Me!ComboSurname & "*' "
>>>
>>>Now a user can enter Johns?n as the surname and get:
>>>
>> Johnson
>> Johnsen
>> ... etc. ...
>>>
>>>--
>>>MGFoster:::mgf00 <atearthlink <decimal-pointnet
>>>Oakland, CA (USA)
>>>
>>>-----BEGIN PGP SIGNATURE-----
>>>Version: PGP for Personal Privacy 5.0
>>>Charset: noconv
>>>
>>>iQA/AwUBRKl+b4echKqOuFEgEQJ4LgCgiU9B868HgrKlHOQDSx/2nhIYztMAnjLO
>>>XnZg0zwK9AKInlkEERsnn2Or
>>>=wjqz
>>>-----END PGP SIGNATURE-----
>Surname text box at all. Do you know why this would be? I've tried a
>couple of things myself but they don't seem to work...
>R.
