1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: MS Access - How to get a field auto-update based on date entered in a form

Discussion in 'Business Applications' started by duBe68, Oct 2, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. duBe68

    duBe68 Thread Starter

    Joined:
    Oct 2, 2013
    Messages:
    9
    Hello,
    Firstly, let me apologise, I'm a complete Access noob & am trying to teach myself as I've got no one to help me in person.

    Ok,
    I'm trying to build a Time Sheet database that should record daily duties via an entry form:
    "frm_Work_Hours"

    This form updates an underlying table:
    "Work_Hours"

    The date field of the "Work_Hours" table is:
    "Date_Worked"

    However, workers get paid fortnightly and all their entitlements, allowances & overtime are calculated on these fortnightly periods. To be more specific, the workers are paid for a certain amount of overtime for each fortnight upfront & when they exceed that, then they are entitled to all excess time back in TIME OFF. So it's pretty important that each day's duties are assigned to the correct fortnight period so that their overtime & so-forth are calculated correctly.

    Each fortnightly period is numbered according to year. For example, each year there are 26 fortnightly periods, so as an example today (3rd October 2013) falls in period 2013-19 (23rd September 2013 to 6th October 2013).

    I've created a table called:
    "tbl_Pay_Period"

    Which has the following Fields:
    "ID" (Auto-number)
    "Period_Start" (date - the first day of each new fortnight period)
    "Period_Number" (number - eg: 2013-19)

    Back in the "Work_Hours" table, I inserted a new (Number) FIELD called:
    "Pay_Period"

    and set it's LOOKUP to:
    Display Control: List Box
    Row Source Type: Table/Query
    Row Source: SELECT[tbl_PayPeriod].ID, [tbl_PayPeriod].Period_Start, [tbl_PayPeriod].Period_Number FROM tbl_PayPeriod;
    Bound Column: 1
    Column Count: 3
    Column Heads: No
    Column Widths: 0cm;0cm;5cm

    This works as expected (displays the "Period_Number" field) but I have to manually select which "Period_Number" from the list.

    I'd like to automate the process by having the date entered in "Date_Worked" force a behind-the-scenes auto-select of the correct "Period_Number" based on the appropriate "Period_Start".

    I hope that all makes sense; sorry for the lengthy questions, but I thought it might be best to fully explain myself.

    Thanks heaps in advance,
    duBe
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    duBe68, welcome to the Forum.

    You can't explain with too much detail, it is far better than insufficient detail.

    Your form will require some VBA code in the Date Worked field's After Update Event Procedure, this can either use a VBA created Recordset (query) or use the Dlookup function.
    I would reset the Period_Number" field in the table back to a simple Text field rather than a lookup list box.
     
  3. duBe68

    duBe68 Thread Starter

    Joined:
    Oct 2, 2013
    Messages:
    9
    Thanks OBP,
    I'll try to find some examples of VBA code & see if I can get the gist of that.

    Thanks for pointing me in the right direction.

    Cheers,
    duBe
     
  4. duBe68

    duBe68 Thread Starter

    Joined:
    Oct 2, 2013
    Messages:
    9
  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    First of all you should test that someone has actually entered a date and not cleared an old one.
    In answer to your question on that forum all the VBA dates should be enclosed in the # symbol which converts them to USA format.
    Or enforce the format using the Format function ie format([Period_Start], "mm/dd/yyyy"), but the convention is the # symbol.

    PS I would have provided the VBA for you.
     
  6. duBe68

    duBe68 Thread Starter

    Joined:
    Oct 2, 2013
    Messages:
    9
    Thanks OBP,
    I hope I didn't offend you; that was certainly not my intention, I was just trying to get a solution as quick as possible.

    Turns out that the VBA solution posted on that thread doesn't work as expected; the form keeps dropping the "Period_Number" when I exit the form & come back to it.

    Don't really understand why, but I modified the "Work_Hours" table to include a "Period_Number" field & set it's SOURCE CONTROL to the "PayPeriod" table's "Period_Number" field.

    I then modified the VBA for the "Me.Date_Worked" "AfterUpdate()" Event to:

    That solved the issue of the Period_Number disappearing from the form & although I came up with the fix, I'm not too sure I fully understand it.

    As for the date issue, I'm really lost with that.
    I really can't get my head around how to fix it with the month as the first numbers.

    I'm stuck with the (obviously incorrect) belief that the error arises because if the first numbers (in my case the days - DD) are >= 12, then the check goes straight to DECEMBER months to check as it thinks of the first numbers as MONTHS - MM, instead of dealing with the first numbers as days -DD.

    This throws-up all kinds of erroneous returns, making the Period_Number reported useless for the intended purpose.

    So, as I'm a noob, I'm struggling to understand how exactly to overcome that, or even if that's correct.

    Anyhoo, I'll keep plugging away experimenting with different things & hopefully I'll come across something that works. However, if I haven't offended you too much, & you're still in a generous frame of mind & have the time, I'd be VERY grateful for any VBA code that would fix the issue.

    Thanks again OBP, I do appreciate your assistance.

    Kind regards,
    duBe
     
  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    DuBe, if you can provide me with a zipped Access 2000-2007 version of the database I will create the code for you.
    I am not offended as I know the pressure of trying to solve a problem and being in the UK (Time Zone differences) doesn't help either.
     
  8. duBe68

    duBe68 Thread Starter

    Joined:
    Oct 2, 2013
    Messages:
    9
    Thanks heaps OBP,
    your code & query absolutely worked the way I was hoping to achieve, without the problems I got with the other workaround mentioned previously.

    Unlike the other method offered on the other thread, this code does exactly as expected.
    I wish I understood it all, but I'm getting there.

    Thanks again OBP, I really appreciate it.

    Kind regards,
    duBe
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1109804

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice