referencing a subform in sql 1

[AndrewWolford]

Hello everyone,

I can't seem to figure out why my query isn't working, I could use some help...

All I'm trying to do is reference a text box on a subform in a sql query, but it doesn't seem to be working. Here is my query:
SELECT *
FROM table1
WHERE table1.id = forms!formMain![table1 subform]!id;

"table1 subform" is a subform on formMain that has a couple text boxes, one being "id."

If I create a test textbox and run the query as:
SELECT *
FROM table1
WHERE table1.id = forms!formMain![txtTest];

it works. For some reason it doesn't like the subform part, but if I put the same code behind a comand button (tempvariable = forms!formMain![table1 subform]!id) it captures the value.

For the life of me, I can't figure out why SQL doesn' like that code... Does anyone have any suggestions?? Thanks!

Andrew

 

[WildHare]

The correct syntax for referring to a control on a subform is in the form

{Main Form Name}!{Subform-control-name}.FORM.{subform-control-name}, so you probably need to change

WHERE table1.id = forms!formMain![table1 subform]!id;
to
WHERE table1.id = forms!formMain![table1 subform].FORM.id;

Making sure that [table1 subform] refers to the CONTROL that contains the subform, not necessarily the sub-form's actual name.



If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[AndrewWolford]

Yes!!!! It worked!!! I've been trying to figure this stupid thing out all day! Thank you thank you thank you.

I figured it had to be something like that that I was missing. Thanks for your help!

Andrew

 

[silver2kgt]

I had the same problem. I think I was having problems because my main form has a space in the name, but not sure. I gave up trying to do it the way I kept reading about. So here is how I got around it for those that might read this later...

PC Tracking is my main form
frmNewPC is a subform of the main which has two combo boxes
cmbManufacturer and cmbModel (whose data varies depending on what the user selects in cmbManufacturer)

In the On Change event for cmbManufacturer, I have the following code...

Private Sub cmbManufacturer_Change()
On Error GoTo Err_Manufacturer_Change

strvalue = Me.cmbManufacturer.Value

' Note: The following line doesn't actually wrap in my code
strSQL = "select strmodel from tblHardwareDefaults where strType = ""Monitor"" And strManufacturer = """ & strvalue & """"

Me.cmbModel.RowSource = strSQL

Me.cmbModel.Requery
GoTo Exit_Manufacturer_Change

Err_Manufacturer_Change:
MsgBox (err.Description)
GoTo Exit_Manufacturer_Change

Exit_Manufacturer_Change:
End Sub

This way you don't have to worry about all the Forms!Form stuff!