Need combo box to refresh when moving to new record 2017

Question

Since the last thread on this subject hasn't been updated sine 2010, I'm starting a new one. This previous thread which I reference below came close to helping me but I need more info. (Not that it makes a difference for this particular issue but I'm using Access2016.) 

I have a form (product skus and their respective details) with a combobox that selects and displays info for a specific sku in my form. The combobox works and selects the correct record. 

The problem with the combobox is it 1) doesn't update when another record is selected with next or previous arrows 2) doesn't clear out after change 3) stays on last value after view change from form to design view. I need for either #1 or #2 to work. And if #3 can work too, that would be the biggest win ever!

I know people usually keep combobox filters in a form header and just display the data below. But this will not work in this case because it will encourage data entry errors. It's just one combobox prominently displaying a sku number. Even if I also display the sku number down on the form, the records and the filter will still get out of sync and cause confusion. 

(My form record source is Tbl_UniqueSku. This table mostly stores foreign keys, so my combobox row source is based on a query. That query consists of Tbl_UniqueSku.ID | Tbl_Category.CategoryCode | Tbl_PartNumber.PartNo | Tbl.PartNumber.PartName. This has nothing to do with why the data displayed in the combo doesn't sync with the form when the records are advanced through buttons or record selectors. I tried a dummy simple form based only on one table with a combobox filter based on a single field from that same table and I have the same issue.) 

#1:

The data in this cbo selector (the sku number) does not update if I navigate to a different record in the form with other record selection buttons. 

So the sku displayed in this combobox is out of sync with my form unless I'm only using the combobox for navigation.

My skus are chronological: 1001, 1002, 1003 etc. So it doesn't make sense to exclusively use the combobox to navigate to each subsequent record. It makes sense to have the combobox AND next/previous arrows as options to navigate to the desired sku. 

I have tried macros and vba in all the event properties I can think of for both the form and the cbo. I've tried every variation of requery, refresh, value = null, value = id. No results.

#2:

If the combobox cannot reflect the current record then I would at least like NO value to display after it is updated. Whatever it takes for it not to display a different sku from the form record is a result I will settle for. I've tried everything I can think of to attack the problem from this angle and am also coming up empty. 

#3:

An additional issue is when I switch back and forth from form view to design view the combobox filter for this form stays on the last selected value, while the form always displays the first record from my table. So again, these two are out of sync. I'm not ready to split out a front end and back end yet because I'm still designing the database but I also have to do the data entry. Splitting it would make that less efficient for me at this point. 

I've researched this issue for longer than I care to admit. I've found a few people addressing this issue but no acceptable solution. Or maybe the programming solutions I've come across are valid but I'm just to dense to apply theory as practice. I need a syntax to reference. 

Here's the closest help I've found in my research. [1] https://social.msdn.microsoft.com/Forums/office/en-US/fded19ff-b578-4799-a4a9-f811b91dd7fb/need-combo-box-to-refresh-when-moving-to-new-record?forum=accessdev

The commenter who's advice sort of half way helps is bhammerstrom who says, "What I do is: in the AfterUpdate event of the combo, un-hide a text box (me.txt.visible = True) that is placed directly in the same spot at the cbo (except leaving the down arrow visible). The text box is bound the field in the subform to display the current info."

I tried this but the focus is still on the cbo after change and so the un-hidden text box appears behind the combobox until I move my cursor to another field. I've taken a crack at it but can't figure out how program the cbo to loose focus after update. Googled forever. Nothing. If I could get that to work it would suffice for #2 but I'm still empty handed on #3. 

The other answers on this linked thread sounded promising but when I tried what I could from the info provided I had no luck. It could be good advice but I'm too dumb to use it and need more thorough instruction and hopefully a syntax reference. 

The only thing I can think of that I haven't tried is to make a new column in my Tbl_UniqueSku and just store null values there. Then in my combobox row source query I can add that field. And then in my form properties I can make that null storing column the display column for the combobox, so it's always null after a selection. But this workaround seems like total sacrilege to everything a database is supposed to stand for. 

Please help me!

[1] https://social.msdn.microsoft.com/Forums/office/en-US/fded19ff-b578-4799-a4a9-f811b91dd7fb/need-combo-box-to-refresh-when-moving-to-new-record?forum=accessdev

Answer 1

Try creating an On Current event procedure for the form:

Private Sub Form_Current()
    Me.ComboBoxName = Me.ID
End Sub

where ComboBoxName is the name of the combo box, and ID is the field in the record source of the form that corresponds to Tbl_UniqueSku.ID.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

In addition to the suggestion by Hans, you can also include a blank value for the query by using a UNION query:

SELECT '' AS Customer, '' AS Customer_Code FROM Customers UNION SELECT Customer, Customer_Code FROM Customers ORDER BY Customer

Then refresh the combo as needed to show the blank value. One way to do that is:

Me.YourCombo.RowSource = Me.YourCombo.RowSource

---

-- Scott McDaniel, Microsoft Access MVP

Answer 3

You'll find an example of synchronizing a navigational combo box with a form's current record, using code in the form's Current event procedure as described by Hans, in FindRecord.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

The first option in the opening form is the one appropriate to your situation.  Note that the combo box is also requeried and synchronized with the current record in the form's AfterUpdate and AfterDelConfirm event procedures.  This ensures that the combo box reflects any deletions or insertions of rows via the form, or any changes to existing data which would require corresponding changes to the values in the combo box.

There is no need for the combo box's RowSource to return an empty row as the value of the combo box will be Null when at an empty or uncommitted new record in the form.  Otherwise it will always show the correct value for the current record.

The use of a 'hybrid' control, in which a text box is superimposed on a combo box, is not related to this issue.  This solution is used when one or more correlated combo boxes are used in a continuous form, and prevents the apparent, though not actual, loss of values in some of the rows when the form is at a row in which the current restriction in the combo box's RowSource property prevents the control from mapping to the relevant non-key text value when the control's value is a hidden 'surrogate' key.  You'll find examples in ComboDemo.zip in my same OneDrive folder.

---

Ken Sheridan, Stafford, England

Answer 4

Thanks this was basically it! For me it turned out to be:

Private Sub Form_Current()
    Me.ComboBoxName = Me.TextBoxName
End Sub

 

My text box references the PK of my UniqueSkus table. The magic was all in the =. I think I was trying to tell it to requery or refresh instead of using =.