I have a form with StartDate, 3 Comboboxes (Employee, Consumer, Service), and a button. What I'm struggling with is code to
do the following:
User enters a start date (mandatory), and values in all of the comboboxes (mandatory), then hits the button. The subform then
creates 7 records with those default values, with each record dated according to StartDate, where each record is a day of the
I've attached what I have currently, which is hardly anything - I can't figure out how to create the records on the click
event. Also attached is the desired result after clicking Go given the values on the main form.
I also need to add a button to submit these records, which on a click will add the records to the table while deleting (or
undoing) any records that do not have all field complete.
*note - A consumer can only have a particular service once per day, so there should never be duplicates of
Consumer+Service+Date. I figured I could pretty easily add a field to the table that uses these values to create a uniqueID,
then prevent duplicates, but I also need the form to display these values if within the range and values (excluding Employee)
in the main form.
I have some code (from Stackoverflow) but I'm not sure of how to use the counter/calendar table it suggests, or what field
"AddDate" is referencing.
Private Sub Go_Click()
Dim rs As DAO.Recordset
Dim sSQL As String
Dim sSDate As String
Dim sEDate As String
sSDate = "#" & Format(Me.StartDate, "yyyy/mm/dd") & "#"
sEDate = "#" & Format(Me.StartDate + 7, "yyyy/mm/dd") & "#"
sSQL = "SELECT * FROM tblTimeCardHours WHERE DateWorked Between " & sSDate _
& " AND " & sEDate
Set rs = CurrentDb.OpenRecordset(sSQL)
If rs.RecordCount < 7 Then
AddRecords sSDate, sEDate
Me.frmTimesheetEntrySubform.Form.RecordSource = sSQL
Sub AddRecords(sSDate, sEDate)
''Uses counter table with integers from 0 to
''highest required number
''Another useful table is a calendat table, which would
''save some work here.
sSQL = "INSERT INTO tblTimeCardHours (DateWorked) " _
& "SELECT AddDate FROM " _
& "(SELECT " & sSDate _
& " + [tblTimeCardHours.TimeCardDetailID] AS AddDate " _
& "FROM [tblTimeCardHours] " _
& "WHERE " & sSDate _
& "+ [tblTimeCardHours.TimeCardDetailID] Between " & sSDate _
& " And " & sEDate & ") a " _
& "WHERE AddDate NOT In (SELECT DateWorked FROM tblTimeCardHours)"
CurrentDb.Execute sSQL, dbFailOnError