Results 1 to 12 of 12
  1. #1
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249

    Is it possible to have a form that updates a table selected by a combo box in the form?

    I have four tables that I'm currently using to keep track of employees that have qualified in four different job positions. Is it possible to have a single form that I could use to enter the person's name and qualification date and then via either a combo box or check boxes, have that one form update the desired table?



    Or would it actually be better to have a single table that contains the qualification dates of the four positions for each person that is entered? Does it matter?

    I might should add that right now the only thing the tables are being used for are to feed combo boxes on another form to annotate who was working what positions. In the future I'd also want to run a query to see when someone was certified for a certain position.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Should be one table with another field for PositionID.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Quote Originally Posted by June7 View Post
    Should be one table with another field for PositionID.
    Is that just to keep the number of tables down so the DB runs smoother? It seems to make sense that way, but it would make the combo boxes on the other form much harder to build. I'd have to tell each combo box which portion of this single table to pull the list of names from. If a person was qualified in Position A and C, I would not what their name to show up as an option in Position B for example. To make things a little more complicated, there could be up to 5 people work at position B.

  4. #4
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Depends on your data structure.

    From the info you gave I would use a data structure like this
    Employees
    EmployeeID
    Employee name
    Address
    DOB

    Positions
    PositionID
    PositionInfo

    Employeeposition
    Employeeid
    PositionID
    DateofQualification

    Would need to see your data structure to give a better Design

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Why would there be more than one combobox?

    Yes, there are ways to deal with limiting a list to exclude item (such as a person) who is already dedicated elsewhere.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Quote Originally Posted by RayMilhon View Post
    Depends on your data structure.

    From the info you gave I would use a data structure like this
    Employees
    EmployeeID
    Employee name
    Address
    DOB

    Positions
    PositionID
    PositionInfo

    Employeeposition
    Employeeid
    PositionID
    DateofQualification

    Would need to see your data structure to give a better Design
    Thanks for the detailed response. I'll think about that structure some. It is more detailed than what I was thinking of, but that might be better in the long run if we expand the use of this data base.

    Quote Originally Posted by June7 View Post
    Why would there be more than one combobox?

    Yes, there are ways to deal with limiting a list to exclude item (such as a person) who is already dedicated elsewhere.
    The data base is to track test results and for each test we conduct there is always one person acting as the Sim Controller, one as a BOA Controller, and between one and five acting as Mission Controllers. After each test (there can be 3 to 10 a day depending on the test event) we have to record all of the results. Part of those results is who was assigned to each position during that run of the test.

  7. #7
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Thinking about this more, I think I can do what I need with a single employee table (we actually call the employees operators):

    Operators
    -> OperatorID AutoNumber
    Operator_Name Short Test
    Sim_Controller_Cert Date/Time
    Mission_Cert Date/Time
    BOA_Cert Date/Time

    With that single table, I would just need to limit those combo boxes I talked about earlier to only show names in each position combo box if there was a date in that field. Any thoughts on how I would go about that?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    So if combobox should list only operators certified for Sim Controller, consider:

    SELECT OperatorID, Operator_Name FROM Operators WHERE Not Sim_Controller Is Null;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Quote Originally Posted by June7 View Post
    So if combobox should list only operators certified for Sim Controller, consider:

    SELECT OperatorID, Operator_Name FROM Operators WHERE Not Sim_Controller Is Null;
    Thanks June7. That looks like VBA code and the only place I know how to insert VBA code is in the Event section of a code. But if I put it in there, VBA turns it red which I think means there is something wrong with it. I'm probably way off base on what I'm supposed to do with that code you game me.

  10. #10
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    What June7 Posted is SQL if you put that into the Record Source of the combo box. That will give you a list of Operators where the Sim_Controller field has data in it.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Ray meant Row Source of combobox.

    Build a query object with query designer then switch to SQLView you will see SQL statement. Can put SQL statement directly in RecordSource and RowSource properties.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Thanks, I've got that working now. Little by little it is all starting to make some sense!

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

Similar Threads

  1. Replies: 6
    Last Post: 06-23-2015, 06:58 PM
  2. Replies: 6
    Last Post: 11-30-2013, 02:41 PM
  3. Replies: 8
    Last Post: 06-03-2013, 03:55 PM
  4. Combo Box on form updates unintentionally
    By zipmaster07 in forum Forms
    Replies: 2
    Last Post: 03-06-2012, 10:21 PM
  5. Form no longer updates table; what'd I do?
    By SevenSignz in forum Forms
    Replies: 8
    Last Post: 03-04-2012, 12:25 PM

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