List Boxes Do Not Requery (Access 2003)

A

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
 
A

Allen Browne

That approach should work.

What's the RowSource for the list boxes? Does it contain a dependency on the
form itself, e.g. criteria of:
forms!form1!text0
so that it isn't updated until something in the form is updated?

Are the list boxes bound, so that changing them dirties the record, and this
could cause a concurrency problem when you are updating via code as well?

Unless Rst is a RecordsetClone of the form, did you include both these lines
*before* you requery the list box (so as to ensure the data had been
written):
Rst.Close
Set Rst = Nothing

If you are using attached tables, what settings do you have in the lower
half of this tab:
Tools | Options | Edit/Find
Does the list box have 1000s of records, or any relevant check boxes
unchecked?

Any other kinds of dependencies you can think of?

If you are using A2003 SP3, have you applied the hotfix referenced here:
http://allenbrowne.com/bug-Access2003SP3.html

Hopefully that triggers some useful thought.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

AlanJBS said:
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
Click to expand...
 
A

AlanJBS

Thanks Allen - but unfortunately none of those suggestions were able to help.

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

Allen Browne said:
That approach should work.

What's the RowSource for the list boxes? Does it contain a dependency on the
form itself, e.g. criteria of:
forms!form1!text0
so that it isn't updated until something in the form is updated?

Are the list boxes bound, so that changing them dirties the record, and this
could cause a concurrency problem when you are updating via code as well?

Unless Rst is a RecordsetClone of the form, did you include both these lines
*before* you requery the list box (so as to ensure the data had been
written):
Rst.Close
Set Rst = Nothing

If you are using attached tables, what settings do you have in the lower
half of this tab:
Tools | Options | Edit/Find
Does the list box have 1000s of records, or any relevant check boxes
unchecked?

Any other kinds of dependencies you can think of?

If you are using A2003 SP3, have you applied the hotfix referenced here:
http://allenbrowne.com/bug-Access2003SP3.html

Hopefully that triggers some useful thought.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

AlanJBS said:
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
Click to expand...
Click to expand...
 
A

Allen Browne

As you say, it's a shame that this delay is needed, but you could try
something like this:

Private Sub RefreshLists()
Dim dtEnd As Date
Static bRunning As Boolean

'Jump out if already executing.
If bRunning Then
Exit Function
Else
bRunning = True
End If

'Yield for 5 seconds
dtEnd = DateAdd("s", 5, Now())
Do
DoEvents
Loop Until Now() >= dtEnd

'Perform the requery
Me.SalesOrderItems.Requery
Me.SalesItemsAllocated.Requery

'Reset the static flag
bRunning = False
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

AlanJBS said:
Thanks Allen - but unfortunately none of those suggestions were able to
help.

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

Allen Browne said:
That approach should work.

What's the RowSource for the list boxes? Does it contain a dependency on
the
form itself, e.g. criteria of:
forms!form1!text0
so that it isn't updated until something in the form is updated?

Are the list boxes bound, so that changing them dirties the record, and
this
could cause a concurrency problem when you are updating via code as well?

Unless Rst is a RecordsetClone of the form, did you include both these
lines
*before* you requery the list box (so as to ensure the data had been
written):
Rst.Close
Set Rst = Nothing

If you are using attached tables, what settings do you have in the lower
half of this tab:
Tools | Options | Edit/Find
Does the list box have 1000s of records, or any relevant check boxes
unchecked?

Any other kinds of dependencies you can think of?

If you are using A2003 SP3, have you applied the hotfix referenced here:
http://allenbrowne.com/bug-Access2003SP3.html

Hopefully that triggers some useful thought.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

AlanJBS said:
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
Click to expand...
Click to expand...
Click to expand...
 
A

AlanJBS

Thanks Allen - that works too. As it happens, 5 secs is about right - 3
doesn't work but 4 secs did - so I am keeping it on 5!

Thanks for all your help very much appreciated!

Regards Alan

Allen Browne said:
As you say, it's a shame that this delay is needed, but you could try
something like this:

Private Sub RefreshLists()
Dim dtEnd As Date
Static bRunning As Boolean

'Jump out if already executing.
If bRunning Then
Exit Function
Else
bRunning = True
End If

'Yield for 5 seconds
dtEnd = DateAdd("s", 5, Now())
Do
DoEvents
Loop Until Now() >= dtEnd

'Perform the requery
Me.SalesOrderItems.Requery
Me.SalesItemsAllocated.Requery

'Reset the static flag
bRunning = False
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

AlanJBS said:
Thanks Allen - but unfortunately none of those suggestions were able to
help.

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

Allen Browne said:
That approach should work.

What's the RowSource for the list boxes? Does it contain a dependency on
the
form itself, e.g. criteria of:
forms!form1!text0
so that it isn't updated until something in the form is updated?

Are the list boxes bound, so that changing them dirties the record, and
this
could cause a concurrency problem when you are updating via code as well?

Unless Rst is a RecordsetClone of the form, did you include both these
lines
*before* you requery the list box (so as to ensure the data had been
written):
Rst.Close
Set Rst = Nothing

If you are using attached tables, what settings do you have in the lower
half of this tab:
Tools | Options | Edit/Find
Does the list box have 1000s of records, or any relevant check boxes
unchecked?

Any other kinds of dependencies you can think of?

If you are using A2003 SP3, have you applied the hotfix referenced here:
http://allenbrowne.com/bug-Access2003SP3.html

Hopefully that triggers some useful thought.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

List Boxes Do Not Requery 5
list box requery 1
Enable Button based on Two list boxes 5
AfterUpdate Requery 2
Requery table from form 4
Change ListBox.RowSource - Is Requery needed. 1
Requery combo box in subform 5
Howto make Combobox requery based on dependant combobox values 1

Top