Query to Submit Multiple Records at Once

[bryan0]

Hello all,



I'm fairly experienced with Access, but I need some guidance...
I am using Access 2010 with Windows 7 to create my most complex database yet, along with a form that can be used to read and submit to the database. In a number of instances, I will need to submit multiple records at once to the same table.

For example, I have a series of combo boxes where the user can specify a "commodity code" and a "Scope" for each commodity code:


I would want to submit the records to my table, and have it use the same CATEGORY_ID_FK for each (which would be known from another field in the form:


So, if I entered in the form:

Commodity Code	Scope
A715	Norway
B637	Norway
C841	Ecuador

Then assuming my CATEGORY_ID_FK was known to be "7818" from another field in the form, I would want my "CATEGORY_GSCKS" table to be populated like this:

CATEGORY_GSCKS_ID (primary key)	CATEGORY_ID_FK	COMMODITY_CODE	COMMODITY_SCOPE
1	7818	A715	Norway
2	7818	B637	Norway
3	7818	C841	Ecuador

I would also want to be able to recall this info back to the form when required, by selecting "7818" as the Category. Is all of this possible? Am I going down the completely wrong path?

Any help would be tremendously appreciated.

[June7]

Why not bound form/subform arrangement? Main form bound to the table that has the 7818 category ID field as a primary key. Subform bound to the related table. Then user enters 3 records by selecting the commodity and scope for each. The subform container control Master/Child links will automatically populate the Category_ID_FK field.

[bryan0]

Ahhh, that is exactly what I was looking for. Thanks!

Now my only issue is that when i add text and numbers to the fields in the subform (using the datasheet view), it automatically saves the records to my table, even before I click the "submit" button. Is it possible to not immediately write the records, and have it write/delete/edit everything when I click my submit button?

[June7]

Records are committed to table through bound form when form is closed, move to another record, run code to save, or when the subform loses focus. I expect the last event is what is happening in your case if the Submit button is on the main form.

What you want is not something I've ever bothered with.

Might be able to have code in the subform BeforeUpdate event to question the user if they really want to save the record. BeforeUpdate has a Cancel parameter. If user says no then invoke the Cancel. In which case there is no need for the Submit button because the BeforeUpdate would trigger. Code will keep user on the subform and undo the entries.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Save edit?", vbYesNo) = vbNo Then
Cancel = True
Me.Undo
End If
End Sub