Problem referencing txtbox in query

[LB79]

Hello,

I'm having a problem referencing a txtbox from a query.
I linked the query to the txtbox using the “build” feature and selecting the path to the txtbox which is:

[Forms]![MainForm]![subfrm].[Form]![txtbox]

However when I run the query it says “The Microsoft jet database engine does not recognise [Forms]![MainForm]![subfrm].[Form]![txtbox]
As a valid field name or expression.”

Thanks

 

[Atomic Shrimp]

Whereabouts in the query are you inserting the reference? (criteria row, or fieldname/expression row)

How are you running the query? i.e. from a saved, named query, or from VBA RunSQL command?

I'd be inclined to try:
[Forms]![MainForm]![subfrm]![txtbox]

 

[Endre]

Don't forget to set up the query parameters when defining the query - placing the text box reference in the criteria row for the data column is not enough. When building the query, from the menu options choose Query > Parameters. Enter exactly your text box reference (your syntax to the field in the sub-form is correct) and specify your data type.

 

[LB79]

Thats great! Got it working... Please can you tell me what it is Ive just done though... how has it worked?

Thanks

 

[Endre]

I'm not a total guru in this area, so I will give the default answer and say it is "Jet Database thing....." I normally run queries using VBCode and if I use a select query I will load the results into a dataset. I will often "parameterise" these queries exactly as you have done, but when running the SQL in VBCode you don't need to implicitly declare the parameters as we had to here in the MSAccess Query e.g:

Dim rs_GetData As DataSet
Set rs_GetData = CurrentDb.OpenRecordset("SELECT * FROM tbl_TBL1 WHERE tbl_TBL1.fldField1 = """ & [Forms]![MainForm]![subfrm].[Form]![txtbox] & """;")

This would load into the recordset all records from the table tbl_TBL1 where it's field fld_FIELD1 has the same value as that in the textbox in your subform (provding your subform is open). NOTE: There is no additional parameter declaration, just that the object reference must be valid and available. In this example note the use of the triple quotes as this is a text field. If it was numeric you would only use single quotes. (You can check the SQL statement before it runs by putting a breakpoint in your code, displaying the result in the immediate window, then take that text and pasting it into the SQL screen of a new query and then run the query.)

With the above code you can enumerate through the records you have parameterised e.g.

Do While Not rs_GetData.EOF
     MsgBox (rs_GetData!Field1)
     rs_GetData.MoveNext
Loop

So, in summation - only do this paramterisation if creating saved queries for use in Forms, Query displays etc. and Reports. For queries in VBCode you don't need to.

 

[LB79]

Thanks for the explanation