Adding records to a table using VBA. (1 Viewer)

Hi All,

I'm gearing up to start a new project, but I have one concern.

I need to be able to automatically add records to a table.

So.

I have Table 1 - Called Tbl_subba.

In this I have 4 fields. Id which is an auto ID, Blanket Agreement which is a text field, Detailer which is a text field and subba which will end up as a calculated text field.

So, when I add a new blanket agreement on a different table, I need it to update this table and do the following

1) For each Blanket agreement Add 11 Records.
2) Each record will have the same Blanket agreement, but the detailer would be either Forecast, Amendment 1-5, Continuation 1-5.
3 The subba would look up the blanket agreement and do the following depending on the detailer

Forecast - Subba would be GBSXXXX (same as blanket agreement)
Amendment - would be GBSXXXXA1-5
Continuation - Would be GBSXXXXC1-5

I can do the Subba formula, but not sure how I would do the code for the VBA to add the 10 records.

Would I have to do a for x = 1 to 11 code and then do If X= 1 etc.

But how would I actually add the record in VBA? How would I fill in the details of each record??

perhaps something like

Thanks I'll give it a try and see what works

I might approach it this way. First, the English, then some code to look at. The things in red should be looked up on the web so you understand what each part does.

Open a RecordSet to your table, probably best as a DynaSet. You would fill in your new records by filling in the fields individually. Inside your loop you would have a .AddNew and a .Update, with field manipulation in between. Outside the loop, recordset opening and closure would use .OpenRecordset (before the loop) and .Close (after the loop).

Thanks Doc Man!!

Works perfectly

Glad to help.