 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / December 2007Users and Asset Data linked on Form
Hello everyone, I have two tables, one a Staff list and the other an Asset list extracted from seperate databases, the information in the tables has absolutely no common data, I have a form with the user name and other bits and pieces on from the staff list and also a drop down list of all asset numbers which I can then select the appropiate asset number from then, that asset number and other details I would like to be associated to that user. I have tried, I have done this in the past about 4 years ago but I cannot remember any of it! Any help would most appreciated Cheers David David, The first thing to clarify is whether there will only ever be one Asset associated with any given Staff, or whether a Staff can have more than one Asset. Or, less likely I suppose, whether an Asset can be associated with more than one Staff.  SignatureSteve Schapel, Microsoft Access MVP > Hello everyone, > I have two tables, one a Staff list and the other an Asset list [quoted text clipped - 12 lines] > Cheers > David Thanks Steve, A user certainly could have several assets for instance a PC, printer, scanner, etc. It is also possible for a PC to be shared. But for the moment just to show how you would do it and hopefully I will be able to adapt it from there lets assume one asset is allocated to one person, how would that be achieved/ Cheers David David, Well, that's the point, you see. How it would be achieved in one case is quite different from how it would be achieved in another, so staring with one and then adapting to the other doesn't really come into it. If we assume one asset is allocated to one person, you would need to put an AssetNumber field in the Staff table, where each Staff has their associated asset entered, which would be done via a combobox on the Staff form. Whereas if we are talking one-to-many relationships, you would need a separate table for the asset allocation data, which includes both the StaffID and AssetNumber fields, so the data can be related to both the Staff and Asset tables. And the data would be entered via a subform, which is based on the Allocation table, and placed onto the Staff form. So, tryu it like this... 1. Make an Allocation table, with these fields: AssetNumber (same data type as corresponding field in Assets table) StaffID (same data type as id field in Staff table) 2. If you want to keep historical tracking of asset allocation, this would also be the place to add other relevant fields e.g. AllocationDate. 3. Make a Continuous View from based on this Allocations table. 4. Make the control for StaffID hidden (set Visible property to No) 5. Make the control for AssetNumber as a combobox, whose Row Source is the Assets table. Adjust the properties of this combobox so you can see the columns you want to see in the drop-down list. 6. Place this form as a subform on the Staff form. Set the Link Child Fields and Link Master Fields properties as StaffID. 7. Open the Staff form at the record for one of the Staff members, and try entering in the subform the Asset(s) associated with that person SignatureSteve Schapel, Microsoft Access MVP > Thanks Steve, > A user certainly could have several assets for instance a PC, printer, [quoted text clipped - 5 lines] > Cheers > David Thanks Steve, That's extremely helpful Cheers David |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.