Problem Using DoCmd.RunSQL

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chip - 09 Apr 2006 05:06 GMT

Hi!,
I am fairly new to ACCESS and have had help to get me this far with the
problem and thought I had figured it out but I am having to ask for some
assistance to get this "DoCmd.RunSQL Insert into" script to run using data
from a subform.

Below is the command I am using from a button on a form to add a line to my
inventory table. The data (product# and quantity) for the added line will
come from data appearing on a sub-form. It runs correctly but it is not
pulling the data from the sub-form as when the command is activated from the
button 2 boxes popup each with the script showing below for each textbox
asking for the data that is on the form. If I input the data by keybord the
Insert command works perfectly!!! But I just cannot get it to pull the data
from the form. Any assistance would be greatly appreciated with getting this
figured out...

Mainform is named {pohdrecv}
subform on that mainform is named {mmpolnsub}
textboxes on {mmpolnsub} that the data will come from are named {prodno} and
{received}

Also the script below is a single line before copying it here to this message.

Here is the VB command I am using:

DoCmd.RunSQL "INSERT INTO tbl_inventory ([product#], [quantity])VALUES
([forms]![pohdrecv]![mmpolnsub].[form]![prodno],[forms]![pohdrecv]![mmpolnsub].[form]![received])"

And as Always THANKS IN ADVANCE!!!!
Chip

Reply to this Message

Allen Browne - 09 Apr 2006 07:47 GMT

The parameter boxes suggest that the Expression Service is not able to find
the objects you referred to.

Perhaps the subform control has a Name that is different from the name of
the form loaded into the control (its Source Object.) To test that, open the
Immediate Window (Ctrl+G) while the form is open, and enter:
? [forms]![pohdrecv]![mmpolnsub].[form]![prodno]

If the name seems correct, try concatenating the values into the string:
Dim strSql As String
strSql = "INSERT INTO tbl_inventory ([product#], quantity) VALUES " & _
Me.mmpolnsub.Form!prodno & ", " & Me.mmpolnsub.Form!received & ");"
'Debug.Print strSql
dbEngine(0)(0).Execute strSql, dbFailOnError

That should get you going, but a more fundamental question is when this
fires. Do you trust the use to remember to click the button? Is there any
chance they might click the button more than once? How can you tell later if
the values in tbl_inventory accurately reflect the true quantities? Is there
a better way to design this, so Access can calculate the correct quantities
or you, and the results can never be wrong?

If these questions are relevant, you might be interested in reading:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi!,
> I am fairly new to ACCESS and have had help to get me this far with the
[quoted text clipped - 33 lines]
> And as Always THANKS IN ADVANCE!!!!
> Chip

Reply to this Message

Chip - 09 Apr 2006 15:26 GMT

THANKS!!!
you were correct! I had the forms mis-named in the script! I did not look
that direction as it did not give me an error like it usally does if it
cannot find a textbox.

THANKS A MILLION!!!
Chip

> The parameter boxes suggest that the Expression Service is not able to find
> the objects you referred to.
[quoted text clipped - 60 lines]
> > And as Always THANKS IN ADVANCE!!!!
> > Chip

Reply to this Message