Combo box & Command button to report

[Solstice]

Sorry, I'm sure this sort of thing has been done to death but I've spent all morning looking up solutions and nothing has worked so far



I can cope with access at a fairly basic level - I can design tables and forms, run queries and reports. For the most part, my database works, but I'm now trying to exceed the limits of my knowledge! I've been asked by work to put a database together to keep client records.

My table contains a list of clients and their details. I have a query from it which is just called "Application Query". I've set up a form with a comman button that runs a report based on this query, and pulls up EVERY client. Which is fine, and it works.

What I want is to be able to filter by client. I know how to do this using the macro builder and [Enter Client] in order to enter the name, but the problem I'm having is that some of the spellings of names are unconventional, and if there is a spelling error, it brings up a blank report. So, what I want is a combo box with a list of clients, so that users can just click the client name and click a command button to bring up the report for that specific person.

On my form, I have the combo box (cboClient) which is linked to the query. It brings up a list of all of the clients. But I can't seem to make my command button (cmdOpen) use the selection in the combo box and I'm either getting a full report or the headings with no data. My report is called repApplication Query.

I don't really understand coding and I've been looking for a basic tutorial, but most of the information I've found has been far more complex than what I need (i.e. using multiple entries in a list box, or more than one combo box).

My code for the command button is currently:

Private Sub cmdOPen_Click()
DoCmd.OpenReport "repApplicationQuery", acViewPreview, , , , Nz(Me!Client, "")
End Sub

Which just opens the full report with everyone's details in it. Any suggestions or a link to a basic tutorial would be appreciated.

[rpeare]

I wouldn't use a filter. If your form is a combo box that has the unique identifier for the client as the bound column just create a 'single client' report and in the criteria if the query running the report in the client id field have

[forms]![formname]![fieldname]

Where formname is the name of your selection form and fieldname is the name of the combo box that has the client ID as the bound column.

[RuralGuy]

PMFJI but the OP does it just the way I do it all of the time. The filter works great in a report. I would use a procedure like:

Private Sub cmdOpen_Click()
   Dim strWhere As String
   strWhere = "[YourClient] = '" & Me.Client & "'"
   DoCmd.OpenReport "repApplicationQuery", acViewPreview, , strWhere
End Sub

...substituting the actual field name for YourClient of course. This WhereCondition argument assumes the field is a string. If it is a number then remove the single quotes:

Private Sub cmdOpen_Click()
   Dim strWhere As String
   strWhere = "[YourClient] = " & Me.Client 
   DoCmd.OpenReport "repApplicationQuery", acViewPreview, , strWhere
End Sub

[Solstice]

It works! Hooray! Thank you so much for that!

PMFJI but the OP does it just the way I do it all of the time. The filter works great in a report. I would use a procedure like:

Private Sub cmdOpen_Click()
   Dim strWhere As String
   strWhere = "[YourClient] = '" & Me.Client & "'"
   DoCmd.OpenReport "repApplicationQuery", acViewPreview, , strWhere
End Sub

...substituting the actual field name for YourClient of course. This WhereCondition argument assumes the field is a string. If it is a number then remove the single quotes:

Private Sub cmdOpen_Click()
   Dim strWhere As String
   strWhere = "[YourClient] = " & Me.Client 
   DoCmd.OpenReport "repApplicationQuery", acViewPreview, , strWhere
End Sub

[RuralGuy]

Excellent! Thanks for posting back with your success.