Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2012

    Unanswered: Please help with MS Access 2010 Form design!

    Hello Everyone.

    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)
    DeviceDescription (text)
    DateRegistered (date/time)
    DeviceSerialNumber (text)
    MAC_Address (text)
    IMEI_Number (text)
    ICCID (text)
    PhoneNumber (text)
    Notes (text)
    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)
    FirstName (text)
    MiddleName (text)
    LastName (text)
    Other (text) ---Some devices donít have a user, but are listed as a test stand for an area
    DomainID fk

    DeviceLocationID pk (autonumber)
    DeviceLocation (text)

    DomainID pk (autonumber)
    Domain (text)

    DeviceTypeID pk (autonumber)
    DeviceType (text)

    ManufacturerID pk (autonumber)
    Manufacturer (text)

    DeviceModelID pk (autonumber)
    DeviceModel (text)
    ProductNumber (text)

    DeviceRamID pk (autonumber)
    DeviceRamAmount (text)

    DeviceProcessorID pk (autonumber)
    DeviceProcessor (text)

    DeviceHardDriveID pk (autonumber)
    DeviceHardDriveTypeID fk (number)
    DeviceHardDriveStorageID fk (number)
    DeviceManufacturerID fk (number)

    DeviceHardDriveStorageID pk (autonumber)
    DeviceDiskSpace (text)

    DeviceHardDriveTypeID pk (autonumber)
    DeviceHardDriveType (text)

    DeviceVideoCardID pk (autonumber)
    DeviceVideoCard (text)

    DeviceBIOSID pk (autonumber)
    BIOSVersion (text)

    DeviceWarrantyID pk (autonumber)
    DeviceWarrantyTypeID fk (number)
    DeviceWarrantyExpirationDate (date/time)

    DeviceWarrantyTypeID pk (autonumber)
    DeviceWarrantyType (text)

    DeviceRetiredID pk (autonumber)
    DeviceRetiredTypeID (number)
    DeviceRetiredNotes (text)

    DeviceRetiredTypeID pk (autonumber)
    DeviceRetiredType (text)

    OperatingSystemID pk (autonumber)
    DeviceOperatingSystem (text)

    DeviceID pk/fk (number)
    DeviceOperatingSystemID (number)
    OS_SerialNumber (text)
    OSCDKey (text)

    DeviceNetworkAdapterID pk (autonumber)
    DeviceNetworkAdapter (text)

    DeviceNetworkAdapterID pk/fk (number)
    DeviceID pk/fk (number)
    Attached Thumbnails Attached Thumbnails ERD_Diagram.png  
    Last edited by havachip; 10-31-12 at 15:55.

  2. #2
    Join Date
    May 2004
    New York State
    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; 10-31-12 at 23:00. Reason: on second thought...

  3. #3
    Join Date
    Oct 2012
    Thanks for the reply Sam.

    I'll read up on the sub-forms and come back with any questions I come up with.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts