473,884 Members | 2,305 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Access Check-in and Check Out Database ???

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?

Dec 20 '06 #1
5 11664
On 20 Dec 2006 05:58:58 -0800, st********@hotm ail.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 users 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?
Dec 20 '06 #2
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********@hotm ail.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?
Dec 20 '06 #3
st********@hotm ail.com wrote in
news:11******** **************@ 73g2000cwn.goog legroups.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","GunSt atus", _
"IsNull(Checkin DT) 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

Dec 20 '06 #4
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********@hotm ail.com wrote in
news:11******** **************@ 73g2000cwn.goog legroups.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","GunSt atus", _
"IsNull(Checkin DT) 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
Dec 21 '06 #5
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)

Dec 21 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1767
by: Asier | last post by:
I know i can create a Microsoft Access object with: Dim accessApp as Access.Application But my customer's computers doesn't have the same Access version. Some have Access 97, some Access 2000, some Access XP, and others have two versions (97 and 2000). How can I "force" to use one of them? How can I know which versions are installed?
2
2622
by: Gustavo Rahal | last post by:
Hi How can I access a MS-Access DB from a linux machine? I am doing a website that will be hosted on Linux and has to access a Windows machine with the MS-Access DB. I read about the PythonWin extensions and ADO but it seems like it only works on a windows machine... can I install this library on Linux? I am new with python and a complete newbie with MS-Access.
3
2042
by: Angel Mazo | last post by:
Dear all, I currently have a site with Access 97 installed and I am planning to migrate (not all PC's at the same time) from Access 97 to Access 2002. My questions are: .. Can I have Access 97 FrontEnds and Access 2002 FrontEnds using the BackEnd created with Access 97? .. If previous is yes, can I open the BackEnd (for maintenance
2
1785
by: Kelt | last post by:
I have a user who currently has Access 97 installed on her PC. She has purchased a new PC and would like to have Access installed on it. She would like to upgrade her current Access to Access 2003. When installing the Access 2003 upgrade on the new PC, do I have to install Access 97 first, then run the Access 2003 upgrade CD? And if so, do I need any unlocking or any type of codes during the Access 97 installation? Or can I just run...
1
5175
by: Mike | last post by:
Hi Hi We are currently upgrading from access 97 to office 2003 on site. We would like to upgrade our access databases to Access 2003 but we wish to remove Microsoft access software from our end users so that we can control the creation of databases. Is there a way that our end users can still use these upgraded access databases without having access 2003 installed on their machines. The mdes will reside on a server so they can be...
1
2344
by: mangia | last post by:
I have several .NET websites running successfully on Windows 2003 Sever/IIS6 for over a year now. Today I decided to change my anonymous FTP site access using specific 'granted' or ‘denied’ IP addresses to 'non anonymous’ and require username password access. I created a user account and gave it the file permissions, tested and FTP site access works fine (again). Something done brought down the .NET websites! Now when...
1
8065
by: Vincel2k2 | last post by:
Hi, I need to provide Access reports from our web site. When the app gets to the output command I get this error I get this error: The formats that enable you to output data as a Microsoft Excel, rich-text format, MS-DOS text, or HTML file are missing from the Windows Registry. Also after the snapshot is created how do I get it to the users web page? Thanks Here is my code: Private Sub Button1_Click(ByVal sender As System.Object, ByVal...
37
5286
by: jasmith | last post by:
How will Access fair in a year? Two years? .... The new version of Access seems to service non programmers as a wizard interface to quickly create databases via a fancy wizard. Furthermore, why would you even continue to use Access as a backend when you have a much superior option in SQL express? What about as a future front-end development tool? Let's get serious. Microsoft continues to publish numerous articles and videos on how you...
7
2749
by: Craig Alexander Morrison | last post by:
Warning to Access Developers about ACE. DON'T use multivalued fields (MVF) they are worse than LookUp fields and SubDatasheets, in fact they (MVFs) are a logical progression in the dumbing down that started with these abominations. Having seen and evaluated Access 2007 for a few months now I think this is a terrible product for developers. If you must use it use Jet 4 or SQL Server 2000 or IBM DB2* as the backend, depending upon your...
3
1866
by: mrstephengross | last post by:
Hi folks... I've been sifting through archived postings on this, but haven't quite found an answer yet. I've got a templated stand-alone function: template<typename Tvoid access(const T & t) { ; } And a class 'Foo': struct Foo { friend void access(const Foo & f);
0
9799
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10768
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10868
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9591
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing, and deploymentwithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7984
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7137
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6009
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4623
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4231
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.