Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2012

    Unanswered: run a query only once at first start up of year

    I have a table with three hundred records that I use to assign can numbers to other tables. There are two fields to the table

    CanNumber numeric and assigned yes/no

    At the start of every yearly campaign I run a query which resets all three hundred records assigned field no. I want to do this in a manner that it only gets done the once if its been done then msgbox its already done and not allow it to be done again. I am just looking for ideas at this point.

    Thanks in advance


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    Why do you need to run this only once? If its already been run then presumably theres nothing more to be done
    Presumably youd only run this if the can number is unallocated
    So a way of handling this would be to identify those rows with unallocated can numbers.
    Whether you do that as a separate query in say a macro or form is upto you.
    Or when you do your update only update those rows with an empty can number. You could do that in a where clause or as a subselect
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2012

    I put the following coce in the on open event of the splash screen


    Private Sub Form_Open(Cancel As Integer)

    On Error GoTo HandleError

    Dim DB As Database
    Dim CheckYear, TodaysDate, PrevYear, PlusYear As Date
    Dim RS As Recordset
    Dim i As Integer

    Set DB = CurrentDb

    Set RS = DB.OpenRecordset("tblCampaignYear", dbOpenDynaset)

    For i = 1 To CommandBars.Count
    CommandBars(i).Enabled = True
    Next i

    DoCmd.ShowToolbar "Menu Bar", acToolbarNo


    TodaysDate = Date
    CheckYear = Year(TodaysDate)

    PrevYear = DLast("CampaignYear", "tblCampaignYear")
    PrevYear = Year(PrevYear)

    If PrevYear = CheckYear Then
    Exit Sub
    End If

    If MsgBox("Select Yes To Start New Campaign", vbYesNo, "New Campaign") = vbYes Then
    DoCmd.OpenQuery "QryUnassignCans"
    RS!CampaignYear = Date
    RS.Bookmark = RS.LastModified
    Exit Sub
    End If

    If Error <> 0 Then
    Resume Next
    GeneralErrorHandler Err.Number, Err.Description, "modBusinessLogic", _
    Resume Next
    End If

    Exit Sub

    End Sub

    this checks a year table and compares with todays date. If they are equal then the campaign has already been set. If they are not equal it means they have not been reset. the msg box gives you then the two options 1) reset then it will will not ask again this year 2) ignore and will ask the same question next time.

    Thanks for all the input and it did lead me to work it out myself.


Tags for this Thread

Posting Permissions

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