Multiple actions with one form

[BenD]

Hello,

I have created a form that I would like to do several things for an
inventory database I am creating (I have used Access for about 4 days). The
form is based on a query that displays equipment currently checked out by
users (as well as date it was checked out, etc.). It currently has 4 fields:
Employee ID, Equip Number, Date Out and Date Returned

I was wondering if it is possible for me to set up the form so that upon
simply entering the Equip Number and Employee ID (and the relevant date
field), the form will update the query to add a new record if the item is
being checked out, or save the record if it is being returned (basically just
add the Date Returned to the record). I already have the query set up so
that adding a Date Returned will remove the record from the query, so the
Equip Number should have no duplicates in the query. Hopefully that makes
sense.

I'm sure this would be simple using VBA, but I don't know VB

Any help would be greatly appreciated.

-BenD

 

[Jeff Boyce]

Remember that queries in Access are "windows" on the data that actually is
stored in the tables.

You've described a "how" ... as in "how you want to do something".

Could you describe a bit more about the "why"... as in "how having this
would help you accomplish something"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[BenD]

Sure. I'm creating this database for a coworker (/boss?) in the waning days
of my internship (I have 6 days left).

He wanted the database set up so that we could leave it on one screen (the
form), and let anyone wanting to check out or return lab equipment simply
scan the appropriate bar codes (Employee ID and Equip Number) with a scan
gun, maybe press a button or two, and be done.

At the same time, he wants to be able to see what is currently checked out
(the query), as well as a history of who checked out what (I stored this in a
table).

I can accomplish this easily with 2 forms, but my coworker only wants me to
use one form so that people unfamiliar with Access won't be confused. Using
only one form to accomplish this is where I am stuck.

-BenD

 

[John W. Vinson]

Sure. I'm creating this database for a coworker (/boss?) in the waning days
of my internship (I have 6 days left).

He wanted the database set up so that we could leave it on one screen (the
form), and let anyone wanting to check out or return lab equipment simply
scan the appropriate bar codes (Employee ID and Equip Number) with a scan
gun, maybe press a button or two, and be done.

At the same time, he wants to be able to see what is currently checked out
(the query), as well as a history of who checked out what (I stored this in a
table).

I can accomplish this easily with 2 forms, but my coworker only wants me to
use one form so that people unfamiliar with Access won't be confused. Using
only one form to accomplish this is where I am stuck.

What are the two forms? What are their Recordsource properties? And most
importantly - what is the structure and relationships of your tables?

An inventory application will *typically* (maybe not always) have a table of
items related one-to-many to a table of transactions (checkout or checkin) on
those items; a Form based on the items table with a Subform based on the
transactions table would go a ways toward your desired goal. But since we
don't know the underlying struture of your data tables, it's hard to know
whether this advise is relevant.

 

[BenD]

OK, sorry, I'm still pretty new to this. Originally I had my database set up
to include 3 tables: an items table (individual Equip Numbers, item
descriptions, etc.), an employee table (a complete list of employees and
associated Employee IDs) and an inventory history table (included Employee
ID, Equip Number, Date Out and Date In, plus an AutoNumbered ID key). The
items and employee table were related one-to-many to the inventory history
table.

I then had a query to display items that were currently checked out (Date In
criteria = Is Null). This included the first 4 fields from the inventory
history table, plus the employees' names and item descriptions for easier
reference.

Finally, I had 2 forms - 1 for Checkout and 1 for Checkin. The Checkout
form asked for Employee ID, Equip Number and Date Out, then created a new
record in the inventory history table (and also showed up in the query). The
Checkin form let you scan in the Equip Number to find the matching record
(using a Find Record button) in the query, enter the Date In, then save the
record (removing it from the query).

I thought this would be fine, but my coworker wanted to know if I could
consolidate this process to use just 1 screen. I think I can see where you
are going with your suggestion, so I will play around with that, but if you
think of anything else please let me know.

Thank you,

BenD