Problem Using DoCmd.RunSQL

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

Guest

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
 
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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Chip said:
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
Click to expand...
 
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

Allen Browne said:
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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Chip said:
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
Click to expand...
Click to expand...
 
Back
Top