run time error 3021. no current record 1

[brendanmc]

Hi,

I've been trying to nut this out for days with no luck (doesn't help that I program very rarely)

I am receiving a runtime error 021. no current record. I can confirm that the record exists.

I am using a form, that when opened is set to default as a new record. There are four fields. The first two are establishment and MRN (medical Record Number), which are what the code is using to track a record. None of these are set to be a key as they need to be duplicated individually, but not as a pair.

Ideally the user enters the establishment, then enters an MRN. If the MRN and establishment already exist together, then the system should automatically bring up the other details so they can edit the record. If they don't exist as a pair, the user continues to add the details as if it was a new record.

I based my code on what scriverb used in thread702-833357. I have also tried a few other thread suggestions with no luck. Any help is greatly appreciated. Code is below.

NOTE the debug points to the line: Me.Bookmark = RecordsetClone.Bookmark
thanks
Brendan

Private Sub MRN_AfterUpdate()


Dim vMRN As String, vEstID As String
If Not IsNull(Me![MRN]) Then
If IsNull(DLookup("[MRN]", "[file_tracking]", "[MRN] = '" & Forms![File_tracking_frm]![MRN] & "' and [EstablishmentID] ='" & Forms![File_tracking_frm]![establishmentid] & "'")) Then
'add record code
Me![location_code].SetFocus
Else
vMRN = Me.[MRN]
vEstID = Me.[establishmentid]
DoCmd.SetWarnings False
' DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
Me.RecordsetClone.FindFirst "[MRN] = '" & vMRN & "' and [establishmentID] = '" & vEstID & "'"
MsgBox vMRN & " and " & vEstID
Me.Bookmark = RecordsetClone.Bookmark
Me![location_code].SetFocus
End If
Else
Me![MRN].SetFocus
End If

 

[RoyVidar]

No current record usually means exactly that - there is not current record - i e, the .findfirst didn't find anything. The reason for that, is probably something else. To avoid the error, I'd try testing wether it found anything, i e after the .findfirst try this (and also, qualify the RecordsetClone reference)

[tt]if not Me.RecordsetClone.nomatch then
Me.Bookmark = Me.RecordsetClone.Bookmark
end if[/tt]

Though, I'm often more comfortable with using/assigning to a recordset variable, as the wizard created version of this does, and is probably found in a lot of samples.

Roy-Vidar

 

[brendanmc]

A good idea. I will give it a go when I'm next at work and let you know how it all goes.
Thanks
Brendan

 

[brendanmc]

HI,
I've added the line into code (see below) and while running it skips straight over the if statement and into the remaining code and I recieve the no current record error. It still occurs at the same point: Me.Bookmark = RecordsetClone.Bookmark

I'm guessing it must be finding something since it is skipping your suggestion (unless I have my thinking backwards)

I appreciate the help on this. I have tried varying other parts of code as well as the form setup, with no luck.

thanks.

Private Sub MRN_AfterUpdate()

Dim vMRN As String, vEstID As String, vrst As String
vMRN = Me.[MRN]
vEstID = Me.[establishmentid]

If Not IsNull(Me![MRN]) Then

If IsNull(DLookup("[MRN]", "[file_tracking]", "[MRN] = '" & Forms![File_tracking_frm]![MRN] & "' and [EstablishmentID] ='" & Forms![File_tracking_frm]![establishmentid] & "'")) Then
MsgBox "you are in no record exists"
'add record code
Me![location_code].SetFocus
Else
vMRN = Me.[MRN]
vEstID = Me.[establishmentid]
Me.RecordsetClone.FindFirst "[MRN] = '" & vMRN & "' and [establishmentID] = '" & vEstID & "'"

' debug down

If Not Me.RecordsetClone.NoMatch Then
MsgBox vMRN & " looks like a non match and " & vEstID
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

' debug up

MsgBox vMRN & " and " & vEstID
Me.Bookmark = RecordsetClone.Bookmark
Me![location_code].SetFocus
End If
Else
Me![MRN].SetFocus
End If

 

[lupins46]

(unless I have my thinking backwards)

You have put your 'no match' msgbox in the bit of code which executes when it DOES find a match and vice versa.

 

[RoyVidar]

No it doesn't find anything at all, that's why it skips the If statement - the meaning of if not .nomatch is that it's only supposed to enter the "true" part of the statement if there's no non matches - double negative -> only enter if there is a match - and that's the only place where you should assign any bookmark to the form bookmark, else you'll be getting the error. And again, this suggestion will only take care of the error.
[tt]
Me.RecordsetClone.FindFirst "[MRN] = '" & _
vMRN & "' and [establishmentID] = '" & vEstID & "'"
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
MsgBox vMRN & " and " & vEstID
Me![location_code].SetFocus
else
MsgBox vMRN & " looks like a non match and " & vEstID
End If
' Remove all the "Debug up" thingies[/tt]

But, as said, why you're getting .NoMatch, is something else, probably to do with the .FindFirst not finding the criteria you're feeding it, so you probably need to work on those too.

Roy-Vidar

 

[brendanmc]

Thanks, I will continue tracking down the offending criteria. My thinking must have been backwards re the double negative. The positive side is that my VB skills are improving along the way.

 

[brendanmc]

you are right about the criteria. It seems to be falling apart on the recordset clone. So the code seems to be fine.

I added the following code to determine the source table's contents (it works OK):


Set db = CurrentDb
Set rs = db.OpenRecordset("file_tracking")
rs.MoveFirst
Do Until rs.EOF
rs.MoveNext
MsgBox rs![location_code] & " , " & rs! [last_contact_dt] & " , " & rs![MRN] & " , " & rs![establishmentid]
Loop

Since it is showing me the details, it does appear that the recorsetclone criteria is wrong, or my setup is wrong (and my understanding).

Some questions.
Do all the fields on the form have to be sourced from the same table? My setup has.
* The table has four fields in total. 2 of the 4 fields are based on rowsource query comboboxes.
* The two fields used for recorsetclone search use both of these comboboxes
* The entire form (form properties) has its row source set as the table which contains the information.

From what I can see the field types are set correctly on the form and table. If I populate all the fields on the form, they are all written to the table (which is the rowsource).

If you can point to some areas to investigate further, it would be much appreciated. I'm sure it is some simple rule that I have overlooked.

Thanks
Brendan

 

[RoyVidar]

Lot of places to search ...

Some ...

[ul][li]what if you substitute the recordset you're opening with the form recordsetclone in the loop? (btw - let the movenext be the last line before loop)[/li]
[li]do you have any filters on the form (i e form recordset(clone) not containing all the records of the table?[/li]
[li]does any of the form controls contain special characters?[/li]
[li]you say the fields are set up correct - they are text fields?[/li][/ul]

[tt] Me.RecordsetClone.FindFirst "[MRN] = """ & _
vMRN & """ and [establishmentID] = """ & vEstID & """"[/tt]


Roy-Vidar

 

[brendanmc]

Good news indeed, a solution has been found. Thanks for all your help on this, it has really helped.

As you mentioned, there are alot of places to search. So using that logic I thought I'd recreate the form from scratch, see if it would work and then compare with the one that did not.

In the end it was a simple checkbox under form properties. The form had its data entry checkbox set to 'Yes', changing this to 'no' allowed for the recordsetclone search.

It found it interesting that the VB code would not override this setting, but I guess it was only looking at the record being added.

Thanks again for all your help.

In case anybody is interested, the code below works OK for a simple check on the recordsetclone.

Dim vMRN As String, vEstID As String, db As DAO.Database, rsc As Recordset

Set rsc = Me.RecordsetClone
Set db = CurrentDb
vMRN = Me.[MRN]
vEstID = Me.[establishmentid]

rsc.FindFirst "[MRN] = '" & vMRN & "' and [establishmentID] = '" & vEstID & "'"
If rsc.NoMatch Then
MsgBox "not found"
Else
Me.Bookmark = rsc.Bookmark
End If

** Just make sure you don't have data entry set to yes ;-)