none
VBA for Access 2007 - code to check date entered in form against date in list to update a field in a table RRS feed

  • Question

  • Hello,

    firstly I will apologise for my complete ignorance of all things technology, I'm just trying my best with no background knowledge.

    Ok,

    I've built a simple Access 2007 database that is a simple time-sheet database that records worker's duties each day and that's fine; I haven't built any reporting yet (still got to learn more there first).

    I would now like to improve the database to do some "AUTO-FILLING" so to speak and I've been told that to achieve what I want to achieve, I'll need to build some VBA code attached to an "AFTER-UPDATE()" event.  Problem is, I know absolutely nothing about Access in general & even less about VBA, so I'm hoping someone here can assist me???

    SOME BACKGROUND INFO:

    Workers are paid an upfront "Overtime Allowance" each pay (fortnight) that includes pre-payment for 14 hours overtime each fortnight.  Any additional overtime (over the 14 hours pre-paid) performed in that fortnight will be given back to the worker as time off at a time of their choosing.

    Each fortnight period is given a number (eg, 2013-01, 2013-02 ....... 2013-26, 2014-01......... 2014-26, etc) as there 26 fortnightly pay periods in each year & this determines how much time-off each worker is owed at any given point in time.

    If a worker does 10 hours overtime in period 2013-01 (not over the 14 hours pre-paid) then no problems, they don't accrue any time-off and they don't owe anything either they just restart for the next pay period, but if they do 16 hours overtime in period 2013-02 (2 hours over the 14 hours pre-paid) then I need to capture the extra 2 hours and accrue it to the worker's time-off owed balance.

    So I'd like to automate the process of assigning the work performed on any given day, to the relevant pay period as the worker might accidentally put it to the wrong period and might therefore miss out on any time off owed.

    TABLES & FIELDS AFFECTED:

         "Work_Hours"

    This table has the following fields which are affected by what I want to achieve:

         "Date_Worked" (Date/Time)

         "Pay_Period" (Number) [many side of relationship]

    The field "Pay_Period" has "one-to-many" relationship to a field called "ID" in another table called "tbl_PayPeriod" which has the following fields:

        "ID" (AutoNumber) [one side of relationship]

        "Period_Start" (Date/Time) [has a static date for the START of each pay period for next 10 years as it's content]

        "Period_Number" (Text) [has a static number for each pay period for the next 10 years as it's content]

    RELEVANT FORM:

    I've created a form called "frm_Work_Hours" based on "tbl_PayPeriod" where the worker imputs their daily work.

    OUTCOME ENVISAGED:

    I've built the tables & fields this way, as I envisage the worker would put the date in the "Date_Worked" field in the "frm_Work_Hours" form then tab to the next field. Upon doing that, some VBA code would work away in the background to check the "Date_Worked" input against the "Period_Start" fields in the "tbl_PayPeriod" table.

    If the "Date_Worked" is between equal to or greater than any "Period_Start" but less than the next "Period_Start" then assign the relevant "Period_Start" "ID" from the "tbl_PayPeriod" to the "Pay_Period" field on the relevant "Work_Hours" table entry.

    I apologise if that logic is skewed; it makes sense to me, but I'm not a techo type, so I'm tipping it's not going to make much sense to programmers.

    REAL QUESTION:

    I'd be SUPER-APPRECIATIVE if someone could show me the VBA code I'd need to insert into the "Date_Worked" "AfterUpdate()" event to make this happen.

    Again, I'm sorry for the extraordinarily long-winded explanation, but I thought it best to be as informative as I could to get to the answer as quickly as possible.

    I sincerely thank you for taking the time to read & consider my question.

    Kind regards,

    duBe

    Friday, October 4, 2013 12:59 AM

Answers

  • Hi Dube,

    I think you are trying to retrieve the Pay Period based on a date?

    If so the try this:

    me.pay_Period = Dlookup("Pay_Period","tbl_PayPeriod","Period_Start <#" & me.Date_Worked & "# AND Period_Start +14 >= #" & me.Date_Worked & "#")

    Dlookup is a little inefficient but an easy solution. 

    You should also consider using Elookup instead see http://allenbrowne.com/ser-42.html


    Brian, ProcessIT- Hawke's Bay, New Zealand

    • Marked as answer by duBe68 Friday, October 4, 2013 4:19 AM
    Friday, October 4, 2013 3:57 AM

All replies

  • Hi Dube

    first you need a simple test like

    if me.HoursWorked > 14 then...

    Then you would run an insert query to create the next period and insert the balance of hours as calculated by me.Hoursworked - 14

    Suggest that you build the query "on the fly" in code using values from your form...

    Hope this offers some direction...


    Brian, ProcessIT- Hawke's Bay, New Zealand

    Friday, October 4, 2013 1:12 AM
  • Thanks Brian,

    I will need that to build the auto-check hours for overtime later, so thank you very much for that, certainly going to help when I get to that stage.

    .

    Not sure if you can point me in the right direction to compare the "Date_Worked" entry to the "Period_Start" list,  select the relevant "ID" for that "Period_Start" but that's where I'm stuck at the moment.

    .

    Greatly appreciate your assistance Brian,

    Kind regards,

    duBe

    Friday, October 4, 2013 1:23 AM
  • Sorry if this confuses things even more, but what I'm thinking (in my special version of pseudo programming) is something like:

    Private Sub Date_Worked_AfterUpdate()    
        If [tbl_PayPeriod].[Period_Start] > Me.Date_Worked Then
            Me.Pay_Period = [tbl_PayPeriod].[ID] - 1
        End If

    End Sub

    I realise this is probably causing offence to programmers, as it is not programming, but I hope it conveys my idea of what I want to achieve.

    Thanks again in advance,

    duBe








    • Edited by duBe68 Friday, October 4, 2013 3:52 AM
    Friday, October 4, 2013 1:41 AM
  • Hi Dube,

    I think you are trying to retrieve the Pay Period based on a date?

    If so the try this:

    me.pay_Period = Dlookup("Pay_Period","tbl_PayPeriod","Period_Start <#" & me.Date_Worked & "# AND Period_Start +14 >= #" & me.Date_Worked & "#")

    Dlookup is a little inefficient but an easy solution. 

    You should also consider using Elookup instead see http://allenbrowne.com/ser-42.html


    Brian, ProcessIT- Hawke's Bay, New Zealand

    • Marked as answer by duBe68 Friday, October 4, 2013 4:19 AM
    Friday, October 4, 2013 3:57 AM
  • AWESOME! Thanks HEAPS Brian,

    that worked a treat:

    Private Sub Date_Worked_AfterUpdate()
    
        Me.Pay_Period = DLookup("ID", "tbl_PayPeriod", "Period_Start <#" & Me.Date_Worked & "# AND Period_Start +14 >= #" & Me.Date_Worked & "#")
        
        ' If [tbl_PayPeriod].[Period_Start] > Me.Date_Worked Then
        '     Me.Pay_Period = [tbl_PayPeriod].[ID] - 1
        ' End If
    
    End Sub

    Now, when the worker puts in the Date_Worked and tabs to the next field, the correct ID of the Period_Number displays in the form. That is fantastic, thanks heaps.

    I wonder if I could be so bold as to ask one final question with relation to this?

    As the "ID" doesn't really mean anything (AutoNumber), can you assist me with how to make the actual "Period_Number" display on the form instead of the "ID"?

    I changed the ID to Period_Number in the DLookup but it just kept coming back with errors.

    Thanks heaps again Brian, I GREATLY appreciate your assistance.

    Kind regards,

    duBe

    Friday, October 4, 2013 4:19 AM
  • All good now,

    I had a bit of a play around & came up with a bit of an ad-hoc work-around by:

    Creating a new unbound text box on the form & inserting a bit more into the DLookup code you gave me for the Date_Worked_AfterUpdate() event:

    Private Sub Date_Worked_AfterUpdate()
    
        Me.Pay_Period = DLookup("ID", "tbl_PayPeriod", "Period_Start <#" & Me.Date_Worked & "# AND Period_Start +14 >= #" & Me.Date_Worked & "#")
        Me.txt_PayPeriod_Number = DLookup("Period_Number", "tbl_PayPeriod", "Period_Start <#" & Me.Date_Worked & "# AND Period_Start +14 >= #" & Me.Date_Worked & "#")
        
    
    End Sub
    

    Then I set the "Pay_Period" form field's visible property to NO so it can't bee seen.

    That seems to work the way I want however I'm tipping there are far more elegant ways to go about it.

    Thanks again Brian, you really got me sorted out very quickly and easily.

    Cheers,

    duBe

    Friday, October 4, 2013 5:20 AM
  • Actually,

    upon closer inspection, I've noticed that is coming up with incorrect Period_Numbers.

    It appears to me that it's comparing the dates based on what appears to be the American date format (mm/dd/yyyy) instead of the Australian date format (dd/mm/yyyy).

    I'm not using the American format & I can't find how to rectify this in the query.

    Can anyone please point me in the right direction?

    Thanks in advance,

    duBe

    Friday, October 4, 2013 5:28 AM
  • Glad to have helped.

    Re the Dates all SQL uses US Date formats. Just convert using Format([MyDate],"mm/dd/yyyy")


    Brian, ProcessIT- Hawke's Bay, New Zealand

    Friday, October 4, 2013 6:01 AM