Connected/Cascading List Boxes

[Gwen H]

I have two list boxes on a form, reportFreq and officerName. Based on the
value selected in reportFreq, I want to populate officerName from one of two
queries. If "monthly" is selected in reportFreq, I want to pull all the
values in the "Loan_Officer" field from query1. If "quarterly" is selected,
then I want to pull all the values in the "Loan_Officer" field from query2.
(Both queries have a field named the same thing.

Here is my VBA code, which isn't working. When I select one value in
reportFreq, officerName is still blank. Any help you can provide would be
appreciated.

Thanks in advance,
GwenH

Private Sub print_officerName_Click()
On Error GoTo Err_print_officerName_Click
'Print the Loan Activity reports for each officer. Declare variable to
hold banker's name.
Dim strOfficer As String
strOfficer =
"[Assistant_Retail_Manager_Goals_Incentive.Assistant_Retail_Manager] = " &
"forms!printPDF!ARM"
'Decide which report to print based on the value of the officerName
field on the form.
If Len(Me!printPDF!reportFreq) = "Monthly" Then
With officerName
.RowSource = _
"SELECT Loan_Officer FROM Current_Month_Loan_Activites_Query"
.Requery
.SetFocus
Else
With officerName
.RowSource = _
"SELECT Loan_Officer FROM Current_Quarter_Loan_Activities_Query"
.Requery
.SetFocus
End If
Exit_print_officerName_Click:
Exit Sub

Err_print_officerName_Click:
MsgBox Err.Description
Resume Exit_print_officerName_Click

End Sub

 

[Douglas J Steele]

Other than the line of code that assigns a value to strOfficer for no
apparent reason, your code looks as though it should work.

Take a look at the properties for your list box. Is RowSourceType set to
Query/Table? Is ColumnCount set to 1? Is BoundColumn set to 1? Is
ColumnWidths something other than 0?

If you run those two bits of SQL as queries, do they return the lists you
expect?

 

[Rob Oldfield]

I find it strange that you're not getting any errors from this, but two
things come immediately to mind:

Me!printPDF!reportFreq won't work. It will make Access look for a control
called printPDF on the current form.
The Len function is going to return a number. That's never going to equal
"Monthly".

Try this instead:
If Me.reportFreq = "Monthly" Then (search Google for an explanation of the
difference between ! and .)

(btw... you don't the requeries if you get the rest of it working.)

 

[SteveS]

I have two list boxes on a form, reportFreq and officerName. Based on the
value selected in reportFreq, I want to populate officerName from one of two
queries. If "monthly" is selected in reportFreq, I want to pull all the
values in the "Loan_Officer" field from query1. If "quarterly" is selected,
then I want to pull all the values in the "Loan_Officer" field from query2.
(Both queries have a field named the same thing.

Here is my VBA code, which isn't working. When I select one value in
reportFreq, officerName is still blank. Any help you can provide would be
appreciated.

Thanks in advance,
GwenH

Private Sub print_officerName_Click()
On Error GoTo Err_print_officerName_Click
'Print the Loan Activity reports for each officer. Declare variable to
hold banker's name.
Dim strOfficer As String
strOfficer =
"[Assistant_Retail_Manager_Goals_Incentive.Assistant_Retail_Manager] = " &
"forms!printPDF!ARM"
'Decide which report to print based on the value of the officerName
field on the form.
If Len(Me!printPDF!reportFreq) = "Monthly" Then
With officerName
.RowSource = _
"SELECT Loan_Officer FROM Current_Month_Loan_Activites_Query"
.Requery
.SetFocus
Else
With officerName
.RowSource = _
"SELECT Loan_Officer FROM Current_Quarter_Loan_Activities_Query"
.Requery
.SetFocus
End If
Exit_print_officerName_Click:
Exit Sub

Err_print_officerName_Click:
MsgBox Err.Description
Resume Exit_print_officerName_Click

End Sub

Also, after each .SetFocus, there should be "End With" (without the quotes).

I would write it like this though:

'--snip--
With officerName
If Me!printPDF!reportFreq = "Monthly" Then
.RowSource = "SELECT Loan_Officer FROM
Current_Month_Loan_Activites_Query"
Else
.RowSource = "SELECT Loan_Officer FROM
Current_Quarter_Loan_Activities_Query"
End If
.Requery
.SetFocus
End With
'--snip--