Combobox as record selector is blank and does not update with navigation controls

[Freeflow]

[Solved] Combobox as record selector is blank doesn't update with navigation controls

I have a form with a subform (dataset). I have followed this advice

https:// support.office.com/en-us/article/Enable-users-to-find-a-record-by-selecting-a-value-from-a-list-e3ed7711-433a-4931-9cab-b0f71a90c329

to add a combo box such that the dataset in the subform shows records related to the value in the combobox (I added spaces after the // so I could post here)

However I am now faced with two problems.

1. If I close the database then reopen it and then open the form the combo box is blank and not showing the first record although the dataset in the subform does show the records corresponding to the value that should be in the combobox (first record in the source used for the combo box).

2. The combobox does not update in response to the navigation controls but the dataset does correctly update.

This combo box to select a record seems a standard requirement so what else am I doing wrong.

The two underlying tables are GenericName and Composition and there is a 1 to many relationship between the row ID in GenericName and the NameID field in the composition table.

 

[sneuberg]

If the combo box is not being populated when the form loads then I suspect there's something wrong the the combo boxes' row source. Would you go to the combo boxes' properties click on the dots ... on the right of the row source and see what you have. If you can see what's wrong go to the SQL view and post the SQL. Maybe we'll be able to see what's up.

 

[sneuberg]

On the other hand it might be faster if you start over and recreated the combo box with the wizard. Maybe you made an incorrect choice in the process of creating it.

 

[Grumm]

Maybe the column shown in the combobox is an empty one.
Like sneuberg said, show us the sql behind the combobox.
You can also display the headers ("ColumnHeads") to see what data should be displayed in the combobox.

 

[Freeflow]

When you follow the linked to procedure then the resulting combo box is unbound

Control Source is empty

Row Source has a Select statement of the type

SELECT [lstSM-Names].[SM_Man], [lstSM-Names].[Tradename], [lstSM-Names].[SM_PhEur], [lstSM-Names].[PhEurNo] FROM [lstSM-Names];

Row source/type is Table/Query

 

[Freeflow]

Maybe the column shown in the combobox is an empty one.

The first column [SM-Man] above is fully poupulated and I'd already checked that there are no empty records.

 

[sneuberg]

I think I misunderstood you in that I thought you were saying the combo box wasn't populated with the records, but now rereading your post it seems your complaint is that the combo boxes' current selection doesn't reflect the record being displayed. I went through the procedure in your posted link and that wizard doesn't give that sort of the result you want. So we need to do something more to get that functionality.

The selection that appears in the combo box depends on what the bound column is set to so somehow we need to set that. I'm going to have to play with this a bit, but maybe one of the other forum member can tell you what to do right away.

 

[sneuberg]

I have a way to achieve what I think you want, but before you go through this I’d ask you to consider if you really want to do this. Whatever is displayed in this combo box should be displayed somewhere on the form already so what’s the point of displaying it twice. Secondly this will set the selection of the combo box during navigation. So if you click on Next Record and go through half the records the combo box will be scrolled down to that record when you go to use it. I’d think you would want it at the first record. But here’s what you can do if you want this.

1. Backup your database
2. Go to the properties Other Tab of the combo box and note the name. Let’s say it’s Combo1 for these instructions
3. Go to the properties Data Tab of the combo box and noted the Bound Column. Let’s say it’s 1 for these instructions
4. Note the name of the bound column from the row source. From your previous post if the Bound Column is 1 then it would be the first field in the select statement or SM_Man.
5. Go to the form’s properties, Event tab, click on the far right of the On Current ; the dots …
6. On the Choose Builder form select Code Builder and click OK
7. Insert a line after the Private Sub Form_Current() and type:

Me.Combo1 = Me.Recordset![SM_MAN]

where Combo1 is whatever the name of the combo box really is and SM_MAN is the bound column’s name.

Let us know if this works for you.

 

[Freeflow]

That's exactly what is needed.

When the form is first opened after loading the database the first record is shown in the combo box.

The combobox tracks the recordset displayed by the navigation buttons.

Selection using the combobox selects the correct record. The pull down list from the combobox always starts from the first record (not what you imply above).

After selection from the combobox the navigation button work as expected (you don't jump to an unexpected record, or a record adjacent to the record you had before you used the combobox.

Many thanks for your assistance.

 

[Freeflow]

Just an update on this solution. The form data source was a Table. This led to an anomalous behaviour in that the pull down list for the combo box was in alphabetical order BUT the navigation buttons were working on ID number.


This anomalous behaviour was corrected by changing the source of the form from a Table to a Query of the Table Ordered by the Combobox field.

This wasn't noticable initially as the ID and Alphabetical sequence were identical (as that's how the table was created initially). The anomaly only became apparent when referencing items I'd added at the end of the table since its initial creation and thus where ID was not equivalent to alphabetical order.

I hope that's clear.