Cascading combo boxes

[lisa1965]

I have a form with 2 combo boxes, the first box is a name selection and the second box lists all companies that employ that name. The cascading part of the combo boxes works great.

I used the following code on the first combo box, employee name:

Private Sub cboEmployee_AfterUpdate()
Me.cboClient.RowSource = "SELECT DISTINCT [ClientName] FROM AccidentTable WHERE [EmployeeLastName] = '" & Me.cboEmployee & "'; "
Me.cboClient.Requery
End Sub

When the user makes a selection from second combo box, which is filtered by the above code. The value strClient is never populated when the report executes. See code below:

Private Sub Command2_Click()

On Error GoTo Err_Command2_Click
Dim strClient As String
Dim strEmmployee As String
Dim stDocName As String


' Build criteria string for Employee Last Name field
If IsNull(Me.cboEmployee.Value) Then
strEmployee = "Like '*'"
Else
strEmployee = "= '" & Me.cboEmployee.Value & "'"
End If


' Build criteria string for Client field
If IsNull(Me.cboClient.Value) Then
strClient = "Like '*'"
Else
strClient = "= '" & Me.cboClient.Value & "'"
End If


stDocName = "rptIncidentReport"

' Combine criteria strings into a WHERE claus for the filter
strFilter = "[EmployeeLastName] " & strEmployee & " and [ClientName] " & strClient

'This function closes the report if the report is open and then re-opens the report.
Set accobj = Application.CurrentProject.AllReports.Item(stDocName)
If accobj.IsLoaded Then
If accobj.CurrentView = acCurViewPreview Then
DoCmd.Close acReport, stDocName
DoCmd.OpenReport stDocName, acPreview, , strFilter
End If
Else
DoCmd.OpenReport stDocName, acPreview, , strFilter
End If

What am I missing to get the strClient populated with the selection from the second combo box?

Thanks

 

[ajetrumpet]

The quote in the wrong place....??

' Combine criteria strings into a WHERE claus for the filter
strFilter = "[EmployeeLastName] " & strEmployee & " and [ClientName] " <---end of string?? & strClient " <--- should it be here instead??

'This function closes the report if the report is open and then re-opens the report.
Set accobj = Application.CurrentProject.AllReports.Item(stDocName)
If accobj.IsLoaded Then
If accobj.CurrentView = acCurViewPreview Then
DoCmd.Close acReport, stDocName
DoCmd.OpenReport stDocName, acPreview, , strFilter
End If
Else
DoCmd.OpenReport stDocName, acPreview, , strFilter
End If

 

[bliddel]

In Sub Command2_Click() you Dimmed strEmmployee As String but you set strEmployee = "Like '*'". I recommend setting Option Explicit to find mis-spelled variable problems like this. However ,this is NOT the cause of your trouble...

I could easily be wrong, and am mostly guessing, but I believe that .Value is the default property of combo box. Prior to .Net, (and I use mostly Access 2003) I've had trouble being that explicit about what I want to concatenate. I might try dropping the .value off of strClient = "= '" & Me.cboClient.Value & "'", like this: strClient = "= '" & Me.cboClient & "'"

The best thing about VBA and VB (over prehistoric BASIC for DOS) is the ability to execute line by line and examine variable values during execution. Put in a breakpoint, step through the code with the F8 key, and evaluate the variables with the immediate window. I think your problem will quickly become obvious.

 

[ajetrumpet]

I could easily be wrong, and am mostly guessing, but I believe that .Value is the default property of combo box. I might try dropping the .value off of strClient = "= '" & Me.cboClient.Value & "'", like this: strClient = "= '" & Me.cboClient & "'"

There have been a few questions about this, and while I haven't used "Me.Control" AND "Me.Control.Value" in every situation possible, everytime I have needed to reference a control's value, "Me.Control" AND "Me.Control.Value" have been interchangable.

 

[lisa1965]

Thank you, that worked.