"RecordSorce" prperty does not always update detail area in a form

[Winfried]

Hi all,

I have a problem filling the detail area of an ACCESS form triggered
from another form via VBA. I use ACCESS 2003 SP2 with Windows Xp SP2.


I have the following piece of VBA code inside a click event inside
"Microsoft Office Access Class Object"

With Forms(frmExternal)
SQL_String = "SELECT * FROM " & tbl_SATURN & " ORDER BY PK DESC;"
.RecordSource = SQL_String
End With

Among other things this "click" sub calls another sub that updates
database table "tbl_SATURN". After finishing that sub the above
mentioned VBA code will be executed at the end of the "click" sub. The
result shall be the update of the detail area of form "frmExternal".

If "tbl_SATURN" only contains some records there is rather seldom an
update in the form, if there are some records in "tbl_SATURN" sometimes
the result is as desired, i.e. the complete list.

In these cases the result of the query before remains in the detail
area unchanged. If the result is as expected also all missing rows from
the run before that not worked will now be shown.


If I set a breakponit to the ".RecordSource = SQL_String" VBA line and
I use afterwards <F8> to step into this command to execute it all works
fine every time!! If I press instaead <F5> I again have the situation
sometime it works, sometimes not.

This behaviour looks to me as if I have to give the .RecordSource
property "some time" to do what it shall should/has to do. All very
strange?!?!!

If I use the same piece of code inside the form "frmExternal" and use
there "With Me." instead of "With Forms(frmExternal)" all works always
fine.

Has anyone of you an idea what I do wrong respectively, a hint or
workaround how I can solve this or where the problem can be?

Thanks in advance
Winfried

 

[Wolfgang Kais]

Hello Winfried.

I have a problem filling the detail area of an ACCESS form triggered
from another form via VBA. I use ACCESS 2003 SP2 with Windows Xp SP2.

I have the following piece of VBA code inside a click event inside
"Microsoft Office Access Class Object"

With Forms(frmExternal)
SQL_String = "SELECT * FROM " & tbl_SATURN & " ORDER BY PK DESC;"
.RecordSource = SQL_String
End With

Among other things this "click" sub calls another sub that updates
database table "tbl_SATURN". After finishing that sub the above
mentioned VBA code will be executed at the end of the "click" sub.
The result shall be the update of the detail area of form
"frmExternal".

If "tbl_SATURN" only contains some records there is rather seldom
an update in the form, if there are some records in "tbl_SATURN"
sometimes the result is as desired, i.e. the complete list.

In these cases the result of the query before remains in the detail
area unchanged. If the result is as expected also all missing rows
from the run before that not worked will now be shown.

If I set a breakponit to the ".RecordSource = SQL_String" VBA line
and I use afterwards <F8> to step into this command to execute it
all works fine every time!! If I press instaead <F5> I again have
the situation sometime it works, sometimes not.

This behaviour looks to me as if I have to give the .RecordSource
property "some time" to do what it shall should/has to do. All very
strange?!?!!

If I use the same piece of code inside the form "frmExternal" and use
there "With Me." instead of "With Forms(frmExternal)" all works always
fine.

Has anyone of you an idea what I do wrong respectively, a hint or
workaround how I can solve this or where the problem can be?

YOu are using the names frmExternal and tbl_SATURN as if they were
string variables. Variables of type String usually are marked with
the abbreviation "str". Make sure that the variables have the
correct values.
If tbl_SATURN isn't a variable but the name of a table, use:
SQL_String = "SELECT * FROM tbl_SATURN ORDER BY PK DESC;"
If frmExternal isn't a variable but the name of the form, use:
With Forms("frmExternal")
If I'm right, then probably the whole code can be shortened:
Forms("frmExternal").Requery

 

[Winfried]

Hi Wolfgang,

"frmExternal" and "tbl_SATUN" are string variables containing a formula
name respectively teh name of an ACCESS database table. Their values
are correct.

To keep my problem simple I omitted the WHERE clause of my query in
"SQL_String" variable. Because of this I unfortunately cannot use or
test the ".Requery"

Regrads,
Winfried