none
Microsoft Access Combo Boxes RRS feed

  • Question

  • Hi guys,

    So i am making a simple database that require multiple Combo Boxes that is Linked to the same Database and everytime i try to Choose something on one of the combo boxes the rest already changed, so its like Synchronized. I was just wondering how do i make the combo boxes not Synchronized or like Input it individually when it's still link to the same Database

    Thanks

    Monday, April 9, 2018 6:22 PM

Answers

  • Right. Let's pretend your database is about issuing out tools to workers. You might have the following tables in your database:

    tblWorkers - stores personal information about each worker

    tblTools - stores descriptive information about each tool you check out

    tblCheckOuts - stores the information of which tools were checked out by which worker

    In the above scenario, there's a many-to-many relationship between workers and tools, so we needed a third table to break the relationship down to a one-to-many. In other words, there's now a one-to-many relationship between workers and checkouts, and also a one-to-many between tools and checkouts.

    On your form, you can base the main form against tblWorkers, so you can display the worker information on the header. You can then add a subform, which is based on tblCheckOuts, displayed as a Continuous or Datasheet form.

    In the subform, you can use a Combobox to select the Tools the worker is checking out. You would select one tool per row/record. So, if the worker needs to check out multiple tools, they would have several records showing in the subform.

    Hope it helps... 

    Monday, April 9, 2018 7:51 PM
  • Hi Andy,

    Yes! Relationships are created by linking matching (by data types) fields from one table to another. For my suggested structure, it might look something like this:

    tblWorkers
    WorkerID, Autonumber, PK
    ...

    tblTools
    ToolID, Autonumber, PK
    ...

    tblCheckOuts
    CheckOutID, Autonumber, PK
    WorkerID, Number (Long Integer), FK
    ToolID, Number (Long Integer), FK
    ...

    Hope it helps...

    Tuesday, April 10, 2018 2:40 PM
  • So for Example i am making a Sign in and Sign out Borrowing Tool

    I would like to have Multiple Combo Boxes to pick the Tools by the ID, for Example 

    The First Combo Box Could say:
    ID : 4
    Tool : Drill (Auto-Fill)
    Brand : Dewalt (Auto-Fill)

    And then you select the second Combo Box to get another Tool
    ID : 6
    Tool : Screw (Auto-Fill)
    Brand : Dewalt (Auto-Fill)

    So i want Different Combo Boxes to Display the Different/Same Tools and Have Separate Record For each of the combo boxes but still link to one database

    Monday, April 9, 2018 7:09 PM

All replies

  • Hi Andy,

    You can have multiple databases "linked" to the same database; although I am not sure what you mean by "linked."

    Synchronicity usually happens if all the Comboboxes were bound to the same field in your table.

    Can you explain the purpose of these multiple Comboboxes? Thanks.

    Monday, April 9, 2018 6:29 PM
  • So for Example i am making a Sign in and Sign out Borrowing Tool

    I would like to have Multiple Combo Boxes to pick the Tools by the ID, for Example 

    The First Combo Box Could say:
    ID : 4
    Tool : Drill (Auto-Fill)
    Brand : Dewalt (Auto-Fill)

    And then you select the second Combo Box to get another Tool
    ID : 6
    Tool : Screw (Auto-Fill)
    Brand : Dewalt (Auto-Fill)

    So i want Different Combo Boxes to Display the Different/Same Tools and Have Separate Record For each of the combo boxes but still link to one database

    Monday, April 9, 2018 7:09 PM
  • Hi Andy,

    Ah, if I understand it correctly, you may be storing data as a repeating group in one table. Instead, you should use a related/child table to store the borrowed tools and then use a Subform on your form. The subform will probably be in Continuous View, so the multiple comboboxes will be in separate rows. In that way, you can select the first tool and the second tool separately (because they'll be in two separate records in the child table).

    Hope it makes sense...

    Monday, April 9, 2018 7:18 PM
  • Ahhh okay thank you so much, may i ask how do i create a related/child table?
    Monday, April 9, 2018 7:44 PM
  • Right. Let's pretend your database is about issuing out tools to workers. You might have the following tables in your database:

    tblWorkers - stores personal information about each worker

    tblTools - stores descriptive information about each tool you check out

    tblCheckOuts - stores the information of which tools were checked out by which worker

    In the above scenario, there's a many-to-many relationship between workers and tools, so we needed a third table to break the relationship down to a one-to-many. In other words, there's now a one-to-many relationship between workers and checkouts, and also a one-to-many between tools and checkouts.

    On your form, you can base the main form against tblWorkers, so you can display the worker information on the header. You can then add a subform, which is based on tblCheckOuts, displayed as a Continuous or Datasheet form.

    In the subform, you can use a Combobox to select the Tools the worker is checking out. You would select one tool per row/record. So, if the worker needs to check out multiple tools, they would have several records showing in the subform.

    Hope it helps... 

    Monday, April 9, 2018 7:51 PM
  • Thank you very much!, One last question will there be relationships links for those Tables?, if so how would you link them?
    Tuesday, April 10, 2018 5:47 AM
  • Hi Andy,

    Yes! Relationships are created by linking matching (by data types) fields from one table to another. For my suggested structure, it might look something like this:

    tblWorkers
    WorkerID, Autonumber, PK
    ...

    tblTools
    ToolID, Autonumber, PK
    ...

    tblCheckOuts
    CheckOutID, Autonumber, PK
    WorkerID, Number (Long Integer), FK
    ToolID, Number (Long Integer), FK
    ...

    Hope it helps...

    Tuesday, April 10, 2018 2:40 PM
  • Thank you so much!
    Tuesday, April 10, 2018 4:54 PM
  • Hi Andy,

    You're welcome. We're happy to assist. Good luck with your project.

    Tuesday, April 10, 2018 5:18 PM
  • Hello Andy Holmes1210,

    It seems that your issue has been resolved, right? If so, I'm glad to hear and I will suggest you mark helpful reply to close the thread. If not, please feel free to let us know your current issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 11, 2018 3:07 AM
  • Hi again,

    I cant seem to make my Subform to work properly and its stressing me out, The combo boxes doesn't work in the subform also

    And i would like to make the Worker as a Dropdown menu

    Could you possibly help me step by step? :(

    Wednesday, April 11, 2018 4:59 PM
  • Hi Andy,

    It would probably be faster/easier if you could share your database, so we can make the changes for you (or at least see what you may be doing differently, so we can provide a step-by-step suggestions).

    Wednesday, April 11, 2018 5:37 PM
  • How can i share the Database?, Screenshots?
    Wednesday, April 11, 2018 5:39 PM
  • Hi Andy,

    Screenshots would definitely help. You can also upload the db to a file share and post the link here. As a last resort, you can email it to me.

    Cheers!

    Wednesday, April 11, 2018 5:49 PM
  • I can email it to you, what's your email?
    Wednesday, April 11, 2018 5:53 PM
  • I can email it to you, what's your email?

    The address should be in my profile but it's thedbguy @ gmail.com

    Note: Remove the spaces I added to the address above. I'm hoping to avoid email harvesters (spam bots) to see/get my email address.

    Cheers!

    Wednesday, April 11, 2018 6:08 PM
  • Sent :D
    Wednesday, April 11, 2018 6:13 PM
  • I sent it back with changes...
    Wednesday, April 11, 2018 6:35 PM