Hi - I'm trying to use Mr. Dobson's example starting on page 389 but am 
having a problem that I hope someone can help me with.  I have an SQL 2000 
table named "area" with two columns area & region.  Both columns make up 
the primary key.

I have an adp. Access 2000 project with an unbound form named "frmArea".

The form has a General declaration of ...

Dim rst1 As ADODB.Recordset

The form has code that is launched on it's open event like this ...
.....  just like in the book ...

Private Sub Form_Open(Cancel As Integer)

Dim rst1 As ADODB.Recordset
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = CurrentProject.Connection
rst1.source = "AreaTest"
'Not necessary because of Shape provider for
'CurrentProject connection
'rst1.LockType = adLockOptimistic
'rst1.CursorType = adOpenKeyset
rst1.Open


Me.txtArea = rst1("Area")
Me.txtRegion = rst1("Region")


End Sub

So far so good because a record set is returned and I can see the first 
record.

The only other element on my form is a command button "next" to display 
the next record.  Here's my code on the click event ....

Private Sub cmdNext_Click()
'Set Me.RecordSource = "EXEC AreaTest"
rst1.MoveNext
If Not rst1.EOF Then
    Me.txtArea = rst1("Area")
    Me.txtRegion = rst1("Region")
   
Else
    MsgBox "Already at end of recordset."
    rst1.MovePrevious
End If
End Sub


When I click the command button I get the error message ....

Run-time error '91':
Object variable or With blockvariable not set

Any help would be greatly appreciated.  Thanks

Jim Corvino

That's an easy one Jim and you'll do a "forehead smack" when I tell you...
its because you have the recordset scoped inside form_open, so the
cmdNext_click  can't see it.  Move the declaration of the recordset to the
form level and it should be fine.

DC
-----Original Message-----
From: Jim Corvino [mailto:JCorvino@R...]
Sent: Tuesday, April 23, 2002 3:21 PM
To: Access
Subject: [access] unbound Access 2000 form & SQL Server 2000


Hi - I'm trying to use Mr. Dobson's example starting on page 389 but am 
having a problem that I hope someone can help me with.  I have an SQL 2000 
table named "area" with two columns area & region.  Both columns make up 
the primary key.

I have an adp. Access 2000 project with an unbound form named "frmArea".

The form has a General declaration of ...

Dim rst1 As ADODB.Recordset

The form has code that is launched on it's open event like this ...
.....  just like in the book ...

Private Sub Form_Open(Cancel As Integer)

Dim rst1 As ADODB.Recordset
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = CurrentProject.Connection
rst1.source = "AreaTest"
'Not necessary because of Shape provider for
'CurrentProject connection
'rst1.LockType = adLockOptimistic
'rst1.CursorType = adOpenKeyset
rst1.Open


Me.txtArea = rst1("Area")
Me.txtRegion = rst1("Region")


End Sub

So far so good because a record set is returned and I can see the first 
record.

The only other element on my form is a command button "next" to display 
the next record.  Here's my code on the click event ....

Private Sub cmdNext_Click()
'Set Me.RecordSource = "EXEC AreaTest"
rst1.MoveNext
If Not rst1.EOF Then
    Me.txtArea = rst1("Area")
    Me.txtRegion = rst1("Region")
   
Else
    MsgBox "Already at end of recordset."
    rst1.MovePrevious
End If
End Sub


When I click the command button I get the error message ....

Run-time error '91':
Object variable or With blockvariable not set

Any help would be greatly appreciated.  Thanks

Jim Corvino

DC,

Thanks for your quick reply and I know your pointing me in the right 
direction so excuse my ignorance.  I'm new to this enviroment and tried a 
couple of adjustments but now I have other errors.  so when you say put 
the recordset at the form level could you be so kind to show me in sample 
code?  Under what event?  Thanks for your patience.

Jim

just declare it outside of any procedures or events, at the top of the
form's code module.

---- top of file ----
Option Explicit
PRIVATE rs AS ADODB.Recordset

public sub form_load
...

---- the rest of the code ----


-----Original Message-----
From: Jim Corvino [mailto:JCorvino@R...]
Sent: Tuesday, April 23, 2002 5:38 PM
To: Access
Subject: [access] Re: unbound Access 2000 form & SQL Server 2000


DC,

Thanks for your quick reply and I know your pointing me in the right 
direction so excuse my ignorance.  I'm new to this enviroment and tried a 
couple of adjustments but now I have other errors.  so when you say put 
the recordset at the form level could you be so kind to show me in sample 
code?  Under what event?  Thanks for your patience.

Jim