Automatically filling in records

[Guest]

I issue tickets that uniquely numbered. A batch of 25 will be issued to a
specific cost centre.

If I tell Access the Ticket Numbers, the cost centre and the date issued can
it automatically update each record with the Cost Centre?

Fields:Ticket (Primary Key) (Number), CostCentre (Lookup list) (Text)
DateIssued (Date)

 

[Graham R Seach]

Ab,

I'd create another form into which you can enter multiple ticket numbers,
and add a button whose code executes insert queries to do it automatically.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Guest]

I've got a form listing the ticket numbers that need updating. How do I
create an insert query?
--
Thanks in advance
Ab Harper

Ab,

I'd create another form into which you can enter multiple ticket numbers,
and add a button whose code executes insert queries to do it automatically.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

 

[Graham R Seach]

Ab,

For the sake of example, let's assume the following:
* That you store booked tickets in a table called tblBookedTickets,
containing the following 4 columns:
- BookingID (Autonumber, Primary Key)
- PerformanceID (Long Integer, Foreign Key to the Performance
table)
- TicketNo (Long Integer)
- DateBooked (Date/Time)
* That you have the ticket numbers listed in a ListBox.
* That you have a TextBox called txtPerformanceID that contains the
primary key of the performance whose tickets you want to book.
* That you have a button called "cmdBook".

Place the following code into the button's Click event:

Private Sub cmdBook_Click()
Dim ws As Workspace
Dim db As Database
Dim sSQL As String
Dim iCtr As Integer

'Trap any errors
On Error Goto Proc_Err

'Only book the tickets if there are tickets to book
If Me.lstTickets.ListCount > 0 Then
Set ws = DbEngine(0)
Set db = CurrentDb

'Open a transaction, so either ALL the tickets are
'booked in one go, or NONE of them are booked.
ws.BeginTrans

'Cycle through the listbox, booking every ticket listed in it.
For iCtr = 0 To Me.lstTickets.ListCount - 1
'Assuming the Ticket No is in column 0 (the 1st column) of the
ListBox.
sSQL = "INSERT INTO tblBookedTickets " & _
"(PerformanceID, TicketNo, DateBooked) " & _
"VALUES (" & Me.txtPerformanceID & "," & _
Me.lstTickets.ItemData(iCtr) & ",
Date())"

db.Execute sSQL, dbFailOnError
Next iCtr
End If

'If we got this far, everything went OK,
'so commit the transaction.
ws.CommitTrans

Proc_Exit:
'Clean up
Set db = Nothing
Set ws = Nothing
Exit Sub

Proc_Err:
'An error occurred
ws.Rollback
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Resume Proc_Exit
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I've got a form listing the ticket numbers that need updating. How do I
create an insert query?