Save Multiple records onClick

[Tambe257]

I have a retail store product database that I've created for our company. We have 29 stores, and each item potentially has a unique price/cost per store, so I have a StorePrices table that holds all of the price records. Here is the table layout:



I have a form that allows me to enter the pricing info per store one by one, which works fine, but most of the time our products can be entered with the exact same criteria in many stores. I have other tables, as you can see above, that create groups of stores. (ex. Boston, New York, NJ, etc.)

What I would like to do is enter a Price, PriceDate, Taxcode_1, etc. and then click a button that creates records in the StorePrices table for a group of stores. We have 8 stores in NYC, 18 in Boston, and 3 in NJ, so this would save a lot of clicking if I want to enter the same prices for all Boston stores.

What I have so far is this VBA code below that saves what is entered in the fields as a new record. Rather than enter the StoreID, I want to loop through a set of StoreIDs, adding a new record for each, with all of the same info, except a different storeID each time.

Here is the stand alone code:

CurrentDb.Execute "INSERT INTO [StorePrices](StoreID, Price, PriceDate, Taxcode_1, Taxcode_2)" & "VALUES('" & Me.StoreID & "','" & Me.Price & "','" & Me.PriceDate & "','" & Me.Taxcode_1 & "','" & Me.Taxcode_2 & "')"



I'm new to VBA, but from my other programming experience I assume I should create an array of the storeIDs, and then iterate through them all, saving a new record each time. So something like:

Stores = Array("105", "107", "111", "115")

For Each Store in Stores
CurrentDb.Execute "INSERT INTO [StorePrices](StoreID, Price, PriceDate, Taxcode_1, Taxcode_2)" & "VALUES('" & StoreID & "','" & Me.Price & "','" & Me.PriceDate & "','" & Me.Taxcode_1 & "','" & Me.Taxcode_2 & "')"
Next Store

This isn't working, but I have a feeling that I'm not too far off.

[aytee111]

How will the stores be selected - by choosing a city? You don't want to hardcode the stores like you have it above, you want to get them from a table or a form. If they select a city you can join in the city table and compare it to what they selected on the form.

[davegri]

You could have on your form a combobox dropdown that lets you choose a group.
Then create a query (in vba) that pulls the store numbers for that group.
Open that query via DAO an loop thru it doing your existing inserts.
That insert also needs to include the ItemID.
Table StorePrices is better named StoreItem as it is the linking table in a many-to-many relationship.

[Tambe257]

Yes, I only hard coded the array to simplify the example a bit. I would have a combo box that selects the StoreGoupsID ideally.

How will the stores be selected - by choosing a city? You don't want to hardcode the stores like you have it above, you want to get them from a table or a form. If they select a city you can join in the city table and compare it to what they selected on the form.

[Tambe257]

Hi davegri - Yes I would like to choose the StoreGroupID from a combo box ideally. I have no knowledge yet of DAO code, so I'll have to read up on that to see what I can come up with. Thanks.

You could have on your form a combobox dropdown that lets you choose a group.
Then create a query (in vba) that pulls the store numbers for that group.
Open that query via DAO an loop thru it doing your existing inserts.
That insert also needs to include the ItemID.
Table StorePrices is better named StoreItem as it is the linking table in a many-to-many relationship.

[Tambe257]

I'm having a bit of trouble getting started. Currently my StorePricesSubForm links the master field ItemNum from the main form, to ItemID on my subform. I added ItemID to the VBA and tried to run it again, but when I click the button nothing happens and no records are saved. Does my VBA look like it should work? I know that hard coding the array isn't the best solution, but I'm tying to figure out if even that portion works. If I can get it working, then I can figure out how to implement the combo box. The VBA is in the On Click event procedure of a button. Complete code is:

Private Sub Mult_Records_Click()

Stores = Array("105", "107", "111", "115")


For Each Store In Stores
CurrentDb.Execute "INSERT INTO [StorePrices](ItemID, StoreID, Price, PriceDate, Taxcode_1, Taxcode_2)" & "VALUES('" & (ItemID) & "','" & (StoreID) & "','" & Me.Price & "','" & Me.PriceDate & "','" & Me.Taxcode_1 & "','" & Me.Taxcode_2 & "')"
Next Store

End Sub

As for the combo box, as you can see from my image above I have tables grouping the stores. I would make a combo box that shows the GroupName from the StoreGroupCodes table. I'd have to then loop through each store with the corresponding StoreGroupCodesID in the StoreGroups table. I'll have to figure that out next.

You could have on your form a combobox dropdown that lets you choose a group.
Then create a query (in vba) that pulls the store numbers for that group.
Open that query via DAO an loop thru it doing your existing inserts.
That insert also needs to include the ItemID.
Table StorePrices is better named StoreItem as it is the linking table in a many-to-many relationship.

[davegri]

try this. I had to dummy up the values from your form with the first 5 DIMs. You can comment them out. You might need to add back the me. prefix in the SQL.

Sub subTestArray()
    Dim itemID As Long
    itemID = 4
    Dim price As Currency
    price = 0.55
    Dim pricedate As Date
    pricedate = #11/11/2015#
    Dim taxcode_1 As Single
    taxcode_1 = 4.44
    Dim taxcode_2 As Single
    taxcode_2 = 2.56
    
    Dim sSQL As String
    Dim store As Variant
    Dim stores As Variant
    stores = Array("105", "107", "111", "115")
    For Each store In stores
        sSQL = "INSERT INTO [StorePrices](ItemID, StoreID, Price, PriceDate, Taxcode_1, Taxcode_2)  VALUES(" & [itemID] & "," & store & "," & price & "," & pricedate & "," & taxcode_1 & "," & taxcode_2 & ")"
        Debug.Print sSQL
        CurrentDb.Execute sSQL, dbFailOnError
    Next store
End Sub

And the debug.print showed

INSERT INTO [StorePrices](ItemID, StoreID, Price, PriceDate, Taxcode_1, Taxcode_2)  VALUES(4,105,0.55,11/11/2015,4.44,2.56)
INSERT INTO [StorePrices](ItemID, StoreID, Price, PriceDate, Taxcode_1, Taxcode_2)  VALUES(4,107,0.55,11/11/2015,4.44,2.56)
INSERT INTO [StorePrices](ItemID, StoreID, Price, PriceDate, Taxcode_1, Taxcode_2)  VALUES(4,111,0.55,11/11/2015,4.44,2.56)
INSERT INTO [StorePrices](ItemID, StoreID, Price, PriceDate, Taxcode_1, Taxcode_2)  VALUES(4,115,0.55,11/11/2015,4.44,2.56)

[Tambe257]

Thanks davegri. I'm getting a "Compile Error: Method or data member not found" on the Taxcode_2. I think it's only Taxcode_2 because that is the last one in the sequence. To test it I removed the Taxcode_2, and then it gave the error on Taxcode_1. Does that mean that it can't find the input field on the form? Here is my code currently:

Private Sub MultRecords_Click()
Dim sSQL As String
Dim store As Variant
Dim stores As Variant
stores = Array("105", "107", "111", "115")
For Each store In stores
Me.sSQL = "INSERT INTO [StorePrices](ItemID, StoreID, Price, PriceDate, Taxcode_1, Taxcode_2) VALUES(" & [ItemID] & "," & store & "," & Price & "," & PriceDate & "," & Taxcode_1 & "," & Taxcode_2 & ")"
Debug.Print Me.sSQL
CurrentDb.Execute Me.sSQL, dbFailOnError
Next store
End Sub

[davegri]

That's right. Do you have textboxes on the form bound to table fields taxcode_1 and taxcode_2?
You want to use the names of those textboxes in the query. You might need to just change them to me.taxcode_1 & _2.

[Tambe257]

Yes, I have text boxes for Price and PriceDate, and combo boxes for Taxcode_1 & Taxcode_2. All on the same subform that this button is located. I added Me.PriceDate, Me.Price, etc. to all 4, but I still receive that error. I've been Googling the error for an hour, but I haven't found the answer yet.

[davegri]

Ah, comboboxes! Those pesky guys can have columns with different values.
Add these debug.print lines to see if you need to specify which column is needed, then change the query as required.
You can also look at your rowsource for the comboboxes to help figure it out.

Private Sub MultRecords_Click() 
Dim sSQL As String
Dim store As Variant
Dim stores As Variant

debug.print taxcode_1.column(0)
debug.print taxcode_1.column(1)

stores = Array("105", "107", "111", "115")
For Each store In stores
Me.sSQL = "INSERT INTO [StorePrices](ItemID, StoreID, Price, PriceDate, Taxcode_1, Taxcode_2) VALUES(" & [ItemID] & "," & store & "," & Price & "," & PriceDate & "," & Taxcode_1 & "," & Taxcode_2 & ")"
Debug.Print Me.sSQL
CurrentDb.Execute Me.sSQL, dbFailOnError
Next store
End Sub

[Tambe257]

Even if I remove the two Taxcodes from the code above it will still throw an error at the last value in the INSERT INTO line. It seems that none of the values are being recognized. I assume that I don't have to specify the subform name, like StorePricesSubForm.Price, since Me.Price should be sufficient. This is confusing for sure!

[davegri]

If the click event is from a button on the main form, you will indeed need to specify the subform names. If the click event is from a button on the subform, no.
From the mainform:

me!subform!.Form!taxCode_1

I don't know what your subform is named, but subform refers to the control name holding the actual form.

[Tambe257]

Ok, that's how I understood it. It is a click event on a button right in the subform, and as confirmation, when I type out the code "Me.T" it shows auto complete options of Taxcode_1 and Taxcode_2. That being the case, it doesn't seem to be an issue of the click even not being connected to the form properly.

[davegri]

Ahhhh. Drop the ME. from the front of the sSQL. That implies a control, which it is not.