Update Data via RECORDSET

  • Thread starter Thread starter Bobby Bosco
  • Start date Start date
B

Bobby Bosco

I have a table that the users need to update. It is a
perfect candidate for continuous forms BUT the users don't
want to scroll because they cannot see all the data that
way.

I don't know much (okay, almost nothing) about using
recordsets except what I could get out of HELP and that is
not all that helpful to me.

So I built a form with all the fields unbound. When the
form loads, I populate those fields via a recordset. That
works very nicely.

However, when I try to save the changed data stepping
through a recordset I get the following error.

Object Variable or With block variable not set.

Here is the code that I am using.

OnLoad...

Dim rst As Recordset
Dim db As Database

Set db = CurrentDb
Set rst = db.OpenRecordset("mt_tagsets", dbOpenDynaset)

Me!TagCap01 = rst!TagCaption
Me!TagTip01 = rst!TagControlTips
If rst!Used = -1 Then
Me!tog01 = -1
Me.tog01.Caption = "ON"
Me.tog01.ForeColor = 16384
Else
Me!tog01 = 0
Me.tog01.Caption = "OFF"
Me.tog01.ForeColor = 255
End If

rst.MoveNext
....

The EXIT code... (this is where I want to update the
table data from the unbound form data)

Dim rst As Recordset
Dim db As Database
Set rst = db.OpenRecordset("mt_tagsets", dbOpenDynaset)

If rst!TagID = 1 Then
rst!TagCaption = Me.TagCap01
rst!TagControlTips = Me.TagTip01
rst!Used = Me.tog01
End If

rst.MoveNext
....


tia

Bobby
 
Are you closing your recordset? Before you exit the
procedure, you should include the line

rst.close

I can't tell if you are attempting the following from your
code fragment, but note that if you want to use a
recordset twice within a single function or procedure
(with two different "SET" commands) you have to first set
the recordset to nothing:

rst.close
Set rst = Nothing

Then you can use the rst variable again.

Phil Freihofner
Oakland
 
Bobby Bosco said:
I have a table that the users need to update. It is a
perfect candidate for continuous forms BUT the users don't
want to scroll because they cannot see all the data that
way.

I don't know much (okay, almost nothing) about using
recordsets except what I could get out of HELP and that is
not all that helpful to me.

So I built a form with all the fields unbound. When the
form loads, I populate those fields via a recordset. That
works very nicely.

However, when I try to save the changed data stepping
through a recordset I get the following error.

Object Variable or With block variable not set.

Here is the code that I am using.

OnLoad...

Dim rst As Recordset
Dim db As Database

Set db = CurrentDb
Set rst = db.OpenRecordset("mt_tagsets", dbOpenDynaset)

Me!TagCap01 = rst!TagCaption
Me!TagTip01 = rst!TagControlTips
If rst!Used = -1 Then
Me!tog01 = -1
Me.tog01.Caption = "ON"
Me.tog01.ForeColor = 16384
Else
Me!tog01 = 0
Me.tog01.Caption = "OFF"
Me.tog01.ForeColor = 255
End If

rst.MoveNext
...

The EXIT code... (this is where I want to update the
table data from the unbound form data)

Dim rst As Recordset
Dim db As Database
Set rst = db.OpenRecordset("mt_tagsets", dbOpenDynaset)

If rst!TagID = 1 Then
rst!TagCaption = Me.TagCap01
rst!TagControlTips = Me.TagTip01
rst!Used = Me.tog01
End If

rst.MoveNext
...


tia

Bobby
Click to expand...

1. You need this line

Set db = CurrentDb

before
Set rst = db.OpenRecordset("mt_tagsets", dbOpenDynaset)
Click to expand...

2. Before you can update any of the fields in the recordset's current
record, you have to call its .Edit method. And then to get your changes
to be saved, you have to call the recordset's .Update method when you're
done. So it should look like this:

rst.Edit
If rst!TagID = 1 Then
rst!TagCaption = Me.TagCap01
rst!TagControlTips = Me.TagTip01
rst!Used = Me.tog01
End If
Click to expand...
rst.Update
 
Phil and Dirk,

Thank you both so very much! I am just starting into
the world of VBA and all. This really helps a bunch and I
thank you both (and all the others) for sharing your
knowlege with us Newbies!

I have to run right now but I'll give them a try
tonight and let you know.

Bobby
 
Back
Top