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?

Solution 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:

Solution 2

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

Solution 3

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.

Comments

[Muhnamana]

Still getting the same error...it keeps highlighting the .RecordSource line though after hitting the debug.

[Muhnamana]

Could it have anything to do with the " instead of '?

[Fionnuala]

I doubt it, but I have just noticed another problem. What is [Form_Test (subform)]? Do you really have a control called [Form_Test (subform)]? Have you pasted the sql into a query window to test?

[Fionnuala]

As an aside, be very careful with parentheses in VBA. They are not always needed and improperly used, they mean something different than a plain argument or parameter.

[Muhnamana]

Man at this point I'm confused...I'm using the same code, on another form that works perfectly fine...this one just doesn't seem to work. Maybe I'll go back to square one, unless there is easy code to requery a form from within VBA?

[Fionnuala]

Yes, but is it a form or a subform? Have you pasted the sql into the query design window? You do not need to requery if you change the recordsource.

[Muhnamana]

I designed Form1 and Form2. Then I dragged and dropped Form2 into Form1 thus creating a subform. Is that a no no?

[Fionnuala]

let us continue this discussion in chat