I have a form that allows the user to select the date at the top. As the user changes the date, it should change the record to reflect. If there is a record for that date already then switch to that record, but if no record for that date create a new one. The table is set to not allow duplicates on the date column. Here is the code I have on the Form_Load event and the respective subs that I call:
Code:
Private Sub Form_Load()
Me.cobYear.Value = Year(Date)
Me.cobMonth.Value = Month(Date)
DaysChange Me
Me.cobDate.Value = Day(Date)
UpdateDate Me
DoCmd.Maximize
End Sub
Sub DaysChange(objForm As Form)
Dim i As Integer
Dim DaysInMonth As Integer
Dim LeapDay As Integer
LeapDay = 0
If (Int(objForm.cobYear / 400) = (objForm.cobYear / 400)) Or ((Int(objForm.cobYear / 4) = (objForm.cobYear / 4)) And Not (Int(objForm.cobYear / 100) = (objForm.cobYear / 100))) Then
LeapDay = IIf(objForm.cobMonth = 2, 1, 0)
End If
DaysInMonth = DLookup("DaysInMonth", "tblMonths", "MonthNumber =" & objForm.cobMonth) + LeapDay
For i = 1 To DaysInMonth
objForm.cobDate.AddItem Item:=i
Next i
End Sub
Sub UpdateDate(objForm As Form)
If Not objForm.cobDate = "" And Not objForm.cobMonth = "" And Not objForm.cobYear = "" Then
objForm.tbDate.Value = DateSerial(objForm.cobYear, objForm.cobMonth, objForm.cobDate)
DayOfWeek = Weekday(objForm.tbDate.Value, 2)
'Me!subfrmDispatchSheet.Form.cobRouteID.Requery
objForm.lblDayOfWeek.Caption = WeekdayName(Weekday(objForm.tbDate.Value))
DateOfRecord = objForm.tbDate.Value
End If
End Sub
And this is the code for when a user changes the date:
Code:
Private Sub cobDate_Change()
UpdateDate Me
ChangeRecord
End Sub
Private Sub cobMonth_Change()
DaysChange Me
UpdateDate Me
ChangeRecord
End Sub
Private Sub cobYear_Change()
DaysChange Me
UpdateDate Me
ChangeRecord
End Sub
I have tried a few ways to do this.
1) I tried completely in code:
Code:
Private Sub ChangeRecord()
If DCount("ShiftDate", "tblShiftRecap", "ShiftDate =" & Me.tbDate.Value) = 0 Then
Else
Me.tbShiftID.Value = DLookup("ShiftID", "tblShiftRecap", "ShiftDate =" & Me.tbDate.Value)
End If
Me.Requery
End Sub
How can I do this on a single form? I know how to do it if I add a subform but not if all the fields are in my single form.
Unfortunately, this tries to add a new record when I load up the form.
2) I tried doing it in the query also
Code:
SELECT tblShiftRecap.ShiftID, tblShiftRecap.MQFStartTime, tblShiftRecap.MQFFinishTime, tblShiftRecap.MQFLoadingFinishTime, tblShiftRecap.MQFCases, tblShiftRecap.MQFRoutes, tblShiftRecap.MQFStops, tblShiftRecap.MQFTimeNotes, tblShiftRecap.NYPStartTime, tblShiftRecap.NYPFinishTime, tblShiftRecap.NYPLoadingFinishTime, tblShiftRecap.NYPCCases, tblShiftRecap.NYPRoutes, tblShiftRecap.NYPStops, tblShiftRecap.NYPTimeNotes, tblShiftRecap.LaborHoursCooler, tblShiftRecap.LaborHoursDry, tblShiftRecap.LaborHoursFreeze, tblShiftRecap.PalletsShipped, tblShiftRecap.POsReceived, tblShiftRecap.CutsMarkouts, tblShiftRecap.SafetyIncidents, tblShiftRecap.OrdersAudited, tblShiftRecap.SpotCheckedPallets, tblShiftRecap.InternalErrors, tblShiftRecap.SPVRObservations, tblShiftRecap.BuildingAndGrounds, tblShiftRecap.Equipment, tblShiftRecap.Trainees, tblShiftRecap.Notes, tblShiftRecap.ShiftDate
FROM tblShiftRecap
WHERE (((tblShiftRecap.ShiftDate)=GetDateOfRecord()));
and the functiont that the SQL calls:
Code:
Public Function GetDateOfRecord()
GetDateOfRecord = DateOfRecord
End Function