Advanced DoCmd.RunSQL

[Jone]

Hi everybody!

I made a form, in that form I have a combo box that’s called “Combo2†and I
have an unbound text box that’s called “Qty†then I have a sub form called
“PrintLables†I want to make a DoCmd.RunSQL to add records in that sub form,
for example I will select an Item form my list in the combo2 then enter an
amount in the qty box and when I press "add items" it will add the item times
the qty, let’s say I entered 15 it will make 15 new records in my sub form
from that item I selected before

 

[Alex Dybenko]

Hi,
you can run a loop in VBA and run DoCmd.RunSQL number of times entered in
Qty

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Steve Sanford]

This is easy to do, but I need to know a few things.

What is the name of the table you want to add the records to?
What is the name in the field that "Combo2" data should be stored in?
What is the data type of the bound field that the data from "Combo2 is
stored in (text or numeric)?

What is the name of the Foreign Key field name in the subform table?
What is the name of the Primary Key field of the main form (parent form)
recordset?

HTH

 

[Steve Sanford]

More.....

One Main form record is linked to Many subform records.

1 Many
PK_FieldName -------------> FK_FieldName


In the following AIR code, you will need to change three names to your names

MyTable, PK_FieldName, and FK_FieldName

Also, I don't use spaces in names, so change the Sub name to your button name.


Remember, this is untested.....

'***code beg************
Public Sub Add_Items_Click()
Dim i As Integer
Dim iQty As Integer
Dim sSQL As String

iQty = Me.Qty

'if MyItem is a number ****
' use this
sSQL = "Insert into MyTable (FK_FieldName, MyItem)"
sSQL = sSQL & " Values (" & Me.PK_FieldName & ", " & Me.Combo2 & ");"


'if MyItem is TEXT ****
' use this
'sSQL = "Insert into MyTable (FK_FieldName, MyItem)"
'sSQL = sSQL & " Values (" & Me.PK_FieldName & ", '" & Me.Combo2 & "');"


For i = 1 To iQty
CurrentDb.Execute sSQL
Next

Me.PrintLables.Requery
End Sub
'***code end***************

HTH