Results 1 to 13 of 13
  1. #1
    Chronus13 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    11

    Combo boxes

    Quick question, I hope. So i have a form with a combo box that pulls a last name from table 1. I have a text box that grabs data from table 1 as well dependent on the choice in the first combo box, this will populate a first name. The problem is this. I have another combo box that needs to use either of those boxes selections to determine which table this box pulls from, it will pull from either table 2 or table 3. Is this possible and how?

    thanks,
    Wes

  2. #2
    Welshgasman is online now Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    630
    Set the correct rowsource depending on what is in the other controls.

    Sounds like your DB is not normalised correctly though?, as I would expect all the similar data to be in the same table, with a field to identify.
    Please, please use # when posting code snippets.
    Cross Posting Etiquette: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,635
    Agree, design doesn't sound right. First and Last names ought to be coming from the same table 99.99% of the time. If you need to know which table either value comes from, it also sounds like data is being duplicated if not just being unnecessarily split.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,480
    Perhaps you could describe the issue in plain English without any database jargon (combo boxes...).
    What exactly is the situation and the issue as you understand it?

  5. #5
    Chronus13 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    11
    Hopefully this helps. I attached a doc of how my form should be working. I appreciate any thoughts.
    Attached Files Attached Files

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,480
    Your file indicates HOW you think your form will/does work. The issue to be resolved is the WHAT, as in what is it in your "business" that requires "fixing"? -plain English, no database terms.

  7. #7
    Chronus13 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    11
    Guess I didn't explain well, my apologies. I am trying to create an audit form, where I pull an employees name and toolbox so I can select individual tools for said employee and be able to select different 'discrepancies' for each tool. Say the tool has rust, I want to be able to select the employee, the tool associated with the employees toolbox, and 'rust'. After all the 'discrepancies' are made for the employee in the form I ultimately want to be able to email that to a supervisor in some manner. Keeping a record of this would also be ideal but not necessary, I would like to be able to use the form over and over and over for all the employees. Well crap, now I just realized something, when I send to the supervisor, I would need to send up to 15 employee audits at the same time to avoid him getting 15 different emails. Maybe a report some how?

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,480
    So, the issue in business terms may be that your employees are not maintaining their tools and tool boxes up to some standard.
    You want to review each employee's toolbox, look and identify any/all incidents of "substandard maintenance practices" and report same to the supervisor.

    I think you need to deal with tables and relationships before getting too deeply into forms and potential combo boxes.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,635
    Getting tables schema right is job one. Methinks something like this quick and rough notion - consider it a starting point:

    tblEmployee
    EmplID EmplNum Fname Lname Supervisor
    1 123 1
    2 124 1
    tblTBox
    TboxID EmplID_fk etc.
    1 1
    2 1
    tblTools
    ToolsID TboxID_fk Tool
    1 1 wrench
    2 1 punch
    3 1 chisel
    tblSupervisors
    SupervisorID FName LName etc.
    1
    tblInspections
    InspectID ToolsID_fk InspectDate Comments
    1 1
    2 2
    3 3

    Supervisor notion depends on whether you want to include them in employee table or not. They are an employee, but including them will result in null in every record where the employee is not a supervisor, unless you go with a true/false type of field. The downside of a separate table might be apparent when someone fills in on a temp basis. Now you probably have to archive them in one and add them to the other. Depends on your business, but I would probably go with one table but others might have a better take on this.

    EDIT - forgot to mention that I took the approach that an employee could have more than one box. Even if they don't, this setup will allow for that expansion in the future so it is what I would do.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  10. #10
    mike60smart is online now Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    631
    Your Table1 and Table2 should be just Table1

    Table1 just needs EmployeeID as the ForeignKey which links to the EmployeeID Primary Key in Table3

  11. #11
    Chronus13 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    11
    Capture.zipSo I did all my tables over almost like you showed. But now if you look at the attachment, there is a small issue. When I click the plus sign on the tblTbox, I am able to extend it to see the rest of the data its tied to. When I try to do that with the tblEmployee it gives me an error and I don't know why.

    "This expression is typed incorrectly or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

    Did I by chance do the relationship wrong? Because the EmplId is exactly the same in both tables. I appreciate the help.

  12. #12
    mike60smart is online now Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    631
    Hi

    The plus sign in your tables are called Subdatasheets and these should not be visible to the user.

    Setting this property to [None] significantly improves performance, especially over a network, when you open the table. It also impacts references to the table from queries, forms, and reports.

    You should not be using tables for data input as all Data input should be via Forms.



  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,635
    It looks like you have a 1 to 1 relationship between employees and toolboxes. Is that intentional?
    Please post pictures using the image button on the toolbar. Having to download, unzip then open files is a pita and posting the image is much faster/easier for you and everyone else.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 09-22-2016, 08:42 AM
  2. Changing text boxes to combo boxes
    By Lou_Reed in forum Access
    Replies: 8
    Last Post: 09-15-2015, 11:09 AM
  3. Linking Combo boxes and Text boxes
    By Nathan4477 in forum Forms
    Replies: 6
    Last Post: 07-29-2015, 08:50 AM
  4. Replies: 11
    Last Post: 08-06-2014, 09:47 AM
  5. Replies: 9
    Last Post: 06-04-2014, 10:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums