Requery a listbox when switching from tabs 3

[mraetrudeaujr]

This thread relates directly to my previous thread, but maybe I was being too vague.

I am using a listbox to display the results of a query that is run when the 'tabbed' form is first opened. What I want to do is to be able to 'requery' or 'update' the results of the display of results, after a user has entered in a few more records. I realized that I could just have the user close and then reopen the form/database, but I have to automate this application as much as possible. Any help will be greatly appreciated. Thank you.

 

[PHV]

The ListBox object has a Requery method, so where is the problem ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

Hi
It is usually best to just add to an existing thread. Have you looked at (?):
[tt]Me!ListboxName.Requery[/tt]

If this is not what you mean, a few notes on the names of the form and controls may help.

 

[mraetrudeaujr]

Remou:Yes, I looked at the previous threads and couldn't find anything that fits my situation.

PHV. Hi, I'm back again. I went to the properties on the listbox, to the On Got Focus, and plugged in the following procedure:

Private Sub List33_GotFocus()

    DoCmd.Requery (List33)

End Sub

...with the list box name being "List33" and the query being "qry List Box Records View"

Nothing happened. I added a new record and then clicked on the tab containing this listbox, and the new record wasn't there. This is why I came back and started a thread.

 

[PHV]

Another way to try:
Private Sub List33_GotFocus()
Dim strSave As String
strSave = Me!List33.RowSource
Me!List33.RowSource = ""
Me!List33.RowSource = strSave
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mraetrudeaujr]

PHV, I tried what you gave me, but it doesn't update my listbox.

I tried running the 'requery' procedure based on the 'click' event (for clicking on the tab...to go back to this "Quick Look" tab) and it would run the query, but not put it in my listbox. If you need anymore specific information, please advise me. Thank you.

 

[mraetrudeaujr]

I went back to the Help files again, and there was some information about 'refreshing' the listbox (query). I figured out how to go to the menu and use the Refresh tool, but this still doesn't get me where all the user has to do is enter their records, click on the Quick View tab, and the newly entered records are listed. I'll keep searching, of course.

 

[keun]

mraetrudeaujr:
I know everyone will jump all over me because I do things the hardest way possible, but here is how I do it:

Make a new macro
There is only one action: Requery
Then go down and enter your control name: List33
Save this as whatever you want, example: "req List33"

Go to your form.
Go to the properties of your List Box
Event Tab
On Got Focus: pick your macro from the list: req List33

Done!

 

[mraetrudeaujr]

Keun, I followed your procedure/advice but it still didn't work. Is there something missing? All I have is the tab 'title' (Quick Look and a list box. This list box is populated by a query. This allows the agents to view the most recent activity in our logbook when answering phone calls. The other two tabs are specifically designed to input the information from our casework. Any other areas that I might take a look at? Your help is greatly appreciated. Thank you.

 

[Remou]

Hi
By "add to existing thread", I meant add to your own thread. Did you try:
[tt]Me!List33.Requery[/tt]
Which PHV mentioned in the first post and I noted in the second post? You do not say whether you tried it or not.

 

[mraetrudeaujr]

I don't know if this will help, but just in case;
I can go to 'Records', select 'Refresh', and the newly created record will show up.

 

[mraetrudeaujr]

Remou, yes I tried all of the suggestions from the posts, but nothing has worked so far. It just doesn't seem like it should be that complicated, right? You have a tabbed form; you are entering data through this tabbed form to populate a table; one tab uses a list box to view the most recently inputted records (using the date...and descending); and the other two tabs are for entering the data itself. Pretty straightforward, I thought. I just cannot believe that it has gotten so complicated. I have much more work to do on this application before deployment out on the floor, but this is hanging me up. Any suggestions?

 

[mraetrudeaujr]

Okay, I might be getting somewhere close to the problem. When I enter the information in the second tab 'General Information' and go to the next (third) tab 'Arrest Information', then go back to the first tab it will not "requery"...yet. It seems as though I have to move off of this record either by adding a new record, deleting this record, or going back a record, THEN when I go back to the first tab and click in the list box, it will 'requery'. Still, this isn't quite what I need. Imagine trying to explain this to an end-user that has no interest in taking the extra steps to be able to 'see' his recently entered records?

 

[Remou]

Ah. Have you tried a save record?

 

[mraetrudeaujr]

Yes, it appears that when I try to save a 'dummy' record to try this requery out, then it will requery. It appears that the newly-created record is still 'open' until you go to another record, or get out of this form and then come back into it. Does this help?

 

[Remou]

Yes. That is the way Access works. So put the save on the line before the requery.

Private Sub List33_GotFocus()
DoCmd.RunCommand acCmdSaveRecord
Me!List33.Requery
End Sub

 

[mraetrudeaujr]

Thanks Remou for the part about adding the 'save' before the actual 'requery' code...and thank you PHV for getting me started in the right "thinking" path. It now works just like I need it to.