View All Records - Select using a combo box

[weircolin]

Hi

I am using two combo boxes Category and Topic. Topic uses the choice
made in Category to decided what options it will display.

I have created a form which allows me to select a Category and Topic
and then I click on a button that runs a report and shows me all the
people with the selected information.

The problem I am having is that if I Select a Category and I want to
know ALL the people that come under that heading and not each
individual topic. How would I do that?

ie.

The first category is "Benefits", and say there is 5 topics which are:

DLA
Carers Allowance
Attendance Allowance
How to Access
Direct Payments

Now I want to know how many people fall under the category "benefits"
without having to go through each individual Topic. Is there a way of
doing this?

I tried adding * to each of the Categories but it didn't work!

Cheers

Colin

 

[guido via AccessMonster.com]

I would set the button 'On Click' code similar to:
-----
Dim strWhere as String

strWhere = "[Category] = " & Category.Value

If Not(IsNull(Topic.Value)) Then
strWhere = strWhere & " And [Topic] = " & Topic.Value
End If

DoCmd.OpenReport "My Report", acViewPreview, , strWhere

 

[weircolin]

Hi

Thanks for your reply. Getting a problem still. When I select only a
category I get a message saying "Extra ) in query expression
'([Category] = 1And[Topic] = )'

When I select a topic too, it asks me to enter perameter value.

This is my code

Private Sub Command69_Click()
On Error GoTo Err_Command69_Click

Dim strWhere As String

strWhere = "[Category] = " & Category.Value

If Not (IsNull(Topic.Value)) Then
strWhere = strWhere & "And[Topic] = " & Topic.Value
End If

DoCmd.OpenReport "calculate", acViewPreview, , strWhere

Exit_Command69_Click:
Exit Sub

Err_Command69_Click:
MsgBox Err.Description
Resume Exit_Command69_Click

End Sub

Any suggestions?

Thanks

 

[guido via AccessMonster.com]

The error with "Extra )..." it seems that it is running the if statement when
no topic is selected. That means the default value is not Null. I would try
the empty string:
If Not (Topic.Value="") Then

If it is propting you for a parameter when selecting both, is it asking for
"Topic"? If so, the query (or table) attached to the report doesn't recognize
that field name. For the statement:
strWhere = strWhere & "And[Topic] = " & Topic.Value
Make sure the field in the square brackets (i.e. Topic) is the same as the
query/table and it is present. If it still prompts, check for other calls for
that field in the query or report. To test for that, comment out the entire
if statement from the code. If you are still getting the prompt, it is not a
result of the code, but something else.

 

[weircolin]

Hi

Thanks for getting back to me with this. Where would I be looking to
put the If Not (Topic.Value="") Then?

Thanks

 

[guido via AccessMonster.com]

I noted in your code where the change goes. Also, I add a message box for
debug purposes only. You would delete the line when the code is running. The
message box should display when a topic is entered and should not come up
when it is left blank.

Private Sub Command69_Click()
On Error GoTo Err_Command69_Click

Dim strWhere As String

strWhere = "[Category] = " & Category.Value
strWhere = strWhere & "And[Topic] = " & Topic.Value
MsgBox(strWhere) 'debug
End If

DoCmd.OpenReport "calculate", acViewPreview, , strWhere

Exit_Command69_Click:
Exit Sub

Err_Command69_Click:
MsgBox Err.Description
Resume Exit_Command69_Click

End Sub

 

[weircolin]

Hi

Thanks for this! Working great! Much appreciated!

I noted in your code where the change goes. Also, I add a message box for
debug purposes only. You would delete the line when the code is running. The
message box should display when a topic is entered and should not come up
when it is left blank.

Private Sub Command69_Click()
On Error GoTo Err_Command69_Click

Dim strWhere As String

strWhere = "[Category] = " & Category.Value
strWhere = strWhere & "And[Topic] = " & Topic.Value
MsgBox(strWhere) 'debug
End If

DoCmd.OpenReport "calculate", acViewPreview, , strWhere

Exit_Command69_Click:
Exit Sub

Err_Command69_Click:
MsgBox Err.Description
Resume Exit_Command69_Click

End Sub