none
Help With Combo box in form RRS feed

  • Question

  • Hello, I am creating a simple Access database to keep all of our service records for Vehicles. Each vehicle has its own table, 11 in total. All tables have the same data columns, and do not need to be related to one another. I am trying to make a user friendly FORM that once a week we can enter in (Date, Hours, Service Info, Items Replaced, Next Service) all of which each table has in them. Instead of having to make 11 different FORMS one for each vehicle, I would like to make ONE FORM with a drop down list where the user can pick the vehicle and then the 5 different data inputs can be entered and they link to the corresponding table in the drop down list. Is this possible? and How. TIA.

    Chris

    Friday, December 1, 2017 3:07 AM

All replies

  • Hi Chris,

    You could create a form based on one table and then add a combo box in the Form. List all the vehicles tables' name in the combo box and try to use below OnChange event to change the RecordSource of the Form.

    Private Sub cbxTableSelect_Change()
    Me.RecordSource = cbxTableSelect.Value
    End Sub

    Here is the demonstrate.

    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.

    Friday, December 1, 2017 5:17 AM
  • Hi cajd83,

    you had mentioned that,"Each vehicle has its own table, 11 in total. All tables have the same data columns, and do not need to be related to one another."

    if all table has the same data columns then you not need to create a separate table for each vehicle.

    you just need to create 2 table like 1 table will contain vehicle information and other table will contain the service history. then you can make a relationship between these two tables.

    you can use the vehicle id and service id field to make a relationship and develop an one to many relationship as one vehicle will be serviced multiple times.

    this is the correct way to design a database.

    try to follow the database designing rules to create a database.

    Database design basics

    11 important database designing rules which I follow

    currently it's looks like you have the basic information regarding Access.

    so I suggest you to use Readymade Templates that comes with Access.

    there is a template for vehicle maintenance. you can directly use it.

    it has all the tables, forms, reports with attractive user interface.

    it also provides much more functionality like you can mail your data etc.

    below are some screen shots of it.

     

    you just need to use that template and no need to create anything and no need to worry about any rules and other things.

    Regards

    Deepak


    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.

    Friday, December 1, 2017 8:45 AM
    Moderator
  • It is bad design to have a table for each vehicle. You should have one tabel for all vehicles. Having one table allows you to have one form.

    Beside that, you might need a separate tabel to store your weekly information. If not you can only store the most recent info.


    Groeten, Peter http://access.xps350.com/

    Friday, December 1, 2017 8:47 AM
  • Not to jump on the bandwagon, but the other posters are right - you should NOT have a separate table for each vehicle. Access is a relational database, and you should store data based on an "entity" (in this case, a Vehicle, and Service for that Vehicle). 



    -- Scott McDaniel, Microsoft Access MVP

    Friday, December 1, 2017 10:48 AM
  • Thank you everyone for your help, Its been a long time since I have dabbled with ACCESS (School days!!). I understand now that there is no need for individual tables for each vehicle as ACCESS can pull data all from one table to create reports. I Will have a look at the Template that ACCESS already has and see if I can modify it to suit our needs. Thanks again.
    Sunday, December 3, 2017 10:46 PM
  • Hello cajd83,

    Has your original issue been resolved? If it has, I would suggest you mark the helpful reply as answer or provide your solution and mark as answer to close this 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.

    Monday, December 4, 2017 9:41 AM