Object variable or With block variable not set. Noobie here :(

N

nemodied

New member
Local time
Today, 08:58
Joined
Nov 8, 2008
Messages
4
Update/Insert Query with AutoNumber.. Noobie here :(

Hi guys, I'm trying to do an Insert/Update VBA code into my project, but everytime I try to run it it gives this error:
Object variable or With block variable not set. It happens on both the Insert and Update.

The CustomerID is an auto-number.

Thanks for any help!!! :confused::confused::confused::)


Code: 
Private Sub Save_Click()
  Dim db As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim cno As Integer
  Dim X As Integer
  Forms("Reservation")!Rents.Enabled = True
  If (Forms("Reservation")!FirstNameField = Null) Or _
        (Len(Trim(Forms("Reservation")!FirstNameField)) = 0) Or _
        (Len(Trim(Forms("Reservation")!LastNameField)) = 0) Then
    MsgBox "Cannot Save - Customer has no name!"
  Else
    If Forms("Reservation")!CustIDField = "" Then
    db.Execute "Insert Into Customer (FirstName,LastName,TelNo,Email,Street,City,State,Zip,PicID) " & _
    "Values ('" & Forms("Reservation")!FirstNameField & "','" & _
    Forms("Reservation")!LastNameField & "','" & _
    Forms("Reservation")!TelephoneField & "','" & _
    Forms("Reservation")!EmailField & "','" & _
    Forms("Reservation")!StreetField & "','" & _
    Forms("Reservation")!CityField & "','" & _
    Forms("Reservation")!StateField & "','" & _
    Forms("Reservation")!ZipField & "','" & _
    Forms("Reservation")!PicIDField & "')"
  Else
  db.Execute "Update Customer " & _
      "Set FirstName='" & Forms("Reservation")!FirstNameField & "'," & _
      "LastName='" & Forms("Reservation")!LastNameField & "'," & _
      "TelNo='" & Forms("Reservation")!TelephoneField & "'," & _
      "Email='" & Forms("Reservation")!EmailField & "'," & _
      "Street='" & Forms("Reservation")!StreetField & "'," & _
      "City='" & Forms("Reservation")!CityField & "'," & _
      "State='" & Forms("Reservation")!StateField & "'," & _
      "Zip='" & Forms("Reservation")!ZipField & "'," & _
      "PicID='" & Forms("Reservation")!PicIDField & "' " & _
      "Where CustomerID=" & Forms("Reservation")!CustIDField
    cno = Forms("Reservation")!CustIDField
  End If
 
Last edited:
You are missing a End If statement:

Code: 
Private Sub Save_Click()
  Dim db As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim cno As Integer
  Dim X As Integer
  Forms("Reservation")!Rents.Enabled = True
  If (Forms("Reservation")!FirstNameField = Null) Or _
        (Len(Trim(Forms("Reservation")!FirstNameField)) = 0) Or _
        (Len(Trim(Forms("Reservation")!LastNameField)) = 0) Then
     MsgBox "Cannot Save - Customer has no name!"
  Else
     If Forms("Reservation")!CustIDField = "" Then
        db.Execute "Insert Into Customer (FirstName,LastName,TelNo,Email,Street,City,State,Zip,PicID) " & _
                   "Values ('" & Forms("Reservation")!FirstNameField & "','" & _
                   Forms("Reservation")!LastNameField & "','" & _
                   Forms("Reservation")!TelephoneField & "','" & _
                   Forms("Reservation")!EmailField & "','" & _
                   Forms("Reservation")!StreetField & "','" & _
                   Forms("Reservation")!CityField & "','" & _
                   Forms("Reservation")!StateField & "','" & _
                   Forms("Reservation")!ZipField & "','" & _
                   Forms("Reservation")!PicIDField & "')"
     Else
        db.Execute "Update Customer " & _
                   "Set FirstName='" & Forms("Reservation")!FirstNameField & "'," & _
                   "LastName='" & Forms("Reservation")!LastNameField & "'," & _
                   "TelNo='" & Forms("Reservation")!TelephoneField & "'," & _
                   "Email='" & Forms("Reservation")!EmailField & "'," & _
                   "Street='" & Forms("Reservation")!StreetField & "'," & _
                   "City='" & Forms("Reservation")!CityField & "'," & _
                   "State='" & Forms("Reservation")!StateField & "'," & _
                   "Zip='" & Forms("Reservation")!ZipField & "'," & _
                   "PicID='" & Forms("Reservation")!PicIDField & "' " & _
                   "Where CustomerID=" & Forms("Reservation")!CustIDField
        cno = Forms("Reservation")!CustIDField
     End If
   [B][COLOR="Blue"]End If[/COLOR][/B]
End Sub

.
 
Re: Update/Insert Query with AutoNumber.. Noobie here :(

nemodied said:
Hi guys, I'm trying to do an Insert/Update VBA code into my project, but everytime I try to run it it gives this error:
Object variable or With block variable not set. It happens on both the Insert and Update.
Click to expand...
You haven't set your connection (db).

Just add the line (assuming it's the current database):
Set db = CurrentProject.Connection

Also, "db" suggests database as in DAO, whereas with ADO you are working with connections. So a better naming convention might be adoCon instead of db.

Also, you have declared rs as a recordset but you make no use of it i.e. it is redundant. Are you wanting to do this with recordset or with SQL as you currently have it?

Also, what is the purpose of cno? It doesn't do anything.

Chris
 
Re: Update/Insert Query with AutoNumber.. Noobie here :(

EDIT: working on something.
 
Thanks for all the help! It just seemed that I forgot to set the connection haha!

Here is the working code I used to Update/Insert Customer Information!

Code: 
Private Sub Save_Click()
  Dim db As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim X As Integer
  Forms("Reservation")!Rents.Enabled = True
  Forms("Reservation")!Rents!BundleField1.Enabled = True
  Forms("Reservation")!Rents!QtyField1.Enabled = True
  If (Forms("Reservation")!FirstNameField = Null) Or _
        (Len(Trim(Forms("Reservation")!FirstNameField)) = 0) Or _
        (Len(Trim(Forms("Reservation")!LastNameField)) = 0) Then
    MsgBox "Cannot Save - Customer has no name!"
  End If
  If (Forms("Reservation")!TelephoneField = Null) Or _
        (Len(Trim(Forms("Reservation")!TelephoneField)) = 0) Then
    MsgBox "Cannot Save - Customer has no Telephone!"
  End If
  If (Forms("Reservation")!StreetField = Null) Or _
        (Len(Trim(Forms("Reservation")!StreetField)) = 0) Or _
        (Len(Trim(Forms("Reservation")!CityField)) = 0) Or _
        (Len(Trim(Forms("Reservation")!ZipField)) = 0) Or _
        (Len(Trim(Forms("Reservation")!StateField)) = 0) Then
    MsgBox "Cannot Save - Customer requires a complete address!"
  End If
  If (Forms("Reservation")!EmailField = Null) Or _
        (Len(Trim(Forms("Reservation")!EmailField)) = 0) Then
    MsgBox "Cannot Save - Customer has no email address!"
  End If
  If (Forms("Reservation")!PicIDField = Null) Or _
        (Len(Trim(Forms("Reservation")!PicIDField)) = 0) Then
    MsgBox "Cannot Save - Customer requires a Picture ID directory!"
  Else
  Set db = CurrentProject.Connection
  Set rs = New ADODB.Recordset
    If Forms("Reservation")!CustIDField = "" Then
    rs.Open "Select Max(CustomerID) as CustomerID From Customer", db
    Forms("Reservation")!CustIDField = rs!CustomerID + 1
    rs.Close
    db.Execute "Insert Into Customer (CustomerID,FirstName,LastName,TelNo,Email,Street,City,State,Zip,PicID) " & _
    "Values ('" & Forms("Reservation")!CustIDField & "','" & _
        Forms("Reservation")!FirstNameField & "','" & _
        Forms("Reservation")!LastNameField & "','" & _
        Forms("Reservation")!TelephoneField & "','" & _
        Forms("Reservation")!EmailField & "','" & _
        Forms("Reservation")!StreetField & "','" & _
        Forms("Reservation")!CityField & "','" & _
        Forms("Reservation")!StateField & "','" & _
        Forms("Reservation")!ZipField & "','" & _
        Forms("Reservation")!PicIDField & "')"
  Else
  db.Execute "Update Customer " & _
      "Set FirstName='" & Forms("Reservation")!FirstNameField & "'," & _
      "LastName='" & Forms("Reservation")!LastNameField & "'," & _
      "TelNo='" & Forms("Reservation")!TelephoneField & "'," & _
      "Email='" & Forms("Reservation")!EmailField & "'," & _
      "Street='" & Forms("Reservation")!StreetField & "'," & _
      "City='" & Forms("Reservation")!CityField & "'," & _
      "State='" & Forms("Reservation")!StateField & "'," & _
      "Zip='" & Forms("Reservation")!ZipField & "'," & _
      "PicID='" & Forms("Reservation")!PicIDField & "' " & _
      "Where CustomerID=" & Forms("Reservation")!CustIDField
  End If
Set rs = Nothing
Set db = Nothing
  Set db = CurrentProject.Connection
  Set rs = New ADODB.Recordset
    rs.Open "Select * from Screens where FormName='Reservation' " & _
      "order by FormVariable", db
    If Not rs.EOF Then
      rs.MoveNext
    If rs!FormValue = Forms("Reservation")!CustIDField.Caption Then
        db.Execute "delete from screens where formname='Reservation'"
      End If
    End If
rs.Close
db.Close
    Forms("Reservation")!NewRadio.Value = 1
    Forms("Reservation")!NewRadio.Enabled = True
    Forms("Reservation")!CustIDField.Enabled = False
    Forms("Reservation")!SearchButt.Enabled = False
    MsgBox "Customer Information Saved!"
    Forms("Reservation")!FirstNameField.SetFocus
  End If
End Sub
 
You must log in or register to reply here.

Similar threads

M
Solved Object variable or with block variable not set
Replies
7
Views
1,169
theDBguy
theDBguy
M
Solved Object variable or With block variable not set Error
Replies
10
Views
4,196
Momma
M
A
Run Time error 91 Object Variable or With Block variable not set
Replies
14
Views
2,074
MajP
MajP
A
Solved Run-time error '91' (Object variable or With block variable not set)
Replies
6
Views
3,813
MajP
MajP
SurreyNick
Solved Intermittent "Object variable or With block variable not set" error with VBA routine
Replies
18
Views
2,949
SurreyNick
SurreyNick

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom