Issue Displaying Records in a Unbound Combo box

[martycube]

I having the following issue as outlined in the attached file.

 

[CyberJoe]

I suspect you have a key relationship issue. Not enough info on how the forms are getting the data. Is the form using a query and what are the properties of the combo box? I am not familiar with Allen's code you uses it look like it was setting the combo box up using VBA and meant to refresh the subform. I am not well versed in this but after having issues with my combo box recently I suspect you have some sort of issue with the underlying query. WhenI was getting inconsistent results the issue was the join type I used in the query.

 

[martycube]

CyberJoe

See attached

 

[JHB]

Looking at the thread title, an unbound control can't be used to show data from a table/query, it must be bound.

 

[martycube]

The issue with having the combo box bound is that the lookup table contains a 170K records so the is no way to display all the records since the combo box has a limit of 65K records

 

[JHB]

The issue with having the combo box bound is that the lookup table contains a 170K records so the is no way to display all the records since the combo box has a limit of 65K records

It sound like you are mixing some thing up here, bound it to the corresponding field in the table has nothing to do from where you're getting the data in the list.
Could you post your database with some sample data, zip it because you haven't post 10 post yet.

 

[martycube]

It sound like you are mixing some thing up here, bound it to the corresponding field in the table has nothing to do from where you're getting the data in the list.
Could you post your database with some sample data, zip it because you haven't post 10 post yet.

Because of the size of tblSnowMedICD which has 170K records the file will only zip down to 7M which is over the 2M limit.

 

[missinglinq]

So make a copy of your file, then delete most of the records from the table, then zip that copy down and post i.

Linq ;0)>

 

[martycube]

So make a copy of your file, then delete most of the records from the table, then zip that copy down and post i.

Linq ;0)>

Here is the file.

 

[JHB]

You're using lookup fields at table level, which is not recommended.
Then you also set the first column in the combobox as hidden in the form, (Column.widths = 0 which contain the ID number from the table tblSnomedICD).
Then you further more using the ID number to do some search in the tblSnomedICD.[Snomed Name] which is a text field, (so nothing is found/return) for setting the cmbSurHisName.RowSource, so therefore the list in the combobox is empty.
You need to rethink the setup for the field SurHisName, the only think I can suggest is storing the text instead of the ID number, and ofcause get rid of the lookup field in the table.

 

[martycube]

You're using lookup fields at table level, which is not recommended.
Then you also set the first column in the combobox as hidden in the form, (Column.widths = 0 which contain the ID number from the table tblSnomedICD).
Then you further more using the ID number to do some search in the tblSnomedICD.[Snomed Name] which is a text field, (so nothing is found/return) for setting the cmbSurHisName.RowSource, so therefore the list in the combobox is empty.
You need to rethink the setup for the field SurHisName, the only think I can suggest is storing the text instead of the ID number, and ofcause get rid of the lookup field in the table.

Sorry it has taken this long to reply but have had some personal issues.
1st you are correct about not having a lookup field on a table but for my app it seemed to be appropriate because of the necessity to limit choices to items in the table. This table also contains 170K entries which why I have the function that I am describing below.

You are also correct in that I am storing the ID field in the SurHisName field, but by hiding that column I can search on the Snomed Name field. The reason that nothing comes up is because you have to type 3 or 4 characters before the cmbSurHisName.RowSource is set. You can then select an item from the list, but so that it continues to display you must tab to the next field. Something that I will address once I find the answer to the real issue.

I have attached a screenshot that shows what the field looks like when I have added 3 new records. As you can see the drop-down is showing values to select from.

The issue is when that page loses focus or when you re-start the app the values disappear even though they are still in the table. Some how I have to set cmbSurHisName.Rowsource to some event where the function above still works.

Thanks
Cube

 

[JHB]

1st you are correct about not having a lookup field on a table but for my app it seemed to be appropriate because of the necessity to limit choices to items in the table. This table also contains 170K entries which why I have the function that I am describing below.

Sorry, but I can not see that the above should be the reason for using lookup field type at table level.

..
The issue is when that page loses focus or when you re-start the app the values disappear even though they are still in the table.

Actually the only value there is the table field "SurHisName" is the ID number, but because you've set the fieldtype to lookup field, you'll see the text from the table "tblSnomedICD".

You ask why Surgery/Procedure control doesn't show the text selected when the page loses focus or when you re-start the app.
The text disappear because the list in the combo box is empty, if you comment out the OnCurrent event in the form and set the rowsource to the table "tblSnomedICD" you'll see the selected text appear.
Therefore if you want to see the text, my suggestion was to store the text instead of the ID number.

 

[martycube]

Sorry, but I can not see that the above should be the reason for using lookup field type at table level.

Actually the only value there is the table field "SurHisName" is the ID number, but because you've set the fieldtype to lookup field, you'll see the text from the table "tblSnomedICD".

You ask why Surgery/Procedure control doesn't show the text selected when the page loses focus or when you re-start the app.
The text disappear because the list in the combo box is empty, if you comment out the OnCurrent event in the form and set the rowsource to the table "tblSnomedICD" you'll see the selected text appear.
Therefore if you want to see the text, my suggestion was to store the text instead of the ID number.

Thanks JHB. Once I uncommented the rowsource for the SnomedICD table in the on current event as you said the text appears. That left me with issue where the cmbSurHisName is re-set in the Function ReloadSuburb, So after the lookup field search is complete I re-set the rowsource to the SnomedICD table and the issue was resolved.

Thanks Again
Cube

 

[JHB]

Good you got the problem solved.