Need help designing/programming form 1

[nwalk81]

My database basically tracks computers given out to individuals.

I originally had a many to many relationship where there was a hardware table, recipinet table, and a hardware & recipient table, which relates the two prior tables. I run into trouble trying to add hardware to recipients and vice a versa on my forms (front-end). To better explain: I have individual forms for hardware and recipients with buttons to view the recipients and hardware where appropiate; however, when I attempt to add either (recipients or hardware) the changes are not reflected in the hardware & recipient table, but they are added to the hardware and recipient tables individually.
I'm trying to keep the database normalized, but I just can't solve this problem.

Could anyone help?

Nicole

 

[carmo]

The hardware & recipient table should contain the primary key of the hardware table and the primary key from the recipient table. Are you writting the data to the Associative table (hardware & recipient) ? In other words are you writting the primary key values of both tables to the Associative table (hardware & recipient)?

 

[willir]

nwalk81

You have already identified the M:M relationship.
One user can have many PC's
But how can one PC be checked out to many users???

Simplictically speaking, this is a 1:M and the device table would track the owner.

tblDevice
DeviceID
DeviceType
DeviceName
...
UserID

Here, you reflect the current owner on the actual device table. Note the one current owner could be "available". I am going to assume you already have a User table.


Alternative
You can have a M:M for PC's and such in a shared environment -- shift employees use the same PC, several maintenance employees use the same PC to track work orders, here the design would more akin to

tblOwnerProfile
UserID
DeviceID

Here, you update the profile for the device and user everytime this changes.


Lastly, you should probably have a way of tracking more than just who has the a PC. May I suggest a transaction table...

tblTrackOut
TrackID
UserID
DeviceID
DateOut
CheckOut
DateIn
CheckIn
etc


Now getting back to updating the table. Provided you are using a form that edits the correct table, you should not have a problem. Any information from other tables can be included as a subform on the main table. For example, in my case, where truely do have a M:M for devices and users, I use the tblOwnerProfile table -- this is where I make the changes (using combo boxes for both ID fields), and then include one subform for users and another for the device.

Richard

 

[nwalk81]

The M:M relationship exists becuase computers come into the organization, go out to students, the same computer my come back to the organization, and go back out to a different student.

In my hardware & recipient table there are four columns:
Hardware ID (manually inputed from hardware table)
Recipient ID (manually inputed from recipient table)
(both primary keys)
Date received
Date returned

This is to track all computers, each time they go it.

I can not get new inputs to reflect in Hardware & Recipient table (which would be corresponding #'s from hardware and recipient tables respectively)
Changes only reflected in Hardware table and Recipient table, but not linking table (hardware & recipient)

I hope this explanition is better.
Thanks for your contribution.

 

[nwalk81]

Can anyone tell me how I would write the primary keys from my form to my associated table. I would greatly appreciate a swift response.
Nicole

 

[willir]

Nicole

Do you mean how to create the form and table for tracking receipts?

My..
tblTrackOut
TrackID
UserID = RecipientID
DeviceID = HardwareID
DateOut
CheckOut - PC pre-checked status - scratched, working, known problems
DateIn
CheckIn - PC checked upon return

Seems to be pretty close to your HardwareReceipt table. Since it is possible to check out the same device multiple times, you can not use the HardwareID + ReceiptID. I therefore used an autonumber, TrackID as the primary key.

Note: I added the fields CheckIn and CheckOut to track any known issues with the PC. This way, you can indicate that a PC was not in top-notch shape when checked out, or was damaged by the user.

Using your terminology...

tblReceipt
ReceiptID - Primary key
RecipientID - Foreign key to Recipient table
HardwareID - Foreign key to Hardware table
DateReceived
DateReturned

(Comment: Date Recieved and Returned may be too similar and cause confusion.)


Use this table to create your form. Hint: Use the form wizard for this task. Add two subforms to the main form... One for recipient information, one for the hardware information. The two subforms use the foreign keys, RecipientID and HardwareID to link to the appropraite table.


I noticed that you use the term...
Hardware ID (manually inputed from hardware table)
Recipient ID (manually inputed from recipient table)

Key phrase being "manually inputed".

For both fields, if you change the text boxes generated from the form wizard to a combo box that points to the approriate table, you will be able to grab the info directly from the appropriate parent or master table. This will gratly simplify data entry and avoid data entry errors.

Hope this helps.

Richard

 

[nwalk81]

Rich,

I tried your method and it works; however, it is not user friendly. I am building this database for use by individuals who are not versed in Access. I wanted to find an easier way to get the HW ID and the Recip ID into the Receipt table without the person having to match the hardware id number to the recipient id number.

EX. I have a Recipient form which comes from my recipinet table, on this form is a button(View Hardware) which shows the hardware that is assigned to this person. I want to find a way to input data in a new entry of the view hardware form, and have the new HW ID link with the current Recip ID and both numbers go into a new entry of the Receipt table.

Can anyone help?

Nicole

 

[willir]

Nicole

Sorry if the my suggestion did not seem user friendly.

I use this principle with two combo boxes a fair amount with great success - select recipient from the recipient table, select hardware from the hardware table, which creates the linkage and record in the receipt table, and then fill in additional details.

There is one gotcha that I see in your explanantion...
without the person having to match the hardware id number to the recipient id number.

Combo and list boxes are pretty powerful. Although you need to "bind" the ID number, you can actually have the combo box display the asset tag or serial number for the hardware, and the user name instead of using the ID number for the recipient.

For example, the combo box could display a persons social security number but "behind" the scene use the ID number. This is a very friendly feature.

Here is a walk through on the setup...

I will use the recipient in the aforementioned form you tried but found not user friendly as an example...

In design mode, look at the properties under the Data tab for the combo box used for selecting the recipient. Highlight the Row Source field, and click on "..." icon to the right of the field to open up the query builder.

If necessary, add the recipient table to the query builder. (Not the receipt table) You should see this by default, but if required, right click in the design area and select "Show Table".

Setup the query as follows using the Recipient table
Column 1: RecipientID
Column 2: Recipeint Last Name, sort ascending
Column 3: Recipient First Name, sort ascending

Close the query builder and save when prompted.

Bound Column should be 1 (RecipientID)

(Note: The Control Source will be RecipientID from the receipt table.)

Switch to the Format tab.

Column Count: 3
Column Width: 0";1";1"
List Width: 2"

Save the form and try again -- you will now see the recipient's last name in the combo box. The connected subform should display the full user information you have setup. When you select the "down arrow" for the combo box, you will the first and last names of the recipients sorted by last name. After selecting another recipient, the subform should change to reflect the new recipient.

Alterntively, if you type a last name in the combo box, it will select the first match from the recipient table.

There are three parts to this example...
a) have the combo box select both the bound field and a user friendly field or fields;
b) Column Width of 0" will "hide" the column;
c) the bound field, in this case RecipientID from the Recipeint table, is used for the Control Source, in this case the RecipientID in the Receipt table, creating your link.

Repeat this setup for the combo box used for the hardware selection.

Richard

 

[nwalk81]

Rich,

Thank you very much for your time and patience. I have the database working efficiently.

Nicole