asked on

Reference the value in a control on a subform for an INSERT INTO query? (Working around inability to INSERT INTO referencing a COLUMN.)

Imagine you have a simple INSERT INTO statement as follows:

strSQL2 = "INSERT INTO tblInvoiceLine (invlinedescription, invlinepriceeach) SELECT linedescription, priceeach FROM tblOrderLine WHERE ordernumber = " & Me.orderid.Value

Open in new window

And now instead of, say linedescription or whatever you want to insert a stockcode which appears as column in my subform.

I understand I can not use a column with an INSERT INTO statement and so I have created a text box (txtstockcode) on my form which references the column. It's control source is:

=[ordereditem].[column](1)

How do I reference the value in the control txtstockcode in my INSERT INTO query?

My form names are frmOrder and frmSalesOrderItems so I guess my statement will refer to something like:

SELECT '" & frmOrder.frmSalesOrderItems.txtStockCode.Value & "',

But that is where I am stuck!

Gustav Brock

It will be:

SELECT '" & frmOrder!NameOfYourSubformControlHoldingFormfrmSalesOrderItems.Form!txtStockCode.Value & "',

/gustav

ASKER CERTIFIED SOLUTION

IrogSinta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott McDaniel (EE MVE )

See this article:

http://support.microsoft.com/kb/113352

It refers to Access 97, but the same concepts apply to any version of Access.

Computer_Support_Norwich

ASKER

Quick and beautiful. Thanks to you both.

cactus_data I did try this first:

SELECT '" & frmOrder!frmSalesOrderItems.Form!txtStockCode.Value & "'

But this errored 'Object Required' and then I tried this courtesy of IrogSinta which worked fine:

SELECT '" & Forms!frmOrder.Form!frmSalesOrderItems!txtStockCode & "'

Thanks!

Computer_Support_Norwich

ASKER

Thanks for quick and accurate response.

Computer_Support_Norwich

ASKER

Hmm, now I have a problem with this code, but only because I did not explain myself fully. The answer given above does work okay where there is only a single line in my child table, but not for multiple lines. I will raise it as a new question!