ACCESS FEED: [MS_AccessPros] No current record with .Move first

Selasa, 06 November 2012

[MS_AccessPros] No current record with .Move first

Good morning/afternoon!

I am getting the error "Run time error 3021 No current record" on the .MoveFirst in this portion of the code:
If Not .EOF Then
With rsLkC
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With

I created the code (see below) in order to alert the user if there are more than one potential LookingEvents for a contact in the combo box list. The code determines if any of the contacts in a LookingEvent are already in a current LookingEvent.

FYI: LookingContacts table links buyers/contacts together. There is also a LookingEvent table with the basic info including End Date. So for example one person could possibly be in several current (no enddate) events if they were looking for a personal home, and also looking for a business property.

Thanks in advance!
Connie

'Alert if another looking event with that Contact
Set db = CurrentDb
'Get list of contacts for this LookingEvent then loop through each checking for another unended looking event for each
Set rsLE = db.OpenRecordset("SELECT LookingContacts.* FROM LookingContacts INNER JOIN LookingEvent " _
& "ON LookingEvent.LookingID = LookingContacts.LookingID WHERE LookingContacts.LookingID = " & Me.LookingID)
With rsLE
If Not .EOF Then
.MoveFirst
Do While Not .EOF
'See if any unended Looking Events for the contact in rsLE
Set rsLkC = db.OpenRecordset("SELECT LookingContacts.ContactID FROM LookingContacts " _
& "INNER JOIN LookingEvent On LookingContacts.LookingID = LookingEvent.LookingID " _
& "WHERE ContactID = " & rsLE!ContactID & " And LookingContacts.LookingID <> " & Me.LookingID & " And EndDate Is Null")
If Not .EOF Then
With rsLkC
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With
'If another unended looking event let user know to
If lngCount > 0 Then
Msgbox "There is more than one unended Looking Event with this contact. Click the arrow to select a different one." _
& vbNewLine & "" _
& vbNewLine & "If not in list, this person is not the primary person. Open the Looking Event form and search."
Exit Sub
End If
End If
.MoveNext
rsLkC.Close
Loop
End If
.Close
End With