Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2011
    Posts
    17

    Unanswered: Writing to Multiple Tables from a Single Text Box - Possible? (Necessary?)

    Using: Access 2007.

    I had some training on Access 2003 in college, including basic SQL and VBA, but it's fuzzy now.

    Problem Background: I am collecting data on machine malfunctions from several different departments. There are several departments, several machines per department, and several malfunctions for each machine.

    Briefly, drilling down: Department>Machine>Malfunction.

    On a given date, there can be multiple malfunctions on multiple machines in a given department.

    What I Did:
    I created a different table for each machine, listing its malfunctions, using date and department as the primary key.

    I also created a table for the other data: product and operator, also using date and department as the primary key.

    What I Want to Be Able to Do: Query malfunction by machine, department, date, operator, product... everything.

    Create a form where a user can enter date, department, product, operator, and multiple malfunctions, and have the data store itself in the relevant tables.

    Where I'm Confused:When you make a form, the text box is attached to a specific table. Since I'm writing to multiple tables, I would like the primary key data to write to multiple tables.

    Is this possible?
    Can I sidestep the issue by rearranging my data?



    I really appreciate any help. Thanks!

  2. #2
    Join Date
    Apr 2011
    Posts
    11
    It will be much easier for you to query by machine if, instead of storing each machine as a separate table, instead you have a table of machines with a unique ID for each machine.

    e.g., assuming your machine names are not unique,

    Machine ID, Machine, Department
    ----------------------------------
    101, Electromagnet, Complaints
    102, Drill Press, Engineering
    103, Laser Cutter, Administration

    Then you can reference these machine IDs in a malfunction table:

    Machine ID, Malfunction
    ----------------------
    101, fuse blown
    103, caught on fire
    101, button came off

    etc.

    So then someone can enter a machine name and you can query the malfunction table for that machine.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There is a flaw in the design of your database. All machines should be stored into a single table with a column distinguishing each of them.

    As it is now, you can use a UNION query as the RecordSource for the form but these are read-only (thus the form will be too) and the performance is poor. You can then use SQL Statements to write back to the desired tables (provided there is something that allows you to determine which ones).

    I would advice you to reconsider the database design to make it compliant with the database normalization rules, it would be more profitable in the long term. See for instance: Database Design :: Normalization Basics - Techniques
    Have a nice day!

  4. #4
    Join Date
    Aug 2011
    Posts
    17

    Normalization Should Solve This...

    Thank you - I'd forgotten about normalization. I reviewed the rules, and things are sorted out now.

    I only have one continuing problem: the "other: ______" entry. If something goes wrong and it's not in our list, we want to add it. Is there any way to do this from a standard data-entry form?

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by GLink View Post
    Thank you - I'd forgotten about normalization. I reviewed the rules, and things are sorted out now.
    You're welcome!
    Quote Originally Posted by GLink View Post
    I only have one continuing problem: the "other: ______" entry. If something goes wrong and it's not in our list, we want to add it. Is there any way to do this from a standard data-entry form?
    What could not be in the list? A machine? You can always use a form to create new machines in the table. As for the list, do you use a ListBox or a ComboBox to select the machine?
    Have a nice day!

  6. #6
    Join Date
    Aug 2011
    Posts
    17
    Not a machine, a malfunction. There are always more things that can go wrong. Right now the form seems to be using combo boxes that query the relevant table. (The table itself uses a list box.)

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    A ComboBox has a NotInList event that you could use to open a modal form that would open on a new record in the Incidents table ( DoCmd.OpenForm "Frm_NewIncident", , , , acFormAdd, ,acDialog - see also: Forms: Form only allows new records to be entered) and allow the user to enter data for a new incident. When this form is closed, the combo is requeried (Me.ComboIncidents.Requery) and then contains the new entry. For a simpler way of handling this see also: Forms: Add item to combo box using OnNotinList event
    Have a nice day!

  8. #8
    Join Date
    Aug 2011
    Posts
    17
    Thanks! I'm going to give it a spin later this week and see what I can do.
    (Right now I need to get out a basic but aesthetic front-end so I have something to show at the meeting tomorrow.)

    One related problem I'm having: I created a table that lists each machine and all the possible malfunctions and gives them a code as a key.

    The input form puts the code into the relevant table, but on the form I have a drop-down showing the machine/malfunction columns only. After the user selects a machine/malfunction combination, only the machine name appears in the combo box window. this can be disconcerting, as the user may be entering multiple malfunctions for a given machine.

    Is there any way to force it to show the malfunction instead?

    I've checked the properties sheet. It claims to have three columns, with the first one (the code, I assume) having a width of zero. The others have sufficient width to show. List width and combo-box width also sufficiently wide.

    The data row source is drawing from a query where code, machine, and malfunction are all checked off as "show." (See attachment.)

    I'm stumped. How do you control what shows in the combo box window after a selection is made?

  9. #9
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    If I understand correctly, you could use two combo boxes, one for the machine, and one for the malfunction.

    The machine would have an SQL reference similar to:

    Code:
    SELECT yourTableName.[Machine]
    FROM yourTableName
    WHERE yourTableName.[Machine] IS NOT NULL
    The malfunction would have an SQL reference similar to:

    Code:
    SELECT yourTableName.[Malfunction]
    FROM yourTableName
    WHERE yourTableName.[Machine] = '" & Me.nameOfYourMachineComboBox.Value & "'"
    Which would let the user select the machine, followed by a list of known malfunctions for that machine.

    Also, if your allowing users to select multiple malfunctions, it could be worth considering a multi-select listBox for the malfunction, instead of a combo box... But that's more of a design choice, and isn't strictly necessary.


    In terms of getting it to display the entire selection (i.e., every column in the comboBox) after selection, you can't... You can specify which column to display, but it can still only show one column.

    You can alternatively create a couple of Labels, or TextBoxes to display the other 'missing' information, which I can help you to setup. Personally though, I would just use two comboBoxes and update the table with their chosen values.
    Looking for the perfect beer...

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The widths of the columns in a ComboBox (ColumnWidths property) only concern the List part of the combo (i.e. the part that shows when the combo "drops down"). The TextBox part of the combo (i.e. the part that's visible when the list part is "shut") can only display the contents from one column. You specify this column with the BoundColumn property of the combo.
    Have a nice day!

  11. #11
    Join Date
    Aug 2011
    Posts
    17
    If I understand correctly, you could use two combo boxes, one for the machine, and one for the malfunction.

    The machine would have an SQL reference similar to:

    Code:
    SELECT yourTableName.[Machine]
    FROM yourTableName
    WHERE yourTableName.[Machine] IS NOT NULL
    The malfunction would have an SQL reference similar to:

    Code:
    SELECT yourTableName.[Malfunction]
    FROM yourTableName
    WHERE yourTableName.[Machine] = '" & Me.nameOfYourMachineComboBox.Value & "'"
    Which would let the user select the machine, followed by a list of known malfunctions for that machine.
    I've tried that, but it throws up a pop-up instead of using the combo box. It may be my syntax, or the fact that I don't want the machine and malfunction listed in the table I'm accessing. I just want to put their code in that table. However, I would like text visible to the user so they can see what they've put in already.

    In terms of getting it to display the entire selection (i.e., every column in the comboBox) after selection, you can't... You can specify which column to display, but it can still only show one column.
    I would be happy to just display the malfunction column in the combo box after selection instead of the machine. I just can't seem to find the option in the properties table.

Posting Permissions

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