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

Selasa, 06 November 2012

[MS_AccessPros] Re: No current record with .Move first

Just solved this by referencing the correct recordset instead of leaving it empty as in .EOF.

Connie

--- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@...> wrote:
>
> 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
>