Parking ticket database

[Tom via AccessMonster.com]

Hi,
I need to create a database to track parking tickets. The tickets come in
books of 50 and each ticket has a number. The tickets are numbered
sequentially. The database will need to track who the ticket book was issued
to, the date it was issued, the ticket numbers for each ticket, and the
outcome of each ticket (ie voided, issued). The problem that I am having is
that each ticket will need to be a record. When the ticket book of 50 tickets
is issued I dont want to have to enter 50 records manually. I would like to
enter the number range and the name of the officer recieving the book and
have access create a new record for each ticket with that officers name and
that tickets number. When the ticket is issued or voided the record for that
ticket can be updated. The database should be fairly simple, there is not a
lot of information to track, but I cant figure out how to create a range of
records from the form without creating them one at a time.

Thanks for any help,

Tom

 

[Guest]

You don't need to and should not create records for all the tickets when the
book is entered. I am assuming the book has it's own identifying number.
So what you need are two tables. One for the book, and one for the tickets.

tblBook
Book_ID (primary Key)
Issued_To
First_Ticket (ticket number of first ticket in the book)
Last_Ticket (ticket number of the last ticket in the book)

tblTicket
Ticket_Number (primary key)
Book_ID (foreign key to tblBook)
Ticket_Status (issued, void, lost, etc)

Of course, you will want other info in the table, but you need the above to
manage the database.

Now, you will need a form to enter Ticket Info. On the form, I would
suggest you use two controls for the Ticket Number. One would be a hidden
text box that is bound to the Ticket_Number field in the table. The other
would be an unbound Combo box used to search for a ticket. In case you are
not familiar with how to use a combo for a search like this, here is some
untested air code as an example. First, you will need a row source for the
combo to list the existing tickets:
SELECT Ticket_ID FROM tblTicket ORDER BY Ticket_ID;
In the example code, I am going to assume Ticket_ID is a numeric field. If
it is text, the syntax will have to be adjusted.

Private Sub cboTicket_AfterUpdate()
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("tblTicket")
rst.FindFirst "[Ticket_ID] = " & Me.cboTicket
Me.Bookmark = rst.Bookmark
Set rst = Nothing

End Sub

You will also want to use the Not In List Event of the combo so you can add
new records if you want. This requires you set the Limit To List property of
the combo to Yes.

Private Sub cboTicket_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Ticket Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO tblTicket (Ticket_ID) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Ticket_ID] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Response = acDataErrAdded
Else
Me.cboTicket.Undo
Response = acDataErrContinue
End If

End Sub

 

[tomanddani via AccessMonster.com]

Thanks Klatuu,
The ticket "books" do not have an identifying number. In fact they are
actually not even bound. They come wrapped in cellophane and are stored in
each officers metal ticket clipboard. As soon as a "book" of tickets is given
to an officer they are opened and are "individuals". The problem I'm having
is to enter the individual tickets (with the ticket number and officer name),
so there is a record for each one which I can update with more information
later, without having to enter each one manually.

Thanks for your help,
Tom

 

[Guest]

Are you going to have a form for entering ticket information or are you going
to do all the work directly in the table?

 

[Tom via AccessMonster.com]

Yes, I will of course use a form. I know that I should never work directly in
the table. The form would allow me to add new records for each ticket and
also look up records and make changes to the record.

Tom

 

[Guest]

If you want to create all 50 records at once (I wouldn't do it that way), you
will need to write a loop that will add them. On the form where you enter
the information on who the book was issued to, you will needt two text boxes,
one for first ticket number and one for last ticket number. It would go
something like this (untested air code):

Dim rst As Recordset
Dim lngTicketNum As Long

Set rst = CurrentDb.OpenRecordset("tblTickets")

With rst
For lngTicketNum = Me.txtFirstTicketNumber To Me.txtLastTicketNumber
.AddNew
!Ticket_ID = lngTicketNum
.Update
Loop
End With

 

[Tom via AccessMonster.com]

Thanks for your reply,
The reason I felt that it was important to enter all the tickets as records
at the begining was because it is also important to note if a ticket has not
been used or is missing. If a record has no activity for a certain period of
time, or is not updated as to its outcome, then an investagation will need to
be conducted to find the ticket. What way would you sugest to do this?

Thsnks for all the help.
Tom

 

[John Vinson]

If you want to create all 50 records at once (I wouldn't do it that way), you
will need to write a loop that will add them. On the form where you enter
the information on who the book was issued to, you will needt two text boxes,
one for first ticket number and one for last ticket number. It would go
something like this (untested air code):

As an alternative, you could use an auxiliary table Num, with one
field N; I routinely have one with values from 0 through 10000 or so.

An Append query

INSERT INTO Tickets(TicketNo, OfficerID)
SELECT Forms!MyForm!txtStartNo + N, Forms!MyForm!cboOfficer
FROM Num
WHERE N < Forms!MyForm!txtHowMany;

can be executed from a click event of a button on the form.

John W. Vinson[MVP]

 

[Tom via AccessMonster.com]

Hi John,
The problem is that the tickets can start with 6,000,000 to 6,000,100 and
somtimes (because one was pulled from an older box) 3,000,000 to 3,000,100.
I'm not sure if your method would still work if I could not anticipate the
ticket numbers. Would it still work? What do think the best method for what
I'm tring to do would be?

P.S. Thank you for all the help that you have unknowingly given me in the
past. Many times I have searched the archives for help with a problem that I
was struggling with and your comments appeared. Thank you for that.

Tom

 

[John Vinson]

Hi John,
The problem is that the tickets can start with 6,000,000 to 6,000,100 and
somtimes (because one was pulled from an older box) 3,000,000 to 3,000,100.
I'm not sure if your method would still work if I could not anticipate the
ticket numbers. Would it still work? What do think the best method for what
I'm tring to do would be?

If you use a Long INteger field, yes it will - if the textbox
txtStartNum in my example contained 6000000, and txtHowMany contained
101, you'ld get 6,000,000 through 6,000,100 added to the table.

P.S. Thank you for all the help that you have unknowingly given me in the
past. Many times I have searched the archives for help with a problem that I
was struggling with and your comments appeared. Thank you for that.

You don't know *how* gratifying that is, Tom. Thank you.

John W. Vinson[MVP]

 

[Guest]

PMFJI,

It sounds like you should also enter the Officer's name (or better the PK)
and the date of issue to the officer. Then you know the ticket numbers, who
it was issued to and when it was issued.

On the form would be the text boxes for the beginning ticket number, the
ending ticket number, the issue date (default to today's date) and a combo
box for the Officer's name (or primary key).

A report could be made to find all tickets where the "outcome"
(voided,issued,...) was NULL.

HTH