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

    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:

    DevicePrimary
    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)

    PrimaryUser
    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

    DeviceLocation
    DeviceLocationID pk (autonumber)
    DeviceLocation (text)

    Domain
    DomainID pk (autonumber)
    Domain (text)

    DeviceType
    DeviceTypeID pk (autonumber)
    DeviceType (text)

    DeviceManufacturer
    ManufacturerID pk (autonumber)
    Manufacturer (text)

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

    DeviceRAM
    DeviceRamID pk (autonumber)
    DeviceRamAmount (text)

    DeviceProcessor
    DeviceProcessorID pk (autonumber)
    DeviceProcessor (text)

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

    DeviceHardDriveStorage
    DeviceHardDriveStorageID pk (autonumber)
    DeviceDiskSpace (text)

    DeviceHardDriveType
    DeviceHardDriveTypeID pk (autonumber)
    DeviceHardDriveType (text)

    DeviceVideoCard
    DeviceVideoCardID pk (autonumber)
    DeviceVideoCard (text)

    DeviceBIOS
    DeviceBIOSID pk (autonumber)
    BIOSVersion (text)

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

    DeviceWarrantyType
    DeviceWarrantyTypeID pk (autonumber)
    DeviceWarrantyType (text)

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

    DeviceRetiredType
    DeviceRetiredTypeID pk (autonumber)
    DeviceRetiredType (text)

    DeviceOperatingSystem
    OperatingSystemID pk (autonumber)
    DeviceOperatingSystem (text)

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

    DeviceNetworkAdapter
    DeviceNetworkAdapterID pk (autonumber)
    DeviceNetworkAdapter (text)


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

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    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.

    Sam
    Last edited by Sam Landy; 11-01-12 at 00:00. Reason: on second thought...

  3. #3
    Join Date
    Oct 2012
    Posts
    5
    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
  •