Full Version: Subforms Adding Records
UtterAccess Forums > Microsoft® Access > Access Forms
StevenAFC
Hi all,
I am doing this for a friend, and personally don't have a great deal of expierance using Access!
Basically I have set up 3 tables :
tblEquipment
equipmentID
item (being its name)
room (where its located)
tblExperiment
experimentID
name
date
tblRelationship
relationshipID
equipmentID
experimentID
I have set up relationships between the id's from the 2 tables into tblRelationship.
Basically this database is to list the experiments done, and equipment used.
So now I have created a form which cycles through the experiments, and a subform which shows the equipment used in each experiment. This works great, but I want to be able to add equipment to experiments, this would mean creating a new field in the relationships table.
I have added a combo box to the subform, which lets me select a record, however when I do so, instead of selecting an existing record it creates a new one.
Even more bizarely, it takes the original equipmentID and sets that as the name of the new one, despite showing the name in the combo box.
jzwp11
Your subform should be based on tblRelationship. You have it based on a query that uses both the tblRelationship and tblEquipment. Additionally, the equipment combo box in the subform should be bound to the equipmentID field. Your form has a combo box tied back to the item field so that when you select an item from the box it assigns the ID value to the item field. I have corrected the issue in the attached database. Check out the frmExperiment2 form. Also, you used the word date as a field name in your experiment table. The word date is a reserved word in Access; I changed it to expdate
GroverParkGeorge
Hello.

You've included the room number as an identifying attribute of equipment by making the room an attribute field in equipment. Is each piece of equipment ALWAYS located in the same room? It looks to me as if most of these items are portable and would be moved from room to room as needed.

Also, both "name" and "date" are reserved words in Access. It is not good practice to use them for field names, so i recommend you change them to "ExperimentName" and "ExperimentDate" respectively.

I'll post up some further suggestions in a while.

=====
Looks like I was a bit slower than jzwp11. Here's my version. Should be similar.


George
Edited by: GroverParkGeorge on Sat Sep 6 22:57:18 EDT 2008.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.