Filter code not working as it should

[lovett10]

If Me.cbrCustomer & "" <> "" And IsNull(Me.cbrQuoteNumber) And IsNull(Me.cbrEngineer) And IsNull(Me.DateStart) And IsNull(Me.DateFinish) Then
        strFilter = "[CustomerVisit] = '" & Me!cbrCustomer & "'"
                        DoCmd.Close
                            DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter

Here is my code, there is something wron as it isnt filtering properly.

The table field is called CustomerVisit and the combobox is called cbrCustomer

Thanks in advanced for any help.

Ps Access 2010

 

[vbaInet]

Just posting code without explaining what you're trying to do isn't helpful.

 

[lovett10]

Just posting code without explaining what you're trying to do isn't helpful.

Its code to open a report at filter the results by the value in the combobox

 

[vbaInet]

Do you not have a CustomerID field?

 

[lovett10]

Do you not have a CustomerID field?

not in the report no. i just want the report to filter by the text in the combo box cbrCustomerVisit

 

[vbaInet]

Two or more customers can have the same name so you need to use the CustomerID to filter and include it in your report. Once you've done that you can use this:

If Len(Nz(Me.cbrCustomer, "")) <> 0 And Len(Me.cbrQuoteNumber & "") = 0 And Len(Me.cbrEngineer & "") And Not IsDate(Me.DateStart) And Not IsDate(Me.DateFinish) Then

You may find that the combo box is actually returning the CustomerID if that's the "hidden" bound field.

 

[lovett10]

is there any way of searching for the customer name as apose to the id?

Alterntively make a box so that when the customer name is shown it shows the customer id that goes with it, so i can use that to search??

 

[vbaInet]

Yes, but if you want to be guaranteed that you get the exact customer you searched for, you use the ID. If you want multiple matches to a particular name, then you can use the customer name.

Remember what you can use a Combo Box for?

 

[lovett10]

Yes, but if you want to be guaranteed that you get the exact customer you searched for, you use the ID. If you want multiple matches to a particular name, then you can use the customer name.

Remember what you can use a Combo Box for?

either way would work, whats the simplest way ?

 

[vbaInet]

Use the ID field.

So do you not know that you can hide a column in a Combo Box?

 

[lovett10]

Use the ID field.

So do you not know that you can hide a column in a Combo Box?

the problem i have is i select the name, when it filters however it filters by ID, i want it to filter by name.

 

[vbaInet]

The Name is linked to an ID.

 

[lovett10]

The Name is linked to an ID.

Yes. but i want it to filter by the customer name not the customer ID

 

[vbaInet]

But what is the reasoning behind this?

 

[lovett10]

But what is the reasoning behind this?

Because there isnt customer ID on the report and the report is what im filtering

 

[vbaInet]

You don't seem to want to follow best practices and the right way of doing things. I've told you several times that you need the ID in the report but you're adamant you don't.

 

[lovett10]

You don't seem to want to follow best practices and the right way of doing things. I've told you several times that you need the ID in the report but you're adamant you don't.

Its not that i dont think i need it, i dnt know how to link them together so when the user chooses a name, the Id is automatically selected

 

[vbaInet]

Alright. All you need to do is include the ID field in the Record Source of your report. Your report should be based on a query and it's in that query you include the ID field.

Can you show me screenshots of the fields in your query and report's record source.