Unanswered: Please help with MS Access 2010 Form design!
Iíve designed a database in MS Access 2010 to help keep track of our computer inventory where I am an Intern. The current database in use isn't normalized at all and is basically designed like a spreadsheet. Itís use is solely for the IT department (currently 1 guy and myself) to help keep track of equipment. Right now the only devices heís keeping track of are computers and phones, but Iíd like his database to be flexible enough to include other devices. It doesnít need to be incredibly detailed because any of the networked devices can be queried using an application called Desktop Central.
My database has 22 tables in all. Iíd like to be able to create a form which would allow me to input all of the data from one form. Iíve been beating my head against the wall trying to figure out how to make this work. Iím running into several problems.
If I create a form from the DevicePrimary table, the central table in the design, and add tables with a one-to-many relationship to the form, I can insert a new record from here as long as I donít leave any fields representing a table blank. If I do Iíll get an error such as ďThe Microsoft Access database engine cannot find a record in the table ĎDeviceRAMí with key matching field(s) ĎDeviceRAMIDí. Iíve allowed these fields to accept null values so Iím scratching my head as to why this error occurs.
Iíd also like to prevent input from accepting values that are already in the database, but Iíd like to be able to add new values from here as well. Is there a way to allow the fields to have a drop down of current values but also work as a text box for new values?
Any input would be greatly appreciated. Please feel free to tear my design apart, and point me to any good Access tutorials, particularly dealing with Form creation. If anyone could direct me to a download of an Access database using forms to input data to several linking tables that would be helpful as well. Iím pretty new to this and a good lashing will help me learn.
I attached a copy of my ERD design if anyone is interested enough in taking a look at that.
Here are my tables:
DeviceID pk (autonumber)
PrimaryUserID fk (number)
DeviceLocationID fk (number)
DeviceTypeID fk (number)
DeviceManufacturerID fk (number)
DeviceModelID fk (number)
DeviceRamID fk (number)
DeviceProcessorID fk (number)
DeviceHardDriveID fk (number)
DeviceVideoCardID fk (number)
DeviceBIOSID fk (number)
DeviceWarrantyID fk (number)
DeviceRetiredID fk (number)
PrimaryUserID pk (autonumber)
Other (text) ---Some devices donít have a user, but are listed as a test stand for an area
If I create a form from the DevicePrimary table, the central table in the design, and add tables with a one-to-many relationship to the form, I can insert a new record from here
Are you using sub-forms? If not, FIRST AND FOREMOST read up on sub-forms in the help file. THEN do what you need to to put those sub-forms on the main form and enter data into them.
It's also possible that that's not the best way to do it, but by using comboboxes to locate the info you need. Also, with comboboxes you can add new detailed records for ram amount, device type, or any other parameter you want to limit. The combobox wizard will do it all for you. You will find it helpful to read up on them in the help file.
Last edited by Sam Landy; 11-01-12 at 00:00.
Reason: on second thought...