Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Chronus13 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    24

    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
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    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 use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  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
    16,716
    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
    24
    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
    16,716
    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
    24
    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
    16,716
    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
    12,737
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Your Table1 and Table2 should be just Table1

    Table1 just needs EmployeeID as the ForeignKey which links to the EmployeeID Primary Key in Table3
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  11. #11
    Chronus13 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    24
    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
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    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.


    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    Chronus13 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    24
    Click image for larger version. 

Name:	Capture.JPG 
Views:	11 
Size:	22.0 KB 
ID:	45070Click image for larger version. 

Name:	Capture1.JPG 
Views:	11 
Size:	59.5 KB 
ID:	45073Click image for larger version. 

Name:	Capture2.JPG 
Views:	12 
Size:	21.0 KB 
ID:	45072
    I appreciate the help everyone. I was able to figure out the relationships of my tables. I went and did more research on making relationships and tables work. So low and behold, it finally did. I think. The second picture, the name fields are auto populated from the first form's choice picked by the user. The idea is to have the Toolbox field auto fill with the ToolboxID. This would hopefully allow the connection between the Toolbox ID and all the tools associated with that ID from tblTools. So i would have the name at the top auto filled, then the Toolbox ID would auto fill, then the appropriate toolbox tools would show for me at the bottom of the form. this way I could choose a tool and its discrepancy. Will this work or do I need to change something? So far the form works like I want, just stuck on the Toolbox ID and the associated tools at the bottom.
    Attached Thumbnails Attached Thumbnails Capture1.JPG  

  15. #15
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Wes

    Can you upload a zipped copy of the database with any Confidential data removed?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

Page 1 of 2 12 LastLast
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