Selecting dates...

[lithium]

Okay, I'm throwing together a weekly tracker and need some guidance. This basically has the five days listed out in seperate boxes on a from (nothing fancy, just text boxes). I want the database to load up and if it's wednesday, automatically load the previous data from monday and tuesday in their respective fields.

How can I accomplish this?

[Micron]

This is not as clear as you may think.

And if it's Tuesday, do what?
Are these textboxes bound to a table or query?
Do you simply want those textboxes to show Monday to Friday for a given week?
"automatically load the previous data from monday and tuesday in their respective fields"
What data, the date for Monday and Tuesday? Data in a subform datasheet you didn't mention?

That you would have 5 textboxes for 5 days indicates this is not set up correctly, or else you have some sort of special requirement that I can't envision.

[lithium]

Hopefully I can clarify.

I will have individual boxes (each box is labeled Monday, Tuesday, etc.) and inside these "boxes" on the form will have time tracking items (job, how many hours, notes field).

I want the database to load up and detect that it is, for instance, Wednesday today. The database would automatically go pre-fill what they entered on Monday and Tuesday. The text boxes are not bound but once the user hits Submit on a day's report, it loads that individual days tracking items into the database table, tied to their name.

The main question is, what logic do I have to program in order to have the database know todays date, be able to detect if it is Tuesday or a wednesday (any Monday-Friday) and load the previous boxes with any submitted information?

Tables:
j_Type (job type, just has various jobs being worked on)
tblEmp (employee name, Job Type, Hours)

I have the boxes broke out into individual days on the front end because many users plug the information in daily, and some do a weekly. The use of a spreadsheet style or new record on a subform seems to bother them. They wanted the Monday-Friday laid out on the form and each box populates that days specific details according to waht day it is currently.

[ssanfu]

I want the database to load up and detect that it is, for instance, Wednesday today.

That part is easy. The unbound text boxes should have names with a suffix like "EmpName2", "EmpName3", "EmpName4", etc...
This makes it easier to do looping and cut down on code.
The Weekday(date) function returns a value if 1 - 7, so 2 through 6 are Mon - Fri.

The database would automatically go pre-fill what they entered on Monday and Tuesday.

This part is not difficult, it just tales a LOT of code, since you have to write all of the code to do what Access normally handles.

The text boxes are not bound but once the user hits Submit on a day's report, it loads that individual days tracking items into the database table, tied to their name.

This will require some thought so duplicate entries are not made. But an append query should handle the data submission.

[lithium]

Yeah, my main concern comes from how to add the logic of "if it's Tuesday, preload all Monday stuff on this date". Basically how to tell access "it's 6/24/16 today, go find yesterday's items and load that box

[ssanfu]

You have dates in the table?

Get today's weekday number. Today is Fri, so the number would be 6.
Start a loop:

WkDayNum = weekday(Date())

For x = WkDayNum - 1 to 2 step -1
   Use DateAdd("d", x * -1, Date() ) to get the date of the records to retrieve (by emp num)
   retrieve the records and put the data into the unbound text boxes on the form

Next x

[lithium]

So, I think it's on the right track. I did learn though, I only need a listbox (lstJob1, lstJob2, etc) for each day of the week (Monday-fri) to query date field (I have a txtDate that this code feeds the date too) to populate and none of the other boxes.

I set the weekdate to think it's Tuesday (for testing) and it seems like it goes back two days instead of one? I ran it today and the date it retrieved was the 25th, not the 26th? Am i overlooking something?

Dim LDate As DateDim LValue As String
WkDayNum = "3" 'Weekday(Date)
Dim strLoad As String


For X = WkDayNum - 1 To 2 Step -1
   LDate = DateAdd("d", X * -1, Date)
   LValue = WeekdayName(X, True, vbMonday)
   MsgBox (LDate)
    Select Case X
        Case 2
        
            txtDate.Value = LDate
            lstJob1.Requery
            
        Case 3
        
        Case 4
        
        Case 5
        MsgBox (LValue)
           lstJob1.RowSource = (DLookup("[jCode]", "tblEmp", "jDate = " & "'" & LDate & "'"))
            txtDate.Value = LDate
           
        Case 6
    End Select


Next X

[ssanfu]

I'm really lost on what you are trying to do.

You cannot use a list box like you show in the code - you would also have to set the Row Source Type property for the list box to "Value List".

See the dB for code to fill text boxes with dates. The date text box defaults to today's date. If today is a Mon, nothing gets filled in. Set the date to a Fri and Mon - Thu dates get filled in.