- Joined
- Jul 30, 2017
- Messages
- 2
- Reaction score
- 0
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].
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.
Also, someone posted [here] and [here] about this [2 and 3]. But none of the answers seem relevant to my needs. I'm not sure if me.filter = "" applies to my case or where I should put it. The problem doesn't occur on open or close of form. Just on changing form view and record nav.
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/F...esh-when-moving-to-new-record?forum=accessdev
[2] https://www.pcreview.co.uk/threads/clear-filter-on-closing-a-form.3164143/
[3] https://www.pcreview.co.uk/threads/how-to-clear-a-filter-in-property-sheet.1154606/
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].
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.
Also, someone posted [here] and [here] about this [2 and 3]. But none of the answers seem relevant to my needs. I'm not sure if me.filter = "" applies to my case or where I should put it. The problem doesn't occur on open or close of form. Just on changing form view and record nav.
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/F...esh-when-moving-to-new-record?forum=accessdev
[2] https://www.pcreview.co.uk/threads/clear-filter-on-closing-a-form.3164143/
[3] https://www.pcreview.co.uk/threads/how-to-clear-a-filter-in-property-sheet.1154606/
