"Object variable or with block not set" (1 Viewer)

MayaMana · Nov 9, 2012

Hey, I recently switched over to using SQL for the backend of my Access databases. The codes I have use to work, all I have just modified them a little to be able to use the SSRS easier.
However since I made the changes I keep getting an error message stating:

Object variable or With block not set

My code is basically the following

Code:

Private Sub Save_Click()
On Error GoTo ErrorHandler
Dim db As Database
Dim sn As Recordset
    
strCount = 0
    

If Not IsNull(Me.dp1) Or Me.dp1 <> "" Then
'If you are adding a new record, then this code will work, if you finding an existing record you will need a where clause and an edit instead of addnew
    Set db = CurrentDb
    Set sn = db.OpenRecordset("SELECT * FROM dbo_FIDataEntry ORDER BY ID DESC", dbOpenDynaset, dbSeeChanges)
    
        sn.AddNew
        sn!DateTime = Now()
        sn![N/S] = "N"
        sn!datapoint1 = Me.dp1
        sn!datapoint2 = Me.dp2
        sn!Name = Me.name
        sn!Shift = Me.Shift
        
End If

'Updates and closes the record
       sn.Update
       sn.Close
   
       
'Clears the fields for the next entry
Me.dp1 = Null
Me.dp2 = Null
Me.Shift = Null
Me.operator = Null

Exit_ErrorHandler:
    Exit Sub

ErrorHandler:
    MsgBox Err.Description
    Resume Exit_ErrorHandler
    
End Sub

I get the error message but it is still saving the data to SQL, but not clearing the fields. I have not encountered this issue before and I am not having any luck finding out how to fix it. :banghead:

Rx_ · Nov 9, 2012

http://www.access-programmers.co.uk/forums/showthread.php?t=200178
Found this, would you check it out and please report back as to your solution?
Will let you go through the thread - hopefully it will help you out.

MayaMana · Nov 9, 2012

I added DAO. before the database and record set and changed everywhere I had sn to rst. I still get an error stating that

Object variable or With block is not set.

After reading through those sites that was the only difference I could find in terms of what they are talking about. I am still relatively new to all of this, so I may be misunderstanding what I am reading. That is the same thing I have come across while searching for an answer to my issue. I do not get the "Run Time error 91" nor does anything get highlighted on my code page.

Rx_ · Nov 9, 2012

So you did try Dim db As DAO.Database ?

Access may not let us step through the code for a record update.
Can you put a code break in front of:
Me.dp1 = Null
then use the debug menu to step through.
Trying to determine exactly where your error happens.

MayaMana · Nov 9, 2012

Yes I changed,
Dim db As Database to Dim db As DAO.Database

I am not sure how to add a break in the code. I forget how to force the debug to show something, when it doesn't automatically do it.

It seems that the vba code no longer likes my IF code

Code:

If Not IsNull(Me.dp1) Or Me.dp1 <> "" Then

If there is something entered into every textbox it works like it should but if not I get the error code.

boblarson · Nov 9, 2012

Not sure if this has anything to do with it, but this code:

sn!Name = Me.name

is a problem. Using NAME as an object name or field name is not good because almost everything in Access has a NAME property. So, when if you are going to use that (and I highly suggest you don't as it can cause other issues as well) you will need to use square brackets:

sn![Name] = Me.[name]

BigHappyDaddy · Nov 9, 2012

This might help your IF statement:

Code:

If Not (Nz(Me.dp1,"")="") Then

The code after the THEN should run as long as there is a value in Me.dp1.

Good Luck.

Rx_ · Nov 9, 2012

Thanks Bob,
The "Name" reserved of course (DOH!)
That is the "Second" time a reserved word has gotten past me today.

boblarson · Nov 9, 2012

Rx_ said:
Thanks Bob,
The "Name" reserved of course (DOH!)
That is the "Second" time a reserved word has gotten past me today.

Well, I just looked again and one had escaped me too:

sn!DateTime = Now()

should be

sn![DateTime] = Now()

Because DateTime is also a Reserved Word.

BigHappyDaddy · Nov 9, 2012

This strikes me as a potential error, if the IF statement is FALSE:

Code:

If Not IsNull(Me.dp1) Or Me.dp1 <> "" Then
'If you are adding a new record, then this code will work, if you finding an existing record you will need a where clause and an edit instead of addnew
    Set db = CurrentDb
    Set sn = db.OpenRecordset("SELECT * FROM dbo_FIDataEntry ORDER BY ID DESC", dbOpenDynaset, dbSeeChanges)
 
        sn.AddNew
        sn!DateTime = Now()
        sn![N/S] = "N"
        sn!datapoint1 = Me.dp1
        sn!datapoint2 = Me.dp2
        sn!Name = Me.name
        sn!Shift = Me.Shift
 
End If
 
'Updates and closes the record
[COLOR=red]    sn.Update[/COLOR]
[COLOR=red]    sn.Close[/COLOR]

Since the SET sn statement is inside the IF statement, would executing the sn.Update and sn.Close lines generate error(s).

Would we want these two lines inside the if statement?

Code:

If Not IsNull(Me.dp1) Or Me.dp1 <> "" Then
'If you are adding a new record, then this code will work, if you finding an existing record you will need a where clause and an edit instead of addnew
    Set db = CurrentDb
    Set sn = db.OpenRecordset("SELECT * FROM dbo_FIDataEntry ORDER BY ID DESC", dbOpenDynaset, dbSeeChanges)
 
        sn.AddNew
        sn!DateTime = Now()
        sn![N/S] = "N"
        sn!datapoint1 = Me.dp1
        sn!datapoint2 = Me.dp2
        sn!Name = Me.name
        sn!Shift = Me.Shift
        'Updates and closes the record
[COLOR=red]     sn.Update[/COLOR]
[COLOR=red]     sn.Close[/COLOR]
End If

MayaMana · Nov 9, 2012

It would only need the [] if there was a space or / or in the name. Also I made those a bit generic they aren't labeled the same in my actual form.

BigHappyDaddy, you are right that was my issue. I was just getting back on to say I'm an idiot lol. I went through and fixed it so that the "End IF" is after the update and close.

This is the issue with having to rush through things. I figured it was something small as such. Thanks for the help though, I don't think could have figured it out without typing out my issue.

boblarson · Nov 9, 2012

MayaMana said:
It would only need the [] if there was a space or / or in the name.

No, if they were truly named that, even without the spaces, they would need the square brackets. And why, oh, why do people insist on using generic names for things in their examples because it can waste time trying to troubleshoot. Just use the real names, for heaven's sake.

MayaMana · Nov 12, 2012

boblarson said:
No, if they were truly named that, even without the spaces, they would need the square brackets. And why, oh, why do people insist on using generic names for things in their examples because it can waste time trying to troubleshoot. Just use the real names, for heaven's sake.

Well I haven't had any issues with it without using them. So thank you for making me wish I never bothered putting anything up on here.

boblarson · Nov 12, 2012

MayaMana said:
Well I haven't had any issues with it without using them. So thank you for making me wish I never bothered putting anything up on here.

You are upset because we pointed out something important to know? That's like saying "I don't need to wear a seatbelt because I've never been in a crash before."

I will make sure that I never answer any question you may ever post again since the truth bothers you so much. But I can't guarantee others won't post the truth. And feel free to go elsewhere if you think you can find other places where people won't upset you by posting the truth.

I'm done.

"Object variable or with block not set" (1 Viewer)

MayaMana

Registered User.

Rx_

Nothing In Moderation

MayaMana

Registered User.

Rx_

Nothing In Moderation

MayaMana

Registered User.

boblarson

Smeghead

BigHappyDaddy

Coding Monkey Wanna-Be

Rx_

Nothing In Moderation

boblarson

Smeghead

BigHappyDaddy

Coding Monkey Wanna-Be

MayaMana

Registered User.

boblarson

Smeghead

MayaMana

Registered User.

boblarson

Smeghead

Similar threads

Users who are viewing this thread