Results 1 to 8 of 8
  1. #1
    ItsBeenAWhile is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Oct 2016
    Posts
    4

    Use combobox to update field in form

    Hello,

    I am using MS Access 2010.

    I have a Clients table and a Jobs table, the Jobs table has a ClientID that refers to the Clients table.

    I have a form to add or edit Jobs, and I have a subform with the infomation from the client table. I want to have a combo box with all the Client Ids and when I choose one, it is saved on the the Jobs.ClientId field both on new record and on edit record.

    I tried linking the Jobs form and the Clients subform using the Link Master/Child fields settings, but when I do this, creating a new record forces me to create a new record in the clients table too, and when I try to change the id from the combobox it throws an error because it is trying to change the ClientId in the clients table (which it can't because it's autonumber and PK) instead of the saving the selected id to the Jobs table ClientId.

    Can anyone help?

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The clients can be a combobox (or listbox) on the Jobs form with the control source set to the ClientID. If you want to display further client information you can use a subform for that which will display only, no entry.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm a little confused as to which is the one and which is the many form. I'd say the main form would be Clients and the sub ought to be Jobs but you have it the other way around. My logic is that each client (one at a time) could have several jobs identified with them, so if you have it backwards, that's not helping. Keeping with my logic, the other aspect to consider if that you want to create a new client, that has to happen first as you have discovered, which cannot happen if you put them on the subform.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    ItsBeenAWhile is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Oct 2016
    Posts
    4
    Quote Originally Posted by Micron View Post
    I'm a little confused as to which is the one and which is the many form. I'd say the main form would be Clients and the sub ought to be Jobs but you have it the other way around. My logic is that each client (one at a time) could have several jobs identified with them, so if you have it backwards, that's not helping. Keeping with my logic, the other aspect to consider if that you want to create a new client, that has to happen first as you have discovered, which cannot happen if you put them on the subform.
    Yes, Micron, my logic is I have one client that can have many jobs.

    I created the clients. Now in my Create Jobs Form, I want to have a combobox with the ClientIds and select the client I want and have the Clients.ID be saved in the Jobs.CLIENTID

  5. #5
    ItsBeenAWhile is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Oct 2016
    Posts
    4
    Quote Originally Posted by aytee111 View Post
    The clients can be a combobox (or listbox) on the Jobs form with the control source set to the ClientID. If you want to display further client information you can use a subform for that which will display only, no entry.
    Hi aytee111, I have tried this. But if I set the control source to Jobs.ClientId the combo will only display the Client Id that is in the current record. I need all the client Ids so I can change it if I need to.

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    A combobox is a list of items, set up in the Row Source property. It will show the current value but you can click on the down arrow to show all clients.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You don't need a subform for the add jobs function unless you want to show all the jobs currently associated with a given client at the same time. In that case, when you first open the form, the subform would have no values because a linked field on the form is not populated. I say this if the subform was to be a list of jobs in a datasheet view and the main form was for adding the job details based on a selected client from a combo.

    First, your combo box has to be unbound for any of this to work (if you posted that info already, I missed it) and it cannot have a control source. Assuming the client info you want to show in the combo is a unique value in the table, the rowsource for the combo would be something like SELECT tblClients.ClientID FROM tblClients. You pick one from the list, and perhaps in the AfterUpdate event for the combo, you fill either/both of the forms by requerying the main (if a field on the sub is linked to the main or both if there is no link) based on the selected value. If you're going to display more than one column in the combo, ensure you're basing the requery on the correct column. The approach should work for attempting to add jobs where there is no client (which I think is an issue you raised in your first post) because if they're not in the combo, they don't exist in the table. What I'm not following is if you're attempting to add the job details in the main or sub so I might have missed something in the approach. To effect this
    select the client I want and have the Clients.ID be saved in the Jobs.CLIENTID
    you'd need the combo to have 2 columns (e.g. ID and ClientName) showing the second column and not the first (never show ID's like autonumbers to the user) and use the AfterUpdate event to populate the desired form control with the first column's value. However, this could cause an issue as the control might be updatable. To be honest, I think I'd take an entirely different approach from your process; more like what I posed in the last sentence of my first paragraph of this post.
    Last edited by Micron; 10-25-2016 at 04:26 PM. Reason: clarification

  8. #8
    ItsBeenAWhile is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Oct 2016
    Posts
    4
    Thank you aytee111 and Micron, with your posts I was able to solve the problem.

    I made the combobox unbound and on the Row Source used my clients table, to display all the records but on the control source I set Jobs.CLIENTID so it would update on the Jobs table.

    Thank you both!

    I have marked the post as solved. Thank you!

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

Similar Threads

  1. Replies: 2
    Last Post: 08-27-2015, 07:38 AM
  2. Replies: 4
    Last Post: 08-25-2013, 07:43 AM
  3. Replies: 7
    Last Post: 04-18-2012, 11:38 AM
  4. Replies: 6
    Last Post: 07-28-2011, 04:07 AM
  5. Replies: 0
    Last Post: 04-17-2008, 09:24 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