I am trying to get some code at the beginning of my form load to get the user to the correct screen.
when the form opens I want them to enter a date into a message box and then compare that date with the previously entered dates.
if the date has been previously entered, i want it to go to that record to have it modified to prevent multiple entries for the same date. if it isn't i want to go to a new record and have that date populated as the week ending date. This is the code that I have written but it does not want to do the go to new record command if the date is not found. what am i doing wrong
Private Sub Form_Load()
Dim Message, Title, MyValue
Message = "Please enter a Friday Week End Date for which to enter Data" ' Set prompt.
Title = "Weekly Report Entry" ' Set title.
' Display message, title, and default value.
MyValue = InputBox(Message, Title)
Dim UniqueIDCount As String
Dim DateYr As String
Dim DateDy As String
Dim DateMo As String
DateDy = Day(MyValue)
DateMo = Month(MyValue)
DateTemp = MyValue
DateYr = Year(MyValue)
WkNumber = DateYr & "-" & DatePart("ww", DateTemp, vbSaturday, vbFirstFourDays)
UniqueIDCount = DCount("Wk_Number", "tbl_Weekly_Report", "Wk_Number ='" & WkNumber & "'")
If UniqueIDCount > 0 Then
Dim varX As Variant, rs As Dao.Recordset, lngID As Long
Set rs = Me.RecordsetClone
lngID = (DLookup("Weekly_Report_ID", "tbl_Weekly_Report", "Wk_Number ='" & WkNumber & "'"))
rs.FindFirst "[Weekly_Report_ID]=" & lngID
varX = rs.Bookmark
Me.Bookmark = varX
Set rs = Nothing
Me.Wk_End_Date_Cmbo.Value = DateMo & "/" & DateDy & "/" & DateYr
Else
If Weekday(DateTemp) <> 6 Then
MsgBox ("Please select a Friday")
Else
DoCmd.GoToRecord acDataForm, "frm_Weekly_Report", acNewRec 'this is where i am getting the error
Me.Wk_End_Date.Value = DateMo & "/" & DateDy & "/" & DateYr
Me.Wk_End_Date_Cmbo.Value = DateMo & "/" & DateDy & "/" & DateYr
Me.WE_Year.Value = DateYr
Me.Wk_Number.Value = WkNumber
End If
End If
End Sub