VBA ACCESS error 91 object variable or with block variable not set

Question

In a form, when pressing a button called "asignar", I want to insert values to an existing table called "movimientos"

Private Sub ASIGNAR_Click()
  Dim db As Database
  Dim rs As DAO.Recordset

  Set rs = db.OpenRecordset("MOVIMIENTOS")
  rs.AddNew
  rs("ESTATUSDOC").Value = "Blah"
  rs("FOLIOFED").Value = "Blah"
  rs("NOMBREDOC").Value = "Blah"
  rs("PREL").Value = "Blah"
  rs("CURP").Value = "Blah"
  rs.Update
End Sub

When I press run it keep showing error:

Error 91 object variable or with block variable not set

Newd · Accepted Answer · 2017-06-30 15:41:24Z

3

I think your syntax is a little off, your code should look like this:

Private Sub ASIGNAR_Click()

   Dim db As DAO.Database
   Dim rs As DAO.Recordset

   Set db = CurrentDb
   Set rs = db.OpenRecordset("MOVIMIENTOS", dbOpenDynaset, dbAppendOnly)

   rs.AddNew
   rs("ESTATUSDOC").Value = "Blah"
   rs("FOLIOFED").Value = "Blah"
   rs("NOMBREDOC").Value = "Blah"
   rs("PREL").Value = "Blah"
   rs("CURP").Value = "Blah"
   rs.Update

   rs.Close 
   Set rs = Nothing 
   Set db = Nothing

End Sub

The issue is primarily the way that you were declaring and setting your db variable. I have also adjusted the OpenRecordset to match what you are doing.

edited Jun 30, 2017 at 15:41

answered Jul 7, 2015 at 16:23

Newd

2,1832 gold badges18 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matt Hall Over a year ago

All good, though don't forget to close opened recordsets rs.Close and clean your memory with Set rs = Nothing and Set db = Nothing

Newd Over a year ago

@MattHall Thanks for spotting that.

Rdster · Accepted Answer · 2015-07-07 16:56:16Z

0

Changing

Set rs = db.OpenRecordset("MOVIMIENTOS")

to

Set rs = CurrentDB.OpenRecordset("MOVIMIENTOS")

should fix it as well. You can then lose the Dim db As Database

answered Jul 7, 2015 at 16:56

Rdster

1,8722 gold badges19 silver badges30 bronze badges

Collectives™ on Stack Overflow

VBA ACCESS error 91 object variable or with block variable not set

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related