Solved

Access Database: question on Conditional Statements for field population.

Posted on 2010-11-16
10
499 Views
Last Modified: 2012-05-10
Hello - I am creating this access database for one of my school's inventory/ equipment checkout.
Each piece of equipment has a unique asset tag on it.
What I am trying to accomplish is for when I assign a user a piece of equipment in the "Laptop Assigned" field in the users table, I want the "Assigned To" information to populate in the equipment table for the piece of equipment that has that asset tag.
Now there are multiple types of equipment that can be assigned to a user (PDA, Laptop, Camera etc) and the equipment table has a "Type" definition for each item.
I had been using a List Box but the problem with that ended up being that it listed out anyone who had any piece of equipment assigned to them, not just the user with that specific asset tag assigned to them.
I have attached an image that lists the 2 tables and the fields in them.
Basically this is what I want to have happen (if possible)...
If in Users I put an number (asset tag) in the "Laptop Assigned" Field, I want the "Assigned To" field in the Equipment Inventory Table to populate w/ that Users "Full Name" as long as Users "Laptop Assigned" = Equipment Inventory"Asset Tag" & Equipment Invenoty "Type" = Laptop
And then the same thing with PDA Assigned, then the type would be PDA etc.

Hope I made this clear enough!

Thanks in advance for any help!
tables.PNG
0
Comment
Question by:dnetsol
10 Comments
 
LVL 5

Expert Comment

by:tygrus2
ID: 34151292
Depend on which table you want to be the master for holding the link between equipment and user. Having the user listed in equipment and the equipment in the users table is redundant. Do you need to keep track have the history of use ? If so, then the "assigned" should be a separate table with the two ID's and the start and end dates.

You can use a query to join the tables for a report or a combo box for the set/lookup.

A relational database should enable you to query related and calculated information without having it explicitly saved in several places.
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34152472
As I look into your Users Table, there are fields which need to be remove from it and move to another table. Laptop Assigned, PDA Assigned, etc. are duplications if those items are found already at Equipment Inventory.

If you can post a sample db, it would be useful for us to assist you better.

Sincerely,
Ed
0
 

Author Comment

by:dnetsol
ID: 34157897
tygrus2  - A history is not needed, I just need it to display who the current assigned user is.
Ed - On the Equipment Table there is a field for Type.  This is where the separate fields for Laptop Assigned PDA assigned etc come into play.
The goal is, if I put a Tag number in Laptop Assigned on My User form, which is the only place where the equipment assignment will be edited, I want the item in the Equipment table that matches Type "Laptop" and matches the Asset Tag to the number that was put in the field.
I have a lot of equipment types that the staff could sign out, I don't want to make a table for each of those as then  updating equipment lists would be a pain.

Thanks :)
0
 

Author Comment

by:dnetsol
ID: 34165471
Per request here is a "fake" version of it with just the fields associated with my issue.
Just to re iterate - What I am looking for is if from the User Form if I put an asset tag in the Laptop Assigned field, I want the users Full name to populate in the Equipment Inventory Table under the Assigned To Field for that specific laptop
And if I put a tag in in the PDA Assigned field in the users form, I want the same thing to happen on the Equipement Inventory Table for that PDA.
TestDB.accdb
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:dnetsol
ID: 34200545
any thoughts on this?
0
 
LVL 19

Accepted Solution

by:
MINDSUPERB earned 500 total points
ID: 34203296
This what I got. See attached file.

Based on what you want, this is how it works:

You enter the tag on either PDA or Laptop. Then after update of that box, the full name of the user will automatically update the Assigned To into the Equipment table.

Sincerely,
Ed


TestDB.accdb
0
 

Author Comment

by:dnetsol
ID: 34223874
Thanks Ed this is awesome. I have one more question for you though.  If the assignment changes.  IE say they had Laptop 1000 and now they have laptop 1002 can you tell me what the code to put in the query is to clear out the old field?  Or perhaps a query that checks upon form closing if that is easier.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36116648
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

This article describes some very basic things about SQL Server filegroups.
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now