Date search

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables linked together via a User ID. The table containing the user's info uses the Look up wizard to find the user's name (so the relationship is tblUsers.Name -> tblPersonalInfo.[User ID]). Using this set-up, I have created a form that cycles through all the users and a subform to display their personal info for editing or adding records to (i.e work performance based on date).

I am unsure of the syntax for how I would go about finding a record for a given date (unbounded text box) - since the date is on the subform, can I just do a find using the date field or do I need to include the user ID? I put the following routine on the AfterUpdate event of the date control:

Private Sub Date_AfterUpdate()
Dim inputDate As Date
inputeDate = Me!Date

Me.RecordsetClone.FindFirst "Date = " & inputDate
If Me.RecordsetClone.NoMatch Then
Me.Undo
MsgBox "Record not found"
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
Me.RecordsetClone.Close
End Sub

Unfortunately, when I enter a date for which a record exists, I get the message "Record not found" - does anyone know what I am doing wrong?

Thanks in advance,
Carrie
 
you need to delimit the date value with pound signs,
otherwise your date value is treated as a mathmatic
equation.

Me.RecordsetClone.FindFirst "Date = #" & inputDate & "#"

-----Original Message-----
I have two tables linked together via a User ID. The
Click to expand...
table containing the user's info uses the Look up wizard
to find the user's name (so the relationship is
tblUsers.Name -> tblPersonalInfo.[User ID]). Using this
set-up, I have created a form that cycles through all the
users and a subform to display their personal info for
editing or adding records to (i.e work performance based
on date).
I am unsure of the syntax for how I would go about
Click to expand...
finding a record for a given date (unbounded text box) -
since the date is on the subform, can I just do a find
using the date field or do I need to include the user ID?
I put the following routine on the AfterUpdate event of
the date control:
Private Sub Date_AfterUpdate()
Dim inputDate As Date
inputeDate = Me!Date

Me.RecordsetClone.FindFirst "Date = " & inputDate
If Me.RecordsetClone.NoMatch Then
Me.Undo
MsgBox "Record not found"
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
Me.RecordsetClone.Close
End Sub

Unfortunately, when I enter a date for which a record
Click to expand...
exists, I get the message "Record not found" - does
anyone know what I am doing wrong?
 
Thank you for the tip. However, when I put the pound signs in, I got a compile error: syntax error on that line. Am I not supposed to put in any spaces?
 
Oops! I just figured out the syntax problem... forgot to put the "&" in there. Still, I'm getting the message "Record not found" yet it should have found a record. Do I need to link this to the User ID field somehow? And if so, do I use the Logon or the User ID?
 
try this...

Private Sub Date_AfterUpdate()
Dim strDate As String
strDate = Me!Date
If DCount("[User ID]", "tblPersonalInfo", _
"[YourDateField]=#" & strDate & "# And " _
& "[User ID]='" & strUserID & "'") = 0 Then
MsgBox "Date not found"
Else
Me!BoundDateControlName.SetFocus '<---
DoCmd.GoToRecord , Me.Name, acFirst
DoCmd.FindRecord strDate
End If
End Sub

NOTE: If the control calling this procedure is on your
main form then swap out the line marked by the arrow (<---
) with this

Me!SubformControlName.Form!BoundDateControlName.SetFocus

Good luck!
-----Original Message-----
Oops! I just figured out the syntax problem... forgot to
Click to expand...
put the "&" in there. Still, I'm getting the
message "Record not found" yet it should have found a
record. Do I need to link this to the User ID field
somehow? And if so, do I use the Logon or the User ID?
 
Back
Top