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

3
votes

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?

3
sqlms-accessvbaruntime-error

3 Answers

3
votes

I have just fixed this error. I was referencing the subform's source object, rather than its name given in the form properties.

2
votes

I had the same error. What I missing was the double quotes around a string. This error is a bit misleading. Check the syntax etc and you will find the issue was related to comma or double quotes etc.

1
votes

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:

combo link