List Boxes Do Not Requery

[AlanJBS]

Using Access 2003 - I have a form with two list boxes. Both row sources for
the list boxes are based on queries from the same table.

The rowsource query for List Box A has a criteria to include records only if
a particular field's value is zero (lets call it Field1).

The rowsource query for List Box B has a criteria to include records only if
Field1 has a value greater than zero.

If the user selects a line in List Box A and clicks a command button, that
triggers off some code which puts a value in Field1 of the underlying table.

It is simple DAO code...

Rst.Edit
Rst!Field1 = [NewValue]
Rst.Update

.... and the table does change appropriately

This should have the effect of moving the selected item from List Box A to
List Box B when they are requeried. So therefore I have followed the table
update code with code that uses the requery command for the list boxes,
namely:

Me.[List Box A].requery
Me.[List Box B].requery

But it doesn't work - the list boxes do not requery.

If F9 is pressed, they will requery but I don't want the user to have to
remember to do this. I have tried using SendKeys "{F9}" after the requery
commands above but that doesn't work.

I have tried putting DoEvents before and after the requery commands but that
doesn't work.

These requery commands are also on the OnCurrent event. If you manually move
to the previous or next record and return to the original, the list boxes
requery. If I try to move to previous or next and then return to the original
using code, once again it doesn't work.

I have used this double list box technique in earlier versions of Access and
it worked OK.

Any help gratefully appreciated

Alan

 

[Jeanette Cunningham]

Hi Alan,
If this is a bound form, then I suggest that you requery the form instead of
the list boxes.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[AlanJBS]

Thanks Jeanette - but that didn't work.

The problem seems to be allied to timings and I have cobbled together some
code which works 95% of the time - very unsatisfactory (hence the message box
I have to display) but all I can do. Here is what I have done just in case
you have any comments - but thanks anyway...


Private Sub RefreshLists()

Me.ContainerID.SetFocus

Dim n As Long
For n = 1 To 300000000
Next

DoCmd.Requery "SalesOrderItems"
DoCmd.Requery "SalesItemsAllocated"

DoCmd.Hourglass False
MsgBox "Please press the 'F9' key to refresh the display lists if they
have not refreshed automatically.", vbInformation, "Refresh Screen"

Me.SalesOrderItems.Requery
Me.SalesItemsAllocated.Requery

End Sub

Hi Alan,
If this is a bound form, then I suggest that you requery the form instead of
the list boxes.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Using Access 2003 - I have a form with two list boxes. Both row sources
for
the list boxes are based on queries from the same table.

The rowsource query for List Box A has a criteria to include records only
if
a particular field's value is zero (lets call it Field1).

The rowsource query for List Box B has a criteria to include records only
if
Field1 has a value greater than zero.

If the user selects a line in List Box A and clicks a command button, that
triggers off some code which puts a value in Field1 of the underlying
table.

It is simple DAO code...

Rst.Edit
Rst!Field1 = [NewValue]
Rst.Update

... and the table does change appropriately

This should have the effect of moving the selected item from List Box A to
List Box B when they are requeried. So therefore I have followed the table
update code with code that uses the requery command for the list boxes,
namely:

Me.[List Box A].requery
Me.[List Box B].requery

But it doesn't work - the list boxes do not requery.

If F9 is pressed, they will requery but I don't want the user to have to
remember to do this. I have tried using SendKeys "{F9}" after the requery
commands above but that doesn't work.

I have tried putting DoEvents before and after the requery commands but
that
doesn't work.

These requery commands are also on the OnCurrent event. If you manually
move
to the previous or next record and return to the original, the list boxes
requery. If I try to move to previous or next and then return to the
original
using code, once again it doesn't work.

I have used this double list box technique in earlier versions of Access
and
it worked OK.

Any help gratefully appreciated

Alan

 

[Jeanette Cunningham]

Thanks Jeanette - but that didn't work.

The problem seems to be allied to timings and I have cobbled together some
code which works 95% of the time - very unsatisfactory (hence the message
box
I have to display) but all I can do. Here is what I have done just in
case
you have any comments - but thanks anyway...


Private Sub RefreshLists()

Me.ContainerID.SetFocus

Dim n As Long
For n = 1 To 300000000
Next

DoCmd.Requery "SalesOrderItems"
DoCmd.Requery "SalesItemsAllocated"

DoCmd.Hourglass False
MsgBox "Please press the 'F9' key to refresh the display lists if they
have not refreshed automatically.", vbInformation, "Refresh Screen"

Me.SalesOrderItems.Requery
Me.SalesItemsAllocated.Requery

End Sub

Hi Alan,
If this is a bound form, then I suggest that you requery the form instead
of
the list boxes.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Using Access 2003 - I have a form with two list boxes. Both row sources
for
the list boxes are based on queries from the same table.

The rowsource query for List Box A has a criteria to include records
only
if
a particular field's value is zero (lets call it Field1).

The rowsource query for List Box B has a criteria to include records
only
if
Field1 has a value greater than zero.

If the user selects a line in List Box A and clicks a command button,
that
triggers off some code which puts a value in Field1 of the underlying
table.

It is simple DAO code...

Rst.Edit
Rst!Field1 = [NewValue]
Rst.Update

... and the table does change appropriately

This should have the effect of moving the selected item from List Box A
to
List Box B when they are requeried. So therefore I have followed the
table
update code with code that uses the requery command for the list boxes,
namely:

Me.[List Box A].requery
Me.[List Box B].requery

But it doesn't work - the list boxes do not requery.

If F9 is pressed, they will requery but I don't want the user to have
to
remember to do this. I have tried using SendKeys "{F9}" after the
requery
commands above but that doesn't work.

I have tried putting DoEvents before and after the requery commands but
that
doesn't work.

These requery commands are also on the OnCurrent event. If you manually
move
to the previous or next record and return to the original, the list
boxes
requery. If I try to move to previous or next and then return to the
original
using code, once again it doesn't work.

I have used this double list box technique in earlier versions of
Access
and
it worked OK.

Any help gratefully appreciated

Alan

 

[Jeanette Cunningham]

There seems to be something that is interferring with the record being
saved.
Is there a problem with the criteria for the list boxes - such as null to
zero conversions?
What happens if you temporarily comment out all the other code for this form
and just leave the code that manages the list boxes?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[AlanJBS]

Hi Jeanette

I have it working now - roughly what I said I was doing in my last post -
namely building in a time delay.

Thanks very much for your time and help - very much appreciated!

Regards
Alan

There seems to be something that is interferring with the record being
saved.
Is there a problem with the criteria for the list boxes - such as null to
zero conversions?
What happens if you temporarily comment out all the other code for this form
and just leave the code that manages the list boxes?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Using Access 2003 - I have a form with two list boxes. Both row sources
for
the list boxes are based on queries from the same table.

The rowsource query for List Box A has a criteria to include records only
if
a particular field's value is zero (lets call it Field1).

The rowsource query for List Box B has a criteria to include records only
if
Field1 has a value greater than zero.

If the user selects a line in List Box A and clicks a command button, that
triggers off some code which puts a value in Field1 of the underlying
table.

It is simple DAO code...

Rst.Edit
Rst!Field1 = [NewValue]
Rst.Update

... and the table does change appropriately

This should have the effect of moving the selected item from List Box A to
List Box B when they are requeried. So therefore I have followed the table
update code with code that uses the requery command for the list boxes,
namely:

Me.[List Box A].requery
Me.[List Box B].requery

But it doesn't work - the list boxes do not requery.

If F9 is pressed, they will requery but I don't want the user to have to
remember to do this. I have tried using SendKeys "{F9}" after the requery
commands above but that doesn't work.

I have tried putting DoEvents before and after the requery commands but
that
doesn't work.

These requery commands are also on the OnCurrent event. If you manually
move
to the previous or next record and return to the original, the list boxes
requery. If I try to move to previous or next and then return to the
original
using code, once again it doesn't work.

I have used this double list box technique in earlier versions of Access
and
it worked OK.

Any help gratefully appreciated

Alan