Syntax problem selecting a control in a subform

Question

Syntax problem selecting a control in a subform

katzen 40

Once duped by the lure of being allowed to put spaces in control and table names, many times sorry. I will never do it again. I'm doing OK on the logic, but keep breaking down over syntax since doing this.

I have a form "f HT Creates" which has a subform "f Individual Pieces Entry". The subform contains a list of pieces by the artist named in "f HT Creates". Each piece has a unique "Piece ID".

Another form, "f Search Box for IP" creates a dynamic list of individual pieces for ain the collection, with the objective to discover and display the artist information for the piece. I want my user to be able to click on the control in the list corresponding to the Piece ID (named, simply "id") and pull up "f HT Creates" and have it display the artist information in the main form and in the subform the piece with "Piece ID" equal to the one clicked on in "f Search Box". The value of the selected "Piece ID" on form "f Search Box for IP" is translated earlier in the code to be the variable "lngID".

My original version of "f Search Box" does this for the "Artist ID", which is on the parent form "f HT Creates". I cloned it, made the appropriate changes to field names, and successfully used it for another control on the parent form "f HT Creates".

I cannot get it to run in this version, where the field to compare is on the subform. The lines in question are:

    strCriteria = "Form![f HT Creates]![f Individual Pieces Entry].Form![Piece ID] = " & lngID

    rs.FindFirst strCriteria

    If Not rs.NoMatch Then

    DoCmd.OpenForm "f HT Creates", acNormal, , strCriteria, , acDialog

When I run it, Access gives me a run-time error 3070 and tells me it "does not recognize 'Form!f HT Creates!f Individual Pieces Entry.Form!Piece Number' -- without the brackets. When I open the debugger, and look at the present value of strCriteria at the two points, it is also without the brackets. It appears to be a syntax error on my part when defining the control for strCriteria, but cannot figure out what is wrong with my syntax.

Can anyone help, please?

katzen 40 Reputation points

2026-01-21T02:10:06.1066667+00:00

I didn't figure this out and would appreciate any help doing so; I do like to learn and understand things. But I thought of a control that I could use that's on the parent form and produces almost the same result. Thanks to anyone who's been trying to figure out what I'm doing wrong here.

2 answers

Your answer

katzen 40 Reputation points

2026-01-21T02:10:06.1066667+00:00

I didn't figure this out and would appreciate any help doing so; I do like to learn and understand things. But I thought of a control that I could use that's on the parent form and produces almost the same result. Thanks to anyone who's been trying to figure out what I'm doing wrong here.

Answer 1

Duane Hookom 26,825 Volunteer Moderator

Where/ how are you defining “rs”?

katzen 40 Reputation points

2026-01-21T02:12:25.19+00:00
At the top of the code:

Dim rs As DAO.Recordset

then a few lines later:

Set rs = CurrentDb.OpenRecordset("t HT Creates", dbOpenDynaset)
Duane Hookom 26,825 Reputation points Volunteer Moderator

2026-01-21T17:29:04.91+00:00
Copy

strCriteria = "[field name from rs] = " & lngID rs.FindFirst strCriteria
katzen 40 Reputation points

2026-01-24T03:19:16.0866667+00:00
Sorry it took me so long to get back to you.

At the top of the code:

Dim rs As DAO.Recordset Dim strCriteria As String Dim lngID As Integer

And just before the code shown in the question:

Set rs = CurrentDb.OpenRecordset("t HT Creates", dbOpenDynaset)

As I said, it worked with criteria where the control is on the parent form being opened. I just can't seem to get the syntax correct for the control on the subform.
katzen 40 Reputation points

2026-01-24T03:26:22.33+00:00

Is that my part of my problem? The subform is a different table (t Individual pieces) from that of the parent (t HT Creates), and the record set is defined as the table for the parent form? Would that syntax work if I change the recordset definition to be the subform's table?
Duane Hookom 26,825 Reputation points Volunteer Moderator

2026-01-24T04:21:22.4866667+00:00

You stated earlier “The value of the selected "Piece ID" on form "f Search Box for IP" is translated earlier in the code to be the variable "lngID".”

It seems there is some code you aren’t sharing. Do you know how to set a breakpoint and step through your code to view values?

Answer 2

It looks like you're encountering a syntax issue when trying to reference a control on a subform in Microsoft Access. The error message indicates that Access is not recognizing the control path correctly. Here are a few suggestions to help you resolve this issue:

Correct Syntax for Subform Reference: When referencing controls on a subform, ensure that you use the correct syntax. The correct way to reference a control on a subform is as follows:
```
   strCriteria = "[Piece ID] = " & lngID
```
This assumes that you are already on the parent form and that you have the subform control name correctly set.
Using the Subform Control Name: If you are trying to reference the subform from the main form, make sure you are using the name of the subform control, not the name of the form itself. For example:
```
   strCriteria = "[f Individual Pieces Entry].[Form]![Piece ID] = " & lngID
```
This syntax indicates that you are accessing the form object of the subform control.
Debugging the Criteria: You can debug by printing out the strCriteria before using it in rs.FindFirst to ensure that it is formatted correctly. Use:
```
   Debug.Print strCriteria
```
This will help you see the exact criteria being passed.
Check for Spaces: Since you mentioned using spaces in control names, ensure that you are enclosing control names with spaces in square brackets. For example:
```
   strCriteria = "[f HT Creates]![f Individual Pieces Entry].[Form]![Piece ID] = " & lngID
```
Ensure Controls Exist: Double-check that the controls you are referencing actually exist on the forms and subforms as expected. Sometimes, a typo in the control name can lead to this error.

By following these guidelines, you should be able to resolve the syntax issue and correctly reference the control in your subform.

References:

Share via

Syntax problem selecting a control in a subform

2 answers

Your answer