Filtering a report made directly from a table?

Your user shouldn't know about your ID field nor should they be filtering on it. So what you ideally want to do is filter based on FirstName and LastName. Correct?
 
Your user shouldn't know about your ID field nor should they be filtering on it. So what you ideally want to do is filter based on FirstName and LastName. Correct?

Yes, that's right. Also, the ClientID the user usually knows. This also helps differentiate between those that have the same name.

I really appreciate you helping, its been a long long few weeks.
 
With that in mind, how do you want the filtering to happen? Choose CleintID if there's a value in it otherwise filter by FirstName and LastName?

Your journey has only just begun!
 
I would say ClientID for filtering (there are no blanks). There are 2000+ records its looking at so the more detail the better.

I thought I'd got away from Access. SQL I kind of get and usually just analyse numbers. Designing is a horrid experience!
 
So just ClientID then and nothing else?

Designing is great - drag and drop, change colours etc. But it's the development that can be daunting for some.
 
Yes, clientID, but if the filter could show the name, that'd be the best :)
 
Not sure what you mean by that?

First let's get the filter working. Paste the code you use to open the report.
 
When I create a form which the user selects who/what they want to run the report for, I include other details such as ClientID, FirstName, SecondName. I drop into VBA to open this form when the button is clicked. I add this to the criteria in a query so the report filters on the selected record. Code is:

Private Sub Command83_Click()
On Error GoTo Err_Command83_Click
Dim stDocName As String
stDocName = "Training Review:FCs"

DoCmd.SetWarnings False
DoCmd.RunSQL ("INSERT INTO SysTbl_RptAudit (RptName, DateRun, FileName) SELECT '" & stDocName & "' AS Expr1, Now() AS dte, '" & CurrentDb.Name & "' AS FLE;")
DoCmd.SetWarnings True

DoCmd.OpenReport stDocName, acPreview
Exit_Command83_Click:
Exit Sub
 
You can add these bits:
Code:
Dim strFilter As String

If Len(Me.[COLOR="Blue"]ClientID [/COLOR]& vbNullString) <> 0 Then
    strFilter = "[[COLOR="blue"]ClientID[/COLOR]] = " & Me.[COLOR="blue"]ClientID[/COLOR]
End If

DoCmd.OpenReport stDocName, acPreview,,strFilter
... only amend the bits in blue to suit.
 
Where abouts in the code should I paste it? I suck at VBA!
 
Compare the lines of code I wrote and what you currently have. It's fairly straightforward to see which parts need to be put where.
 
Woo! That has worked :) Got it wrong a few times but all good. Thank you very much for your patience VBAinet!! :)
 

Users who are viewing this thread

Back
Top Bottom