I have a database where my supervisors are really concerned about data being inadvertently overwritten by careless data entry. I am trying to write in some code to prevent this. in a form called Weekly_Report it opens to a new form every time and moves to the control of WE_End_Date_Cmbo which is a date picker combo list. The user will input the date ending date (a Friday) and based on this selection i want it to do 2 things. First to check to see if this week has already been entered and if it has to go to that records so that the new data can be edited and if it has not check this date to make sure that it is a Friday date and continue on with this form. I have the following code written but I am having trouble with the code for navigating to the correct record if it is already existing:
Private Sub Wk_End_Date_Cmbo_AfterUpdate()
Me.Wk_End_Date_Cmbo_Relay_Txt.Value = Me.Wk_End_Date_Cmbo.Value
Dim UniqueIDCount As String
Dim DateYr As String
UniqueIDCount = DCount("Wk_End_Date", "Weekly_Report", "Wk_End_Date = Wk_End_Date_Cmbo_Relay_Txt")
If UniqueIDCount > 0 Then
Me.Wk_End_Date.Value = ""
Dim varX As Variant
varX = (DLookup("Weekly_Report_ID", "Weekly_Report", "Wk_Number = '" & Me.Wk_Number & "'"))
DoCmd.GoToRecord acDataForm, "Weekly_Report", acGoTo = varX
DateTemp = Me.Wk_End_Date_Cmbo_Relay_Txt.Value
DateYr = Year(Me.Wk_End_Date_Cmbo_Relay_Txt.Value)
Me.WE_Year.Value = DateYr
Me.Wk_Number.Value = DateYr & "-" & DatePart("ww", Me.Wk_End_Date_Cmbo.Value, vbSaturday, vbFirstFourDays)
WkNumber = Me.Wk_Number.Value
Else
If Weekday(DateTemp) <> 6 Then
MsgBox ("Please select a Friday")
Else
DoCmd.GoToRecord acDataForm, "Weekly_Report", acNewRec
Me.Wk_End_Date.Value = DateMo & "/" & DateDy & "/" & DateYr
End If
End If
End Sub
Also I am not sure if I need to do the else go to new record if it is already set to a new record. However if they are entering/changing data from another week and enter a new date I want it to go to a new record and not overwrite.
C.