Results 1 to 3 of 3

Thread: Reminder

  1. #1
    Join Date
    Jul 2005

    Red face Unanswered: Reminder

    Hi all,
    I am trying to set a reminder in access for my upcoming monthly bills and to acieve that i have following fields in my table.
    ID AutoNumber
    BillName TextBox
    CompletionDate DateTime
    Paid CheckBox

    Then i have this query that runs and tells me what bills are due this month

    SELECT tblBillReminder.ID, tblBillReminder.BillName, tblBillReminder.CompletionDate, tblBillReminder.Paid
    FROM tblBillReminder
    WHERE (((tblBillReminder.Paid)=0) AND ((Month([CompletionDate]))=Month(Now())));

    I also have this code runs as the onload event of the form
    'On Load of the switchboard check Jobs table for any uncompleted jobs

    Dim intStore As Integer

    'Count of uncomplete jobs that are past the Expected Completion Date
    intStore = DCount("[JobNumber]", "[tblBillReminder]", "[CompletionDate] <=Now() AND [Paid] =0")

    'If count of uncomplete jobs is zero display switchboard
    'Else display message box detailing amount of jobs
    'and give the user the option as to whether to view these or not.
    If intStore = 0 Then
    Exit Sub
    If MsgBox("There are " & intStore & " uncompleted jobs" & _
    vbCrLf & vbCrLf & "Would you like to see these now?", _
    vbYesNo, "You Have Uncomplete Jobs...") = vbYes Then
    DoCmd.OpenForm "frmreminders", acNormal
    Exit Sub
    End If
    End If
    End Sub

    All of the above works fine but for each month i have to manually enter bills and date for my query to work.

    How can i have access automatically generate bills name and date for each month.

    for example

    Billname Creditcard
    CompletionDate 2-15-07

    Billname Gas
    CompletionDate 2-15-07

    upon checking the paid box reminders go away for that month.

    Is this possible??

  2. #2
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 5
    Dont understand what you on about

    but think with this line
    intStore = DCount("[JobNumber]", "[tblBillReminder]", "[CompletionDate] <=Now() AND [Paid] =0")

    change it
    intStore = DCount("[JobNumber]", "[tblBillReminder]", "[CompletionDate] <=#" & Now() & "# AND [Paid] =0")
    hope this help

    See clear as mud

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

  3. #3
    Join Date
    Jul 2005

    Red face How can i enter name and date auomatically

    Thanks Myle for replying,
    I am not sure how this line is going to help me but take a look at my sample database.

    Quote Originally Posted by myle
    intStore = DCount("[JobNumber]", "[tblBillReminder]", "[CompletionDate] <=#" & Now() & "# AND [Paid] =0")
    open tblBill and take a look at billname and completiondate.

    How can I automatically enter name and date for every month?
    Attached Files Attached Files

Posting Permissions

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