Set Subform to Update a Single Field (1 Viewer)

Lukasz337

New member
Local time
Today, 04:54
Joined
Jul 24, 2015
Messages
9
Hi Everyone,


So I'm making a database for an office and I'm having a little trouble getting the information linked together. This is my first access project.


Here's the information:


Desktops_TBL
Desktop ID (PK)
Location (FK)
Serial Number
Product Number
Operating System
etc
User_TBL
User ID (PK)
Location (FK)
Name
Phone Number
Email
etc
Monitors_TBL
ID (PK)
Location (FK)
Serial Number
etc
Location_TBL
Location (PK) (not an autonumber, but something along the lines of a room number in the office)


My Relationships, all one-to-many
Monitor Location FK --> Location PK
Desktop Location FK --> Location PK
User Location FK --> Location PK


How I want it to work:
-New users or equipment is entered via a new entry form and stored in the respective table with a dummy Location (i.e. "Backstock"). This part I understand how to do
-To assign a user and equipment to a location, I want there to be a master form for that location. In that master form, there are subforms displaying the current pieces of equipment attributed (if there are any) to that location. A combo box displays the possible choices, and choosing a choice attributes the equipment to that location.




I have the form with subforms set up. The number of records corresponds to the number of locations in the office. Going through the records displays the correct information of attributed equipment in the subforms.


Where I am stuck:
What I want is for the user of the database to be able to click on the Desktop serial number combo box, choose a different record, and have it assign the Location from the main form to the Desktop Location FK so that they link. When I try to do this, it writes a new blank record in Desttop_TBL, assigning the Desktop ID to the PC Serial Number spot, but that's it.
 

Ranman256

Well-known member
Local time
Today, 07:54
Joined
Apr 9, 2015
Messages
4,337
Having made several Inventory databases, I would put everything into 1 table

tInv table
----------
[EquipID] auto
[EquipType] (PC, Laptop, Monitor, Fax,etc)
[Manuf]
[Model]
[SN]
[AssetTag]
[OwnerID]
[PurchDate]
...etc

Add the extra fields for special types...ie
PCs will have OS, Mhz, etc
Laptops will have Resolution.
...etc, but who cares? Its all in 1 convienient table.
THEN you can produce reports showing 1 empoyees ownership,
or lists by asset.
1 table , no headaches!!!
 

Lukasz337

New member
Local time
Today, 04:54
Joined
Jul 24, 2015
Messages
9
I was thinking about using one table, but would that still be as functional? Do you mean one table for all the data or just one table for all the hardware?


-For one thing I would want to account for something like multiple monitors. A typical location has 1 user, 1 desktop, and 2 monitors. Setting it up with multiple tables and has allowed me to display all the information related to that location in one form using subforms, and the tables themselves are more normalized.
-Secondly, would one table allow for information to be reassigned easily? For example, lets say John is moving to a corner office, and he's taking his computer and monitors with him. They way I have/would like to have it set up is that I can go to the Corner Office record, and assign John, his Computer, and 2 Monitors to the office, letting all the additional information (personal information, PC serial number, asset tag, etc) simply auto update.
 

Users who are viewing this thread

Top Bottom