Conditional Macro run

[Anne]

I am using a form which shows the user the last date of the
YearToDatePayroll and the current week payroll. The user has to decide if
the Current Week has already been updated to the Year to Date table and if
or not to run the update macro. But they keep duplicating the updates.

Now, I have created invisible textboxes, which analysis the dates and if the
table Year to Date show the same date as the current week, I would like to
force the macro to abort.
I inserted the 2nd to 4th line into the run macro command, which is supposed
to do: if LinkResults = 1, give message and exit, if LinkResults =2, giv
messagen and continue to run the macro.
My trials, of course, do not work. Is it possible to make it work?
Thanks,
Anne

Private Sub Update25_Click()
IIf LinkResults = 1, MsgBox("Already Updated")
GoTo Exit_Update25_Click
IIf LinkResults = 2, MsgBox("Table Will be Updates")

On Error GoTo Err_Update25_Click
Dim stDocName As String

stDocName = "ApendCurrentWeekMacro"
DoCmd.RunMacro stDocName

Exit_Update25_Click:
Exit Sub

Err_Update25_Click:
MsgBox Err.Description
Resume Exit_Update25_Click

End Sub

 

[Steve Schapel]

Anne,

Try it like this...

Private Sub Update25_Click()
On Error GoTo Err_Update25_Click
Select Case Me.LinkResults
Case 1
MsgBox "Already Updated"
Case 2
MsgBox "Table Will be Updates"
DoCmd.RunMacro "ApendCurrentWeekMacro"
End Select
Exit_Update25_Click:
Exit Sub
Err_Update25_Click:
MsgBox Err.Description
Resume Exit_Update25_Click
End Sub

 

[Anne]

Thank you, works like a charm.
Anne

Anne,

Try it like this...

Private Sub Update25_Click()
On Error GoTo Err_Update25_Click
Select Case Me.LinkResults
Case 1
MsgBox "Already Updated"
Case 2
MsgBox "Table Will be Updates"
DoCmd.RunMacro "ApendCurrentWeekMacro"
End Select
Exit_Update25_Click:
Exit Sub
Err_Update25_Click:
MsgBox Err.Description
Resume Exit_Update25_Click
End Sub

 

[Anne]

One more question. After the macro runs, the Year to Date Table needs to be
requeried
to show the new data. Even though the macro was rund, the year to date table
is not showing the updated data. As a result of the linkresults still show
Case 2 and it looks as if the update has not been done.
I have a Me.Requery on each subform, but I can't rely on the user to
actually do that after the update.
Any suggestions on how to get the subforms updatated programatically?
Anne

 

[Steve Schapel]

Anne,

Do you mean the data in a subform needs to show the updated data? If
so, you can build this into your code, e.g....
Select Case Me.LinkResults
Case 1
MsgBox "Already Updated"
Case 2
MsgBox "Table Will be Updates"
DoCmd.RunMacro "ApendCurrentWeekMacro"
Me.LinkResults = 0
Me.MySubform.Requery
End Select

 

[Anne]

I get the message: "Can't assign a value to this object"
using the last two lines.
Actually since nothing shows as being changed after the macro is run, The
value of the case also does not change (still showing 2) and therefore
another update is allowed.
Anne

 

[Steve Schapel]

Anne,

What is LinkResults?
Was I correct in my assumption that a subform needs to show the updated
data?
What is the name of the subform control?
What is Update25?
Is Update25 on the main form that the subform is on?
What is the code you are using now?

 

[Anne]

I never changed the name of the command for the macro from the wizard - This
should be something like CmdRunMacroUpdate.

I created a form in design view. Added 2 subforms, sfrmYTD and
sfrmCurrentWeek (I am shortening the names to make it easier).

sfrmYTD shows a few lines of data from the last payroll updated, the
important field, last date updated which is called MaxOfWeDate.

SfmCurrentWeek show a few lines of data from the current week payroll and
the date field is WeDate.

On the main form I put 3 text boxes.
Linkfield2: control =[SfrmYTD]![MaxOfWeDate]
Linkfield1: control =[sfrmCurrentWeek]![WeDate]
LinkResults: control=IIf([linkfield1]>[linkfield2],2,1)

For example, I am testing data MaxOfWeDate = 11/30/04 and WeDate = 12/07/04.
Right now, link Result shows 2
After I run the macro (Update25 - which is on the main form) it will append
the data to the year to date table, but the date in MaxofWeDate on the
subform still shows 11/30/04 and LinkResults still shows 2.

The macro works perfectly., only if I then manually click the requery button
on sfrmYTD will sfrmYTD show MaxOfWeDate as 12/07/04 and the linkresults on
the main form change to 1. If I do not click requery, the macro will allow a
duplicate update.

Thanks for sticking with me on this.
Annelie

Current Code:
Private Sub Update25_Click()
On Error GoTo Err_Update25_Click
Select Case Me.LinkResults
Case 1
MsgBox "Year to Date is already Updated"
Case 2
MsgBox "CURRENT WEEK TABLE WILL BE APPENDED TO YEAR TO DATE
TABLE"
DoCmd.RunMacro "ApendCurrentWeekMacro"

End Select

Exit_Update25_Click:
Exit Sub

Err_Update25_Click:
MsgBox Err.Description
Resume Exit_Update25_Click

End Sub

 

[Steve Schapel]

Anne,

Ok, thanks for the further explanation. So LinkResults is a calculated
control. Try it like this...

Select Case Me.LinkResults
Case 1
MsgBox "Year to Date is already Updated"
Case 2
MsgBox "CURRENT WEEK TABLE WILL BE APPENDED TO YEAR TO DATE
TABLE"
DoCmd.RunMacro "ApendCurrentWeekMacro"
Me.SfrmYTD.Requery
Me.Recalc
End Select

Let me know how this goes.

 

[Anne]

That is it!!!
Thank you so very much.
Anne

Anne,

Ok, thanks for the further explanation. So LinkResults is a calculated
control. Try it like this...

Select Case Me.LinkResults
Case 1
MsgBox "Year to Date is already Updated"
Case 2
MsgBox "CURRENT WEEK TABLE WILL BE APPENDED TO YEAR TO DATE
TABLE"
DoCmd.RunMacro "ApendCurrentWeekMacro"
Me.SfrmYTD.Requery
Me.Recalc
End Select

Let me know how this goes.

--
Steve Schapel, Microsoft Access MVP

I never changed the name of the command for the macro from the wizard - This
should be something like CmdRunMacroUpdate.

I created a form in design view. Added 2 subforms, sfrmYTD and
sfrmCurrentWeek (I am shortening the names to make it easier).

sfrmYTD shows a few lines of data from the last payroll updated, the
important field, last date updated which is called MaxOfWeDate.

SfmCurrentWeek show a few lines of data from the current week payroll and
the date field is WeDate.

On the main form I put 3 text boxes.
Linkfield2: control =[SfrmYTD]![MaxOfWeDate]
Linkfield1: control =[sfrmCurrentWeek]![WeDate]
LinkResults: control=IIf([linkfield1]>[linkfield2],2,1)

For example, I am testing data MaxOfWeDate = 11/30/04 and WeDate = 12/07/04.
Right now, link Result shows 2
After I run the macro (Update25 - which is on the main form) it will append
the data to the year to date table, but the date in MaxofWeDate on the
subform still shows 11/30/04 and LinkResults still shows 2.

The macro works perfectly., only if I then manually click the requery button
on sfrmYTD will sfrmYTD show MaxOfWeDate as 12/07/04 and the linkresults on
the main form change to 1. If I do not click requery, the macro will allow a
duplicate update.

Thanks for sticking with me on this.
Annelie

Current Code:
Private Sub Update25_Click()
On Error GoTo Err_Update25_Click
Select Case Me.LinkResults
Case 1
MsgBox "Year to Date is already Updated"
Case 2
MsgBox "CURRENT WEEK TABLE WILL BE APPENDED TO YEAR TO DATE
TABLE"
DoCmd.RunMacro "ApendCurrentWeekMacro"

End Select

Exit_Update25_Click:
Exit Sub

Err_Update25_Click:
MsgBox Err.Description
Resume Exit_Update25_Click

End Sub

 

[Anne]

Here I am again.
Senario 2
After the update, I have a button for a second macro, which deletes the
current week table. I tried to change the Linkfield1 condition, so that when
the table is deleted it shows blank, or ZLS, etc. but it always comes up
with #Error.
Linkfield1 new condition:
=IIf(IsNull([sFrmCurrentWeek]![WeDate])," ",[sFrmCurrentWeek]![WeDate])
I think the problem is that the sfrmCurrentWeek is empty.

I have tried all kind of searches in the help, on the net, newsgroup and
could never find anything on #error, or any reference of regarding any
control which looks at data in vaious table when one table is empty.
Anne

That is it!!!
Thank you so very much.
Anne

Anne,

Ok, thanks for the further explanation. So LinkResults is a calculated
control. Try it like this...

Select Case Me.LinkResults
Case 1
MsgBox "Year to Date is already Updated"
Case 2
MsgBox "CURRENT WEEK TABLE WILL BE APPENDED TO YEAR TO DATE
TABLE"
DoCmd.RunMacro "ApendCurrentWeekMacro"
Me.SfrmYTD.Requery
Me.Recalc
End Select

Let me know how this goes.

wizard -
This

should be something like CmdRunMacroUpdate.

I created a form in design view. Added 2 subforms, sfrmYTD and
sfrmCurrentWeek (I am shortening the names to make it easier).

sfrmYTD shows a few lines of data from the last payroll updated, the
important field, last date updated which is called MaxOfWeDate.

SfmCurrentWeek show a few lines of data from the current week payroll and
the date field is WeDate.

On the main form I put 3 text boxes.
Linkfield2: control =[SfrmYTD]![MaxOfWeDate]
Linkfield1: control =[sfrmCurrentWeek]![WeDate]
LinkResults: control=IIf([linkfield1]>[linkfield2],2,1)

For example, I am testing data MaxOfWeDate = 11/30/04 and WeDate = 12/07/04.
Right now, link Result shows 2
After I run the macro (Update25 - which is on the main form) it will append
the data to the year to date table, but the date in MaxofWeDate on the
subform still shows 11/30/04 and LinkResults still shows 2.

The macro works perfectly., only if I then manually click the requery button
on sfrmYTD will sfrmYTD show MaxOfWeDate as 12/07/04 and the

linkresults

on

 

[Anne]

I can't figure this one out. I now replicated the situation in a second
database, which is till in Access 2002 (below Access 2003)
After deleting the current week data (need to clarify on below, it does not
delete the table, it deleted the data in the table), the Linkfield1 is
blank, which is great and it still has the default value of
=SfrmCurrentWeek!WeDate, no brackets around the control.
Going back to Access 2003 database, when I change the field, access puts the
brackets back, and then the #error shows.
What could possibly be causing this??????
Anne

Here I am again.
Senario 2
After the update, I have a button for a second macro, which deletes the
current week table. I tried to change the Linkfield1 condition, so that when
the table is deleted it shows blank, or ZLS, etc. but it always comes up
with #Error.
Linkfield1 new condition:
=IIf(IsNull([sFrmCurrentWeek]![WeDate])," ",[sFrmCurrentWeek]![WeDate])
I think the problem is that the sfrmCurrentWeek is empty.

I have tried all kind of searches in the help, on the net, newsgroup and
could never find anything on #error, or any reference of regarding any
control which looks at data in vaious table when one table is empty.
Anne

That is it!!!
Thank you so very much.
Anne

Anne,

Ok, thanks for the further explanation. So LinkResults is a calculated
control. Try it like this...

Select Case Me.LinkResults
Case 1
MsgBox "Year to Date is already Updated"
Case 2
MsgBox "CURRENT WEEK TABLE WILL BE APPENDED TO YEAR TO DATE
TABLE"
DoCmd.RunMacro "ApendCurrentWeekMacro"
Me.SfrmYTD.Requery
Me.Recalc
End Select

Let me know how this goes.

--
Steve Schapel, Microsoft Access MVP

Anne wrote:
I never changed the name of the command for the macro from the

wizard -
This

should be something like CmdRunMacroUpdate.

I created a form in design view. Added 2 subforms, sfrmYTD and
sfrmCurrentWeek (I am shortening the names to make it easier).

sfrmYTD shows a few lines of data from the last payroll updated, the
important field, last date updated which is called MaxOfWeDate.

SfmCurrentWeek show a few lines of data from the current week

payroll
and

the date field is WeDate.

On the main form I put 3 text boxes.
Linkfield2: control =[SfrmYTD]![MaxOfWeDate]
Linkfield1: control =[sfrmCurrentWeek]![WeDate]
LinkResults: control=IIf([linkfield1]>[linkfield2],2,1)

For example, I am testing data MaxOfWeDate = 11/30/04 and WeDate = 12/07/04.
Right now, link Result shows 2
After I run the macro (Update25 - which is on the main form) it will append
the data to the year to date table, but the date in MaxofWeDate on the
subform still shows 11/30/04 and LinkResults still shows 2.

The macro works perfectly., only if I then manually click the

requery
button

on sfrmYTD will sfrmYTD show MaxOfWeDate as 12/07/04 and the

linkresults

on
the main form change to 1. If I do not click requery, the macro will allow a
duplicate update.

Thanks for sticking with me on this.
Annelie

Current Code:
Private Sub Update25_Click()
On Error GoTo Err_Update25_Click
Select Case Me.LinkResults
Case 1
MsgBox "Year to Date is already Updated"
Case 2
MsgBox "CURRENT WEEK TABLE WILL BE APPENDED TO YEAR TO DATE
TABLE"
DoCmd.RunMacro "ApendCurrentWeekMacro"

End Select

Exit_Update25_Click:
Exit Sub

Err_Update25_Click:
MsgBox Err.Description
Resume Exit_Update25_Click

End Sub

 

[Steve Schapel]

Anne,

Try it like this...

=IIf(IsNumeric([sFrmCurrentWeek]![WeDate]),[sFrmCurrentWeek]![WeDate],Null)

 

[Anne]

Thank you, the field now shows blank, instead of #error
Anne

Anne,

Try it like this...

=IIf(IsNumeric([sFrmCurrentWeek]![WeDate]),[sFrmCurrentWeek]![WeDate],Null)

--
Steve Schapel, Microsoft Access MVP

Here I am again.
Senario 2
After the update, I have a button for a second macro, which deletes the
current week table. I tried to change the Linkfield1 condition, so that when
the table is deleted it shows blank, or ZLS, etc. but it always comes up
with #Error.
Linkfield1 new condition:
=IIf(IsNull([sFrmCurrentWeek]![WeDate])," ",[sFrmCurrentWeek]![WeDate])
I think the problem is that the sfrmCurrentWeek is empty.

I have tried all kind of searches in the help, on the net, newsgroup and
could never find anything on #error, or any reference of regarding any
control which looks at data in vaious table when one table is empty.
Anne

 

[Anne]

I spoke too soon again, if now always returns a blank field, even when a
date exists.
Anne

Thank you, the field now shows blank, instead of #error
Anne

Anne,

Try it like this...

=IIf(IsNumeric([sFrmCurrentWeek]![WeDate]),[sFrmCurrentWeek]![WeDate],Null) that

when

the table is deleted it shows blank, or ZLS, etc. but it always comes up
with #Error.
Linkfield1 new condition:
=IIf(IsNull([sFrmCurrentWeek]![WeDate])," ",[sFrmCurrentWeek]![WeDate])
I think the problem is that the sfrmCurrentWeek is empty.

I have tried all kind of searches in the help, on the net, newsgroup and
could never find anything on #error, or any reference of regarding any
control which looks at data in vaious table when one table is empty.
Anne

 

[Anne]

I finally figured it out by comparing the 2 databases.
I re-checked the Access 2002 database and the only difference for the field
was, that I grouped the date field using Group by instead of Max. When I
changed it to maxofWeDate, it worked without having to use any iif
statement. Makes sense, its not a number if I use group by.
Thanks again for your help.
Anne

Anne,

Try it like this...

=IIf(IsNumeric([sFrmCurrentWeek]![WeDate]),[sFrmCurrentWeek]![WeDate],Null)

--
Steve Schapel, Microsoft Access MVP

Here I am again.
Senario 2
After the update, I have a button for a second macro, which deletes the
current week table. I tried to change the Linkfield1 condition, so that when
the table is deleted it shows blank, or ZLS, etc. but it always comes up
with #Error.
Linkfield1 new condition:
=IIf(IsNull([sFrmCurrentWeek]![WeDate])," ",[sFrmCurrentWeek]![WeDate])
I think the problem is that the sfrmCurrentWeek is empty.

I have tried all kind of searches in the help, on the net, newsgroup and
could never find anything on #error, or any reference of regarding any
control which looks at data in vaious table when one table is empty.
Anne