|
|
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?
|
|
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
On Dec 28, 3:13 pm, JString <JStr...[ at ]discussions.microsoft.com> wrote:
[Quoted Text] > 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?
|
|
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.
"JString" <JString[ at ]discussions.microsoft.com> wrote in message
news:51198F9D-AF28-4F7B-91C6-CE03D6757C81[ at ]microsoft.com...
[Quoted Text] >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?
|
|
<AIMS FINGER, COCKS THUMB, DROPS THUMB, SMILES>
|
|
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.
"BruceM" wrote:
[Quoted Text] > 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.
>
> "JString" <JString[ at ]discussions.microsoft.com> wrote in message
> news:51198F9D-AF28-4F7B-91C6-CE03D6757C81[ at ]microsoft.com...
> >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?
>
>
|
|
rsOld is used to handle and close the old recordsets from the listbox.
|
|
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?
"JString" <JString[ at ]discussions.microsoft.com> wrote in message
news:0C46792A-D755-4808-BD00-100D54CE2B78[ at ]microsoft.com...
[Quoted Text] > 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.
>
> "BruceM" wrote:
>
>> 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.
>>
>> "JString" <JString[ at ]discussions.microsoft.com> wrote in message
>> news:51198F9D-AF28-4F7B-91C6-CE03D6757C81[ at ]microsoft.com...
>> >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?
>>
>>
|
|
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" <JString[ at ]discussions.microsoft.com> wrote in message
news:C57D9B27-FC49-47B9-8968-C37F439AFFF1[ at ]microsoft.com...
[Quoted Text] > rsOld is used to handle and close the old recordsets from the listbox.
|
|
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.
"BruceM" wrote:
[Quoted Text] > 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?
>
> "JString" <JString[ at ]discussions.microsoft.com> wrote in message
> news:0C46792A-D755-4808-BD00-100D54CE2B78[ at ]microsoft.com...
> > 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.
> >
> > "BruceM" wrote:
> >
> >> 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.
> >>
> >> "JString" <JString[ at ]discussions.microsoft.com> wrote in message
> >> news:51198F9D-AF28-4F7B-91C6-CE03D6757C81[ at ]microsoft.com...
> >> >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?
> >>
> >>
>
>
|
|
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.
"JString" wrote:
[Quoted Text] > 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.
>
> "BruceM" wrote:
>
> > 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?
> >
> > "JString" <JString[ at ]discussions.microsoft.com> wrote in message
> > news:0C46792A-D755-4808-BD00-100D54CE2B78[ at ]microsoft.com...
> > > 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.
> > >
> > > "BruceM" wrote:
> > >
> > >> 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.
> > >>
> > >> "JString" <JString[ at ]discussions.microsoft.com> wrote in message
> > >> news:51198F9D-AF28-4F7B-91C6-CE03D6757C81[ at ]microsoft.com...
> > >> >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?
> > >>
> > >>
> >
> >
|
|
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.
"JString" <JString[ at ]discussions.microsoft.com> wrote in message
news:ACB02030-B31D-4BE5-A2AD-166A824DA1B3[ at ]microsoft.com...
[Quoted Text] > 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.
>
>
> "JString" wrote:
>
>> 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.
>>
>> "BruceM" wrote:
>>
>> > 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?
>> >
>> > "JString" <JString[ at ]discussions.microsoft.com> wrote in message
>> > news:0C46792A-D755-4808-BD00-100D54CE2B78[ at ]microsoft.com...
>> > > 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.
>> > >
>> > > "BruceM" wrote:
>> > >
>> > >> 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.
>> > >>
>> > >> "JString" <JString[ at ]discussions.microsoft.com> wrote in message
>> > >> news:51198F9D-AF28-4F7B-91C6-CE03D6757C81[ at ]microsoft.com...
>> > >> >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?
>> > >>
>> > >>
>> >
>> >
|
|
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.
"BruceM" wrote:
[Quoted Text] > 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.
>
> "JString" <JString[ at ]discussions.microsoft.com> wrote in message
> news:ACB02030-B31D-4BE5-A2AD-166A824DA1B3[ at ]microsoft.com...
> > 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.
> >
> >
> > "JString" wrote:
> >
> >> 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.
> >>
> >> "BruceM" wrote:
> >>
> >> > 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?
> >> >
> >> > "JString" <JString[ at ]discussions.microsoft.com> wrote in message
> >> > news:0C46792A-D755-4808-BD00-100D54CE2B78[ at ]microsoft.com...
> >> > > 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.
> >> > >
> >> > > "BruceM" wrote:
> >> > >
> >> > >> 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.
> >> > >>
> >> > >> "JString" <JString[ at ]discussions.microsoft.com> wrote in message
> >> > >> news:51198F9D-AF28-4F7B-91C6-CE03D6757C81[ at ]microsoft.com...
> >> > >> >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?
> >> > >>
> >> > >>
> >> >
> >> >
>
>
|
|
|