Finding value in Form Load event

[Guest]

Hello, I have an Access form with a MSCAL control [ApptCal] and
a combo box control [cboApptDate].
The underlying table "ApptDis" has as it's primary key a Date/Time
field "ApptDate".
I have code in place that synchronizes the (cboApptDate] & [ApptCal]
and uses the Bookmark property to synchronize [ApptCal] & table records.
That code is:
Private Sub ApptCal_AfterUpdate()
Me.RecordsetClone.FindFirst "[ApptDate] = #" & Me![ApptCal] & "#"
With Me.RecordsetClone
.FindFirst "[ApptDate] = #" & Me![ApptCal] & "#"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

I'm trying to write code that will Load the form onto the record that
reflects the current Date. Such as:

Private Sub Form_Load()
Dim rst As Object
Dim strCriteria As String

strCriteria = "[ApptDate] = #" & Me![ApptCal] & "#"

Set rst = Me.Recordset.Clone
rst.FindFirst strCriteria

If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
End If
End Sub

I believe my problem is in the strCriteria line, not defining the value(?)
Any help would be greatly appreciated
Paul

 

[Douglas J. Steele]

Does strCriteria get a value in the Load event?

Try putting MsgBox strCriteria after you define it to see.

 

[Guest]

Yes, I get #9/25/2007# as a value, which is the date of the first record in
the table.

Does strCriteria get a value in the Load event?

Try putting MsgBox strCriteria after you define it to see.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello, I have an Access form with a MSCAL control [ApptCal] and
a combo box control [cboApptDate].
The underlying table "ApptDis" has as it's primary key a Date/Time
field "ApptDate".
I have code in place that synchronizes the (cboApptDate] & [ApptCal]
and uses the Bookmark property to synchronize [ApptCal] & table records.
That code is:
Private Sub ApptCal_AfterUpdate()
Me.RecordsetClone.FindFirst "[ApptDate] = #" & Me![ApptCal] & "#"
With Me.RecordsetClone
.FindFirst "[ApptDate] = #" & Me![ApptCal] & "#"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

I'm trying to write code that will Load the form onto the record that
reflects the current Date. Such as:

Private Sub Form_Load()
Dim rst As Object
Dim strCriteria As String

strCriteria = "[ApptDate] = #" & Me![ApptCal] & "#"

Set rst = Me.Recordset.Clone
rst.FindFirst strCriteria

If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
End If
End Sub

I believe my problem is in the strCriteria line, not defining the value(?)
Any help would be greatly appreciated
Paul

 

[Guest]

I replaced the refrence to the [ApptCal] control in the strCriteria line with
Date.
I also replaced Set rst = Me.Recordset.Clone with Set rst = Me.Recordsetclone
Everything works correctly now.
Thank You very much for your input.
Paul

Does strCriteria get a value in the Load event?

Try putting MsgBox strCriteria after you define it to see.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello, I have an Access form with a MSCAL control [ApptCal] and
a combo box control [cboApptDate].
The underlying table "ApptDis" has as it's primary key a Date/Time
field "ApptDate".
I have code in place that synchronizes the (cboApptDate] & [ApptCal]
and uses the Bookmark property to synchronize [ApptCal] & table records.
That code is:
Private Sub ApptCal_AfterUpdate()
Me.RecordsetClone.FindFirst "[ApptDate] = #" & Me![ApptCal] & "#"
With Me.RecordsetClone
.FindFirst "[ApptDate] = #" & Me![ApptCal] & "#"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

I'm trying to write code that will Load the form onto the record that
reflects the current Date. Such as:

Private Sub Form_Load()
Dim rst As Object
Dim strCriteria As String

strCriteria = "[ApptDate] = #" & Me![ApptCal] & "#"

Set rst = Me.Recordset.Clone
rst.FindFirst strCriteria

If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
End If
End Sub

I believe my problem is in the strCriteria line, not defining the value(?)
Any help would be greatly appreciated
Paul