Refresh On New Record

[Guest]

I have read through all of the posts that I can find and so far I can't get
the results I am looking for. I have a form that has some list boxes that
are pulling information from a query (filtered by other controls on the form)
and automatically displaying it on the form. The problem is, when I go to a
new record the information is left in the auto-display list boxes from the
previous record. I can refresh the data with a button, would would like to
refresh it with an event procedure AfterUpdate. I have tried the following:

Me. Requery
When I run a Me.Requery on AfterUpdate I end up back on the first
record (and it appears to be a New Record) as soon as I tab out of the
entered criteria for the list box queries (which is not the last control on
the form, so I should not be going to a new record at this time). I need to
stay on the active record to complete the data entry. I can navigate back to
that record, which is the last record, but ~alas~ the previous data remains
in the list boxes.

Me.Refresh
When I have this as an event procedure on AfterUpdate, it doesn't seem
to do anything. When I go to a new record, the data is still there from the
last record in the list boxes.

So, I guess my questions are as follows:
1) Where/how can I empty the contents of the list boxes of the residual data?
2) Where/how can I run the Requery event to re-dislpay the needed
information (not the residual information) when going back to a previous
record?

 

[strive4peace]

reset criteria, requery, show original record
---


Hi Jody,

Are your listboxes single or multi-select?

if single, you can clear them like this:

'~~~~~~~~~~~~~~~~
me.listbox_controlname = null
'~~~~~~~~~~~~~~~~

for multi-select, here is some code you can put behind your form:
'~~~~~~~~~~~~~~~~
Private Sub ClearList(pControlname As String)
Dim varItem As Variant
For Each varItem In Me(pControlname).ItemsSelected
Me(pControlname).Selected(varItem) = False
Next varItem
End Sub
'~~~~~~~~~~~~~~~~

to use the code, simply send the name of the listbox control.

as for Requery, you can
1. record value of the primary key
2. requery
3. put record pointer back

here is code to do that:

'~~~~~~~~~~~~
dim mRecordID as long

mRecordID = me.IDfield

'~~~~~~~~~~~~~~~~
'statements to do whatever, ie:
ClearList "listboxcontrolname1"
ClearList "listboxcontrolname2"
me.somecontrolname = null
'~~~~~~~~~~~~~~~~

me.Requery

'find the record you were on
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
'~~~~~~~~~~~~~~

where IDfield is the name of your long integer (if data type is
different, adjust accordingly) primary key and is in the recordset for
the form

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

As far as I can gather from your description I think you probably need to
requery each list box control, not the form.

Me.FirstListBox.Requery
Me.SecondListBox.Requery
<and so on>

Do this in the form's Current event procedure. This will requery the
controls so that they show the correct items when you navigate to an existing
record. When you navigate to a new blank record, as the controls which each
list box's RowSource query is referencing will presumably be Null the list
boxes should show no items.

To requery the list boxes as data is entered in each of the controls
referenced by their RowSource queries requery them in the AfterUpdate event
procedure of each referenced control.

To avoid repetitive code you can put a function in the form's Module:

Private Funcrion RequeryLists()

Me.FirstListBox.Requery
Me.SecondListBox.Requery
<and so on>

End Fuction

You then simply need to call the function as the form's On Current event
property and each referenced control's AfterUpdate event property by putting
the following as the event property in the properties sheet for the relevant
event of the form and each referenced control:

=RequeryLists()

Ken Sheridan
Stafford, England

 

[Guest]

Steve and Ken:
Thanks for your help.


The Me.FirstListBox.Requery OnCurrent worked perfectly!