.Requery does not requery listbox
Brendan Reynolds
The reason I think this is the following from your original post:
<begin quote>
I then either need to reopen the form, or reselect the figure two or
three times, or press F9 also two or three times to get the listbox
updated.
<end quote>
So the list box does, eventually, show the new record. We know the data is
getting into the table, and we know that the record source of the list box
is not excluding that record.
I wonder if using Set db = DBEngine(0)(0) instead of Set db = CurrentDb
would make any difference?
--
Brendan Reynolds
bren...@indigo.ie
Access 2000/2002 sample app at
http://brenreyn.brinkster.net
----------------------------
"Sabine Oebbecke" <sabine....@unilever.com> wrote in message
news:3d543b8a$0$189$7586...@news.frii.net...
> Hi Bruce,
>
> No, I haven't decompiled my dbase during the past 1.5 years. Now that
> you mention it :-), I remember that I once found some information on
> this undocumented possibility in the Internet, and then made it once
> with my dbase and never again. Good idea, I will try it on my second PC
> whether this will fix the problem with my original code. In the meantime
> you may have seen my last posting that I totally changed the code to a
> Docmd.RunSQL, which also writes the value from the combo box into the
> table, then the requery on the listbox is made, and it then worked
> immediately and perfectly.
>
> Also many thanks for your offer to have a look at my complete dbase.
> Well, the backend has a size of 1.5 MB zipped, the frontend is 6.6 mb
> zipped (although I think I get something around 4 MB if I remove all the
> reports). The problem would be, I guess, to get it running on a PC with
> a foreign language, as when I started with my dbase approx 3 years ago,
> I was an absolute beginner (and I daily note there is still so much to
> learn) and used spaces in between my table names, etc., used dashes in
> field names and, most importantly, used the ä and ü from the German
> writing which I could imagine confuses PCs in other languages.
> Nevertheless, it would be worth trying, and if I do not get my other
> form running (as I mentioned in one of my earlier postings on this
> issue, I have two forms where this .Requery doesn't work), I would like
> to get back to you, if it is okay for you and the sizes of the zipped
> front and backend dbase not too big.
>
> Many thanks again for taking the time to answer my postings! I really
> appreciate it.
>
> Regards,
> Sabine
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
Sabine Oebbecke
No, using the Set db = DBEngine(0)(0) instead of Set db = CurrentDB()
did not solve the problem as well. I have tested it in my other form
where I had the same problem with a listbox.
In this other form I had a totally different code, but again it was that
a listbox on the same form was not updated by the .Requery (only after
pressing F9 several times or reopening the form, the new value in the
listbox was shown). So in this other code I have now replaced a
Set qd = db.CreateQueryDef("", mySQL)
qd.Execute
simply with
DoCmd.RunSQL (mySQL)
and again this solved the problem so that the .Requery on the listbox at
the end of the code worked.
As I am not such an expert, I do not know why this - for me - minor
change has such an effect. May be you have an explanation for it.
Well, it works now :-), and this is the most important thing.
Many thanks again and best regards,
Brendan Reynolds
further indication that the problem has something to do with timing or
caching.
--
Brendan Reynolds
bren...@indigo.ie
Access 2000/2002 sample app at
http://brenreyn.brinkster.net
----------------------------
"Sabine Oebbecke" <sabine....@unilever.com> wrote in message
news:3d577ed5$0$188$7586...@news.frii.net...
David W. Fenton
<3d577ed5$0$188$7586...@news.frii.net>:
>No, using the Set db = DBEngine(0)(0) instead of Set db =
>CurrentDB() did not solve the problem as well. I have tested it in
>my other form where I had the same problem with a listbox.
>
>In this other form I had a totally different code, but again it
>was that a listbox on the same form was not updated by the
>.Requery (only after pressing F9 several times or reopening the
>form, the new value in the listbox was shown). So in this other
>code I have now replaced a
>
>Set qd = db.CreateQueryDef("", mySQL)
>qd.Execute
>
>simply with
>
>DoCmd.RunSQL (mySQL)
>
>and again this solved the problem so that the .Requery on the
>listbox at the end of the code worked.
>
>As I am not such an expert, I do not know why this - for me -
>minor change has such an effect. May be you have an explanation
>for it.
>
>Well, it works now :-), and this is the most important thing.
I lost the description of the original problem a long time ago, but
is your SQL creating a temp table or something? If so, you'd need
to refresh the TableDefs collection before things would work.
Perhaps .RunSQL refreshes the collections?
In any event, I don't understand why you'd use a QueryDef here in
order to populate a listbox. Sounds awfully complicated.
Didn't your original problem have something to do with adding items
to the listbox?
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Sabine Oebbecke
Thanks for giving your view.
My original problem was that I had two listboxes in two different forms
where the Listbox.Requery did not work, although I have lots of other
listboxes/controls in my dbase where the .Requery updates the
listboxes/controls perfectly.
>In any event, I don't understand why you'd use a QueryDef here in order
to populate a listbox. Sounds awfully complicated.<
In Form 2 it is a calculation of membership fees where at the end the
respective listbox should show up the booking year(s) for which
calculation has already been done. But here it was the same: The
calculation of the fees was correctly carried out, but the listbox only
showed the correct year(s) when I re-entered the form or pressed F9 for
several times.
In neither codes temporary tables are being created but the values are
put into tables which exist in my dbase.
The Docmd.RunSQL (MySQL) code, which I use now, was once given to me by
Fred Parker who responded to another problem which I had also with
listboxes (transferring data from one listbox to another by using
command buttons >, >>, <, <<, where also the listboxes were not
correctly updated by the .Requery), and it looks as if it is THE
solution for any problems with listboxes I have in my dbase. It is only
bad that I remembered it so late, on the other hand bringing this issue
up again may help other people to find a solution for their .Requery
problem.
I could imagine that the Docmd.RunSQL (MySQL) works more "direct" /
"straight" in my dbase so that 1. as Brendan assumes, no timing/caching
problem comes up,
and 2. as you say, the collection is refreshed where "the collection" =
my dbase.
Best regards and thanks again for all your advice,
David W. Fenton
>I could imagine that the Docmd.RunSQL (MySQL) works more "direct"
>/ "straight" in my dbase so that 1. as Brendan assumes, no
>timing/caching problem comes up,
>and 2. as you say, the collection is refreshed where "the
>collection" = my dbase.
If that's the case, then refreshing the relevant collections of
CurrentDB() before the requery should have exactly the same effect
as the DoCmd.RunSQL.
Sabine Oebbecke
In my previous Form 2 (which I still kept), I just tried a
db.TableDefs.Refresh
db.QueryDefs.Refresh
and thereafter the Listbox.Requery, again no success. The listbox won’t
requery. The Docmd.RunSQL (MySQL) seems to be the only bit of code that
helps. Looks as if the solution, which solved my problem, remains a
riddle. Well, it works and that is what counts.
Thanks again and best regards,
David W. Fenton
>In my previous Form 2 (which I still kept), I just tried a
>
>db.TableDefs.Refresh
>db.QueryDefs.Refresh
>
>and thereafter the Listbox.Requery, again no success. The listbox
>won’t requery. The Docmd.RunSQL (MySQL) seems to be the only bit
>of code that helps. Looks as if the solution, which solved my
>problem, remains a riddle. Well, it works and that is what counts.
Yes, but it bothers me to depend on a Docmd statement, because I
just don't like doing that.
I still wonder if your underlying approach to the problem is the
right one, though. I have never found a circumstance where I needed
to write temp tables or querydefs for a listbox. Why not just write
the rowsource in code? Why does it need to be SQL?
Sabine Oebbecke
>but it bothers me to depend on a Docmd statement,
>because I just don't like doing that.
Why does it bother you to depend on a Docmd statement when it works
perfectly ? Could there be any negative effect which I do not notice yet
? So far it works in my Acc97 dbase, also in the same dbase under
Access2000 and 2002.
> I still wonder if your underlying approach to the
> problem is the right one, though.
The thing is that I spent weeks on solving the annoying problem with
some of my Listbox(es).Requery not working. During this time I tried so
many approaches, completely rewriting codes, recreating forms, listboxes
etc. This especially counts for my first listbox problem which you will
find under
http://groups.google.com/groups?hl=de&lr=&ie=UTF-8&threadm=9a6683d.02022
10341.24f8c769%40posting.google.com&rnum=3&prev=/groups%3Fas_epq%3Dsabin
e%2520oebbecke%26ie%3DISO-8859-1%26lr%3D%26hl%3Dde
Here it was also that the code(s) worked properly in the form itself and
at the beginning a listbox.requery updated the listbox, but as soon as
this form was "bound" in / connected to my dbase, it did not work.
> Why not just write the rowsource in code?
In this form amongst other things I tried a rowsource by code which also
brought no success... Fred Parker’s (No Spam) code then worked
perfectly whenever a Listbox.Requery did not have any effect.
> I have never found a circumstance where I needed to write
> temp tables or querydefs for a listbox.
I do not have temp tabledefs, but some temp querydefs which, however,
update existing tables in my frontend, sometimes backend dbase. Then the
listboxes get their information from the row source SELECT DISTINCTROW.
I guess it must be the totality of my dbase that brings up this strange
problem, and I am glad to have one solution that works.
Best regards,
David W. Fenton
>>but it bothers me to depend on a Docmd statement,
>>because I just don't like doing that.
>
>Why does it bother you to depend on a Docmd statement when it
>works perfectly ? Could there be any negative effect which I do
>not notice yet ? So far it works in my Acc97 dbase, also in the
>same dbase under Access2000 and 2002.
Well, in regard to this particular functionality, DoCmd.RunSQL is a
very high-level wrapper around functionality that can be
implemented directly with DAO code. I don't remember if you have to
turn off .SetWarnings for it, or not, but the whole point is that
you are depending on a high-level wrapper function, rather than
doing the same thing directly.
That said, I agree with you that if you've got it working, you've
got it working, however ugly the method may be! :)
>> I still wonder if your underlying approach to the
>> problem is the right one, though.
>
>The thing is that I spent weeks on solving the annoying problem
>with some of my Listbox(es).Requery not working. During this time
>I tried so many approaches, completely rewriting codes, recreating
>forms, listboxes etc. This especially counts for my first listbox
>problem which you will find under
>http://groups.google.com/groups?hl=de&lr=&ie=UTF-8&threadm=9a6683d.
>02022
>10341.24f8c769%40posting.google.com&rnum=3&prev=/groups%3Fas_epq%3D
>sabin e%2520oebbecke%26ie%3DISO-8859-1%26lr%3D%26hl%3Dde
OK, first of all I don't quite understand why you need a saved
querydef. What you're wanting to do can be done without a saved
querydef.
Where you have:
qdf.SQL = "UPDATE TabMembers SET TabMembers.Selection = -1 _
WHERE MemberNo = " & Forms!FrmTest.Listbox1.Column(0)
you could just as easily have:
strSQL = "UPDATE TabMembers SET TabMembers.Selection = -1 "
strSQL = strSQL & " WHERE MemberNo = " &
Forms!FrmTest.Listbox1.Column(0) db.Execute strSQL, dbFailOnError
There is absolutely no need to save a querydef to do this. None.
Once that's been done, requerying the two listboxes should do the
trick.
For troubleshooting, I'd say you'd want to step through the code
before the listboxes are requeried and then check the source data
tables to see if the SQL has actually been properly executed. Also,
you should try pasting the rowsources into new queries and then
running the db.Execute statement and then running the new queries.
If the queries don't update, then you can see why the
listbox.requery isn't giving you the new data.
>Here it was also that the code(s) worked properly in the form
>itself and at the beginning a listbox.requery updated the listbox,
>but as soon as this form was "bound" in / connected to my dbase,
>it did not work.
What do you mean by that? Something has to be failing. You need to
figure out if the .Requery is failing, or if the actual update is
failing. If the listboxes are not getting requeried, I'd then check
if the the rowsources themselves would properly requery.
If the problem continues to be in the requerying of the listbox,
I'd then look at this:
With Me
.Listbox1.Requery
.Listbox2.Requery
End With
and change it to:
Me!Listbox1.Requery
Me!Listbox2.Requery
or:
With Me
!Listbox1.Requery
!Listbox2.Requery
End With
and see if that helps. I don't believe in ever using the dot
operator for references to controls or fields. The dot operator is
for properties and methods of the form, and controls and fields in
the underying recordset are neither.
If you change all of that and it still doesn't work, then your form
must be corrupt. I'd then do an
Application.SaveAsText/.LoadFromText and see if the new form works.
>> Why not just write the rowsource in code?
>
>In this form amongst other things I tried a rowsource by code
>which also brought no success... Fred Parker’s (No Spam) code
>then worked perfectly whenever a Listbox.Requery did not have any
>effect.
I don't see anything in his code that is different, except that he
accounts for the possibility of a multiselect listbox (I assumed
that your listboxes were simple listboxes).
It doesn't change anything at all except the method for updating
the underlying data.
I understand that you're saying it works now, but the problem has
really not been identified.
>> I have never found a circumstance where I needed to write
>> temp tables or querydefs for a listbox.
>
>I do not have temp tabledefs, but some temp querydefs which,
>however, update existing tables in my frontend, sometimes backend
>dbase. Then the listboxes get their information from the row
>source SELECT DISTINCTROW.
You don't need to save a querydef to run an update query.
>I guess it must be the totality of my dbase that brings up this
>strange problem, and I am glad to have one solution that works.
I think your form is corrupt.
Sabine Oebbecke
Hi David,
> Well, in regard to this particular functionality,
> ... rest skipped
Thanks for the explanation. And - it looks as if it is not necessary to
turn off .SetWarnings. It also works without. Although ... I think I
better put it in ...
> That said, I agree with you that if you've got it
> working, you've got it working, however ugly the
> method may be! :)
I like that :-).
> There is absolutely no need to save a querydef
> to do this. None.
Well, I still count myself as a beginner in Access programming. You know
how it is as a beginner, one searches through books and also a bit
through the Internet, one finds some code ... one tries it and it works.
Done! Why whatever code works and what the code is really doing, this is
more or less mostly not clear and not so important to a beginner. But I
will now take up your advice and check my dbase for these kinds of
querydefs, and will change them.
>Where you have:
>qdf.SQL = "UPDATE TabMembers SET TabMembers.Selection = -1 _
>WHERE MemberNo = " & Forms!FrmTest.Listbox1.Column(0)
>you could just as easily have:
>strSQL = "UPDATE TabMembers SET TabMembers.Selection = -1 "
>strSQL = strSQL & " WHERE MemberNo = " &
>Forms!FrmTest.Listbox1.Column(0) db.Execute strSQL, dbFailOnError
Except for the last line of your code with the db.Execute this is the
same as Fred Parker advised. Fred's code only contained as last line
'my' famous Docmd.RunSQL (strSQL). Nevertheless I have now tried your
db.Execute.
> For troubleshooting, I'd say you'd want to step
> through the code before the listboxes are requeried
> and then check the source data tables to see if the
> SQL has actually been properly executed.
I checked it - the table has been correctly updated so I would say that
the SQL has been properly executed.
And now it's getting interesting:
> Also,you should try pasting the rowsources into new
> queries and then running the db.Execute statement and
> then running the new queries.
(Hey, that’s a great trick)
I did it - I created two select queries:
Query1 where TabMembers.Selection = 0
Query2 where TabMembers.Selection = -1
I then ran the db.Execute and then the two select queries with
qry1 = "Query1"
qry2 = "Query2"
DoCmd.OpenQuery qry1, acNormal, acEdit
DoCmd.OpenQuery qry2, acNormal, acEdit
(I also got this out of a book :-) )
so that the queries were opened.
And the result is ...
Query1 still contains the respective member with the value "-1" in the
selection field, although the query should only show those with a "0".
Query2 does not show this member with the "-1".
So here we are. The Listbox.Requery cannot work, as the rowsource is not
correct.
And again, changing the db.Execute strSQL to DoCmd.RunSQL (strSQL)
brought the wanted result: The two queries showed exactly what they
should contain: Query1 all those members with Selection = "0", Query2
those with "-1".
> I don't see anything in his code that is different, ...
But there is the difference db.Execute strSQL / DoCmd.RunSQL (strSQL),
or am I wrong?
> ... except that he accounts for the possibility of a
> multiselect listbox
> (I assumed that your listboxes were simple listboxes).
Yes, the listboxes in Form 1 and Form 2 (from the beginning of this
post) are simple listboxes, but the one we are now talking of is a
multiselect one.
And this brings us to your
> It doesn't change anything at all except the
> method for updating the underlying data.
So in my dbase the DoCmd.RunSQL method seems to be the only method
working properly in some of my forms to get the listboxes’ underlying
data correctly updated. I love this ugly method :-).
But this does not explain why the one method works, the other not. Am I
right ? I don't think my form is corrupt.
Also many thanks for all your other useful hints. I will remember them.
David W. Fenton
[]
>> There is absolutely no need to save a querydef
>> to do this. None.
>Well, I still count myself as a beginner in Access programming.
>You know how it is as a beginner, one searches through books and
>also a bit through the Internet, one finds some code ... one tries
>it and it works. Done! Why whatever code works and what the code
>is really doing, this is more or less mostly not clear and not so
>important to a beginner. But I will now take up your advice and
>check my dbase for these kinds of querydefs, and will change them.
I understand entirely! Today I was at a client whose app was my
third professional app ever. In fact, it started life in Access95
(we converted pretty quickly!). That app has tons of horrid, horrid
things in it (from my point of view today) that are there because,
like you, I had figured out one way to do it, even if it wasn't the
best way.
>>Where you have:
>>qdf.SQL = "UPDATE TabMembers SET TabMembers.Selection = -1 _
>>WHERE MemberNo = " & Forms!FrmTest.Listbox1.Column(0)
>
>>you could just as easily have:
>
>>strSQL = "UPDATE TabMembers SET TabMembers.Selection = -1 "
>>strSQL = strSQL & " WHERE MemberNo = " &
>>Forms!FrmTest.Listbox1.Column(0) db.Execute strSQL, dbFailOnError
>
>Except for the last line of your code with the db.Execute this is
>the same as Fred Parker advised. Fred's code only contained as
>last line 'my' famous Docmd.RunSQL (strSQL). Nevertheless I have
>now tried your db.Execute.
Does it work?
Well, I wonder why it works with the DoCmd.RunSQL?
Have you tried replacing -1 with True and 0 with False?
>And again, changing the db.Execute strSQL to DoCmd.RunSQL (strSQL)
>brought the wanted result: The two queries showed exactly what
>they should contain: Query1 all those members with Selection =
>"0", Query2 those with "-1".
This is so very weird. Would it be too much for you to pull the
necessary parts out into a sample database and send it to me? I'd
really like to chase this down -- it's the kind of thing that can
cost anyone many hours and if we can figure out why, the record on
Google could help people.
>> I don't see anything in his code that is different, ...
>But there is the difference db.Execute strSQL / DoCmd.RunSQL
>(strSQL), or am I wrong?
Dunno what the context is now, so you're probably right. So far as
I'm concerned, there really is no functional difference between the
two, except that you can conrol which database the SQL is executed
in.
Actually, that's a point: is your database variable pointing to
CurrentDB() or to a different db?
>> ... except that he accounts for the possibility of a
>> multiselect listbox
>> (I assumed that your listboxes were simple listboxes).
>Yes, the listboxes in Form 1 and Form 2 (from the beginning of
>this post) are simple listboxes, but the one we are now talking of
>is a multiselect one.
I wouldn't do it with multiple SQL updates as Fred's code did.
Instead, I'd construct a list of the ID numbers for the records you
want to update, and then have the WHERE clause of the update SQL
be:
WHERE FieldID IN ([list])
The list would be stored in a string variable and would be of the
form "1, 2, 3, 4" etc.
>And this brings us to your
>> It doesn't change anything at all except the
>> method for updating the underlying data.
>So in my dbase the DoCmd.RunSQL method seems to be the only method
>working properly in some of my forms to get the listboxes’
>underlying data correctly updated. I love this ugly method :-).
The one difference is that DoCmd.RunSQL strSQL is equivalent to:
db.Execute strSQL
if and only if the database referred to by the db variable is the
CurrentDB(). If it's a different database, then that means the
update will take place in *that* database.
>But this does not explain why the one method works, the other not.
>Am I right ? I don't think my form is corrupt.
Are you using CurrentDB() for setting your db variable?
>Also many thanks for all your other useful hints. I will remember
>them.
Anything you learn from this kind of hair pull will be something
you'll remember for a long time!
Sabine Oebbecke
> That app has tons of horrid, horrid things in
> it (from my point of view today) that are there
> because, like you, I had figured out one way to
> do it, even if it wasn't the best way.
Hey, but the app works, and that’s the point, isn’t it :-)!
AND - is it always sure what the best way is ... ?
> Well, I wonder why it works with the DoCmd.RunSQL?
> Have you tried replacing -1 with True and 0 with False?
Cannot answer your first question (you’re the expert:-) but regarding
the second question I mean to remember, yes, I also tried a True and
False.
> Actually, that's a point: is your database
> variable pointing to CurrentDB() or to a different db?
No, in all affected forms it points to the CurrentDB().
>Are you using CurrentDB() for setting your db variable?
Yes, Set db = CurrentDB()
> This is so very weird.
David, the problem with THIS form (two listboxes and transferring the
info from one listbox to the other) is even more weird! Sorry, if the
story gets a bit long: I think it took me almost half a year to figure
out that I HAVE a problem with this form and the transfer... It started
that some day I noted that the code did not properly transfer the data
and the listbox.requery did not update the listbox(es), although I was
absolutely (!) sure that when this form was firstly set up, everything
was fine with it (got the idea for this form and its original code also
from somewhere). So, I noted that the listbox did not requery and
started right away to check the code: went into design view of the form,
changed a bit here and there, checked the form in normal view, no
success. Late at night I was fed up, so I stopped working on this form,
shut down the computer. Next morning, I immediately started to work
again on this form. So I opened my Access, opened right away the form in
design view, changed again a bit of code here and there, went back into
normal view, and it worked! Then I opened the form via my FrmMenu, and
everything was okay … I thought. Background information: I have a
FrmMenu which is the main form of my dbase. It contains a kind of
Navigator with command buttons on the left hand side, then it has got a
subform into which I "mirror" my other forms via the SourceObject
method. If you have a look on
http://www.vis-team.de/download/og-vis2000_bilder/000001.gif (you can go
up to 000040.gif), you will see what I mean. However, with this form it
was still a bit different, because from one 'mirrored' subform this
respective form was in fact opened with DoCmd.OpenForm on a command
button, it wasn't mirrored. So I could also check this form on its own,
changing from design to normal view, as it was not 'bound' in my
FrmMenu!Subform and I did not need to go via the FrmMenu.
So, I thought it worked, and again I shut down my computer, and in the
afternoon I wanted to show my success to a friend. So I opened my
Access, opened my dbase, went via the FrmMenu and the subform to open
the respective form ... and again it was that the listboxes were not
requeried.
So there was a difference between opening the form on its own after
Access was started, and opening the form via the FrmMenu after Access
was started. And this is not only valid for Access 97, but also for
Access 2000 and Access 2002, which I have on three different PCs.
Then I completely changed the code (it was originally also something
with rst.Edit and rst.Update) to the qdf.SQL = "UPDATE TabMembers SET …
bla bla bla" and qdf.Execute. And again it was, that when I newly opened
Access and then my form, everything was okay and the info correctly
transferred from one listbox to the other. Did I start with opening
Access, going via my FrmMenu and the subform to open the form, it did
not work. That was when I posted my call for help “Problem with
Listboxes: Getting data from one listbox to the other”, and luckily Fred
gave his advice.
> Would it be too much for you to pull the necessary parts
> out into a sample database and send it to me?
No, it wouldn't, but I doubt that the sample dbase will show the same
problem. As I mentioned to Brendan who also offered me to have a look, I
reduced the dbase to the affected forms, tables, queries and codes
(which however still contained the FrmMenu and the SourceObject method),
and then everything was OK and the listbox in Form1 got correctly
updated. Nevertheless, I will have a go and let you know the outcome.
Which brings me to the size of my dbase … It has approx 125 MB, of
course growing and growing to over 200 MB when I work on it so that
regularly I need to get everything transferred into a new and clean
dbase. Could the size of the (also fresh) dbase be a reason for this
problem?
> I wouldn't do it with multiple SQL updates as Fred's code did.
> Instead, I'd construct a list of the ID numbers for the records you
> want to update, and then have the WHERE clause of the update SQL
> be:
> WHERE FieldID IN ([list])
> The list would be stored in a string variable and would be of the
> form "1, 2, 3, 4" etc.
And again something new for me. Thanks! I’ll try and work it out for my
form.
David W. Fenton
>Which brings me to the size of my dbase … It has approx 125 MB,
>of course growing and growing to over 200 MB when I work on it so
>that regularly I need to get everything transferred into a new and
>clean dbase. Could the size of the (also fresh) dbase be a reason
>for this problem?
I can't imagine how it could be.
Clearly, it sounds like something that happens *before* you open
your form is putting Access in a state that prevents the updates
from happening.
Have you tried replacing your DoCmd.RunSQL strSQL with:
db.Execute strSQL, dbFailOnError
That dbFailOnError switch is really important, because if the
update fails, it will prompt you. I don't know what happens with
DoCmd.RunSQL when an update fails.
Sabine Oebbecke
> Have you tried replacing your DoCmd.RunSQL strSQL with:
> db.Execute strSQL, dbFailOnError
Yes, and I was not prompted that the update failed.
> Would it be too much for you to pull the
> necessary parts out into a sample database
> and send it to me?
Here we are, as I have already assumed and experienced: I have set up a
new dbase with the respective forms, queries and tables, and again it is
that then everything is fine and your db.Execute strSQL, dbFailOnError
works perfectly. Only in my normal dbase all codes do not work properly
except for the DoCmd.RunSQL. I think it is a very specific problem only
connected to my dbase. The codes themselves are okay.
Regards,
David W. Fenton
>> Have you tried replacing your DoCmd.RunSQL strSQL with:
>> db.Execute strSQL, dbFailOnError
>Yes, and I was not prompted that the update failed.
That's scary, actually. Are you sure the line of code was
executing?
>> Would it be too much for you to pull the
>> necessary parts out into a sample database
>> and send it to me?
>Here we are, as I have already assumed and experienced: I have set
>up a new dbase with the respective forms, queries and tables, and
>again it is that then everything is fine and your db.Execute
>strSQL, dbFailOnError works perfectly. Only in my normal dbase all
>codes do not work properly except for the DoCmd.RunSQL. I think it
>is a very specific problem only connected to my dbase. The codes
>themselves are okay.
Does your form depend on any code in other modules?
Have you completely recreated your db from scratch?
My questions are intended to suggest that you have code corruption
somewhere in your database, and if something like that become
unreliable in one of my apps, that would be the first thing I'd
assume, and I'd then take appropriate action.
I assume you've decompiled, etc., long ago, and numerous times?
Sabine Oebbecke
Hi David,
> Are you sure the line of code was executing?
Yes, I would say so as the table is correctly updated with the code
strSQL = "UPDATE TabMembers SET TabMembers.Selection = -1 WHERE MemberNo
= " & Forms!FrmTest.Listbox1.Column(0)
> Does your form depend on any code in other modules?
No. All "Private Sub", no call of other codes in any module.
> Have you completely recreated your db from scratch?
The one I wanted to e-mail to you: yes. My normal dbase: Well, as the
dbase I am working with is growing and growing, I create a new db from
time to time and then import everything into it with "Get External Data"
to get it down to its normal size of approx. 125 MB.
> I assume you've decompiled, etc., long ago, and
> numerous times?
Decompiled ...? Well, if you mean the "C:\Program Files\Microsoft
Office\Office\msaccess.exe" c:\mydb.mdb/decompile, I did this only once
long time ago on my Access97 dbase. The normal "Compile and Save All
Modules" I do not often use in my Access97 dbase. But as I regularly
create an MDE (which shows the same behaviour with the listboxes), is it
not that the "Compile and Save All Modules" is done by Access during
creation of the MDE ? But it does not give me an error. However, I use
the same dbase in Access2000 and Access2002, and as always my Access97
dbase is the basic dbase which I then convert into Access2000 and then
from Access2000 into Access2002, I regularly use the "Compile and Save
All Modules" in those 00 and 02 dbases which also does not give me an
error message. The Access00 and 02 dbases have the same problem with the
listboxes.
Which reminds me that Bruce suggested to make the /decompile on my dbase
which I have not yet done ...
David W. Fenton
>> Are you sure the line of code was executing?
>Yes, I would say so as the table is correctly updated with the
>code strSQL = "UPDATE TabMembers SET TabMembers.Selection = -1
>WHERE MemberNo = " & Forms!FrmTest.Listbox1.Column(0)
Yes, of course -- I'd forgotten that.
>> Does your form depend on any code in other modules?
>No. All "Private Sub", no call of other codes in any module.
OK. What other forms/reports are open when the problem occurs?
Anything at all?
>> Have you completely recreated your db from scratch?
>The one I wanted to e-mail to you: yes. My normal dbase: Well, as
>the dbase I am working with is growing and growing, I create a new
>db from time to time and then import everything into it with "Get
>External Data" to get it down to its normal size of approx. 125
>MB.
I would try the decompile, then compact, then import everything
into a new database then compile and save all. If that has no
effect, then try Application.SaveAsText and
Application.LoadFromText. Corruption might survive the import, but
it can never survive .SaveAsText.
However, I have found that it's important to do your whole project,
all the items with code in them, as apparently the data streams
storing the actually compiled code are not wholly independent.
Also, there's the issue of subforms, etc., so I've found it best to
simply export and import everything (i.e., using .SaveAsText).
I believe I have code for this somewhere (though it's not
complicated code), if you'd like to have it.
>> I assume you've decompiled, etc., long ago, and
>> numerous times?
>Decompiled ...? Well, if you mean the "C:\Program Files\Microsoft
>Office\Office\msaccess.exe" c:\mydb.mdb/decompile, I did this only
>once long time ago on my Access97 dbase. The normal "Compile and
>Save All Modules" I do not often use in my Access97 dbase. But as
>I regularly create an MDE (which shows the same behaviour with the
>listboxes), is it not that the "Compile and Save All Modules" is
>done by Access during creation of the MDE ? . . .
I can't say for sure, but I don't think it does. I know that
certain errors that a decompile flushes out will not appear before
the decompile during a compile and save all. That means that
certain compiled code is not being recompiled, and I fear that this
would be carried through to an MDE, even though creating an MDE
really ought to recompile everything from scratch, rather than
using code already marked compiled. I am not saying for sure that
this is what happens, but I wouldn't bet the farm on it.
> . . . But it does not give
>me an error. However, I use the same dbase in Access2000 and
>Access2002, and as always my Access97 dbase is the basic dbase
>which I then convert into Access2000 and then from Access2000 into
>Access2002, I regularly use the "Compile and Save All Modules" in
>those 00 and 02 dbases which also does not give me an error
>message. The Access00 and 02 dbases have the same problem with the
>listboxes.
I have seen code corruption in an A97 database survive the
conversion to A2K with no errors.
That's why I'm suspicious of the MDE creation process.
>Which reminds me that Bruce suggested to make the /decompile on my
>dbase which I have not yet done ...
While I think it's relatively unlikely this will yield any new
information, it's definitely something you should do semi-regularly
during heavy development. I do it at the end of every major
programming session, which is probably too often, but I very, very
seldom experience code corruption.
I think your problem is more likely to be some kind of name
ambiguity. I'm wondering if your listbox has a name that conflicts
with another name somewhere else in the database?
OldManEmu
errors such as these, quite often is has nothing to do with Access versions,
it is to do with the Operating system.
Make sure you and your recipient are both up to the current release of
patches and service packs. Now at least, you are working on a level
platform.
Both application and O/S work together using shared code internally.
Regards
"David W. Fenton" <dXXXf...@bway.net> wrote in message
news:927096AC0df...@24.168.128.70...
Sabine Oebbecke
>>> Does your form depend on any code in other modules?
>>No. All "Private Sub", no call of other codes in any
>>module.
> OK. What other forms/reports are open when the
> problem occurs?
> Anything at all?
Always open is my FrmMenu as it serves as Navigator.
> I would try the decompile, then compact, then import
> everything into a new database then compile and save
> all.
I’ll do it tonight.
> If that has no effect, then try Application.SaveAsText
> and Application.LoadFromText. Corruption might survive
> the import, but it can never survive .SaveAsText.
Does an error message come up then or is any code - if corrupt - removed
during the .SaveAsText? Not that later on my dbase is not working at all
:-).
> However, I have found that it's important to do your
> whole project, all the items with code in them, as
> apparently the data streams storing the actually
> compiled code are not wholly independent.
> Also, there's the issue of subforms, etc., so
> I've found it best to simply export and import
> everything (i.e., using .SaveAsText).
> I believe I have code for this somewhere (though
> it's not complicated code), if you'd like to have it.
As I have never heard of this Application.SaveAsText and
Application.LoadFromText, I would be glad to get the code if it is not
too inconvenient for you. And, to be honest, at the moment I would not
know where to start with this Application.SaveAsText. Is there any
documentation somewhere ... ?
> I think your problem is more likely to be some
> kind of name ambiguity. I'm wondering if your listbox
> has a name that conflicts with another name somewhere
> else in the database?
That could be true for the listboxes in Form 1 and 2 mentioned at the
beginning of my post, but it is definitely not true for the two
listboxes we are talking of now. The names of these two listboxes are
absolutely unique in my dbase.
Regards,
Sabine Oebbecke
Thanks for the hint. Well, I have to admit I do not have updated my
Win98 SE with any service releases or patches so far, but I will have a
go tonight.
Nevertheless, I have Access2002 with SR1 on a different PC with WinME as
O/S, and as far as I understand it, WinME is, as successor to Win98,
already "better" than Win98 SE so I would assume that any SRs or patches
from Win98 are in a way already "included" in WinME ... ? And in my
Access2002 dbase, the problem is the same...
Sabine Oebbecke
So, I have made an import into a new dbase, then the /decompile,
compact, again imported into a new dbase, then made the compile and save
all.
The result:
I opened my dbase, then my FrmMenu, went into the one form with the two
listboxes where I had added your db.Execute strSQL, dbFailOnError to
transfer the data from one listbox to the other ... and it worked. BUT,
I then closed the dbase again, closed Access, restarted Access, reopened
my dbase, then via the FrmMenu back into the form with the two
listboxes, and ... the code didn't work any longer.
More than weird, isn't it?!
Looking forward to getting something to know about the
Application.SaveAsText.
David W. Fenton
>Thanks for the hint. Well, I have to admit I do not have updated
>my Win98 SE with any service releases or patches so far, but I
>will have a go tonight.
>
>Nevertheless, I have Access2002 with SR1 on a different PC with
>WinME as O/S, and as far as I understand it, WinME is, as
>successor to Win98, already "better" than Win98 SE so I would
>assume that any SRs or patches from Win98 are in a way already
>"included" in WinME ... ? And in my Access2002 dbase, the problem
>is the same...
I would not say that the OS is likely to have anything whatsoever
to do with your problems.
David W. Fenton
>>>> Does your form depend on any code in other modules?
>>>No. All "Private Sub", no call of other codes in any
>>>module.
>> OK. What other forms/reports are open when the
>> problem occurs?
>> Anything at all?
>
>Always open is my FrmMenu as it serves as Navigator.
Are there any timers in that form?
[]
>> If that has no effect, then try Application.SaveAsText
>> and Application.LoadFromText. Corruption might survive
>> the import, but it can never survive .SaveAsText.
>
>Does an error message come up then or is any code - if corrupt -
>removed during the .SaveAsText? Not that later on my dbase is not
>working at all
>:-).
SaveAsText writes out a text file version of the form, with all the
properties and settings and code. It cannot be corrupt as it is
just pure text. When you load the saved text file back in, it
recreates the binary object described by the list of properties.
This is a purely new object, with no possibility of corruption
being inherited from the original object, because corruption occurs
only in the binary storage, and not in the text.
Well, it *can* happen in the canonical text of code, but when that
happens you'd know it immediately, because the form would be
completely hosed!
In any event, SaveAsText does not analyze or test what it's
exporting, I don't believe. It just writes it out.
I would suggest running SaveAsText from the problematic database,
then creating a new one, and then running LoadFromText to pull the
saved files into the new MDB.
>> However, I have found that it's important to do your
>> whole project, all the items with code in them, as
>> apparently the data streams storing the actually
>> compiled code are not wholly independent.
>> Also, there's the issue of subforms, etc., so
>> I've found it best to simply export and import
>> everything (i.e., using .SaveAsText).
>
>> I believe I have code for this somewhere (though
>> it's not complicated code), if you'd like to have it.
>
>As I have never heard of this Application.SaveAsText and
>Application.LoadFromText, I would be glad to get the code if it is
>not too inconvenient for you. And, to be honest, at the moment I
>would not know where to start with this Application.SaveAsText. Is
>there any documentation somewhere ... ?
It's undocumented. I learned about it here.
Here's my code:
Sub SaveFormsAsText(strPath As String)
Dim db As Database
Dim cnt As Container
Dim Doc As Document
Dim strForm As String
If dbSA Is Nothing Then Set dbSA = CurrentDb()
For Each cnt In db.Containers
If cnt.Name = "Forms" Then
For Each Doc In cnt.Documents
strForm = Doc.Name
Application.SaveAsText acForm, strForm, strPath &
strForm & ".txt"
Next Doc
Set Doc = Nothing
End If
Next cnt
Set cnt = Nothing
End Sub
Sub LoadFromsFromText(strPath As String)
Dim strForm As String
Dir strPath & "frm*.txt"
strForm = Dir
Do Until strForm = vbNullString
Application.LoadFromText acForm, SubStr(strForm, ".", False), _
strForm Dir
Loop
Dir strPath & "dlg*.txt"
strForm = Dir
Do Until strForm = vbNullString
Application.LoadFromText acForm, SubStr(strForm, ".", False), _
strForm Dir
Loop
Dir strPath & "sub*.txt"
strForm = Dir
Do Until strForm = vbNullString
Application.LoadFromText acForm, SubStr(strForm, ".", False), _
strForm Dir
Loop
Dir strPath & "mnu*.txt"
strForm = Dir
Do Until strForm = vbNullString
Application.LoadFromText acForm, SubStr(strForm, ".", False), _
strForm Dir
Loop
End Sub
(my custom SubStr() function is after my .sig, below; it's just my
version of a Split() function)
The second sub is based on my form naming conventions.
You'd need to adapt the code to deal with your reports and modules,
as well.
I've only ever had the issue with forms, because I hardly ever have
significant code in reports, and with modules it's just really,
really easy to manually copy them, delete and then recreate them.
You don't have the problems that come with doing the same thing
with form/report modules (where the code gets detached from its
events if you just paste it back in).
>> I think your problem is more likely to be some
>> kind of name ambiguity. I'm wondering if your listbox
>> has a name that conflicts with another name somewhere
>> else in the database?
>
>That could be true for the listboxes in Form 1 and 2 mentioned at
>the beginning of my post, but it is definitely not true for the
>two listboxes we are talking of now. The names of these two
>listboxes are absolutely unique in my dbase.
I'm really at a loss. There is no logical explanation for the
behavior that fits all the facts you've given us (which have been
voluminous!).
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Public Function SubStr(mainString As String, Separator As String, _
BeginEnd As Boolean)
' CREATED BY DWF
' Returns a sub-string from before or after Separator
' If BeginEnd is FALSE, returns sub-string BEFORE Separator
' If TRUE, returns sub-string AFTER Separator
' NOTE: this function trims out any leading or trailing spaces
' in the output string
If Len(mainString) = 0 Then
Exit Function
End If
If InStr(mainString, Separator) <> 0 And BeginEnd = -1 Then
SubStr = Trim(Right(mainString, Len(mainString) - _
InStr(mainString, Separator) - Len(Separator) + 1))
ElseIf InStr(mainString, Separator) <> 0 And BeginEnd = 0 Then
SubStr = Trim(Left(mainString, InStr(mainString,
Separator) - 1))
ElseIf BeginEnd = -1 Then
SubStr = vbNullString
Else
SubStr = mainString
End If
End Function
Sabine Oebbecke
Unfortunately I have no time today :-(
I will come back to you tomorrow or the day after:
Best regards,
David W. Fenton
>Unfortunately I have no time today :-(
>I will come back to you tomorrow or the day after:
Gosh! I won't be able to sleep!!! ;)
Sabine Oebbecke
Regards,
David W. Fenton
>Please see my e-mail.
What email? You realize my address is munged, no?
Sabine Oebbecke
>What email? You realize my address is munged, no?
Well, now that you mention :-). I didn't get a delivery error message so
I thought it might be the right one. I have now sent to the correct
address.
David W. Fenton
[I asked:]
>>What email? You realize my address is munged, no?
>
>Well, now that you mention :-). I didn't get a delivery error
>message so I thought it might be the right one. I have now sent to
>the correct address.
I haven't yet received anything.
Sabine Oebbecke
Did it arrive in the meantime ?
Rgds,
David W. Fenton
>Did it arrive in the meantime ?
I have received nothing from you. :(
Sabine Oebbecke
> I have received nothing from you. :(
That's strange, I definitely used the correct e-mail address the second
time, but please note that only today I received a delivery error
message on the first e-mail, so it may be that the second e-mail is
still on its way to you. Nevertheless I will have a third try today.
Regards,
Sabine Oebbecke
Today I received a delivery error message on my second attempt where I
am sure that I used your correct e-mail address dfenton at bway dot net
:(. Do you have an alternative address where I could send the e-mail to
? The size of the zipped dbase is 411 kb, could it be that the mail is
therefore rejected.
Well, maybe it is because I simply forwarded my first e-mail without
creating a new e-mail with the correct address. I will therefore today
have 4th try with a complete new e-mail. I hope it comes through ...
Or maybe it would be an idea that you send me an e-mail so that I can
reply on it... ?
Best regards,
Darryl Kerkeslager
time. My observations on the problem are this:
1. The problem only affects Unbound list boxes.
2. There are actually two separate problems
a. the listbox display is not updated
b. the listbox.value property is not updated until
the form is closed and re-opened, or F9 pressed
3. The first problem is the more bothersome to me. Here was my code:
Dim cnxn as ADODB.Connection
Set cnxn = New ADODB.Connection
With cnxn
.open cnxnString & CurrentProject.FullName
.Execute "INSERT INTO pb15 (pb15_vaccis, pb15_issue_date ) " &
_
"VALUES (" & pbVaccis & ", #" & Date & "#)"
.Close
End With
Set cnxn = Nothing
Me!lstPB15.RowSource = sqlPB15List & " WHERE pb15.pb15_vaccis = "
& pbVaccis
Me!lstPB15.Requery
Me!lstPB15.SetFocus
Me!lstPB15.Selected(1) = True
Dim db As DAO.Database
Set db = CurrentDb
db.QueryDefs("qdfPB15Entry").SQL = sqlPB15Entry & Me!lstPB15.Value
Set db = Nothing
I had two problems - Me!lstPB15.Value was not getting a value, and the
listbox was not updating.
The listbox not updating was solved by replacing the ADO INSERT with a
DAO INSERT:
Dim db As DAO.Database
Set db = CurrentDb
db.Execute "INSERT INTO pb15 (pb15_vaccis, pb15_issue_date ) "
& _
"VALUES (" & pbVaccis & ", #" & Date & "#)"
Set db = Nothing
Perhaps my ADO syntax caused the problem ...?
The second problem was solved by adding any one of the following lines
to the end of the Sub:
Me.Requery
- or -
Me.Refresh
- or -
Me.Recalc
All of which are called on the form, not just the listbox. Nothing in
my SELECT statement directly relied on a form field, but I did set the
value of pbVaccis in an after_upate event by:
pbVaccis = Me!txtVACCIS.Value
So, apparently Access was unwilling to update Me!lstPB15.Value without
double-checking Me!txtVACCIS.Value
I think.
Darryl Kerkeslager
Sabine Oebbecke
I have also found the solution to my problem which now works in all
(!!!) my forms where I had listboxes which did not update properly. You
may not have seen my solution as there are now so many postings on this
issue. Please have a look at my posting dated 9 August 2002 where I
wrote the respective code down.
Dim strSQL As String
strSQL = "Update [TabMyTable] Set bla bla bla "
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
Afterwards the .Requery on the listbox worked fine.
The most important thing is that only the DoCmd.RunSQL (strSQL) really
brought the wanted result. I also had a db.Execute, also a qd.Execute,
but this did not work properly.
This may also help with your problem.