Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    Francois

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    Francois

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Francois

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •