Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append X number new records via VBA (2003 SP1)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append X number new records via VBA (2003 SP1)

    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)

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append X number new records via VBA (2003 SP1)

    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?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append X number new records via VBA (2003 SP1)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •