Code DateAdd() and Make button invisible (Access 2002)

Topic

New Reply

Situation: I currently have a query called qryAprilAdjustYearStartEndDate to update to date fields in a table called tblCompanyFacts. This table has one record and holds the start and ending dates for three different time periods. The query works as advertised, but I would like to take the query out and code the changes. I tried DateAdd(“yyyy”,1,[tblCompanyFacts.AprilStart]) and get error message saying need equal sign.

Second question: Once the code is run I want to make cmdApril invisible. I tried using cmdApril.Visible = False. I tried it in Select Case intAnswer2 and below that without success (See whole block of code below.)

Thank you for your assistance. Fay

Private Sub cmdApril_Click()
Dim intAnswer As Integer
Dim intAsnwer2 As Integer
‘ Check to make sure that you are ready to print the final year end report
intAnswer = MsgBox(“Is all data entered? Are you ready to output the year end report?”, vbYesNo + vbQuestion, “Print End of Year Report”)
Select Case intAnswer
Case vbYes
cmdApril.Tag = “Yes”
DoCmd.OpenReport “rptJonCombined”
Case vbNo
cmdApril.Tag = “” ‘ Used to emplty it in case it is still there from a previous action
DoCmd.Close , acForm
End Select
‘ Check to make sure that you are ready update the year starting and ending dates
intAnswer2 = MsgBox(“Was everything printed satisfactory? If so are you ready to update the starting and ending dates for the year?”, vbYesNo + vbQuestion, “Print End of Year Report”)
Select Case intAnswer2
Case vbYes
DoCmd.OpenQuery “qryAprilAdjustYearStartEndDate”, acViewNormal, acReadOnly
Case vbNo
DoCmd.Close , acForm
End Select
cmdApril.Visible = False
End Sub

Reply | Quote

Replies

1. What’s wrong using a query ? Anyway, if you want to code you can do it in 2 manners:
You can use the Execute Method to execute an update sql statement (see help file for Execute Method) or you can open a recordset and loop through the recordset, edit the record, make change in the field, update the record , close the recordset.

2. Before you can make the cmdApril invisible, you have to move the focus to another control.
Select Case intAnswer2
Case vbYes
DoCmd.OpenQuery “qryAprilAdjustYearStartEndDate”, acViewNormal, acReadOnly
Me.OtherControl.SetFocus
Me.cmdApril.Visible = False
Case vbNo
DoCmd.Close , acForm
End Select

Reply | Quote

Thanks Francois. I will stay with the query I just was thinking about keeping the number of queries down.

The button worked find. But if I reopen the form it is on and it is still April then the button reappears. How do you set it to not return until the next year?

Thank you. Fay

Reply | Quote

Fay,
When you make a control invisible in code, the control is made invisible only for the time that the form is open.
If you want the cmd button stays invisible in the future, you have to store the condition when to make it visible or not somewhere in the database (a table named tblSettings) and test on this value in the on open event of the form.
Or if you only want the cmd button visible in April, you can test on the month of system date

Reply | Quote

Okay I admit it I am out of my league and loosing my hair. I have no clue how to test against the table tblCompanyFacts field name cmdAprilSet to set the cmdApril button. If you have any more patience I would appreciate your help

I set the field value at 2000
Here is my attempt on a test form.
Private Sub Form_Open(Cancel As Integer)
If tblCompanyFacts!cmdAprilSet = Year(Now) – 1 Then
Me.cmdApril.Visible = True
Else
Me.cmdApril.Visible = True
End If
End Sub

Thank you Fay

Reply | Quote

If the name of the control of the subform containing cmdAprilSet then your condition should be:
If Me!cmdAprilSet = Year(Now) – 1 Then
otherwise replace cmdAprilSet with the name of the control but still use Me!…..

The if part and the else part are both true. One of them has to be false.

Reply | Quote