Update text boxes based on date selected in a combo box (1 Viewer)

[Jul]

I have a form that our operators use to do their hourly quality audits on. This is getting to be a huge burden on them because right now, they enter the date, the week ending date, the month every time they have to do an entry, and for me it is a nightmare because they can still enter the wrong information. So, what I was wondering is if there is a way that I can have them select the date from a combo box (easy enough), but from that, have the week ending date and the month fields automatically update as well. Any advice? I would really appreciate it! Thanks so much!!!!

 

[ColinEssex]

You can use

Format([Datefield],"mmmm")

to get the month name and use this function to get the last (saturday) of the week.

Function tbLastDateW(dtmDate)
    '
    'Warning dtmDate must be a valid date
    '
    'Return the Last date of provided week
    '
    Dim intDayCnt As Integer
    
    intDayCnt = 7 - WeekDay(dtmDate, 1)
    tbLastDateW = DateAdd("d", intDayCnt, dtmDate)

End Function

Col

 

[Jul]

I am going to sound really dumb here , but I am totally lost.....I don't really know code in access, I have done a lot of database, but have never used code. I don't know where to even begin with the code.

The other issue is, our months aren't the actual month, we use a cost closing schedule.

What I have is a table that contains the date, week ending date, and month. I guess I was just looking to see how I could set the default value of the week ending date (which is always the Saturday of the week we are in), and the cost month all of which are from the same table. Does that make any sense? Maybe that is what the code would do, but I honestly don't even know where to type that code in for my form.

 

[MsLady]

Hi Jul

It's not wise to save datevalues this way in your table, cos you will only be duplicating data and there is no way to check for inconstitencies. All you need to save is the "date" itself, then you can always use functions to call the datepart (year,month, weekending, etc) whenever you need to display them.

In case you insist on saving differnt date parts in ur table Try this below:

1. Save Colin essex's weekending function in ur form's code window

2.Use this beforeUpdate event in your date combobox:

be sure to edit the control names to match yours!

Private Sub cboDate_BeforeUpdate()
Me.[yourMonthfield] = Format(Me.cboDate.value,"mmmm") 
Me.[yourWeekendingfield] = tbLastDateW(Me.cboDate.value) 
End Sub