Access Check-in and Check Out Database ???

[starke1120]

Im creating a check in – check out database for RF guns. I have a
table that contains models.

ID (primary key)
Model

A table that contains Gun Details
ID (primary key)
Model_id
Gun_Number (assigned gun number by operations)
SerialNum
Location (this is the holding location while guns are not check out)

A table that contains Employee Info

ID (primary key)
FistName
LastName
BadgeID (this is what is scanned to retrieve employee info)

A table that contains records for check in and check out info

ID (primary key)
Gun_ID
Employee_ID
Checked Out (yes or no check box)
Checked In (yes or no check box)

What I picture happening is that the user will select “check in� or
“check-out� from the main menu. If check in is selected a form
will pop-up with a request to scan the user’s badge, then after
scanning a form will pop-up asking for location (this will check out
the gun in this location.

Sorry for the long post, here is my problem. I need to check if there
is a current record in the tblInOut that contains this particular gun
with a “checkedOut� check mark but no “Checked In�. If there
is a record like this then it means the gun was never checked back in.
So in short �. I need to know how to check for a record and then
msgbox before actually adding the record. Any thoughts?

[Tom van Stiphout]

On 20 Dec 2006 05:58:58 -0800, st********@hotmail.com wrote:

I'm not at all sure you have the right database design. Sounds like
you have YesNo fields for checkout and checkin. Aarchh. One of them is
a calculated value so does not belong in the db. Beyond that, such
field tells you nothing about when it was checked out or in.

To your last question: you check for a record using a select query.
For short tables you can also use DLookup. E.g.
if IsNull(DLookup("ID", "Employee Info", "BadgeID=" & strBadgeID))
then Msgbox "Badge not found. Scan again.", vbCritical

-Tom.

>Im creating a check in – check out database for RF guns. I have a
table that contains models.

ID (primary key)
Model

A table that contains Gun Details
ID (primary key)
Model_id
Gun_Number (assigned gun number by operations)
SerialNum
Location (this is the holding location while guns are not check out)

A table that contains Employee Info

ID (primary key)
FistName
LastName
BadgeID (this is what is scanned to retrieve employee info)

A table that contains records for check in and check out info

ID (primary key)
Gun_ID
Employee_ID
Checked Out (yes or no check box)
Checked In (yes or no check box)

What I picture happening is that the user will select “check in” or
“check-out” from the main menu. If check in is selected a form
will pop-up with a request to scan the user’s badge, then after
scanning a form will pop-up asking for location (this will check out
the gun in this location.

Sorry for the long post, here is my problem. I need to check if there
is a current record in the tblInOut that contains this particular gun
with a “checkedOut” check mark but no “Checked In”. If there
is a record like this then it means the gun was never checked back in.
So in short ?. I need to know how to check for a record and then
msgbox before actually adding the record. Any thoughts?

[starke1120]

Tom,

Thanks for your reply. I left out my dates field.. so let me know if
this is still a poor design. I am very new to VBA and Access so if
there is a better way of doing this I will redesign it..

ID (primary key)
Gun_ID
Employee_ID
Checked Out (yes or no check box)
Checked In (yes or no check box)
CheckedOutDate (grabs current date on new record)
CheckedOutTime (grabs curretn time on new record)
CheckInDate (once checked, VBA updates this field)
CheckedInTime (once checked, VBA updates this field)

So when the gun is checked out, the date and time of the record shows
the date and time the gun was checked out. When its checked back in
VBA adds the date and time of the time the check box was marked as Yes
(true)

If you feel this is a poor way to do this let me know. I just can't
figure out how to prevent the gun from being checked out twice. Say if
it was brought back and not checked in I want to know and not just add
a new record as checkout.

Thanks,

Dave

Tom van Stiphout wrote:

On 20 Dec 2006 05:58:58 -0800, st********@hotmail.com wrote:

I'm not at all sure you have the right database design. Sounds like
you have YesNo fields for checkout and checkin. Aarchh. One of them is
a calculated value so does not belong in the db. Beyond that, such
field tells you nothing about when it was checked out or in.

To your last question: you check for a record using a select query.
For short tables you can also use DLookup. E.g.
if IsNull(DLookup("ID", "Employee Info", "BadgeID=" & strBadgeID))
then Msgbox "Badge not found. Scan again.", vbCritical

-Tom.

Im creating a check in - check out database for RF guns. I have a
table that contains models.

ID (primary key)
Model

A table that contains Gun Details
ID (primary key)
Model_id
Gun_Number (assigned gun number by operations)
SerialNum
Location (this is the holding location while guns are not check out)

A table that contains Employee Info

ID (primary key)
FistName
LastName
BadgeID (this is what is scanned to retrieve employee info)

A table that contains records for check in and check out info

ID (primary key)
Gun_ID
Employee_ID
Checked Out (yes or no check box)
Checked In (yes or no check box)

What I picture happening is that the user will select "check in" or
"check-out" from the main menu. If check in is selected a form
will pop-up with a request to scan the user's badge, then after
scanning a form will pop-up asking for location (this will check out
the gun in this location.

Sorry for the long post, here is my problem. I need to check if there
is a current record in the tblInOut that contains this particular gun
with a "checkedOut" check mark but no "Checked In". If there
is a record like this then it means the gun was never checked back in.
So in short ?. I need to know how to check for a record and then
msgbox before actually adding the record. Any thoughts?

[Bob Quintal]

st********@hotmail.com wrote in
news:11**********************@73g2000cwn.googlegro ups.com:

Tom,

Thanks for your reply. I left out my dates field.. so let me
know if this is still a poor design. I am very new to VBA and
Access so if there is a better way of doing this I will
redesign it..

ID (primary key)
Gun_ID
Employee_ID
Checked Out (yes or no check box)
Checked In (yes or no check box)
CheckedOutDate (grabs current date on new record)
CheckedOutTime (grabs curretn time on new record)
CheckInDate (once checked, VBA updates this field)
CheckedInTime (once checked, VBA updates this field)

So when the gun is checked out, the date and time of the
record shows the date and time the gun was checked out. When
its checked back in VBA adds the date and time of the time the
check box was marked as Yes (true)

If you feel this is a poor way to do this let me know. I just
can't figure out how to prevent the gun from being checked out
twice. Say if it was brought back and not checked in I want
to know and not just add a new record as checkout.

Thanks,

Dave

You don't need the checked out and checked in fields. Checked
out is determined by the presence of a record in the table.
Checked in is determined by the presence of the checked in
date/time. The two time fields are unnecessary, the date and
time can and should be stored in a single field per event for
checked out and another field for checked in. That makes 5
fields instead of nine, for exactly the same functionality.

As to the issue of determining if a gun has not been checked in,
you need to look for the presence of the gun_ID where CheckinDT
(DT = Date/Time) in my naming convention.

Add the code to the before update event of your checkout form.

IsNotCheckedIn = NOT IsNull(Dlookup("Gun_ID","GunStatus", _
"IsNull(CheckinDT) AND GUN_ID = " & me.Gun_ID))
If IsNotCheckedIn Then
MsgBox "Gun has not been checked in. Please Check it in
before checking it out", vbokonly
me.undo
cancel = true
end if

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[starke1120]

Thanks Bob, this makes sense. Seems redundent to have a check box when
the DT field basically does the same thing :-)

Dave
Bob Quintal wrote:

st********@hotmail.com wrote in
news:11**********************@73g2000cwn.googlegro ups.com:

Tom,

Thanks for your reply. I left out my dates field.. so let me
know if this is still a poor design. I am very new to VBA and
Access so if there is a better way of doing this I will
redesign it..

ID (primary key)
Gun_ID
Employee_ID
Checked Out (yes or no check box)
Checked In (yes or no check box)
CheckedOutDate (grabs current date on new record)
CheckedOutTime (grabs curretn time on new record)
CheckInDate (once checked, VBA updates this field)
CheckedInTime (once checked, VBA updates this field)

So when the gun is checked out, the date and time of the
record shows the date and time the gun was checked out. When
its checked back in VBA adds the date and time of the time the
check box was marked as Yes (true)

If you feel this is a poor way to do this let me know. I just
can't figure out how to prevent the gun from being checked out
twice. Say if it was brought back and not checked in I want
to know and not just add a new record as checkout.

Thanks,

Dave

You don't need the checked out and checked in fields. Checked
out is determined by the presence of a record in the table.
Checked in is determined by the presence of the checked in
date/time. The two time fields are unnecessary, the date and
time can and should be stored in a single field per event for
checked out and another field for checked in. That makes 5
fields instead of nine, for exactly the same functionality.

As to the issue of determining if a gun has not been checked in,
you need to look for the presence of the gun_ID where CheckinDT
(DT = Date/Time) in my naming convention.

Add the code to the before update event of your checkout form.

IsNotCheckedIn = NOT IsNull(Dlookup("Gun_ID","GunStatus", _
"IsNull(CheckinDT) AND GUN_ID = " & me.Gun_ID))
If IsNotCheckedIn Then
MsgBox "Gun has not been checked in. Please Check it in
before checking it out", vbokonly
me.undo
cancel = true
end if

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[pietlinden]

FWIW, when I wrote something like this, the "loans" table was like
this:

ItemID (Foreign Key)
CheckoutDate - general date
CheckInDate - General date
BorrowerID (FK to Person table)
LoanID (PK)