MS Access 2007 Form


I created a form in MS Access Office 2007.
What I need to do, if possible, is have a field auto populate based on another field.
I need to enter the week ending of the year.  There are 52 weeks in a year.  So in the year 2012, 01/03/12 is in the first week of the year.  If the user enters 01/03/12 in one field the other field should show “01” for example.  I created a table with the entire 365 days (1/1/12 to 12/31/12) in one column and the week ending in the corresponding column.

Please help.
Thanks in advance
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I think what you are looking for is code in the AfterUpdate event of the user entered date field as follows:

Private Sub txtDate_AfterUpdate()
    Me.txtWeekEnding = DLookup("WeekEnding","YourTable","YourDateField = #" & Me.txtDate & "#")
End Sub

Open in new window

That will look up the Week Ending in your table that corresponds to the user enterd date.

You'll have to adjust field and table names to match your application.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michael VasilevskySolutions ArchitectCommented:
You could also use DatePart(“ww”,Date()) to get the week number of a date without creating your own table.
ReyesrjAuthor Commented:

Could you write the script for me?

My table is called “WEEK Ending 2012”.  The first column is labeled “Date”.  It contains the list of dates (365 days).  The second column is labeled “Week Ending”.  It contains the list of week endings (1 to 52).


The user will enter a date into the "DATEHEAL" field in the form.  The "WEEK" field is what I need to automatically lookup the “DATEHEAL” date and return the value of the “Week Ending” number next to that date in the “Week Ending” column.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

You'd have to put the code in the After Update event of your DateHeal textbox:

    Me.Week = DLookup("[Week Ending]","[WEEK Ending 2012]","[Date] = #" & Me.DATEHEAL & "#")

Open in new window

Since this field can be looked at any time up you really should leave the control source of the Week Ending textbox blank (unbound), rather than storing it in your table.

Also take a look at mvasilevsky's solution.
ReyesrjAuthor Commented:
Okay, I could not get the private sub to work so, I'll go with "DatePart(“ww”,Date())".  How do I apply this?
ReyesrjAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.