Solved

Access Database: question on Conditional Statements for field population.

Posted on 2010-11-16
10
502 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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