help with an update query....

[Brook]

Good Day all...

I am trying to run an update query from a Subform on a mainform.

I am wanting to run the qrypdate on the close of an invoice report.

below is my code for my update query, but when I try to run it just from a
command button the system asks me for each value: "Enter Parameter Value"?

UPDATE tblinventory SET tblinventory.invoicenumber =
Forms!frminvoicedetails_Subform!invoicenumber, tblinventory.rugstatus =
Forms!frminvoicedetails_Subform!invoicetype, tblinventory.clientlocation =
Forms!frminvoicedetails_Subform!clientname
WHERE
(((tblinventory.OrderNumber)=Forms!frminvoicedetails_Subform!ordernumber));

Does anyone have any suggestions?

Thanks,

Brook

 

[Ofer]

Are the fields on a subform?
What are the names of the form and SubForm (the name of the subform in the
main form, and not as individual form)?

 

[George Nicholson]

What is frminvoicedetails_Subform? The name of the subform?

You can't reference a subform by it's name. You can only reference it via
the control containing it. (The default property of that control is Form,
i.e., the subform).

Forms!frminvoicedetails_Subform!invoicenumber
should probably be more like:
Forms!NameOfParentForm.NameOfControlContainingSubform.invoicenumber

Depending on how you added the subform to the form, the name of the control
and the name of the subform may be the same. I always change the control
name for the sake of clairty, but that may just be me.

HTH,

 

[John Vinson]

Good Day all...

I am trying to run an update query from a Subform on a mainform.

I am wanting to run the qrypdate on the close of an invoice report.

below is my code for my update query, but when I try to run it just from a
command button the system asks me for each value: "Enter Parameter Value"?

UPDATE tblinventory SET tblinventory.invoicenumber =
Forms!frminvoicedetails_Subform!invoicenumber, tblinventory.rugstatus =
Forms!frminvoicedetails_Subform!invoicetype, tblinventory.clientlocation =
Forms!frminvoicedetails_Subform!clientname
WHERE
(((tblinventory.OrderNumber)=Forms!frminvoicedetails_Subform!ordernumber));

The syntax for referencing a subform field is a bit tricky. The name
of the Subform is irrelevant and is not used; instead, you must
navigate via the mainform and its Subform control. The Name property
of the subform control may or may not be the same as the name of the
form within that control.

Try changing

Forms!frminvoicedetails_Subform!clientname

to

Forms!mainformname!frminvoicedetails_Subform.Form!clientname

assuming that the subform control in fact is named
frminvoicedetails_Subform.

One real concern: it looks like you're storing data (client name for
instance) redundantly. I would be uncomfortable storing a client
*name* in an Inventory table. Are your tables properly normalized?

John W. Vinson[MVP]

 

[Brook]

Hello Ofer,

Yes, all are fields on the subform, actually they are fields on the
subform "passed" to the subform from the Mainform.

The main form : frminvoices and the subform is : frminvoicedetails subform.

Thanks,

Brook

 

[Brook]

Hello George,

The name of the subform is frminvoicedetails subform.

I am unsure what you mean by referencing the control?

Brook

 

[Brook]

John,

I changed my code to the following and am still getting "enter parameter
value".

UPDATE tblinventory SET tblinventory.invoicenumber =
[Forms]![frminvoices]![frminvoicedetails_subform]![forms]![invoicenumber],
tblinventory.rugstatus =
Forms!frminvoices!frminvoicedetails_subform!forms!invoicetype,
tblinventory.clientlocation =
Forms!frminvoices!frminvoicedetails_subform!forms!clientname
WHERE
(((tblinventory.OrderNumber)=[Forms]![frminvoices]![frminvoicedetails_subform]![forms]![serialnumber]));

And the reason that I am storing the client name in my inventory database,
is that I have an unusual inventory. All of my inventory items are unique, no
one item has the same serial number, size, etc.

Brook

 

[John Vinson]

I changed my code to the following and am still getting "enter parameter
value".

UPDATE tblinventory SET tblinventory.invoicenumber =
[Forms]![frminvoices]![frminvoicedetails_subform]![forms]![invoicenumber],
tblinventory.rugstatus =
Forms!frminvoices!frminvoicedetails_subform!forms!invoicetype,
tblinventory.clientlocation =
Forms!frminvoices!frminvoicedetails_subform!forms!clientname
WHERE
(((tblinventory.OrderNumber)=[Forms]![frminvoices]![frminvoicedetails_subform]![forms]![serialnumber]));

It should be Form![invoicenumber], rather than
[forms]![invoicenumber]. Just remove the s in each subform reference.

John W. Vinson[MVP]

 

[Brook]

I don't know what i'm doing wrong, but I made the changes and am still
getting the parameter value prompt?

Is there something else that I can post to help?

Brook

I changed my code to the following and am still getting "enter parameter
value".

UPDATE tblinventory SET tblinventory.invoicenumber =
[Forms]![frminvoices]![frminvoicedetails_subform]![forms]![invoicenumber],
tblinventory.rugstatus =
Forms!frminvoices!frminvoicedetails_subform!forms!invoicetype,
tblinventory.clientlocation =
Forms!frminvoices!frminvoicedetails_subform!forms!clientname
WHERE
(((tblinventory.OrderNumber)=[Forms]![frminvoices]![frminvoicedetails_subform]![forms]![serialnumber]));

It should be Form![invoicenumber], rather than
[forms]![invoicenumber]. Just remove the s in each subform reference.

John W. Vinson[MVP]

 

[John Vinson]

I don't know what i'm doing wrong, but I made the changes and am still
getting the parameter value prompt?

That suggests either that frmInvoices is not open at the time the
query is being executed; or that you have misspelled a form or control
name. How is the query being executed - as the recorsource for a Form
or Report? Is frmInvoices in fact open (i.e. are you opening a report
from a command button on frmInvoices, or the like)?

Doublecheck that you're using the correct Name property of the Form,
of the Subform control on that form (select the box surrounding the
subform, not the subform itself, and view its Properties), and the
correct Name property of the textbox on the subform.


John W. Vinson[MVP]