How do I set up subform fields to offer data based on a previous .

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with a nested subform. On the subform, I have a field [Part]
set up to look at a query and return results based on the previous filed
[item type]. When the subform is opened alone, it works. Once opened in the
main form, I receive a parameter error message. Basically meaning the query
criteria (in the Part field) of [Forms]![Items]![Item Type] is not being
located. I also tried adding the main form [Forms]![Main]![Items]![Item
Type] - which removed the error - but returns zero choices. Can anyone help?

Thanks!!!
 
You won't be able to use [Forms]![Items]![Item Type] if it's open as a
subform as it will be looking for a *form* called Items and won't find one.
The syntax to refer to a control on a subform would be something like
forms!Main!SubFormControlName.form![Item Type] but I don't think that that
will work if you enter it directly into a query (which is what it sounds
like you're doing).

You have two choices...

Either transfer the value you want from the subform back to the main form
(add an unbound textbox to the main form and in the current event on the
subform have parent.NewTextBoxName=me.partid ... that will allow you a
straight form reference to the control.

Build the SQL of the query directly without including any explicit form
references ...
dim sql as string
sql="select * from whatever where field='"+me.subformcontrolname.form![Item
Type]+"'"
...and use that.
 
Rob,

Thank you so much!!! I was actually able to use the first line you mentioned
directly in the query! You've saved me a ton of headaches! Thanks again!

Shannon

Rob Oldfield said:
You won't be able to use [Forms]![Items]![Item Type] if it's open as a
subform as it will be looking for a *form* called Items and won't find one.
The syntax to refer to a control on a subform would be something like
forms!Main!SubFormControlName.form![Item Type] but I don't think that that
will work if you enter it directly into a query (which is what it sounds
like you're doing).

You have two choices...

Either transfer the value you want from the subform back to the main form
(add an unbound textbox to the main form and in the current event on the
subform have parent.NewTextBoxName=me.partid ... that will allow you a
straight form reference to the control.

Build the SQL of the query directly without including any explicit form
references ...
dim sql as string
sql="select * from whatever where field='"+me.subformcontrolname.form![Item
Type]+"'"
...and use that.


Shay0119 said:
I have a form with a nested subform. On the subform, I have a field [Part]
set up to look at a query and return results based on the previous filed
[item type]. When the subform is opened alone, it works. Once opened in the
main form, I receive a parameter error message. Basically meaning the query
criteria (in the Part field) of [Forms]![Items]![Item Type] is not being
located. I also tried adding the main form [Forms]![Main]![Items]![Item
Type] - which removed the error - but returns zero choices. Can anyone help?

Thanks!!!
Click to expand...
Click to expand...
 
Back
Top