I have a main form for navigation between my other forms. For my frm_Weekly_Report I have a main menu text box with a date picker to select a date and a "Go" Button. When the user clicks the button it evaluates this date to ensure that it a Friday date and is stored in a variable called StartDate which is defined as a string. If it is a friday then it would open the frm_Weekly_Report.
On the form load for frm_Weekly_Report I want it to evaluate this StartDate to see if it has been entered already. If it has I want it to go to that record, if not I want it to go to a new record
On frm_Main I have the following code.
Code:
Private Sub Go_To_Weekly_Click()Dim StartDate As String
Dim WEFri As Integer
Me.Test.Value = ""
StartDate = Me.txtStartDate.Value
WEFri = Weekday(StartDate)
Me.Test.Value = WEFri
If WEFri <> 6 Then
MsgBox ("Please select a Friday")
txtStartDate.SetFocus
Else
DoCmd.OpenForm "frm_Weekly_Report"
End If
End Sub
and in the frm_Weekly_Report i have this.
Code:
Private Sub Form_Load()
MyValue = Form_frmMain_txtStartDate.Value
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
Else
DoCmd.GoToRecord acDataForm, "frm_Weekly_Report", acNewRec
Me.Wk_End_Date.Value = DateMo & "/" & DateDy & "/" & DateYr
Me.WE_Year.Value = DateYr
Me.Wk_Number.Value = WkNumber
End If
End Sub
how do I pass the StartDate value to be the MyValue in the Weekly Report form?