W
Winfried
Hi,
I have the following ACCESS VBA based problem.
Inside an ACCESS application I have a form with name "frm_ReportList".
In this form body a query based on a table will be executed and the
result be displayed. Among other things every data row of this query
line contains text boxes. With a click on such a text box the user can
start some action (written in VBA) wheres the contents of this textbox
and some others will also change. After the run the query shall be
executed again based on the modified data base table with the
RecordSource property. All runs perfect, but ....
At the end of the action I display in a message box about
success/non-success of this action. Directly after this Msgbox I call
the sub "UpdateQuery" (code see at the end)
MsgBox "The new data are available as requested",
vbinformation,"Insert done"
DoEvents: Call UpdateQuery
What happens during the above mentioned?
a) When the message box comes up and you click on OK to close the
message box immediately within fractions of one second , the code of
the sub will be (of course) executed but the result of the SQL command
"DisplayFormular.RecordSource = SQL_String" will not be displyed in the
formular body. All remains as before. I set a breakpoint in the sub
after ".RecordSource" property and as I expected the complete code was
executed . When I close the form and call it again all is perfect
b) When I get the message box and when I wait for one or two seconds
before I click on OK in the message box all works as expected and the
SQL statement result will be displayed.
And the "best" is ..... This behaviour sometimes appers very often,
sometimes very seldom. I feel a bit helpless
If have no clue why ACCESS does not behave as it should do. Does
someone know why ACCESS executes the code in two different ways? Or has
someone a possible workaround with ACCESS?
Thanks in advance
Winfried
Sub UpdateQuery()
Dim SQL_String As String
Dim WHERE_ClauseAs String
Dim DisplayFormular As Form
Set DisplayFormular = Forms(frm_ReportList)
WHERE_Clause= ""
SQL_String = "SELECT * " & _
"FROM " & tbl_SATURN_ReportList& " "
If DisplayFormular.cmb_SelectReportType <> "Alle" Then
WHERE_Clause= "WHERE txt_MeldungUeber = '" &
DisplayFormular.cmb_SelectReportType & "'"
End If
If DisplayFormular.cmb_SelectReportYear <> "Alle" Then
If WHERE_Clause= "" Then
WHERE_Clause= "WHERE Year(dat_IntervallStart) = " &
DisplayFormular.cmb_SelectReportYear
Else
WHERE_Clause= WHERE_Clause& " AND Year(dat_IntervallStart) = " &
DisplayFormular.cmb_SelectReportYear
End If
End If
SQL_String = SQL_String & WHERE_Clause& " ORDER BY PS DESC;"
DisplayFormular.RecordSource = SQL_String
Set DisplayFormular = Nothing
End Sub
I have the following ACCESS VBA based problem.
Inside an ACCESS application I have a form with name "frm_ReportList".
In this form body a query based on a table will be executed and the
result be displayed. Among other things every data row of this query
line contains text boxes. With a click on such a text box the user can
start some action (written in VBA) wheres the contents of this textbox
and some others will also change. After the run the query shall be
executed again based on the modified data base table with the
RecordSource property. All runs perfect, but ....
At the end of the action I display in a message box about
success/non-success of this action. Directly after this Msgbox I call
the sub "UpdateQuery" (code see at the end)
MsgBox "The new data are available as requested",
vbinformation,"Insert done"
DoEvents: Call UpdateQuery
What happens during the above mentioned?
a) When the message box comes up and you click on OK to close the
message box immediately within fractions of one second , the code of
the sub will be (of course) executed but the result of the SQL command
"DisplayFormular.RecordSource = SQL_String" will not be displyed in the
formular body. All remains as before. I set a breakpoint in the sub
after ".RecordSource" property and as I expected the complete code was
executed . When I close the form and call it again all is perfect
b) When I get the message box and when I wait for one or two seconds
before I click on OK in the message box all works as expected and the
SQL statement result will be displayed.
And the "best" is ..... This behaviour sometimes appers very often,
sometimes very seldom. I feel a bit helpless
If have no clue why ACCESS does not behave as it should do. Does
someone know why ACCESS executes the code in two different ways? Or has
someone a possible workaround with ACCESS?
Thanks in advance
Winfried
Sub UpdateQuery()
Dim SQL_String As String
Dim WHERE_ClauseAs String
Dim DisplayFormular As Form
Set DisplayFormular = Forms(frm_ReportList)
WHERE_Clause= ""
SQL_String = "SELECT * " & _
"FROM " & tbl_SATURN_ReportList& " "
If DisplayFormular.cmb_SelectReportType <> "Alle" Then
WHERE_Clause= "WHERE txt_MeldungUeber = '" &
DisplayFormular.cmb_SelectReportType & "'"
End If
If DisplayFormular.cmb_SelectReportYear <> "Alle" Then
If WHERE_Clause= "" Then
WHERE_Clause= "WHERE Year(dat_IntervallStart) = " &
DisplayFormular.cmb_SelectReportYear
Else
WHERE_Clause= WHERE_Clause& " AND Year(dat_IntervallStart) = " &
DisplayFormular.cmb_SelectReportYear
End If
End If
SQL_String = SQL_String & WHERE_Clause& " ORDER BY PS DESC;"
DisplayFormular.RecordSource = SQL_String
Set DisplayFormular = Nothing
End Sub