Solved

Access Database: question on Conditional Statements for field population.

Posted on 2010-11-16
10
505 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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
 

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 143

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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Microsoft Access - limit entry to numbers and letters 10 62
Data architecture learning. 17 46
GA Ribbon creator 9 64
Combo box question 6 55
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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.

738 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