List box refresh

[home70]

I have a list box (created with the wizard using the option "Find a record on my form based on the value I select in my list box". Next to the list box I have a button created with the wizard that does the '"add new record" action. The problem is that when I add a new record, the new record doesn't show in the list box until I hit f5 on my keyboard. I've tried doing a requery in various places but it hasn't worked. Thank you.

 

[John Big Booty]

As part of your procedure to add the new record the last thing you need to do is cause the Record Source of the List box to Requery. Something like;

Me.ListBoxName.Requery

 

[home70]

Hi John, thanks for the help. I tried it in a text box's after update and in the form's ondirty, but it didn't help.

 

[John Big Booty]

Could you give me a step by step of how you are adding new records?

 

[home70]

I add a record by clicking the "Add" button I talked about above, and it clears the text boxes (previously the text boxes would contain info from the record for the item highlighted in the list box mentioned above). Then I type something into one of the text boxes and it creates the record. But that's when I don't see the new record's info appear in the list box until I hit f5, then it appears in the list box.

 

[John Big Booty]

See if putting the following at the end of the code does anything;

Me.ListBoxName.Refresh

 

[home70]

I tried it, but it looks like my Access doesn't recognize "refresh" at all.

 

[vbaInet]

Are you sure your listbox isn't based on a query that has a criteria which excludes the new record?

 

[home70]

I'm not sure, but the way I created it was using the wizard and selecting the option on the first screen that says "Find a record on my form based on the value I select in my list box". And there is not any code behind the list box.

 

[vbaInet]

When you created the listbox did you select a query as the row source or a table?

 

[home70]

I'm not 100% sure, so I went back and tried to reenact what I did. This is what I believe I did. I created a form with design view and set a query as its record source. Then I used the list box wizard for the list box, selected the option I said above, and on a subsequent screen it asked me which fields from the form's record source query I wanted to show in the box. I see that there is a Select statement in the list box's row source that was created by the wizard.

 

[vbaInet]

Can we have a quick look at your db?

 

[home70]

I attached it to this post.

For a little more information, I've also found that records deleted using the "Delete" button on the form do not leave the list box, and modifications made to records also don't show, until f5 is pressed.

 

[vbaInet]

I think I should take you back to John's first post (#2) and your reply to that (#3). Where you put the code isn't where John had advised.

So instead of your macro, use code behind your Add and Delete buttons' click events.

Add:

DoCmd.RunCommand acCmdRecordsGoToNew

Delete:

    If Me.NewRecord And Me.Dirty Then
        Me.Undo
    Else
        DoCmd.RunCommand acCmdDeleteRecord
    End If

Or simply add the Requery in the macro just after the add and delete record commands. Action is Requery, Control Name is List22.

 

[home70]

Respectfully, I believe either there has been a miscommunication or that you are mistaken. In post number 5 of this thread I described, step-by-step, my record adding procedure. So when John had suggested, "As part of your procedure to add the new record the last thing you need to do is cause the Record Source of the List box to Requery.", what I did was place a list box requery command in the after update event of the first text box on the form (the text box I fill in first after clicking the "Add" button). If I have misunderstood or misspoken, I don't understand how.

I actually do prefer to use code rather than macros (mainly since I really don't understand macros), but when I replaced the events behind the buttons with the code, it acted the same.

 

[vbaInet]

Here's an update:

For the delete button:

    If Me.NewRecord And Me.Dirty Then
        Me.Undo
    Else
        DoCmd.RunCommand acCmdDeleteRecord
    Me.List22.Requery
    End If

To requery the listbox after adding a record, what you need to do is put Me.List22.Requery in the AFTER INSERT event of the FORM.

 

[home70]

Thanks vbaInet. The delete works great. The adding only works if I click the add button again after I type something into a text box. (I was trying to get the new record to appear in the list box immediately after typing into a text box - without having to press a button. Is this possible?)

 

[vbaInet]

Well, that isn't logical because you have to save the record first for it to appear. If it's not in the table it wouldn't appear. You can put a SAVE button on your form and get the user to click it if you want instantaneous inserts.

 

[home70]

OK, what about putting the Save button functionality in the after update of the text box (before the list box requery)?

 

[vbaInet]

I'll give you a scenario. What if I type "bananas" and I suddenly remembered that I didn't want "bananas" I wanted "apples". This would mean that I'm causing the engine to unecessarily perform saves when in actuality it should only commit a save when the user is completely sure. It's bad practice to commit a save in the after update event of a control. Just doesn't work out.