Finding records in a recordset 1

[eussias]

I have a command button that, when pressed searches through a table (ClientInfo) and checks if there is a record for a specific patient. Currently I have it working so that you can search for a specific ClientNumber (unique for each patient) and that is working fine. Unfortunately not everyone has a ClientNumber and I'm trying to also incorporate a DOB ( Birth Date ) search. It seems to be finding the record fine, but won't then navigate to it. Can anyone help? Below is the code for the Sub...

(txtClientSrch is where the DOB is entered that you are searching for...)

Dim rst As Recordset, myBookmark As String
Set rst = CurrentDb.OpenRecordset("SELECT * FROM ClientInfo&quot
On Error Resume Next
rst.MoveFirst
txtClientSrch.SetFocus
MyValue = txtClientSrch.Text
Do While Not rst.EOF
If rst.Fields("DOB&quot = MyValue Then
myBookmark = rst.Bookmark
Exit Do
End If
rst.MoveNext
Loop
If rst.Fields(&quot;DOB&quot <> MyValue Then
MsgBox &quot;No record for that patient&quot;, vbInformation, &quot;LMTC Database&quot;
txtClientSrch.Text = &quot; &quot;
txtClientSrch.SetFocus
Exit Sub
End If
Me.RecordsetClone.FindFirst &quot;[DOB] = &quot; & MyValue
Me.Bookmark = Me.RecordsetClone.Bookmark
End

 

[mymou]

Hi eussias

Why not use filters to do this (or even a find)?

Stew

 

[930driver]

You can dump this whole part:
Do While Not rst.EOF
If rst.Fields(&quot;DOB&quot = MyValue Then
myBookmark = rst.Bookmark
Exit Do
End If
rst.MoveNext
Loop
If rst.Fields(&quot;DOB&quot <> MyValue Then
MsgBox &quot;No record for that patient&quot;, vbInformation, &quot;LMTC Database&quot;
txtClientSrch.Text = &quot; &quot;
txtClientSrch.SetFocus
Exit Sub
End If


Instead try:

Me.RecordsetClone.FindFirst &quot;[DOB] = &quot; & MyValue
If Me.RecordsetClone.NoMatch Then
Msgbox &quot;Nothing found&quot;
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

I suspect this won't work as it is, because I don't see why your code won't work (even though I think It's cumbersome). I would suggest you look at the format of your [DOB] field (not the way it is presented on the form, but the way it is stored in the table) and the format of MyValue. They must be the same. You could explicitly convert them to Date values (Using CDate() ) to make sure they have the same format.

&quot;The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!&quot;

 

[Skeptik]

Hi!

what 930driver said is correct! also, if th [DBO] field is a date you will have to set the field txtClientSrch as a date too, and set the variable MyValue as a date also! if you don't set the variable and the fields for the same type of data this won't work! if they are all defined as text field then try this:

Me.RecordsetClone.FindFirst &quot;[DOB] = '&quot; & MyValue

One more thing!! i wouldn't search for a record by a Day Of Birth search, because there will exist lots of records that will have the same DOB!! that may even be the reason why your code isn't working, allthough i doubt it! use a field that doesn't repeat itself!

Hope it helps!!
Skep

 

[eussias]

Dim rst As Recordset, myBookmark As String
Set rst = CurrentDb.OpenRecordset(&quot;SELECT * FROM ClientInfo&quot
On Error Resume Next
rst.MoveFirst
Dim MyValue As Date
MyValue = txtDate
Do While Not rst.EOF
If rst.Fields(&quot;DOB&quot = MyValue Then
myBookmark = rst.Bookmark
Exit Do
End If
rst.MoveNext
Loop
If rst.Fields(&quot;DOB&quot <> MyValue Then
MsgBox &quot;No record for that patient&quot;, vbInformation, &quot;LMTC Database&quot;
txtClientSrch.Text = &quot; &quot;
txtClientSrch.SetFocus
Exit Sub
End If
Me.RecordsetClone.FindFirst &quot;[DOB] = &quot; & MyValue
Me.Bookmark = Me.RecordsetClone.Bookmark

This code seems to be working (ie it is finding the record with the DOB, but it isn't moving to the record)
I know there is going to be more than one patient with the same DOB. My primary key for the table (ClientNum) has been used to search for specific records, and works fine, but unfortunately, as yet, not all patients have been assigned a ClientNum (new patients are started weekly) . Is it possible that because there is more than one record for DOB that this is why it isn't navigating to the record?? Can anyone help??

 

[Skeptik]

Hi again!! judging by the code you have, the fact of existing more than one DOB shouldn't be a problem, because it would stop at the first recor found with the same DOB! did you check the fields data tpye? by the way... what makes you think that the record is found?! try something... remove the second part of the code!

Dim rst As Recordset, myBookmark As String
Set rst = CurrentDb.OpenRecordset(&quot;SELECT * FROM ClientInfo&quot
On Error Resume Next
rst.MoveFirst
Dim MyValue As Date
MyValue = txtDate
Do While Not rst.EOF
If rst.Fields(&quot;DOB&quot = MyValue Then
myBookmark = rst.Bookmark
Exit Do
End If
rst.MoveNext
Loop

Me.RecordsetClone.FindFirst &quot;[DOB] = &quot; & MyValue
Me.Bookmark = Me.RecordsetClone.Bookmark

leave the code like this and then get back to me with the result! but before you might wanna try this:

Dim rst As Recordset, myBookmark As String
Set rst = CurrentDb.OpenRecordset(&quot;ClientInfo&quot;,dbopendynaset)
On Error Resume Next
rst.MoveFirst
Dim MyValue As Date
MyValue = txtDate
Do While Not rst.EOF
If rst.Fields(&quot;DOB&quot = MyValue Then
myBookmark = rst.Bookmark
Exit Do
End If
rst.MoveNext
Loop
If rst.Fields(&quot;DOB&quot <> MyValue Then
MsgBox &quot;No record for that patient&quot;, vbInformation, &quot;LMTC Database&quot;
txtClientSrch.Text = &quot; &quot;
txtClientSrch.SetFocus
Exit Sub
End If
Me.RecordsetClone.FindFirst &quot;[DOB] = &quot; & MyValue
Me.Bookmark = Me.RecordsetClone.Bookmark

the problem may be, the way you are trying to open the recordset!! try it and then get back to me!!

Hope it helps.
Skep

 

[eussias]

Skep,

Tried both those suggestions. Neither one wanted to navigate to the record. I entered in a known date and it goes through the first Do While Loop no problem, finds the record, then drops out of that loop. It just doesn't want to navigate to the record. Could it have anything to do with the indexing of the table?
The fields are all of type date... The search text box(txtDate) and DOB field are both ShortDate.
What do you think??

 

[Skeptik]

Hi again!! try this:

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset(&quot;ClientInfo&quot;,dbopendynaset)

Dim MyValue As Date
MyValue = txtDate

On Error Resume Next

rst.findfirst &quot;[DOB] = &quot; & MyValue
If rst.nomatch Then
MsgBox &quot;No record for that patient&quot;, vbInformation, &quot;LMTC Database&quot;
txtClientSrch.Text = &quot; &quot;
txtClientSrch.SetFocus
Exit Sub
End If

humm... i just saw something! try to define mybookmark as date instead of defining it as string!! try this first and only then use the code i just gave you!!

I really hope this works because i'm running out on ideas!!
although, I do apreciate a good chalenge!!

Skep

 

[eussias]

Skep,

Hi again!! I actually tried declaring MyBookmark as Date before, and unfortunately had no luck there either. The code you just gave me doesn't seem to want to work either unfortunately. Was just dropping out after the first If statement. I am really at a loss here as to why this isn't working. Below find the code I use for the ClientNum search which can also be performed, and works perfectly.
There may be something I'm looking right at and overseeing, and would appreciate any input you may have.

Dim rst As Recordset, myBookmark As String
Set rst = CurrentDb.OpenRecordset(&quot;SELECT * FROM ClientInfo&quot
On Error Resume Next
rst.MoveFirst
MyValue = Val(txtClientSrch)
Do While Not rst.EOF
If rst.Fields(&quot;Client Number&quot = MyValue Then
myBookmark = rst.Bookmark
Exit Do
End If
rst.MoveNext
Loop
If rst.Fields(&quot;Client Number&quot <> MyValue Then
MsgBox &quot;No record for that patient&quot;, vbInformation, &quot;LMTC Database&quot;
txtClientSrch.SetFocus
Exit Sub
End If
Me.RecordsetClone.FindFirst &quot;[Client Number] = &quot; & MyValue
Me.Bookmark = Me.RecordsetClone.Bookmark
txtClientSrch.SetFocus

 

[Skeptik]

Hi again!!
i tried to simulate the conditions and i couldn't make a successful record navigation! However, while i try to find a solution you could use a query to find the records! build up a query with all the fields of the table! then put a listbox on the form based on the query you created! if you use the wizard you can select the record on the listbox and the aplication will automaticaly clone the recordset! one more thing... in the query, where it says criteria (or something like that) put the reference to the field of the dob search field in the column of the DOB data field!! you will have to create the DOB search field, on the form! (don't forget to define the DOB search field data type to date)!!

I know it isn't very clear but i'm with a terrible headaque!
try the listbox alternative while i strugle to find a solution! i must say that i'm enjoyng this chalenge!!

Skep

 

[eussias]

I'm enjoying this challenge too, but at the same time am becoming increasingly bewildered as to why this isn't working?? Does anyone out there know anything about searching through records on a Date Field??

 

[930driver]

Might have to put the # signs around myvalue for date like you have to use &quot; around a string value.

Me.RecordsetClone.FindFirst &quot;[Client Number] = #&quot; & MyValue & &quot;#&quot;

Try that.
&quot;The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!&quot;

 

[eussias]

Thanks for all your help guys... finally got it working. I had to include # in the search. Seems to be working fine now. Skep, I also got rid of some redundant code....

Dim rst As Recordset
Dim myBookmark As Date
txtDate.SetFocus
Set rst = CurrentDb.OpenRecordset(&quot;SELECT * FROM ClientInfo WHERE DOB = #&quot; & txtDate.Text & &quot;#&quot
On Error Resume Next
rst.MoveFirst
Dim MyValue As Date
MyValue = txtDate
If rst.Fields(&quot;DOB&quot <> MyValue Then
MsgBox &quot;No record for that patient&quot;, vbInformation, &quot;LMTC Database&quot;
txtDate.Text = &quot; &quot;
txtDate.SetFocus
Exit Sub
End If
Me.RecordsetClone.FindFirst &quot;[DOB] = #&quot; & MyValue & &quot;#&quot;
Me.Bookmark = Me.RecordsetClone.Bookmark

Works perfectly now. Thanks again for all your help

 

[mymou]

Hi all

I know you guys have had fun with this but I can't see the advantage of this over using a filter.

Transactions.Form.Filter = &quot;DOB = #&quot; & txtDate.Text & &quot;#&quot;
Transactions.Form.FilterOn = True

Just checking to see if my apps are missing some cool functionality!

Stew

 

[Robbo1974]

Mymou, very neat and tidy solution - have just used it myself! Thanks Robbo ;-)