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

    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 offline Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    622
    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 online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,565
    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,459
    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
    10
    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,459
    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
    10
    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,459
    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 online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,565
    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 offline Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    620
    Your Table1 and Table2 should be just Table1

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

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