Solved Bound combo box value not displaying on continous form or datasheet (1 Viewer)

mib1019

Member
Local time
Today, 03:21
Joined
Jun 19, 2020
Messages
88
Hello,

I have a subform called Journal Subform, with a combo box called cboContact_ID. The same subform is used on two different main forms. The two main forms hold company information, including a Company_ID.

I have VBA filling the combo box on the subform with the SQL statements, varied only by referencing the current main form name to get the COmpany_ID value, so that the combo filters for contacts only associated with that Company. The Got Focus event on the control runs the VBA. It works correctly.

The combo box itself has two columns: Contact_ID, which is the bound value, and Contact_Name, which should be displaying on the subform. The column width value is 0;1.75".

That may not have anything to do with the problem I see, in that (NOT ALWAYS), the name doesn't actually display. If I click in the field, it displays the Contact_Name for the Contact_ID value that's been recorded for that record.

I also have a split form that is just the complete Journal. All of the Contact Names display properly. So it's just on the subforms that the names are disappearing. I can't figure out why.

Hope you can help me. If so, thanks in advance!

MIB1019
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:21
Joined
Oct 29, 2018
Messages
21,357
Hi. That is expected behavior. You can overlay a bound Textbox on top of your Cascading Combobox to display what's actually stored in the field.
 

June7

AWF VIP
Local time
Today, 01:21
Joined
Mar 9, 2014
Messages
5,423
This is a known issue with cascading combobox using alias display. When list is filtered the alias value is not available for display because item is not in the list. Common topic and many discussions. What's actually stored in field is a number and hardly useful to users. One approach is to only filter the list for new record entry or when the primary data is edited then return to the full list after combobox selection.

Another is to include lookup table in form RecordSource, bind textbox to lookup table field and set Locked Yes and TabStop No.

Might also disable combobox until input/edit of primary data.
 
Last edited:

mib1019

Member
Local time
Today, 03:21
Joined
Jun 19, 2020
Messages
88
But is it a cascading combo box by virtue of it getting the Company_ID value from the main form? There isn't a combo on the subform that feeds it's value into the SQL that populates it?

Thanks!
MIB1019
 

June7

AWF VIP
Local time
Today, 01:21
Joined
Mar 9, 2014
Messages
5,423
It's 'cascading' because it is filtered based on value input to another control. AKA 'dependent' combobox.

Is Company_ID the primary and foreign key values? If yes, then this value is available in the subform.
 

mib1019

Member
Local time
Today, 03:21
Joined
Jun 19, 2020
Messages
88
Here's what I've done:

Changed the row source of cboContact_ID combo not dependent on the Company_ID.
On Enter event of the cboContact_ID, uses an SQL string to filter the combo dependent on the Company_ID.
On Exit event of the cboContact_ID, reset the rowsource to its 'default' state, not dependent.
The Contact Name shows on all the rows now.

Are there any pitfalls that I can expect from this approach?

Thanks for your help!
MIB1019
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:21
Joined
Oct 29, 2018
Messages
21,357
Here's what I've done:

Changed the row source of cboContact_ID combo not dependent on the Company_ID.
On Enter event of the cboContact_ID, uses an SQL string to filter the combo dependent on the Company_ID.
On Exit event of the cboContact_ID, reset the rowsource to its 'default' state, not dependent.
The Contact Name shows on all the rows now.

Are there any pitfalls that I can expect from this approach?

Thanks for your help!
MIB1019
Glad to hear you got it sorted out. The problem should still be there, but maybe not so obvious now. Good luck with your project.
 

mib1019

Member
Local time
Today, 03:21
Joined
Jun 19, 2020
Messages
88
Well, that didn't work after all, just as you suggested. I went the route of the textbox on top of it, locked and with no tab stop. Left the enter and exit events on the combo box. It seems to be fine now.

Thanks again...you guys are great!
MIB1019
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:21
Joined
Oct 29, 2018
Messages
21,357
Well, that didn't work after all, just as you suggested. I went the route of the textbox on top of it, locked and with no tab stop. Left the enter and exit events on the combo box. It seems to be fine now.

Thanks again...you guys are great!
MIB1019
Right. That's the typical approach. Cheers!
 

Users who are viewing this thread

Top Bottom