Query Delay

[JString]

I apologize if this is a duplicate post. I reposted because for some reason
I can't seem to get my posts to show up.

My form contains a persistent snapshot recordset that is used to create a
new recordset which is in turn applied to a listbox's recordset property.
Code is similar to the following:

AllRecords.Requery
Dim rsNew As DAO.Recordset, rsOld As DAO.Recordset
Set rsNew = AllRecords.OpenRecordset(dbOpenSnapshot)
Set mylistbox.Recordset = rsNew
Set rsNew = Nothing

The problem is that the listbox does not reflect changes made to the
underlying data after the procedure is called immediately following a delete
query. But the procedure does work when a bound command button is clicked.
So it seems like there is some sort of delay following the AllRecords.requery
statement that is preventing the changes from propagating to the new
recordset.

Am I right that there is a delay and if so, how do I get around this problem?

 

[a a r o n . k e m p f]

wow, if Jet is too slow-- then you should upsize to SQL Server.

Jet doesn't have any tools for automating index creation/tuning; so
it
makes sense to upsize to a real database that offers these
maintainability features

 

[BruceM]

Ignore Aaron. Like a broken clock he may be right now and then, but not
enough to count on.

Not sure I see the point of rsOld, and I don't know quite what you mean by a
listbox Recordset property unless it is an Access 2007 thing. Did the code
compile? Are you referring to the Row Source? If so, you need to requery
the list box. If not, more information is needed, as the explanation is
unclear.

Also, what do you mean by a "bound" command button? A command button cannot
have a control source.

 

[Walt Kowalski]

<AIMS FINGER, COCKS THUMB, DROPS THUMB, SMILES>

 

[JString]

Ignore rsOld... I accidentally copied it in with the rest of the code.

The command button executes the sub in the usual way a command button would.

 

[JString]

rsOld is used to handle and close the old recordsets from the listbox.

 

[BruceM]

OK, but I expect requerying the combo box after changing its row source is
needed nevertheless. I am familiar with setting the list box Row Source in
code, but not the Recordset. If I understand correctly what you are doing
you need to set the Row Source to rsNew, not the Recordset. After that you
need to requery the list box. It may be something like this:

Dim strSQL as String
strSQL = "SELECT DISTINCT [SomeField] FROM tblYourTable " & _
"ORDER BY [SomeField]"
Me.mylistbox.RowSource = strSQL
Me.mylistbox.Requery

Maybe you can use a Recordset as the row source. I don't see what
AllRecords represents, but I expect it is a named query, or maybe a table.
I have to admit I do not understand the use of dbOpenSnapshot very well, but
as I understand it is a static recordset that does not update right away.
However, even if it works I doubt it is the most effective way of doing what
you need.

You say the procedure "does not work". In what way? Is there an error
message?

 

[BruceM]

Then you will need to declare it there, or as a public variable. If you do
not have Option Explicit at the top of the code module you are not requiring
variable declaration, which means an undeclared variable will be treated as
a variant.

 

[JString]

Correct me if I'm wrong, but the way I understand it is that a listbox's
rowsource property is used to generate its underlying recordset when it is
requeried. The reason why I am trying to handle its recordset outside of the
listbox object is to try and keep the its queries running on the client
machine as much as is possible for fast searching. If I allow the listbox
object to handle its own recordset by using the rowsource property instead,
it would basically defeat the whole purpose of using the listbox in the first
place.

This is also the reason for the AllRecords recordset: each subsequent query
is pulled from this client-side recordset instead of the server, but
AllRecords does need to be refreshed periodically and on demand when a user
performs some action like adding or deleting a record (which is where I am
running into my problem). What I meant by saying that it doesn't work is
simply that the changes that should show up in the list box don't when the
procedure is first called. Here's the basic flow of the process to help
clear things up:

1. User selects a record and clicks a delete button
2. Form runs a delete query on the main data table
3. AllRecords (which is a client-side representation of the main table) is
requeried so it will hopefully reflect these changes.
4. A new, filtered recordset is created from Allrecords

It appears the problem is that by the time step 4 executes, the change in
the main data table is not reflected in the recordsets.

I've been reading up on this and it looks like the Jet engine does allow for
the creation of a new recordset using the openrecordset method before the
parent recordset completes any queries it might be currently running. I
don't know yet but perhaps the NextRecordset method could be a solution.

OK, but I expect requerying the combo box after changing its row source is
needed nevertheless. I am familiar with setting the list box Row Source in
code, but not the Recordset. If I understand correctly what you are doing
you need to set the Row Source to rsNew, not the Recordset. After that you
need to requery the list box. It may be something like this:

Dim strSQL as String
strSQL = "SELECT DISTINCT [SomeField] FROM tblYourTable " & _
"ORDER BY [SomeField]"
Me.mylistbox.RowSource = strSQL
Me.mylistbox.Requery

Maybe you can use a Recordset as the row source. I don't see what
AllRecords represents, but I expect it is a named query, or maybe a table.
I have to admit I do not understand the use of dbOpenSnapshot very well, but
as I understand it is a static recordset that does not update right away.
However, even if it works I doubt it is the most effective way of doing what
you need.

You say the procedure "does not work". In what way? Is there an error
message?

Ignore rsOld... I accidentally copied it in with the rest of the code.

The command button executes the sub in the usual way a command button
would.

 

[JString]

After playing around with it a bit I discovered a few more things...

If I remove the AllRecords.Requery statement, the listbox recordset will
never show the changes if requeried.

Also the procedure WILL work properly if I replace the AllRecords.Requery
statement with some code that forces a complete recreation of AllRecords.
But this is slow... if I could find a way to pause my code until the requery
completes, I think that would be much faster.

Correct me if I'm wrong, but the way I understand it is that a listbox's
rowsource property is used to generate its underlying recordset when it is
requeried. The reason why I am trying to handle its recordset outside of the
listbox object is to try and keep the its queries running on the client
machine as much as is possible for fast searching. If I allow the listbox
object to handle its own recordset by using the rowsource property instead,
it would basically defeat the whole purpose of using the listbox in the first
place.

This is also the reason for the AllRecords recordset: each subsequent query
is pulled from this client-side recordset instead of the server, but
AllRecords does need to be refreshed periodically and on demand when a user
performs some action like adding or deleting a record (which is where I am
running into my problem). What I meant by saying that it doesn't work is
simply that the changes that should show up in the list box don't when the
procedure is first called. Here's the basic flow of the process to help
clear things up:

1. User selects a record and clicks a delete button
2. Form runs a delete query on the main data table
3. AllRecords (which is a client-side representation of the main table) is
requeried so it will hopefully reflect these changes.
4. A new, filtered recordset is created from Allrecords

It appears the problem is that by the time step 4 executes, the change in
the main data table is not reflected in the recordsets.

I've been reading up on this and it looks like the Jet engine does allow for
the creation of a new recordset using the openrecordset method before the
parent recordset completes any queries it might be currently running. I
don't know yet but perhaps the NextRecordset method could be a solution.

OK, but I expect requerying the combo box after changing its row source is
needed nevertheless. I am familiar with setting the list box Row Source in
code, but not the Recordset. If I understand correctly what you are doing
you need to set the Row Source to rsNew, not the Recordset. After that you
need to requery the list box. It may be something like this:

Dim strSQL as String
strSQL = "SELECT DISTINCT [SomeField] FROM tblYourTable " & _
"ORDER BY [SomeField]"
Me.mylistbox.RowSource = strSQL
Me.mylistbox.Requery

Maybe you can use a Recordset as the row source. I don't see what
AllRecords represents, but I expect it is a named query, or maybe a table.
I have to admit I do not understand the use of dbOpenSnapshot very well, but
as I understand it is a static recordset that does not update right away.
However, even if it works I doubt it is the most effective way of doing what
you need.

You say the procedure "does not work". In what way? Is there an error
message?

Ignore rsOld... I accidentally copied it in with the rest of the code.

The command button executes the sub in the usual way a command button
would.

:

Ignore Aaron. Like a broken clock he may be right now and then, but not
enough to count on.

Not sure I see the point of rsOld, and I don't know quite what you mean
by a
listbox Recordset property unless it is an Access 2007 thing. Did the
code
compile? Are you referring to the Row Source? If so, you need to
requery
the list box. If not, more information is needed, as the explanation is
unclear.

Also, what do you mean by a "bound" command button? A command button
cannot
have a control source.

I apologize if this is a duplicate post. I reposted because for some
reason
I can't seem to get my posts to show up.

My form contains a persistent snapshot recordset that is used to create
a
new recordset which is in turn applied to a listbox's recordset
property.
Code is similar to the following:

AllRecords.Requery
Dim rsNew As DAO.Recordset, rsOld As DAO.Recordset
Set rsNew = AllRecords.OpenRecordset(dbOpenSnapshot)
Set mylistbox.Recordset = rsNew
Set rsNew = Nothing

The problem is that the listbox does not reflect changes made to the
underlying data after the procedure is called immediately following a
delete
query. But the procedure does work when a bound command button is
clicked.
So it seems like there is some sort of delay following the
AllRecords.requery
statement that is preventing the changes from propagating to the new
recordset.

Am I right that there is a delay and if so, how do I get around this
problem?

 

[BruceM]

The list box Row Source is just that: the source for what you see in the
list box. If the row source is a SELECT DISTINCT query to show the city
from an Address table, and you delete the only record in which the city is
Boston, the lsit box will continue to show Boston until you either requery
the list box to show the change, or you close and reopen the form. Perhaps
that is what you mean by "generate its underlying recordset when it is
requeried".

To delete a record I would just do:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDelete

The acCmdSelectRecord may not be necessary in a single form, but I am not
exactly sure how that works.

You didn't post the delete query SQL, but to use that method you could do:
CurrentDb.Execute strSQL
where strSQL is the "DELETE FROM SomeTable WHERE..." etc. string.

I think Refresh would be adequate, rather than Requery, so that the
recordset shows the changes after the delete. However, it will not affect
the list box row source. For that you need to requery the list box
explicitly.

Me.mylistbox.Requery

I think you are making this more complex than it needs to be. Delete the
record, refresh the recordset, and requery the combo box. If you are
setting the row source in VBA code you will need to adjust the Row Source
SQL before requerying the list box.

If a list box has a recordset property, I have not figured out what to do
with it. If I try setting a list box recordset in code as you have done I
get a run-time error. If the row source is a table/query the row source is
a recordset, but it is still the list box row source, not its recordset.

After playing around with it a bit I discovered a few more things...

If I remove the AllRecords.Requery statement, the listbox recordset will
never show the changes if requeried.

Also the procedure WILL work properly if I replace the AllRecords.Requery
statement with some code that forces a complete recreation of AllRecords.
But this is slow... if I could find a way to pause my code until the
requery
completes, I think that would be much faster.

Correct me if I'm wrong, but the way I understand it is that a listbox's
rowsource property is used to generate its underlying recordset when it
is
requeried. The reason why I am trying to handle its recordset outside of
the
listbox object is to try and keep the its queries running on the client
machine as much as is possible for fast searching. If I allow the
listbox
object to handle its own recordset by using the rowsource property
instead,
it would basically defeat the whole purpose of using the listbox in the
first
place.

This is also the reason for the AllRecords recordset: each subsequent
query
is pulled from this client-side recordset instead of the server, but
AllRecords does need to be refreshed periodically and on demand when a
user
performs some action like adding or deleting a record (which is where I
am
running into my problem). What I meant by saying that it doesn't work is
simply that the changes that should show up in the list box don't when
the
procedure is first called. Here's the basic flow of the process to help
clear things up:

1. User selects a record and clicks a delete button
2. Form runs a delete query on the main data table
3. AllRecords (which is a client-side representation of the main table)
is
requeried so it will hopefully reflect these changes.
4. A new, filtered recordset is created from Allrecords

It appears the problem is that by the time step 4 executes, the change in
the main data table is not reflected in the recordsets.

I've been reading up on this and it looks like the Jet engine does allow
for
the creation of a new recordset using the openrecordset method before the
parent recordset completes any queries it might be currently running. I
don't know yet but perhaps the NextRecordset method could be a solution.

OK, but I expect requerying the combo box after changing its row source
is
needed nevertheless. I am familiar with setting the list box Row
Source in
code, but not the Recordset. If I understand correctly what you are
doing
you need to set the Row Source to rsNew, not the Recordset. After that
you
need to requery the list box. It may be something like this:

Dim strSQL as String
strSQL = "SELECT DISTINCT [SomeField] FROM tblYourTable " & _
"ORDER BY [SomeField]"
Me.mylistbox.RowSource = strSQL
Me.mylistbox.Requery

Maybe you can use a Recordset as the row source. I don't see what
AllRecords represents, but I expect it is a named query, or maybe a
table.
I have to admit I do not understand the use of dbOpenSnapshot very
well, but
as I understand it is a static recordset that does not update right
away.
However, even if it works I doubt it is the most effective way of doing
what
you need.

You say the procedure "does not work". In what way? Is there an error
message?

Ignore rsOld... I accidentally copied it in with the rest of the
code.

The command button executes the sub in the usual way a command button
would.

:

Ignore Aaron. Like a broken clock he may be right now and then, but
not
enough to count on.

Not sure I see the point of rsOld, and I don't know quite what you
mean
by a
listbox Recordset property unless it is an Access 2007 thing. Did
the
code
compile? Are you referring to the Row Source? If so, you need to
requery
the list box. If not, more information is needed, as the
explanation is
unclear.

Also, what do you mean by a "bound" command button? A command
button
cannot
have a control source.

I apologize if this is a duplicate post. I reposted because for
some
reason
I can't seem to get my posts to show up.

My form contains a persistent snapshot recordset that is used to
create
a
new recordset which is in turn applied to a listbox's recordset
property.
Code is similar to the following:

AllRecords.Requery
Dim rsNew As DAO.Recordset, rsOld As DAO.Recordset
Set rsNew = AllRecords.OpenRecordset(dbOpenSnapshot)
Set mylistbox.Recordset = rsNew
Set rsNew = Nothing

The problem is that the listbox does not reflect changes made to
the
underlying data after the procedure is called immediately
following a
delete
query. But the procedure does work when a bound command button is
clicked.
So it seems like there is some sort of delay following the
AllRecords.requery
statement that is preventing the changes from propagating to the
new
recordset.

Am I right that there is a delay and if so, how do I get around
this
problem?

 

[JString]

It is definately more complex than the usual way of using a listbox, but for
what it is that I'm trying to do, I don't know of a simpler way. I have to
disagree with you though about the row source property, even though it does
represent a set of records as you say. Until Access translates the string
accessed by the property and uses that data to build an actual recordset
object stored in memory, the row source is only a string.

There is something else that is kind of strange that I noticed... after
running the procedure that I outlined, the listbox does not immediately
reflect changes that were made as usual. However, I noticed that if I
alt-tab out to VB and alt-tab back, the changes magically appear! So then it
looks like all that needs to be done is a repaint, but even after adding a
'me.repaint' line in after the code executes, the changes STILL don't show.
So I guess that leads me back to the timing issue and thus the circle
continues.

The list box Row Source is just that: the source for what you see in the
list box. If the row source is a SELECT DISTINCT query to show the city
from an Address table, and you delete the only record in which the city is
Boston, the lsit box will continue to show Boston until you either requery
the list box to show the change, or you close and reopen the form. Perhaps
that is what you mean by "generate its underlying recordset when it is
requeried".

To delete a record I would just do:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDelete

The acCmdSelectRecord may not be necessary in a single form, but I am not
exactly sure how that works.

You didn't post the delete query SQL, but to use that method you could do:
CurrentDb.Execute strSQL
where strSQL is the "DELETE FROM SomeTable WHERE..." etc. string.

I think Refresh would be adequate, rather than Requery, so that the
recordset shows the changes after the delete. However, it will not affect
the list box row source. For that you need to requery the list box
explicitly.

Me.mylistbox.Requery

I think you are making this more complex than it needs to be. Delete the
record, refresh the recordset, and requery the combo box. If you are
setting the row source in VBA code you will need to adjust the Row Source
SQL before requerying the list box.

If a list box has a recordset property, I have not figured out what to do
with it. If I try setting a list box recordset in code as you have done I
get a run-time error. If the row source is a table/query the row source is
a recordset, but it is still the list box row source, not its recordset.

After playing around with it a bit I discovered a few more things...

If I remove the AllRecords.Requery statement, the listbox recordset will
never show the changes if requeried.

Also the procedure WILL work properly if I replace the AllRecords.Requery
statement with some code that forces a complete recreation of AllRecords.
But this is slow... if I could find a way to pause my code until the
requery
completes, I think that would be much faster.

Correct me if I'm wrong, but the way I understand it is that a listbox's
rowsource property is used to generate its underlying recordset when it
is
requeried. The reason why I am trying to handle its recordset outside of
the
listbox object is to try and keep the its queries running on the client
machine as much as is possible for fast searching. If I allow the
listbox
object to handle its own recordset by using the rowsource property
instead,
it would basically defeat the whole purpose of using the listbox in the
first
place.

This is also the reason for the AllRecords recordset: each subsequent
query
is pulled from this client-side recordset instead of the server, but
AllRecords does need to be refreshed periodically and on demand when a
user
performs some action like adding or deleting a record (which is where I
am
running into my problem). What I meant by saying that it doesn't work is
simply that the changes that should show up in the list box don't when
the
procedure is first called. Here's the basic flow of the process to help
clear things up:

1. User selects a record and clicks a delete button
2. Form runs a delete query on the main data table
3. AllRecords (which is a client-side representation of the main table)
is
requeried so it will hopefully reflect these changes.
4. A new, filtered recordset is created from Allrecords

It appears the problem is that by the time step 4 executes, the change in
the main data table is not reflected in the recordsets.

I've been reading up on this and it looks like the Jet engine does allow
for
the creation of a new recordset using the openrecordset method before the
parent recordset completes any queries it might be currently running. I
don't know yet but perhaps the NextRecordset method could be a solution.

:

OK, but I expect requerying the combo box after changing its row source
is
needed nevertheless. I am familiar with setting the list box Row
Source in
code, but not the Recordset. If I understand correctly what you are
doing
you need to set the Row Source to rsNew, not the Recordset. After that
you
need to requery the list box. It may be something like this:

Dim strSQL as String
strSQL = "SELECT DISTINCT [SomeField] FROM tblYourTable " & _
"ORDER BY [SomeField]"
Me.mylistbox.RowSource = strSQL
Me.mylistbox.Requery

Maybe you can use a Recordset as the row source. I don't see what
AllRecords represents, but I expect it is a named query, or maybe a
table.
I have to admit I do not understand the use of dbOpenSnapshot very
well, but
as I understand it is a static recordset that does not update right
away.
However, even if it works I doubt it is the most effective way of doing
what
you need.

You say the procedure "does not work". In what way? Is there an error
message?

Ignore rsOld... I accidentally copied it in with the rest of the
code.

The command button executes the sub in the usual way a command button
would.

:

Ignore Aaron. Like a broken clock he may be right now and then, but
not
enough to count on.

Not sure I see the point of rsOld, and I don't know quite what you
mean
by a
listbox Recordset property unless it is an Access 2007 thing. Did
the
code
compile? Are you referring to the Row Source? If so, you need to
requery
the list box. If not, more information is needed, as the
explanation is
unclear.

Also, what do you mean by a "bound" command button? A command
button
cannot
have a control source.

I apologize if this is a duplicate post. I reposted because for
some
reason
I can't seem to get my posts to show up.

My form contains a persistent snapshot recordset that is used to
create
a
new recordset which is in turn applied to a listbox's recordset
property.
Code is similar to the following:

AllRecords.Requery
Dim rsNew As DAO.Recordset, rsOld As DAO.Recordset
Set rsNew = AllRecords.OpenRecordset(dbOpenSnapshot)
Set mylistbox.Recordset = rsNew
Set rsNew = Nothing

The problem is that the listbox does not reflect changes made to
the
underlying data after the procedure is called immediately
following a
delete
query. But the procedure does work when a bound command button is
clicked.
So it seems like there is some sort of delay following the
AllRecords.requery
statement that is preventing the changes from propagating to the
new
recordset.

Am I right that there is a delay and if so, how do I get around
this
problem?

 

[BruceM]

Dim strSQL as String

strSQL = "SELECT [SomeFieldID], [SomeFieldName] " & _
"FROM [SomeTable] " & _
"ORDER BY [SomeFieldName]"

Me.ComboBoxName.RowSource = strSQL

There is your Row Source. You can add a WHERE condition, if you like. The
code could be in a command button Click event or whatever. If the combo box
Column Count is 2 and the Column widths are 0";1" the Row Source, although
is "only" a string, will give you a combo box list that shows all records
from SomeTable, arranged by SomeFieldName. If the combo box is bound to a
field, and the Bound Column is 1, and the Column Count and Widths are as
stated, selecting a name from the list will cause the ID to be stored in
that field.

I don't know what this means:
"Until Access translates the string accessed by the property and uses that
data to build an actual recordset object stored in memory, the row source is
only a string."
However, I can tell you that I and countless others have successfully built
Row Sources for list boxes and combo boxes without creating a recordset
object. Even if you can use a recordset object as the Row Source, you still
need to set the Row Source property to that object.

I don't know if you tried refreshing as I suggested. You tell me that what
I am suggesting regarding the Row Source string (or named query) will not
work, but if you are not willing to give it a try I can only wish you luck
with the approach to which you are committed.

BTW, DoEvents is the built-in function that "yields execution so that the
operating system can process other events." This is from VBA Help.

It is definately more complex than the usual way of using a listbox, but
for
what it is that I'm trying to do, I don't know of a simpler way. I have
to
disagree with you though about the row source property, even though it
does
represent a set of records as you say. Until Access translates the string
accessed by the property and uses that data to build an actual recordset
object stored in memory, the row source is only a string.

There is something else that is kind of strange that I noticed... after
running the procedure that I outlined, the listbox does not immediately
reflect changes that were made as usual. However, I noticed that if I
alt-tab out to VB and alt-tab back, the changes magically appear! So then
it
looks like all that needs to be done is a repaint, but even after adding a
'me.repaint' line in after the code executes, the changes STILL don't
show.
So I guess that leads me back to the timing issue and thus the circle
continues.

The list box Row Source is just that: the source for what you see in the
list box. If the row source is a SELECT DISTINCT query to show the city
from an Address table, and you delete the only record in which the city
is
Boston, the lsit box will continue to show Boston until you either
requery
the list box to show the change, or you close and reopen the form.
Perhaps
that is what you mean by "generate its underlying recordset when it is
requeried".

To delete a record I would just do:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDelete

The acCmdSelectRecord may not be necessary in a single form, but I am not
exactly sure how that works.

You didn't post the delete query SQL, but to use that method you could
do:
CurrentDb.Execute strSQL
where strSQL is the "DELETE FROM SomeTable WHERE..." etc. string.

I think Refresh would be adequate, rather than Requery, so that the
recordset shows the changes after the delete. However, it will not
affect
the list box row source. For that you need to requery the list box
explicitly.

Me.mylistbox.Requery

I think you are making this more complex than it needs to be. Delete the
record, refresh the recordset, and requery the combo box. If you are
setting the row source in VBA code you will need to adjust the Row Source
SQL before requerying the list box.

If a list box has a recordset property, I have not figured out what to do
with it. If I try setting a list box recordset in code as you have done
I
get a run-time error. If the row source is a table/query the row source
is
a recordset, but it is still the list box row source, not its recordset.

After playing around with it a bit I discovered a few more things...

If I remove the AllRecords.Requery statement, the listbox recordset
will
never show the changes if requeried.

Also the procedure WILL work properly if I replace the
AllRecords.Requery
statement with some code that forces a complete recreation of
AllRecords.
But this is slow... if I could find a way to pause my code until the
requery
completes, I think that would be much faster.


:

Correct me if I'm wrong, but the way I understand it is that a
listbox's
rowsource property is used to generate its underlying recordset when
it
is
requeried. The reason why I am trying to handle its recordset outside
of
the
listbox object is to try and keep the its queries running on the
client
machine as much as is possible for fast searching. If I allow the
listbox
object to handle its own recordset by using the rowsource property
instead,
it would basically defeat the whole purpose of using the listbox in
the
first
place.

This is also the reason for the AllRecords recordset: each subsequent
query
is pulled from this client-side recordset instead of the server, but
AllRecords does need to be refreshed periodically and on demand when a
user
performs some action like adding or deleting a record (which is where
I
am
running into my problem). What I meant by saying that it doesn't work
is
simply that the changes that should show up in the list box don't when
the
procedure is first called. Here's the basic flow of the process to
help
clear things up:

1. User selects a record and clicks a delete button
2. Form runs a delete query on the main data table
3. AllRecords (which is a client-side representation of the main
table)
is
requeried so it will hopefully reflect these changes.
4. A new, filtered recordset is created from Allrecords

It appears the problem is that by the time step 4 executes, the change
in
the main data table is not reflected in the recordsets.

I've been reading up on this and it looks like the Jet engine does
allow
for
the creation of a new recordset using the openrecordset method before
the
parent recordset completes any queries it might be currently running.
I
don't know yet but perhaps the NextRecordset method could be a
solution.

:

OK, but I expect requerying the combo box after changing its row
source
is
needed nevertheless. I am familiar with setting the list box Row
Source in
code, but not the Recordset. If I understand correctly what you are
doing
you need to set the Row Source to rsNew, not the Recordset. After
that
you
need to requery the list box. It may be something like this:

Dim strSQL as String
strSQL = "SELECT DISTINCT [SomeField] FROM tblYourTable " & _
"ORDER BY [SomeField]"
Me.mylistbox.RowSource = strSQL
Me.mylistbox.Requery

Maybe you can use a Recordset as the row source. I don't see what
AllRecords represents, but I expect it is a named query, or maybe a
table.
I have to admit I do not understand the use of dbOpenSnapshot very
well, but
as I understand it is a static recordset that does not update right
away.
However, even if it works I doubt it is the most effective way of
doing
what
you need.

You say the procedure "does not work". In what way? Is there an
error
message?

Ignore rsOld... I accidentally copied it in with the rest of the
code.

The command button executes the sub in the usual way a command
button
would.

:

Ignore Aaron. Like a broken clock he may be right now and then,
but
not
enough to count on.

Not sure I see the point of rsOld, and I don't know quite what
you
mean
by a
listbox Recordset property unless it is an Access 2007 thing.
Did
the
code
compile? Are you referring to the Row Source? If so, you need
to
requery
the list box. If not, more information is needed, as the
explanation is
unclear.

Also, what do you mean by a "bound" command button? A command
button
cannot
have a control source.

I apologize if this is a duplicate post. I reposted because for
some
reason
I can't seem to get my posts to show up.

My form contains a persistent snapshot recordset that is used
to
create
a
new recordset which is in turn applied to a listbox's recordset
property.
Code is similar to the following:

AllRecords.Requery
Dim rsNew As DAO.Recordset, rsOld As DAO.Recordset
Set rsNew = AllRecords.OpenRecordset(dbOpenSnapshot)
Set mylistbox.Recordset = rsNew
Set rsNew = Nothing

The problem is that the listbox does not reflect changes made
to
the
underlying data after the procedure is called immediately
following a
delete
query. But the procedure does work when a bound command button
is
clicked.
So it seems like there is some sort of delay following the
AllRecords.requery
statement that is preventing the changes from propagating to
the
new
recordset.

Am I right that there is a delay and if so, how do I get around
this
problem?

 

[JString]

I know what you're suggesting, and thanks for trying to help me, but adding
an SQL string to the listbox's rowsource is going to demolish my project.
The whole purpose of the persistent snapshot recordset is to provide a client
with everything it needs to perform a search and display the results in the
listbox without having to query the server. This way, the only communication
needed between the client and server should be an occasional requery every
couple of minutes.

I tried the Doevents procedure in several places in my code and found it was
ineffective. I did find a solution though by launching a second form in
dialogue mode which executed the code that was having the timing problem.
Seems that the second form would not close until all queries had completed.

BTW, I guarantee that any form that has a valid rowsource property set will
also have a recordset at least while it's running.

Dim strSQL as String

strSQL = "SELECT [SomeFieldID], [SomeFieldName] " & _
"FROM [SomeTable] " & _
"ORDER BY [SomeFieldName]"

Me.ComboBoxName.RowSource = strSQL

There is your Row Source. You can add a WHERE condition, if you like. The
code could be in a command button Click event or whatever. If the combo box
Column Count is 2 and the Column widths are 0";1" the Row Source, although
is "only" a string, will give you a combo box list that shows all records
from SomeTable, arranged by SomeFieldName. If the combo box is bound to a
field, and the Bound Column is 1, and the Column Count and Widths are as
stated, selecting a name from the list will cause the ID to be stored in
that field.

I don't know what this means:
"Until Access translates the string accessed by the property and uses that
data to build an actual recordset object stored in memory, the row source is
only a string."
However, I can tell you that I and countless others have successfully built
Row Sources for list boxes and combo boxes without creating a recordset
object. Even if you can use a recordset object as the Row Source, you still
need to set the Row Source property to that object.

I don't know if you tried refreshing as I suggested. You tell me that what
I am suggesting regarding the Row Source string (or named query) will not
work, but if you are not willing to give it a try I can only wish you luck
with the approach to which you are committed.

BTW, DoEvents is the built-in function that "yields execution so that the
operating system can process other events." This is from VBA Help.

It is definately more complex than the usual way of using a listbox, but
for
what it is that I'm trying to do, I don't know of a simpler way. I have
to
disagree with you though about the row source property, even though it
does
represent a set of records as you say. Until Access translates the string
accessed by the property and uses that data to build an actual recordset
object stored in memory, the row source is only a string.

There is something else that is kind of strange that I noticed... after
running the procedure that I outlined, the listbox does not immediately
reflect changes that were made as usual. However, I noticed that if I
alt-tab out to VB and alt-tab back, the changes magically appear! So then
it
looks like all that needs to be done is a repaint, but even after adding a
'me.repaint' line in after the code executes, the changes STILL don't
show.
So I guess that leads me back to the timing issue and thus the circle
continues.

The list box Row Source is just that: the source for what you see in the
list box. If the row source is a SELECT DISTINCT query to show the city
from an Address table, and you delete the only record in which the city
is
Boston, the lsit box will continue to show Boston until you either
requery
the list box to show the change, or you close and reopen the form.
Perhaps
that is what you mean by "generate its underlying recordset when it is
requeried".

To delete a record I would just do:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDelete

The acCmdSelectRecord may not be necessary in a single form, but I am not
exactly sure how that works.

You didn't post the delete query SQL, but to use that method you could
do:
CurrentDb.Execute strSQL
where strSQL is the "DELETE FROM SomeTable WHERE..." etc. string.

I think Refresh would be adequate, rather than Requery, so that the
recordset shows the changes after the delete. However, it will not
affect
the list box row source. For that you need to requery the list box
explicitly.

Me.mylistbox.Requery

I think you are making this more complex than it needs to be. Delete the
record, refresh the recordset, and requery the combo box. If you are
setting the row source in VBA code you will need to adjust the Row Source
SQL before requerying the list box.

If a list box has a recordset property, I have not figured out what to do
with it. If I try setting a list box recordset in code as you have done
I
get a run-time error. If the row source is a table/query the row source
is
a recordset, but it is still the list box row source, not its recordset.

After playing around with it a bit I discovered a few more things...

If I remove the AllRecords.Requery statement, the listbox recordset
will
never show the changes if requeried.

Also the procedure WILL work properly if I replace the
AllRecords.Requery
statement with some code that forces a complete recreation of
AllRecords.
But this is slow... if I could find a way to pause my code until the
requery
completes, I think that would be much faster.


:

Correct me if I'm wrong, but the way I understand it is that a
listbox's
rowsource property is used to generate its underlying recordset when
it
is
requeried. The reason why I am trying to handle its recordset outside
of
the
listbox object is to try and keep the its queries running on the
client
machine as much as is possible for fast searching. If I allow the
listbox
object to handle its own recordset by using the rowsource property
instead,
it would basically defeat the whole purpose of using the listbox in
the
first
place.

This is also the reason for the AllRecords recordset: each subsequent
query
is pulled from this client-side recordset instead of the server, but
AllRecords does need to be refreshed periodically and on demand when a
user
performs some action like adding or deleting a record (which is where
I
am
running into my problem). What I meant by saying that it doesn't work
is
simply that the changes that should show up in the list box don't when
the
procedure is first called. Here's the basic flow of the process to
help
clear things up:

1. User selects a record and clicks a delete button
2. Form runs a delete query on the main data table
3. AllRecords (which is a client-side representation of the main
table)
is
requeried so it will hopefully reflect these changes.
4. A new, filtered recordset is created from Allrecords

It appears the problem is that by the time step 4 executes, the change
in
the main data table is not reflected in the recordsets.

I've been reading up on this and it looks like the Jet engine does
allow
for
the creation of a new recordset using the openrecordset method before
the
parent recordset completes any queries it might be currently running.
I
don't know yet but perhaps the NextRecordset method could be a
solution.

:

OK, but I expect requerying the combo box after changing its row
source
is
needed nevertheless. I am familiar with setting the list box Row
Source in
code, but not the Recordset. If I understand correctly what you are
doing
you need to set the Row Source to rsNew, not the Recordset. After
that
you
need to requery the list box. It may be something like this:

Dim strSQL as String
strSQL = "SELECT DISTINCT [SomeField] FROM tblYourTable " & _
"ORDER BY [SomeField]"
Me.mylistbox.RowSource = strSQL
Me.mylistbox.Requery

Maybe you can use a Recordset as the row source. I don't see what
AllRecords represents, but I expect it is a named query, or maybe a
table.
I have to admit I do not understand the use of dbOpenSnapshot very
well, but
as I understand it is a static recordset that does not update right
away.
However, even if it works I doubt it is the most effective way of
doing
what
you need.

You say the procedure "does not work". In what way? Is there an
error
message?

Ignore rsOld... I accidentally copied it in with the rest of the
code.

The command button executes the sub in the usual way a command
button
would.

:

Ignore Aaron. Like a broken clock he may be right now and then,
but
not
enough to count on.

Not sure I see the point of rsOld, and I don't know quite what
you
mean
by a
listbox Recordset property unless it is an Access 2007 thing.
Did
the
code
compile? Are you referring to the Row Source? If so, you need
to
requery
the list box. If not, more information is needed, as the
explanation is
unclear.

Also, what do you mean by a "bound" command button? A command
button
cannot
have a control source.

I apologize if this is a duplicate post. I reposted because for
some
reason
I can't seem to get my posts to show up.

My form contains a persistent snapshot recordset that is used
to
create
a
new recordset which is in turn applied to a listbox's recordset
property.
Code is similar to the following:

AllRecords.Requery
Dim rsNew As DAO.Recordset, rsOld As DAO.Recordset
Set rsNew = AllRecords.OpenRecordset(dbOpenSnapshot)
Set mylistbox.Recordset = rsNew
Set rsNew = Nothing

The problem is that the listbox does not reflect changes made
to
the
underlying data after the procedure is called immediately
following a
delete
query. But the procedure does work when a bound command button
is
clicked.
So it seems like there is some sort of delay following the
AllRecords.requery
statement that is preventing the changes from propagating to
the
new
recordset.

Am I right that there is a delay and if so, how do I get around
this
problem?

 

[JString]

I meant recordsource, not rowsource :]
I've been working with listboxes for too long

 

[BruceM]

I expect you know that a form does not have a row source (from your last
paragraph), so I assume you mean the list box. I have agreed that a row
source can be a recordset, but that doesn't mean you can use a recordset
property to establish the row source. I have not been able to run code in
which I set a combo box recordset property. I expect it is the same for a
list box I have not tried, but perhaps you can set the *Row Source*
property of the combo box to the recordset rsNew as you showed in your code
several messages ago:
Set mylistbox.RowSource = rsNew
Me.mylistbox.Requery

You didn't say where this code is located. How often is the Row Source
reset? Every time you do you need to communicate with the server (I assume
the source of the recordset is there rather than local). I don't see the
value of setting a static recordset (if I understand dbOpenSnapshot
correctly) every time you run the code, but I may be missing something.

It may be best to start a new thread with a subject line such as:
Slow performance when setting list box recordset property

People who are more familiar than I with the recordset property in general
are more likely to see the question there than buried in this thread.

I know what you're suggesting, and thanks for trying to help me, but adding
an SQL string to the listbox's rowsource is going to demolish my project.
The whole purpose of the persistent snapshot recordset is to provide a
client
with everything it needs to perform a search and display the results in
the
listbox without having to query the server. This way, the only
communication
needed between the client and server should be an occasional requery every
couple of minutes.

I tried the Doevents procedure in several places in my code and found it
was
ineffective. I did find a solution though by launching a second form in
dialogue mode which executed the code that was having the timing problem.
Seems that the second form would not close until all queries had
completed.

BTW, I guarantee that any form that has a valid rowsource property set
will
also have a recordset at least while it's running.

Dim strSQL as String

strSQL = "SELECT [SomeFieldID], [SomeFieldName] " & _
"FROM [SomeTable] " & _
"ORDER BY [SomeFieldName]"

Me.ComboBoxName.RowSource = strSQL

There is your Row Source. You can add a WHERE condition, if you like.
The
code could be in a command button Click event or whatever. If the combo
box
Column Count is 2 and the Column widths are 0";1" the Row Source,
although
is "only" a string, will give you a combo box list that shows all records
from SomeTable, arranged by SomeFieldName. If the combo box is bound to
a
field, and the Bound Column is 1, and the Column Count and Widths are as
stated, selecting a name from the list will cause the ID to be stored in
that field.

I don't know what this means:
"Until Access translates the string accessed by the property and uses
that
data to build an actual recordset object stored in memory, the row source
is
only a string."
However, I can tell you that I and countless others have successfully
built
Row Sources for list boxes and combo boxes without creating a recordset
object. Even if you can use a recordset object as the Row Source, you
still
need to set the Row Source property to that object.

I don't know if you tried refreshing as I suggested. You tell me that
what
I am suggesting regarding the Row Source string (or named query) will not
work, but if you are not willing to give it a try I can only wish you
luck
with the approach to which you are committed.

BTW, DoEvents is the built-in function that "yields execution so that the
operating system can process other events." This is from VBA Help.

It is definately more complex than the usual way of using a listbox,
but
for
what it is that I'm trying to do, I don't know of a simpler way. I
have
to
disagree with you though about the row source property, even though it
does
represent a set of records as you say. Until Access translates the
string
accessed by the property and uses that data to build an actual
recordset
object stored in memory, the row source is only a string.

There is something else that is kind of strange that I noticed... after
running the procedure that I outlined, the listbox does not immediately
reflect changes that were made as usual. However, I noticed that if I
alt-tab out to VB and alt-tab back, the changes magically appear! So
then
it
looks like all that needs to be done is a repaint, but even after
adding a
'me.repaint' line in after the code executes, the changes STILL don't
show.
So I guess that leads me back to the timing issue and thus the circle
continues.

:

The list box Row Source is just that: the source for what you see in
the
list box. If the row source is a SELECT DISTINCT query to show the
city
from an Address table, and you delete the only record in which the
city
is
Boston, the lsit box will continue to show Boston until you either
requery
the list box to show the change, or you close and reopen the form.
Perhaps
that is what you mean by "generate its underlying recordset when it is
requeried".

To delete a record I would just do:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDelete

The acCmdSelectRecord may not be necessary in a single form, but I am
not
exactly sure how that works.

You didn't post the delete query SQL, but to use that method you could
do:
CurrentDb.Execute strSQL
where strSQL is the "DELETE FROM SomeTable WHERE..." etc. string.

I think Refresh would be adequate, rather than Requery, so that the
recordset shows the changes after the delete. However, it will not
affect
the list box row source. For that you need to requery the list box
explicitly.

Me.mylistbox.Requery

I think you are making this more complex than it needs to be. Delete
the
record, refresh the recordset, and requery the combo box. If you are
setting the row source in VBA code you will need to adjust the Row
Source
SQL before requerying the list box.

If a list box has a recordset property, I have not figured out what to
do
with it. If I try setting a list box recordset in code as you have
done
I
get a run-time error. If the row source is a table/query the row
source
is
a recordset, but it is still the list box row source, not its
recordset.

After playing around with it a bit I discovered a few more things...

If I remove the AllRecords.Requery statement, the listbox recordset
will
never show the changes if requeried.

Also the procedure WILL work properly if I replace the
AllRecords.Requery
statement with some code that forces a complete recreation of
AllRecords.
But this is slow... if I could find a way to pause my code until the
requery
completes, I think that would be much faster.


:

Correct me if I'm wrong, but the way I understand it is that a
listbox's
rowsource property is used to generate its underlying recordset
when
it
is
requeried. The reason why I am trying to handle its recordset
outside
of
the
listbox object is to try and keep the its queries running on the
client
machine as much as is possible for fast searching. If I allow the
listbox
object to handle its own recordset by using the rowsource property
instead,
it would basically defeat the whole purpose of using the listbox in
the
first
place.

This is also the reason for the AllRecords recordset: each
subsequent
query
is pulled from this client-side recordset instead of the server,
but
AllRecords does need to be refreshed periodically and on demand
when a
user
performs some action like adding or deleting a record (which is
where
I
am
running into my problem). What I meant by saying that it doesn't
work
is
simply that the changes that should show up in the list box don't
when
the
procedure is first called. Here's the basic flow of the process to
help
clear things up:

1. User selects a record and clicks a delete button
2. Form runs a delete query on the main data table
3. AllRecords (which is a client-side representation of the main
table)
is
requeried so it will hopefully reflect these changes.
4. A new, filtered recordset is created from Allrecords

It appears the problem is that by the time step 4 executes, the
change
in
the main data table is not reflected in the recordsets.

I've been reading up on this and it looks like the Jet engine does
allow
for
the creation of a new recordset using the openrecordset method
before
the
parent recordset completes any queries it might be currently
running.
I
don't know yet but perhaps the NextRecordset method could be a
solution.

:

OK, but I expect requerying the combo box after changing its row
source
is
needed nevertheless. I am familiar with setting the list box Row
Source in
code, but not the Recordset. If I understand correctly what you
are
doing
you need to set the Row Source to rsNew, not the Recordset.
After
that
you
need to requery the list box. It may be something like this:

Dim strSQL as String
strSQL = "SELECT DISTINCT [SomeField] FROM tblYourTable " & _
"ORDER BY [SomeField]"
Me.mylistbox.RowSource = strSQL
Me.mylistbox.Requery

Maybe you can use a Recordset as the row source. I don't see
what
AllRecords represents, but I expect it is a named query, or maybe
a
table.
I have to admit I do not understand the use of dbOpenSnapshot
very
well, but
as I understand it is a static recordset that does not update
right
away.
However, even if it works I doubt it is the most effective way of
doing
what
you need.

You say the procedure "does not work". In what way? Is there an
error
message?

Ignore rsOld... I accidentally copied it in with the rest of
the
code.

The command button executes the sub in the usual way a command
button
would.

:

Ignore Aaron. Like a broken clock he may be right now and
then,
but
not
enough to count on.

Not sure I see the point of rsOld, and I don't know quite what
you
mean
by a
listbox Recordset property unless it is an Access 2007 thing.
Did
the
code
compile? Are you referring to the Row Source? If so, you
need
to
requery
the list box. If not, more information is needed, as the
explanation is
unclear.

Also, what do you mean by a "bound" command button? A command
button
cannot
have a control source.

I apologize if this is a duplicate post. I reposted because
for
some
reason
I can't seem to get my posts to show up.

My form contains a persistent snapshot recordset that is
used
to
create
a
new recordset which is in turn applied to a listbox's
recordset
property.
Code is similar to the following:

AllRecords.Requery
Dim rsNew As DAO.Recordset, rsOld As DAO.Recordset
Set rsNew = AllRecords.OpenRecordset(dbOpenSnapshot)
Set mylistbox.Recordset = rsNew
Set rsNew = Nothing

The problem is that the listbox does not reflect changes
made
to
the
underlying data after the procedure is called immediately
following a
delete
query. But the procedure does work when a bound command
button
is
clicked.
So it seems like there is some sort of delay following the
AllRecords.requery
statement that is preventing the changes from propagating to
the
new
recordset.

Am I right that there is a delay and if so, how do I get
around
this
problem?

 

[JString]

It's all about the recordset... rowsource is optional. Remember that this is
only meant for browsing records, not editing them, and the snapshot
recordsets allow for blazing fast searches which are handled on the local
machine only. In many cases I would guess that this method would not make a
big difference, but when you're dealing with a fairly large list of records
in a multi-user environment with network traffic problems, it can come in
handy.

BTW I did find a solution, so I won't need to repost. Thank you for your
concern.

I expect you know that a form does not have a row source (from your last
paragraph), so I assume you mean the list box. I have agreed that a row
source can be a recordset, but that doesn't mean you can use a recordset
property to establish the row source. I have not been able to run code in
which I set a combo box recordset property. I expect it is the same for a
list box I have not tried, but perhaps you can set the *Row Source*
property of the combo box to the recordset rsNew as you showed in your code
several messages ago:
Set mylistbox.RowSource = rsNew
Me.mylistbox.Requery

You didn't say where this code is located. How often is the Row Source
reset? Every time you do you need to communicate with the server (I assume
the source of the recordset is there rather than local). I don't see the
value of setting a static recordset (if I understand dbOpenSnapshot
correctly) every time you run the code, but I may be missing something.

It may be best to start a new thread with a subject line such as:
Slow performance when setting list box recordset property

People who are more familiar than I with the recordset property in general
are more likely to see the question there than buried in this thread.

I know what you're suggesting, and thanks for trying to help me, but adding
an SQL string to the listbox's rowsource is going to demolish my project.
The whole purpose of the persistent snapshot recordset is to provide a
client
with everything it needs to perform a search and display the results in
the
listbox without having to query the server. This way, the only
communication
needed between the client and server should be an occasional requery every
couple of minutes.

I tried the Doevents procedure in several places in my code and found it
was
ineffective. I did find a solution though by launching a second form in
dialogue mode which executed the code that was having the timing problem.
Seems that the second form would not close until all queries had
completed.

BTW, I guarantee that any form that has a valid rowsource property set
will
also have a recordset at least while it's running.

Dim strSQL as String

strSQL = "SELECT [SomeFieldID], [SomeFieldName] " & _
"FROM [SomeTable] " & _
"ORDER BY [SomeFieldName]"

Me.ComboBoxName.RowSource = strSQL

There is your Row Source. You can add a WHERE condition, if you like.
The
code could be in a command button Click event or whatever. If the combo
box
Column Count is 2 and the Column widths are 0";1" the Row Source,
although
is "only" a string, will give you a combo box list that shows all records
from SomeTable, arranged by SomeFieldName. If the combo box is bound to
a
field, and the Bound Column is 1, and the Column Count and Widths are as
stated, selecting a name from the list will cause the ID to be stored in
that field.

I don't know what this means:
"Until Access translates the string accessed by the property and uses
that
data to build an actual recordset object stored in memory, the row source
is
only a string."
However, I can tell you that I and countless others have successfully
built
Row Sources for list boxes and combo boxes without creating a recordset
object. Even if you can use a recordset object as the Row Source, you
still
need to set the Row Source property to that object.

I don't know if you tried refreshing as I suggested. You tell me that
what
I am suggesting regarding the Row Source string (or named query) will not
work, but if you are not willing to give it a try I can only wish you
luck
with the approach to which you are committed.

BTW, DoEvents is the built-in function that "yields execution so that the
operating system can process other events." This is from VBA Help.

It is definately more complex than the usual way of using a listbox,
but
for
what it is that I'm trying to do, I don't know of a simpler way. I
have
to
disagree with you though about the row source property, even though it
does
represent a set of records as you say. Until Access translates the
string
accessed by the property and uses that data to build an actual
recordset
object stored in memory, the row source is only a string.

There is something else that is kind of strange that I noticed... after
running the procedure that I outlined, the listbox does not immediately
reflect changes that were made as usual. However, I noticed that if I
alt-tab out to VB and alt-tab back, the changes magically appear! So
then
it
looks like all that needs to be done is a repaint, but even after
adding a
'me.repaint' line in after the code executes, the changes STILL don't
show.
So I guess that leads me back to the timing issue and thus the circle
continues.

:

The list box Row Source is just that: the source for what you see in
the
list box. If the row source is a SELECT DISTINCT query to show the
city
from an Address table, and you delete the only record in which the
city
is
Boston, the lsit box will continue to show Boston until you either
requery
the list box to show the change, or you close and reopen the form.
Perhaps
that is what you mean by "generate its underlying recordset when it is
requeried".

To delete a record I would just do:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDelete

The acCmdSelectRecord may not be necessary in a single form, but I am
not
exactly sure how that works.

You didn't post the delete query SQL, but to use that method you could
do:
CurrentDb.Execute strSQL
where strSQL is the "DELETE FROM SomeTable WHERE..." etc. string.

I think Refresh would be adequate, rather than Requery, so that the
recordset shows the changes after the delete. However, it will not
affect
the list box row source. For that you need to requery the list box
explicitly.

Me.mylistbox.Requery

I think you are making this more complex than it needs to be. Delete
the
record, refresh the recordset, and requery the combo box. If you are
setting the row source in VBA code you will need to adjust the Row
Source
SQL before requerying the list box.

If a list box has a recordset property, I have not figured out what to
do
with it. If I try setting a list box recordset in code as you have
done
I
get a run-time error. If the row source is a table/query the row
source
is
a recordset, but it is still the list box row source, not its
recordset.

After playing around with it a bit I discovered a few more things...

If I remove the AllRecords.Requery statement, the listbox recordset
will
never show the changes if requeried.

Also the procedure WILL work properly if I replace the
AllRecords.Requery
statement with some code that forces a complete recreation of
AllRecords.
But this is slow... if I could find a way to pause my code until the
requery
completes, I think that would be much faster.


:

Correct me if I'm wrong, but the way I understand it is that a
listbox's
rowsource property is used to generate its underlying recordset
when
it
is
requeried. The reason why I am trying to handle its recordset
outside
of
the
listbox object is to try and keep the its queries running on the
client
machine as much as is possible for fast searching. If I allow the
listbox
object to handle its own recordset by using the rowsource property
instead,
it would basically defeat the whole purpose of using the listbox in
the
first
place.

This is also the reason for the AllRecords recordset: each
subsequent
query
is pulled from this client-side recordset instead of the server,
but
AllRecords does need to be refreshed periodically and on demand
when a
user
performs some action like adding or deleting a record (which is
where
I
am
running into my problem). What I meant by saying that it doesn't
work
is
simply that the changes that should show up in the list box don't
when
the
procedure is first called. Here's the basic flow of the process to
help
clear things up:

1. User selects a record and clicks a delete button
2. Form runs a delete query on the main data table
3. AllRecords (which is a client-side representation of the main
table)
is
requeried so it will hopefully reflect these changes.
4. A new, filtered recordset is created from Allrecords

It appears the problem is that by the time step 4 executes, the
change
in
the main data table is not reflected in the recordsets.

I've been reading up on this and it looks like the Jet engine does
allow
for
the creation of a new recordset using the openrecordset method
before
the
parent recordset completes any queries it might be currently
running.
I
don't know yet but perhaps the NextRecordset method could be a
solution.

:

OK, but I expect requerying the combo box after changing its row
source
is
needed nevertheless. I am familiar with setting the list box Row
Source in
code, but not the Recordset. If I understand correctly what you
are
doing
you need to set the Row Source to rsNew, not the Recordset.
After
that
you
need to requery the list box. It may be something like this:

Dim strSQL as String
strSQL = "SELECT DISTINCT [SomeField] FROM tblYourTable " & _
"ORDER BY [SomeField]"
Me.mylistbox.RowSource = strSQL
Me.mylistbox.Requery

Maybe you can use a Recordset as the row source. I don't see
what
AllRecords represents, but I expect it is a named query, or maybe
a
table.
I have to admit I do not understand the use of dbOpenSnapshot
very
well, but
as I understand it is a static recordset that does not update
right
away.
However, even if it works I doubt it is the most effective way of
doing

 

[BruceM]

A combo box or list box needs a row source in order to have a list. In that
sense it is not optional, so I have to think that I completely misunderstood
the question, and that you were never talking about the list at all. Good
luck with the project.

It's all about the recordset... rowsource is optional. Remember that this
is
only meant for browsing records, not editing them, and the snapshot
recordsets allow for blazing fast searches which are handled on the local
machine only. In many cases I would guess that this method would not make
a
big difference, but when you're dealing with a fairly large list of
records
in a multi-user environment with network traffic problems, it can come in
handy.

BTW I did find a solution, so I won't need to repost. Thank you for your
concern.

I expect you know that a form does not have a row source (from your last
paragraph), so I assume you mean the list box. I have agreed that a row
source can be a recordset, but that doesn't mean you can use a recordset
property to establish the row source. I have not been able to run code
in
which I set a combo box recordset property. I expect it is the same for
a
list box I have not tried, but perhaps you can set the *Row Source*
property of the combo box to the recordset rsNew as you showed in your
code
several messages ago:
Set mylistbox.RowSource = rsNew
Me.mylistbox.Requery

You didn't say where this code is located. How often is the Row Source
reset? Every time you do you need to communicate with the server (I
assume
the source of the recordset is there rather than local). I don't see the
value of setting a static recordset (if I understand dbOpenSnapshot
correctly) every time you run the code, but I may be missing something.

It may be best to start a new thread with a subject line such as:
Slow performance when setting list box recordset property

People who are more familiar than I with the recordset property in
general
are more likely to see the question there than buried in this thread.

I know what you're suggesting, and thanks for trying to help me, but
adding
an SQL string to the listbox's rowsource is going to demolish my
project.
The whole purpose of the persistent snapshot recordset is to provide a
client
with everything it needs to perform a search and display the results in
the
listbox without having to query the server. This way, the only
communication
needed between the client and server should be an occasional requery
every
couple of minutes.

I tried the Doevents procedure in several places in my code and found
it
was
ineffective. I did find a solution though by launching a second form
in
dialogue mode which executed the code that was having the timing
problem.
Seems that the second form would not close until all queries had
completed.

BTW, I guarantee that any form that has a valid rowsource property set
will
also have a recordset at least while it's running.

:

Dim strSQL as String

strSQL = "SELECT [SomeFieldID], [SomeFieldName] " & _
"FROM [SomeTable] " & _
"ORDER BY [SomeFieldName]"

Me.ComboBoxName.RowSource = strSQL

There is your Row Source. You can add a WHERE condition, if you like.
The
code could be in a command button Click event or whatever. If the
combo
box
Column Count is 2 and the Column widths are 0";1" the Row Source,
although
is "only" a string, will give you a combo box list that shows all
records
from SomeTable, arranged by SomeFieldName. If the combo box is bound
to
a
field, and the Bound Column is 1, and the Column Count and Widths are
as
stated, selecting a name from the list will cause the ID to be stored
in
that field.

I don't know what this means:
"Until Access translates the string accessed by the property and uses
that
data to build an actual recordset object stored in memory, the row
source
is
only a string."
However, I can tell you that I and countless others have successfully
built
Row Sources for list boxes and combo boxes without creating a
recordset
object. Even if you can use a recordset object as the Row Source, you
still
need to set the Row Source property to that object.

I don't know if you tried refreshing as I suggested. You tell me that
what
I am suggesting regarding the Row Source string (or named query) will
not
work, but if you are not willing to give it a try I can only wish you
luck
with the approach to which you are committed.

BTW, DoEvents is the built-in function that "yields execution so that
the
operating system can process other events." This is from VBA Help.

It is definately more complex than the usual way of using a listbox,
but
for
what it is that I'm trying to do, I don't know of a simpler way. I
have
to
disagree with you though about the row source property, even though
it
does
represent a set of records as you say. Until Access translates the
string
accessed by the property and uses that data to build an actual
recordset
object stored in memory, the row source is only a string.

There is something else that is kind of strange that I noticed...
after
running the procedure that I outlined, the listbox does not
immediately
reflect changes that were made as usual. However, I noticed that if
I
alt-tab out to VB and alt-tab back, the changes magically appear!
So
then
it
looks like all that needs to be done is a repaint, but even after
adding a
'me.repaint' line in after the code executes, the changes STILL
don't
show.
So I guess that leads me back to the timing issue and thus the
circle
continues.

:

The list box Row Source is just that: the source for what you see
in
the
list box. If the row source is a SELECT DISTINCT query to show the
city
from an Address table, and you delete the only record in which the
city
is
Boston, the lsit box will continue to show Boston until you either
requery
the list box to show the change, or you close and reopen the form.
Perhaps
that is what you mean by "generate its underlying recordset when it
is
requeried".

To delete a record I would just do:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDelete

The acCmdSelectRecord may not be necessary in a single form, but I
am
not
exactly sure how that works.

You didn't post the delete query SQL, but to use that method you
could
do:
CurrentDb.Execute strSQL
where strSQL is the "DELETE FROM SomeTable WHERE..." etc. string.

I think Refresh would be adequate, rather than Requery, so that the
recordset shows the changes after the delete. However, it will not
affect
the list box row source. For that you need to requery the list box
explicitly.

Me.mylistbox.Requery

I think you are making this more complex than it needs to be.
Delete
the
record, refresh the recordset, and requery the combo box. If you
are
setting the row source in VBA code you will need to adjust the Row
Source
SQL before requerying the list box.

If a list box has a recordset property, I have not figured out what
to
do
with it. If I try setting a list box recordset in code as you have
done
I
get a run-time error. If the row source is a table/query the row
source
is
a recordset, but it is still the list box row source, not its
recordset.

After playing around with it a bit I discovered a few more
things...

If I remove the AllRecords.Requery statement, the listbox
recordset
will
never show the changes if requeried.

Also the procedure WILL work properly if I replace the
AllRecords.Requery
statement with some code that forces a complete recreation of
AllRecords.
But this is slow... if I could find a way to pause my code until
the
requery
completes, I think that would be much faster.


:

Correct me if I'm wrong, but the way I understand it is that a
listbox's
rowsource property is used to generate its underlying recordset
when
it
is
requeried. The reason why I am trying to handle its recordset
outside
of
the
listbox object is to try and keep the its queries running on the
client
machine as much as is possible for fast searching. If I allow
the
listbox
object to handle its own recordset by using the rowsource
property
instead,
it would basically defeat the whole purpose of using the listbox
in
the
first
place.

This is also the reason for the AllRecords recordset: each
subsequent
query
is pulled from this client-side recordset instead of the server,
but
AllRecords does need to be refreshed periodically and on demand
when a
user
performs some action like adding or deleting a record (which is
where
I
am
running into my problem). What I meant by saying that it
doesn't
work
is
simply that the changes that should show up in the list box
don't
when
the
procedure is first called. Here's the basic flow of the process
to
help
clear things up:

1. User selects a record and clicks a delete button
2. Form runs a delete query on the main data table
3. AllRecords (which is a client-side representation of the main
table)
is
requeried so it will hopefully reflect these changes.
4. A new, filtered recordset is created from Allrecords

It appears the problem is that by the time step 4 executes, the
change
in
the main data table is not reflected in the recordsets.

I've been reading up on this and it looks like the Jet engine
does
allow
for
the creation of a new recordset using the openrecordset method
before
the
parent recordset completes any queries it might be currently
running.
I
don't know yet but perhaps the NextRecordset method could be a
solution.

:

OK, but I expect requerying the combo box after changing its
row
source
is
needed nevertheless. I am familiar with setting the list box
Row
Source in
code, but not the Recordset. If I understand correctly what
you
are
doing
you need to set the Row Source to rsNew, not the Recordset.
After
that
you
need to requery the list box. It may be something like this:

Dim strSQL as String
strSQL = "SELECT DISTINCT [SomeField] FROM tblYourTable " & _
"ORDER BY [SomeField]"
Me.mylistbox.RowSource = strSQL
Me.mylistbox.Requery

Maybe you can use a Recordset as the row source. I don't see
what
AllRecords represents, but I expect it is a named query, or
maybe
a
table.
I have to admit I do not understand the use of dbOpenSnapshot
very
well, but
as I understand it is a static recordset that does not update
right
away.
However, even if it works I doubt it is the most effective way
of
doing