Runtime Error 13 when filtering combobox

[PinkLady50]

I have two comboboxes on my form. They are both working correctly but I am getting a runtime error on the second box when a selection is made.

The first combobox is Contract Number and should filter alone. The second combobox is Task Order and it's results are dependent on the selection made in the Contract Number.



The Contract Number is filtering correctly but the Task Order combobox is getting a runtime error 13 Type mismatch.

Any help would be greatly appreciated!

Private Sub cboContractNum_AfterUpdate()
     Me.Filter = "[ContractNum] = " & Chr(34) & Me.cboContractNum & Chr(34)
     Me.FilterOn = True
     Me.Requery
          
End Sub
Private Sub cboTO_AfterUpdate()
    Me.Filter = "[ContractNum] = " & Chr(34) & Me.cboContractNum & Chr(34) And "[TO] = " & Chr(34) & Me.cboTO & Chr(34)
     Me.FilterOn = True
     Me.Requery
End Sub

[Homegrownandy]

can you show the SQL for the record source on the combo box with issue?

[aytee111]

Is Me.cboTO a text field?

[PinkLady50]

SELECT DISTINCT tblPerfIssues.TO, tblPerfIssues.ContractNum FROM tblPerfIssues WHERE (((tblPerfIssues.ContractNum)=[Forms]![frmPerfLookup1]![cboContractNum]));

[aytee111]

Replace the CHR(34) with actual quotes, it would make it easier to read.

Me.Filter = "[ContractNum] = " & Me.cboContractNum & " And [TO] = '" & Me.cboTO "'"

[PinkLady50]

Now I get a Syntax error on this code.


Me.Filter = "[ContractNum] = " & Me.cboContractNum & " And [TO] = '" & Me.cboTO "'"

[aytee111]

The above assumes that the first column in the row source of the contract number is a numeric field, and the first column of the TO row source is a text field. If this is not the case then changes need to be made as per adding/removing quotes.

[aytee111]

Oops, there is an "&" missing from the end of that statement - Me.cboTO & "'"

[PinkLady50]

They are both text fields. What should the code look like? I get hung up with the quotes!

[aytee111]

Me.Filter = "[ContractNum] = '" & Me.cboContractNum & "' And [TO] = '" & Me.cboTO & "'"

[PinkLady50]

Now when I run it when I select a contract number from the combobox it pops up a box with Enter Parameter Value for the contract number I selected.

[PinkLady50]

The debug is stopping on the Me.Requery line in the cboContractNum.

Her is the revised code.

Private Sub cboContractNum_AfterUpdate()
Me.Filter = "[ContractNum] = " & "" & Me.cboContractNum & ""
Me.FilterOn = True
Me.Requery

End Sub
Private Sub cboTO_AfterUpdate()
Me.Filter = "[ContractNum] = '" & Me.cboContractNum & "' And [TO] = '" & Me.cboTO & "'"
Me.FilterOn = True
Me.Requery
End Sub

[aytee111]

That could be from one of the row source queries. We/I have not touched that part of the design.

[aytee111]

Me.Filter = "[ContractNum] = '" & Me.cboContractNum & "'"

[aytee111]

Remember, text fields require quotes, numeric fields must not. When already inside a string, such as yours, the double-quote has been taken so you need to use single quotes. Surround the actual value with quotes for text fields.

When in doubt, add Debug.Print statements
Debug.Print Me.Filter
Me.Filter="ContractNum='1234A'" will show
ContractNum='1234A'