ADO Query w/ Form Parameter Error

[Leo]

I have a database to track project results built on two tables: One linked
(and read-only), the second read-write. Both tables include a field called
"SessionID," which is foreign in the second table. This field is linked via
relationships: Display all fields in linked table, and only those in the
read/write table where Session ID is the same.

There is a data entry form based on the linked table. I am trying to use ADO
(which tends to give me slight fits) by using a .open command based on the
following SQL statement: "Select * from tblAHD where SessionID = '" &
strSession & "'"

I have previously designated that strSession = Me.SessionID. However, when I
open the form (I have tried on on both the Open and Current events), I get
the following error: "3021- Either BOF or EOF is True, or the current record
has been deleted"

I have included the entire code below. Hopefully someone has some ideas, and
more experience with ADO than I do.

Leo

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim strSession As String
strSession = Me.SessionID
With rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockPessimistic
.Open "Select * from tblAHD where SessionID = '" &
strSession & "'"
End With
If Not rst.BOF And Not rst.EOF Then
Me.txtAHDSession = rst!SessionID
Me.txtDateClose = rst!DateClosed
Me.cmbAHDEmployees = rst!Processor
Me.cmbDispute1Results = rst!Dispute1Result
Me.cmbDispute2Results = rst!Dispute2Result
Me.cmbDispute3Results = rst!Dispute3Result
Me.txtFinalPremium = rst!FinalPremium
Me.txtPolicyNumber = rst!PolicyNumber
Me.txtStart = rst!TimeStart
Me.txtEnd = rst!TimeEnd
Me.txtTotalTime = rst!TotalTime
ElseIf rst.BOF Then
rst.MoveNext
ElseIf rst.EOF Then
rst.MovePrevious
End If
Exit Sub

 

[J_Goddard via AccessMonster.com]

Hi -

After you open the recordset, BOF is true if no records were retrieved (that
is a common way to check for an empty recordset). The first if statement is
not true because rst.BOF *is* true, and then the code is trying to execute
rst.movenext on an empty recordset, which causes the error. Your code should
be:

' Open recordset
if not rst.BOF then
'
' Populate form fields
'
else
'
' do something else - no data were retrieved
'
endif.

HTH

John

 

[Leo]

Worked like a charm. Thanks.

Hi -

After you open the recordset, BOF is true if no records were retrieved (that
is a common way to check for an empty recordset). The first if statement is
not true because rst.BOF *is* true, and then the code is trying to execute
rst.movenext on an empty recordset, which causes the error. Your code should
be:

' Open recordset
if not rst.BOF then
'
' Populate form fields
'
else
'
' do something else - no data were retrieved
'
endif.

HTH

John