UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Combo Box Not Displaying Values In Datasheet, Access 2016    
 
   
blueman2
post Aug 22 2019, 12:43 PM
Post#1



Posts: 224
Joined: 15-November 14



I have a datasheet form (actually subform) and have added a combo box (Select Buyer) to pull up records based on a criteria. That us all working. The control source is being populated with the correct values when a list record is selected as I can see them in the relevant table. However, there's something wrong somewhere, maybe in the event code (I'm guessing) because the values selected do not show when I click into a record that has different values. Then those different values show. As soon as I click back into a record (actually into the combo box for that record) all the other records show again that have that same value but the others, just previously selected don't appear. I'll try to provide some screen shots and some code. I'm sure you'll need more.

Here you can see that when I select Ken in that record, all records with Ken show
Attached File  Image_36.jpg ( 126.94K )Number of downloads: 0



When I select Marcia in a different record, all records for Marcia show but those for Ken do not. And to be clear, If I select another buyer for that customer, that different buyer will show along with any others selected from that customer. If I click back into the any combo box that has a record stored, all combo boxes for that customer will display but no others will.
Attached File  Image_37.jpg ( 165.75K )Number of downloads: 1


The Customer & Buyer tbls are related by CustomerID
Here is the design view of the combo box (Select Buyer)
Attached File  Image_39.jpg ( 186.91K )Number of downloads: 2



The bound column for the combo box is column 1 and the control source is BuyerContactID
On the "Got Focus" event I have:

CODE
Private Sub cboSelectBuyer_GotFocus()
Me.cboSelectBuyer.Requery
End Sub


I've tried other events and this one at least brings up the proper records in the combo box. I've tried the same requery event on the form and subform itself but it doesn't seem to matter.

Any help would be greatly appreciated!





Go to the top of the page
 
Doug Steele
post Aug 22 2019, 02:13 PM
Post#2


UtterAccess VIP
Posts: 22,191
Joined: 8-January 07
From: St. Catharines, ON (Canada)


See whether the third item in my article Tricks with Combo Boxes helps.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
dale.fye
post Aug 22 2019, 02:28 PM
Post#3



Posts: 160
Joined: 28-March 18
From: Virginia


Strongly recommend against using combo boxes in datasheet view, and especially if you are going to refilter the combo box rowsource. Personally, I prefer to write the query for the datasheet so that it contains the description field associated with the value that is stored in the main table. Then I use either a button or a double-click event of the form (record selector) to popup another form for actually editing or adding a record to the base table.

The rowsource you apply to a combo box displayed in either a continous form or a datasheet applies to all of the records that are displayed on the form. So, if the rowsource filters on some other field in the record then all of a sudden, any of the other rows (records) displayed in the continuous form or datasheet which do not have the same value in the field that is the criteria for your combo box will display as blank, even though they actually contain a value.

The best way to see this is to copy your datasheet form and then change the form style from datasheet to continuous. This will involve cutting all of the labels and placing them above the controls, in the form header. Then copy the control that you are currently displaying as the combo box, place this control immediately below the combo and change the control type from combo to textbox. Then resize the detail section of the form so that you can display multiple records.

When you do this, select the record and then set focus to the combo box. You will see that the textbox displayed below the combo will continue to display the value assigned to that field in that record, but the combo box on the rows which don't have the same value in the field that is used in the criteria string of the combo will display blanks. This is because the Rowsource for those combos no longer contain the value that is bound to the field.

Dale Fye

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
theDBguy
post Aug 22 2019, 02:38 PM
Post#4


Access Wiki and Forums Moderator
Posts: 76,290
Joined: 19-June 07
From: SunnySandyEggo


Hi. This is a known issue as indicated in Doug's article and Dale's post above. My recommendation is to validate the combo choice rather than filter it. You could maybe modify your code to re-sort the choices, if it will help with the selection.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
blueman2
post Aug 22 2019, 03:03 PM
Post#5



Posts: 224
Joined: 15-November 14



Dale,

Thanks for the input. There's a lot to digest there. It does explain why I'm having the problem, but in a nutshell, are you just suggesting that I make a continuous form (pulling the same info) and have a separate button that pops up the combo box choice after which that value selected will go into the BuyerContactID text box?
Go to the top of the page
 
blueman2
post Aug 22 2019, 03:05 PM
Post#6



Posts: 224
Joined: 15-November 14



Hi DB,

Congrats on the milestone.

Are you saying that as opposed to what Dale is suggesting, there might be a way to keep what I have with some type of code that validates the choice in the current combo box?
Go to the top of the page
 
theDBguy
post Aug 22 2019, 04:45 PM
Post#7


Access Wiki and Forums Moderator
Posts: 76,290
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (blueman2)
Hi DB,

Congrats on the milestone.

Are you saying that as opposed to what Dale is suggesting, there might be a way to keep what I have with some type of code that validates the choice in the current combo box?

Hi. You may have misunderstood what Dale was saying. However, what I was saying is rather than use the code you currently have to "filter" the dropdown choices, maybe you can replace it with one to "validate" the choice made instead. As an added idea, I thought maybe you could change your current code to "sort" the choices as if they were filtered. For instance, you could push all the non-valid choices to the bottom of the list, so the user can easily see the ones they are supposed to pick. When I do this, I usually add a "tag" to the invalid choices to indicate the user is not supposed to select them.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
blueman2
post Aug 23 2019, 04:03 PM
Post#8



Posts: 224
Joined: 15-November 14



I finally had some time to re-readM Dale'comment and I understand it now. I'm not sure I understand the validation but I did find another solution that worked for me. I adapted from this video. I think it closely relate's to dale's solution

https://www.youtube.com/watch?v=GopSdt4QNck...eature=youtu.be

I basically created a faux button with the BuyerContactId next to it and it seems to be working.

Thanks

Attached File  Image_46.jpg ( 107.67K )Number of downloads: 0
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th September 2019 - 10:00 AM