Search ComboBox not Working (1 Viewer)

[Tupacmoche]

Hi Form Masters,

I have a combobox created with the Access wizard that works fine except when, I add a new record to the table. Here is the code:

Private Sub CboFind_AfterUpdate()
On Error GoTo CboFind_AfterUpdate_Err

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

CboFind_AfterUpdate_Exit:
    Exit Sub

CboFind_AfterUpdate_Err:
    MsgBox Error$
    Resume CboFind_AfterUpdate_Exit
 End Sub

I have a sproc that adds a row to the table that is linked (SQL 2008 rs) this works fine and is not the issue since the records are always added. The issue is that when I try to select the new record from the combbox a different row is selected. This doesn't effect existing records. But if I restart the application then there is no issue it selects the new record. Clearly, I don't want user to have to restart the application every time they add a new row. Has anyone encountered this issue and know how to fix it?

 

[theDBguy]

Hi. Just before the DoCmd.SearchRecord line, try adding this:

Me.Requery

Hope it helps...

 

[Tupacmoche]

I did and now the combo search box dosn't work at all. I removed it.

 

[theDBguy]

I did and now the combo search box dosn't work at all. I removed it.

Hi. What does "doesn't work" mean? Were you getting any errors? Are you able to post a small copy of your db with test data?

 

[Tupacmoche]

What, I mean is that after adding Me.Requery before DoCmd.SearchRecord line when I used the combobox to select a name from it it did not change the row. Basically, it did nothing.

 

[theDBguy]

What, I mean is that after adding Me.Requery before DoCmd.SearchRecord line when I used the combobox to select a name from it it did not change the row. Basically, it did nothing.

Hi. One way to know for sure what is or is not happening is to step through the code. Are you able to do that? If not, are you able to post a copy of your db, so we can try to do it for you?

 

[Tupacmoche]

I have been googling to find a solution since my post. On the Home Ribbon -> Refresh All works after I add a new row. So, now, I'm need to know what vba command does Refresh All launch so, I can run it from vba. Any ideas?

 

[theDBguy]

I have been googling to find a solution since my post. On the Home Ribbon -> Refresh All works after I add a new row. So, now, I'm need to know what vba command does Refresh All launch so, I can run it from vba. Any ideas?

Well, either Me.Refresh or Me.Requery should do the same thing. That's why I recommended it. See if there's a DoCmd equivalent, like maybe DoCmd.Refresh or maybe DoCmd.RunCommand acCmdRefreshAll, or something like that. Cheers!

 

[CJ_London]

Sounds like your combo is not being requeried - how is it populated? Or perhaps the activecontol is not what you thinking

 

[Tupacmoche]

Hi All,

Finally found the solution. This is the web site that had it:

 [URL="http://www.accessforums.net/showthread.php?t=33098"]http:[/URL][URL="http://www.accessforums.net/showthread.php?t=33098"]//www.accessforums.net/showthread.php?t=33098[/URL]

As, I mentioned earlier when I clicked on 'Refresh All' from the home menu the combobox worked fine. So, I added this line to the AfterUpdate code textbox that is used to add new records:

CommandBars.ExecuteMso "DataRefreshAll"

Now, when, I select the newly added record it works just fine.

 

[theDBguy]

Hi All,

Finally found the solution. This is the web site that had it:

 [URL="http://www.accessforums.net/showthread.php?t=33098"]http:[/URL][URL="http://www.accessforums.net/showthread.php?t=33098"]//www.accessforums.net/showthread.php?t=33098[/URL]

As, I mentioned earlier when I clicked on 'Refresh All' from the home menu the combobox worked fine. So, I added this line to the AfterUpdate code textbox that is used to add new records:

CommandBars.ExecuteMso "DataRefreshAll"

Now, when, I select the newly added record it works just fine.

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.