SearchForRecord via ComboBox stopped working

[Foe]

I have a form that can be opened in one of two ways. Either unfiltered by using a Command Button or filtered by selecting a Record from one of two List Boxes and then clicking the Command Button.

Once the form is open in either filtered or unfiltered mode, I have a Combo Box that allows for moving to different Records from within the form. Initially (as best I can remember) this worked as intended. The Combo Box was navigating Records with a Macro Procedure that would first ShowAllRecords (to remove the filter if one was in place) and then perform a SearchForRecord with a Where Condition:

="[*mailID] = " & Str(Nz([Screen].[ActiveControl],0))

When initially put together this worked as intended. It's been a few weeks since I put it all together and I just recently revisited it to find it no longer works. When anything is selected from the Combo Box the form always goes to the last record. I can see the filter being removed, but I can't get it the form to display the selected record.

I've tried converting the existing macro to VBA, writing VBA from scratch and reverting to an older version of the database.

VBA methods I've tried include:

DoCmd.FindRecord Me!cboSelect*mail

DoCmd.FindRecord cboSelect*mail.Value

DoCmd.SearchForRecord , , acFirst, "[*mailID] = " & Me!cboSelect*mail

DoCmd.SearchForRecord , , acFirst, "[*mailID] = " & Str(Nz(Screen.ActiveControl, 0))

DoCmd.SearchForRecord , "", acFirst, "[*mailID] = " & Str(Nz(Screen.ActiveControl, 0))

DoCmd.SearchForRecord , , acFirst, "[*mailID] = " Me![cboSelect*mail].Column (1)

Things relating to that form that have changed since I last know it to be working:
- The list boxes used to select a record for use with the command button had a double-click method created to skip the pressing of the command button. That code resides on a different form.
- The database (mysteriously) lost weight. I've been forwarding revisions of the DB as I go to the office and it typically weighed in at 3mb. At one point I noticed it starting to weigh in at 2mb. Unfortunately, I no longer have a copy of it when it weighed in at 3mb. I'm not sure what caused the reduction in file size. I've only ever added to this thing. There is minimal data present - only enough to test functionality as I build it in.

Things that may be relevant:
The form I'm working with has a query as it's Record Source and the Recordset Type is Dynaset.
The Combobox in question is unbound with a Row Source from the same query as above.

Around the same time I noticed the problem with the Combo box, the intellisense feature within the VBA editor starting acting screwy. In fact, it was while trouble shooting the combo box (my first attempt at a fix was trash the macro and work in VBA for more control) that I noticed the problem with intellisense. When typing in new code, for example "DoCmd.", as soon as I pressed the period key, the intellisense window would pop up for a brief moment with a list of possible things to follow and would then disappear and "DoCmd." would turn red.

I went into the Editor's setting and turned all features off and back on and that solved the issue for about 10 minutes. I've yet to conquer that one. In fact, I gave up on it and reverted to the previous revision of the DB which still had a functioning intellisense feature. I'm way too new to VBA to not have that feature there holding my hand.

Anyway, sorry for the novel, but I wanted to try and express this issue as clearly as possible. I'm assuming that something somewhere is interfering. It's also possible that I've gotten the code all jumbled up in my many attempts to try different ways to achieve what I'm after.

Any help you can provide would be most appreciated. If further clarification or additional information is needed, I'll try to respond as quickly as possible.

Thanks!

P.S. I'm not really using wildcards in the code, but until I have 10 posts, I'm can't use the word "email" within code tags

 

[Foe]

Quick addendum - I found a copy of the 3mb variant and the combobox isn't working there either. I'm starting to question if it ever worked... have I lost my marbles? I would not have moved on from a feature that wasn't working or at least made a note to return to it.

 

[Foe]

I've been poking at this to try and figure it out on my own and I've made a discovery.

the following code works if I get rid of DoCmd.ShowAllRecords

DoCmd.SearchForRecord , , acFirst, "[EmailID] = " & Str(Nz(Screen.ActiveControl, 0))

If I enter the form w/o filters turned on (click the command button to load the form w/ no selections in the previously mentioned list boxes) I can navigate via the combo box. But, if I enter the form with a filter in place (selecting a record via list box), I am unable to change records since the filter remains.

So it appears that ShowAllRecords is the culprit, which raises the question of how do I turn the filter off to be able to use the combo box in either scenario?

 

[Foe]

Looks like I've managed to solve it on my own. Yay me! Instead of turning the filter off, I've changed the filter to show what I'm after.

    Me.Filter = "[EmailID] = " & Str(Nz(Screen.ActiveControl, 0))
    Me.FilterOn = True

 

[Foe]

Figured out my intellisense issue as well! I had a timer event turned on that was firing every second. Whenever the timer fired, the VBA editor was examining the syntax of the code I was working on as if I had moved onto the next line. When i changed the timer to a larger interval, the odd behaviour stopped occurring.

 

[N1755L]

Thanks for the details/info of your solution(s).

If everyone could do this, there'd be a lot more successful Google searches when people are trying to find a solution to their coding problems.