Adding X number of Records Based on Unbound Value with Sequence Numbers

[SteveHale]

Hello everyone.

I've looked into the following:
1. Append Queries
2. Update Queries
3. DMax Method

I have a Table called "tbl_BusTickets". Every month, we get a fresh supply of Bus Tickets. Each Bus Ticket has its own Serial Number (BusTickNum).
Currently, we are utilizing Access' "Import Excel" feature to get the fresh Bus Ticket Numbers in tbl_BusTickets.

Is it possible to do the following:
1. On Prompt, user gets asked the beginning BusTickNum (ex. 003) - unbound text box
2. Next, user is asked how many tickets to add to tbl_BusTickets (ex. 5) - unbound text box


3. On Button Click, tbl_BusTickets would have created 5 new records (ex. BusTickNum 003, 004, 005, 006, 007)

New records for each Ticket, because tbl_BusTickets is used in the Main Table as a row source for available Bus Tickets.

I've googled what I can google out there but really can't find the right term. Any link or resource you have is greatly appreciated.

Thanks in advance everyone.

-Steve

[John_G]

You'll have to use a small VBA procedure, I think. An append query can append multiple records, but it needs to have somewhere to get the data from, otherwise it can only add one record at a time.

An update query is used to modify records that are already there; it doesn't create new ones.

The VBA code is quite simple, and would look something like this:

Dim rst as recordset
Dim J as Integer   ' Use as a loop counter

'
' Assume the two form fields are called FirstTicket (first ticket number) and  TicketCount (number to add)
'
'
' Open the table as a recordset
'
set rst = currentdb.OpenRecordset("tbl_BusTickets")

For j = FirstTicket to FirstTicket + TicketCount
  rst.Addnew
    Rst!BusTickNum = j
  rst.Update

next J

rst.close

That is the simplest form, of course - it does no validity checks, and only adds data to one field of each record. You would put the code in the On Click event of a button on your form.

[ranman256]

the code will increment tickets as it goes....

btnGo_click()
Dim n as integer, iStart as integer

IStart= BusTickNum+1
for n= iStart to iStart+txtNumTix

  SQL= "Insert into table (field,field) values (" & n & "," & txtBox)
  docmd.runCode sQL
next

end sub

[SteveHale]

Oooh! Sounds and looks promising!

I will give it a go and let you guys know how it turns out.

Thanks John_G and ranman256!!!

-Steve