Append X number new records via VBA (2003 SP1)

Topic

New Reply

I’ve done this before with a query ( I think…I’m having a brain fart.) and I want to do it now with VBA code so users can do this themselves.

A user wants to add X number of records to a table. In doing this, the code will fill out 2 fields of information and leaving the rest of the fields blank. Field 1 is the name of a facility and field 2 is a location number at the facility. So the table looks kind of like this:

Field1 Field 2
Fac1 0001
Fac1 0002
Fac1 0003
Fac2 0001
Fac2 0002
Fac2 0003
etc…

I want to give the user a button so they can add X number of records to this table. And they would need a variable for Field1 and a range of values for Field 2 (like locations 2000 to 2100, adding a record for each facility/location combo).

I imagine I could use a For…Next loop or something like that but I thought there is a more efficient way to do it. Ideas?

Reply | Quote

Replies

As far as I can see, you’d need a For … Next loop for Field1. You could use a nested For … Next loop for Field2, but if the values for Field2 are stored in a table, you could also run the SQL for an append query (you’d construct the SQL string in code).

Post back if you need more detailed assistance (we’ll probably need more info then)

Reply | Quote

The values for Field2 would NOT be stored in a table. I would like to generate them some how, like start Value + 1 up to end value.

Would I still use the For…Next loop for Field2?

Reply | Quote

Yes, in that case you would use a nested For … Next loop for Field2 within the For … Next loop for Field1.

Reply | Quote