Results 1 to 9 of 9
  1. #1
    mlrucci is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    91

    Post Combo box changing ID in table

    Good afternoon, I am having trouble with a combo box changing the name to the ID instead of displaying the change in name.
    2 Tables with a one to many relationship
    1. tblClient_MSA that includes Client_MSAID and ClientName


    2. tblSOW with SOWID and Client_MSAID

    I created a form from the tblSOW that I want display the name of the Client_MSA. Form includes Client_MSAID (textbox), SOWID (textbox) and ClientName (as combo box to change the MSA associated with the SOW if needed). When I change the selection of the combo box from one name to another, it changes the name to the MSAID in both tables. But, as expected, if I changed the ClientMSAID in the form query, then it appropriately changes the name in the qry and in the tables

    Information:
    Form:
    RecordSource: SELECT tblSOW.SOWID, tblSOW.ClientMSAID, tblClient_MSA.ClientName FROM tblClient_MSA INNER JOIN tblSOW ON tblClient_MSA.ClientMSAID = tblSOW.ClientMSAID;



    Combo Box:
    ControlSource: ClientName
    RowSource: SELECT tblClient_MSA.ClientMSAID, tblClient_MSA.ClientName, tblClient_MSA.ClientMSActive
    FROM tblClient_MSA
    ORDER BY tblClient_MSA.ClientName DESC;
    Click image for larger version. 

Name:	cboProperties.PNG 
Views:	15 
Size:	7.9 KB 
ID:	44338

    Trouble Shooting completed:
    1. There are no hidden relationships.
    2. Thought form was corrupt and tried it on a different form.
    3. Possible DB corrupt, Imported tables, forms and qry to new database

    Very basic. I am stumped. Any help would be greatly appreciated.

  2. #2
    Welshgasman is offline Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    622
    That is how it should work, if I understand you correctly.?
    You use the ID, NOT the name. What if the name changes?, someone gets married etc?

    You show the name, but store the ID of the record.
    What is the bound column?
    Please, please use # when posting code snippets.
    Cross Posting Etiquette: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,660
    Try changing the combobox control source to ClientMSAID

  4. #4
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    830
    check the bound column
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  5. #5
    mlrucci is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    91
    My apologies if I was not clear. Sometimes things are clear in my mind and not to others. Problem with getting old. lol Hopes this clarifies.

    Click image for larger version. 

Name:	TableValues.PNG 
Views:	14 
Size:	10.2 KB 
ID:	44340
    Attached Thumbnails Attached Thumbnails tblChanges.PNG  

  6. #6
    mlrucci is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    91
    OMG, I cant believe how stupid I was. Sometimes, the easiest thing is overlooked. Didn't notice I did that. Thank you very much!!! It worked

  7. #7
    Welshgasman is offline Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    622
    You do not store the name, you have the ID ? hence, you can retrieve the name when needed?
    Please, please use # when posting code snippets.
    Cross Posting Etiquette: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  8. #8
    mlrucci is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    91
    I was placing the Client_MSAName as the ControlSource and not the Client_MSAID, pure oversight and lots of frustration!! Thanks for you help!!!

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,660
    Quote Originally Posted by mlrucci View Post
    I was placing the Client_MSAName as the ControlSource and not the Client_MSAID, pure oversight and lots of frustration!! Thanks for you help!!!
    Glad to help. Good luck with the project and thanks for the star!

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

Similar Threads

  1. Combo box changing text box values
    By MikeEmerald in forum Access
    Replies: 13
    Last Post: 02-20-2017, 07:49 AM
  2. Replies: 3
    Last Post: 12-07-2016, 10:17 AM
  3. Replies: 1
    Last Post: 11-23-2015, 07:18 PM
  4. Replies: 1
    Last Post: 09-23-2014, 08:56 AM
  5. Access form combo box value changing
    By justrizlee in forum Forms
    Replies: 1
    Last Post: 12-22-2010, 08:56 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 - Senior Forums