Results 1 to 11 of 11
  1. #1
    kwolfel is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Posts
    15

    Issue with Combo Box Showing Correct Row Source Value

    I am having an issue with what should be an easy thing. I believe I have everything listed correctly in the row source of my combo box. I am using the row source query to pull from tbl_Equipment and create a column in the query that combines the equipment name followed by the description.



    Basically, I want the user to be able to see three columns in the combo pull-down (Unit, EquipmentName, and EquipmentDetails), choose the applicable equipment, and then have it fill in the data of the combined query column that was created.

    However, when the equipment is chosen in the pull-down, it fills in the box with "D2." This is one of the units in column 1 of the row source query. It will only fill it in with that value and does not change the unit number if a different piece of equipment is chosen. I thought it may be a unique value issue so I tried adding in the unique key as an unshown column but this didn't seem to make a difference.

    Row Source Query Details:
    Column 1 (0) is Unit.
    Column 2 (1) is EquipmentName.
    Column 3 (2) is EquipmentDetails.
    Column 4 (3) is EquipmentName; EquipmentDetails (Combined in row source query).

    I have set the following properties:
    Column Count: 4
    Column Widths: 0.5";1";1";0" (Only want user to see first three columns)
    Bound Column: 3

    Here is my row source code:
    Code:
    SELECT tbl_Equipment.Unit, tbl_Equipment.EquipmentName, tbl_Equipment.EquipmentDetails, [tbl_Equipment]![EquipmentName] & "; " & [tbl_Equipment]![EquipmentDetails] AS Equipment FROM tbl_Equipment;
    Can anyone see any major ways I am going wrong here?

  2. #2
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    It is possible the semicolon imbedded in the 4th column is confusing the control into thinking there's a 5th column. Try turning that semicolon into a slash or dash or dollar sign and see what happens.

  3. #3
    kwolfel is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Posts
    15
    Thanks for the recommendation. I tried replacing the semi-colon with a dash but it had no affect on the value showing in the combo box.

    I attempted to go around this issue by creating a query that combines the two fields before going into the row source. I updated the row source query to pull the fields from the new "combined" query. While the "combined" query shows everything correctly, the combo box is still having the issue of showing only "D2." I also re-incorporated the unique key to ensure that was not causing an issue.

    Incorporating the new query updates my row source code to the following:
    Code:
    SELECT qry_EquipmentCombined.ID, qry_EquipmentCombined.Unit, qry_EquipmentCombined.EquipmentName, qry_EquipmentCombined.EquipmentDetails, qry_EquipmentCombined.EquipCombined FROM qry_EquipmentCombined;
    New Row Source Query Details:
    Column 1 (0) is ID.
    Column 2 (1) is Unit.
    Column 3 (2) is EquipmentName.
    Column 4 (3) is EquipmentDetails.
    Column 5 (4) is EquipCombined.

    New Properties:
    Column Count: 5
    Column Widths: 0";0.5";1";1";0" (Only want user to see three columns)
    Bound Column: 4

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Does column 5 really show EquipmentName; EquipmentDetails or is it EquipCombined?
    For clarity,you do not have Lookups at the field level in your table - is that correct?

    Perhaps you could post a copy of your database -- no private/confidential data.

  5. #5
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742

    Additional info Needed

    Let me type slowly, because I'm not sure what I'm about to ask... SHOWING? Weird little jangly noises went off in my brain at that word, and I had to do some digging to see whether I am confused or not. I still don't know, so I MUST be.

    Okay, this next exercise should clear something up for both of us.

    First, in the AfterUpdate event of your combobox, add a line like
    Code:
    MsgBox comboboxname.value & " " & comboboxname.text
    See what values come up in the message box.

    Next, check the ControlSource property of your combobox. if it's not unbound, supposing it specifies "=George.Frank", change the above to
    Code:
    MsgBox comboboxname.value & " " & comboboxname.text & " " & George.Frank
    I've read MS's description of Value, Text, ControlSource and a couple other properties of a combobox control, and I don't see where the BoundColumn of the control affects the Value and Text values of the control. I would EXPECT it to, but I don't know that it does. By all that is holy , the Text and Value properties should be updated with the value of the bound column, but the specs say that that value is stored in the ControlSource, and DON'T explicitly say it's also stored in the control, so we need to verify that.

  6. #6
    kwolfel is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Posts
    15
    Hi Orange,

    Column 5 of the row source query shows EquipCombined, which is a field from query "qry_EquipmentCombine" that combines two fields to show "EquipmentName; EquipmentDetails." Additionally, I do not have any lookups in my table.

    I have attached my database for review. The form in question is "frm_CableList." The two fields that are showing this issue are "Source Component" and "Destination Component."

    Database_Working - Copy.zip

  7. #7
    kwolfel is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Posts
    15
    Hi Dal,

    My combo box control source is bound and, as I stated above, I specified the bound column to be Column(4). When I add the message box code in AfterUpdate to show Column(4) of my combo box, I correctly receive the combined equipment name/description. There should be no reason as to why the combo box does not also show this equipment name/description when I choose an option from it.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I tried to open your database and received attached message.

    When I add the message box code in AfterUpdate to show Column(4) of my combo box, I correctly receive the combined equipment name/description.
    I don't see or get any message box???
    Attached Thumbnails Attached Thumbnails ErrorOnOpeningDatabase.jpg  

  9. #9
    kwolfel is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Posts
    15
    Hi Orange,

    It should still let you look at everything. That error message shows up because a form is suppose to open up when the user enters the database. It contains project and database revision information. I removed the form due to the information it contains about the project.

    Nonetheless, I believe I have figured out the issue. I happened to stumble upon a similar thread under reports. Apparently, Access is set up to show the data in the first column that is greater than 0" width. I moved my combined column to the beginning of the row source query, updated the bound column, and gave it a small width (since I don't want the user seeing it in the pull-down). Now the form is correctly showing the description that I wanted. Thank you for the help everyone!

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  11. #11
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Makes sense. So "Text" and "Value" are the bound column, as expected to be, but the control displays the farthest left visible column. Good to know.

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

Similar Threads

  1. Replies: 3
    Last Post: 12-11-2012, 09:12 AM
  2. Combo Box NOT saving correct value
    By supracharger in forum Forms
    Replies: 8
    Last Post: 06-03-2012, 02:25 PM
  3. Combo Box not showing the correct Field
    By Atlascycle in forum Reports
    Replies: 61
    Last Post: 03-12-2012, 10:51 AM
  4. Text Box Control Source Issue
    By timbit6002 in forum Forms
    Replies: 8
    Last Post: 03-01-2012, 02:03 PM
  5. Can System Restore correct Form Size issue
    By fordtough in forum Forms
    Replies: 0
    Last Post: 04-12-2011, 02:56 PM

Tags for this Thread

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