RecordsetClone and Bookmark Problem

[jaydy]

Hi can anyone help me with this? It doesn't return anything. The currentpatient is a global integer variable. The PatientID field is also a number. I don't understand why this doesn't show the currentpatient's record. Can anybody help, please?


Private Sub Form_Load()

Dim rs As DAO.Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[PatientID] =" & CurrentPatient & ""
If rs.NoMatch Then
MsgBox ("error&quot
Else
Me.Bookmark = rs.Bookmark
End If

End Sub

 

[Glohamar]

Try

Dim rs As Object

and see if that works.

Dave

 

[PaulBricker]

I don't know about the object, but this will work.

Private Sub Form_Load()

Me.RecordsetClone.FindFirst "[PatientID] =" & CurrentPatient & ""
If Me.RecordsetClone.NoMatch Then
MsgBox ("error&quot
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Sub


Paul

 

[jaydy]

i've tried both and it still doesn't work, it can't seem to find the record in the condition patientid = currentpatient

 

[RoyVidar]

Hi!

I'm not sure about this, but I've seen some threads indicating the possibility that the recordset might not be fully loaded when the form enters the on load event, thus not giving a record even if you know it exists.

I'm just wondering, if the above might be the case, if perhaps movelast/movefirst prior to .findfirst might force population of the recordset?

Returning to your initial code, amending according to PaulBricker's suggestion:

[tt] Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.movelast
rs.movefirst
rs.FindFirst "[PatientID] =" & CurrentPatient
If rs.NoMatch Then
MsgBox ("error&quot
Else
Me.Bookmark = rs.Bookmark
End If
set rs=nothing[/tt]

BTW - you are 100% sure the global variable contains a value when this is run? (check it with debug.print/msgbox prior to .findfirst) and sicne it's numeric, it doesn't need any "end qualifiers", should it be text:

[tt] rs.FindFirst "[PatientID] ='" & CurrentPatient & "'"[/tt]

Note the single quotes (')

If none of this works, would it be a possibility to open this form using a where condition? From the calling form:

[tt]docmd.openform "thisform",,,"[PatientID] ='" & CurrentPatient[/tt]

(I think you could then later (form load?) remove the filter, and perhaps try .findfirst, which I believe would work, because the specified record should then be within the forms recordset)

HTH Roy-Vidar

 

[PaulBricker]

Where are you setting the value for your Global Variable, CurrentPatient? Try using
Debug.Print CurrentPatient
before the FindFirst line. See if your variable is returning a value.

Paul