Hello - I have read the Access MVP article about the evils of lookup fields, and have not strayed from the path of righteous database design. However, the problem I am having does make look far off to the left to the evil land of lookup fields and wonder, "Are they really all that bad? They could really solve my problem here." Please somebody, save me from straying down that road.
I am building a database which will track various pieces of equipment and other things. I got good help in here, and believe my tables are pretty well normalized, and now I am at the point of trying to design some simple forms, in order to put a few data items in my tables in order to test the tables/relationships.
Here are the two tables giving me troubles:
tblManufactureModel
pkManufactureModelID - Autonumber
Manufacturer
ModelName
PartNumber
tblEquipment
pkEquipmentID
ManufactureModelID - foreign key in a one to many relationship with tblManufactureModel 's primary key
SerialNumber
(more fields following)
My problem is this - I may have several of the same type of equipment, say, Fluke 77 meter. So I want to enter Fluke 77 Meter particulars in tblManufacturerModel. However, in my form for entering data in tblEquipment I am stumped. When I use a combo box, and set the row source property to tblManufactureModel!PartNumber, I can get the data to show up, and make a selection.
However, I cannot figure out how to get the field tblEquipment!ManufactureModelID to be updated. If I bind the combo box to that field, then of course I get errors, because the combo box is text, and the field wants a number.
I think I want to do something like run a query in the After Update Event for the combo box - but I am not real sure how to do that. I assume it would be something like:
qryPartNumber = SELECT ManufactureModelID FROM tblManufacturerModel WHERE cboPartNumber = tblManufacturerModel!PartNumber
And then I could do something like;
tblEquipment!PartNumber = qryPartNumber
I also am not sure the combo box is really the way to go. I have about 750 different types of items - that could make for a pretty long combo box. Not sure of alternatives, perhaps a text box where the user (me most likely) types in the data, and again have some query run on After Update to see if that part number is already in tblManufacuterModel.
I am posting in this forum, because this problem makes me wonder if perhaps I am looking at this problem completely wrong. A few more things for consideration. Number one, I will be the only person likely entering data such as this into the database. At least initially entering the various things we already have. Number two, the approximately 750 part numbers and some other information is already present in a couple of ridiculous Excel Spreadsheets. Number 3, I have no formal Access or Database training (that suprises you if you are reading this far, I am sure.) I have read a couple of books, and being self taught by the painful trial an error method. None of the books have really addressed my issues - Am I just not able to see the forest for the trees? Or are real life databases this different from books?
Thanks for the help
Scott
I am building a database which will track various pieces of equipment and other things. I got good help in here, and believe my tables are pretty well normalized, and now I am at the point of trying to design some simple forms, in order to put a few data items in my tables in order to test the tables/relationships.
Here are the two tables giving me troubles:
tblManufactureModel
pkManufactureModelID - Autonumber
Manufacturer
ModelName
PartNumber
tblEquipment
pkEquipmentID
ManufactureModelID - foreign key in a one to many relationship with tblManufactureModel 's primary key
SerialNumber
(more fields following)
My problem is this - I may have several of the same type of equipment, say, Fluke 77 meter. So I want to enter Fluke 77 Meter particulars in tblManufacturerModel. However, in my form for entering data in tblEquipment I am stumped. When I use a combo box, and set the row source property to tblManufactureModel!PartNumber, I can get the data to show up, and make a selection.
However, I cannot figure out how to get the field tblEquipment!ManufactureModelID to be updated. If I bind the combo box to that field, then of course I get errors, because the combo box is text, and the field wants a number.
I think I want to do something like run a query in the After Update Event for the combo box - but I am not real sure how to do that. I assume it would be something like:
qryPartNumber = SELECT ManufactureModelID FROM tblManufacturerModel WHERE cboPartNumber = tblManufacturerModel!PartNumber
And then I could do something like;
tblEquipment!PartNumber = qryPartNumber
I also am not sure the combo box is really the way to go. I have about 750 different types of items - that could make for a pretty long combo box. Not sure of alternatives, perhaps a text box where the user (me most likely) types in the data, and again have some query run on After Update to see if that part number is already in tblManufacuterModel.
I am posting in this forum, because this problem makes me wonder if perhaps I am looking at this problem completely wrong. A few more things for consideration. Number one, I will be the only person likely entering data such as this into the database. At least initially entering the various things we already have. Number two, the approximately 750 part numbers and some other information is already present in a couple of ridiculous Excel Spreadsheets. Number 3, I have no formal Access or Database training (that suprises you if you are reading this far, I am sure.) I have read a couple of books, and being self taught by the painful trial an error method. None of the books have really addressed my issues - Am I just not able to see the forest for the trees? Or are real life databases this different from books?
Thanks for the help
Scott
