Microsoft Access can't find the field '|1'

Question

I keep getting a run time error '2465' when running a query via VBA in Access.

Error: Microsoft Access can't find the field '|1' referred to in your expression

I can't seem to find where this issue is occuring. Below is the VBA code that I'm currently using to requery a form.

Dim Test As String
Test = "*" & Combo161.Value

Dim strSQL As String
Dim strWhere As String
strWhere = (Chr(34) + Test + (Chr(34)))

'MsgBox (strWhere)

strSQL = "SELECT * FROM Test_Query WHERE TestID " & strWhere

'MsgBox (strSQL)
[Form_Test (subform)].RecordSource = strSQL
[Form_Test (subform)].Requery

The TestID had a field formatting of text, rather than a number. Does this matter at all?

Answer 1

Try:

Dim Test As String
Test = "*" & Combo161.Value

Dim strSQL As String
Dim strWhere As String
strWhere = (Chr(34) & Test & (Chr(34)))

'MsgBox (strWhere)

strSQL = "SELECT * FROM Test_Query WHERE TestID Like " & strWhere

'To test
'Debug.print strSQL

If this is a subform, then:

Me.[Form_Test (subform)].Form.RecordSource = strSQL
''Not needed when changing record source
''Me.[Form_Test (subform)].Form.Requery

You did not have an equals sign / Like and the concatenator in VBA is &, not +, using + can lead to problems with nulls, but in this case, I reckon the problen is the missing Like, that is

TestID Like "*something"

You can control the contents of a subform with a combo and a link field: