object variable or with block variable not set

[aoude]

starting to create a database and new at this. I keep getting the message 'object variable or with block variable not set'. I don't know why I get this error or how to check which line or area in code is wrong. Please help!

Option Compare Database
Option Explicit

Private Sub Save_IP_Type_Button_Click()
On Error GoTo Error_Sub

Dim inputTypeID As Variant
Dim inputType As Variant
inputTypeID = Me!IPTypeID
inputType = Me!IPType

If IsNull(inputTypeID) Then
MsgBox "You must enter an IP acronym!", vbExclamation, "No Acronym Entered"
ElseIf IsNull(inputType) Then
MsgBox "You must enter an IP type!", vbExclamation, "No Type Entered"
Else
Dim table As Recordset
Set table = globalDatabase.OpenRecordset("SELECT * FROM IPTypeLookup WHERE IPTypeID='" & inputTypeID & "';", dbOpenForwardOnly)
If Not table.BOF And table.EOF Then ' If the acronym entered is already in use
table.Close
MsgBox "IP acronym is already in use!", vbExclamation, "Duplicate IP Acronym"
Exit Sub
End If
table.Close
Set table = globalDatabase.OpenRecordset("SELECT * FROM IPTypeLookup WHERE Type='" & inputType & "';", dbOpenForwardOnly)
If table.BOF And table.EOF Then ' If the IP Type entered does not exist
table.Close
Set table = globalDatabase.OpenRecordset("IPTypeLookup", dbOpenTable)
table.AddNew
table!IPTypeID = inputTypeID
table!Type = inputType
table.Update
table.Close
DoCmd.Close acForm, Me.Name
Else ' If the IP Type entered does exist
table.Close
MsgBox "IP type already exists!", vbExclamation, "Duplicate IP Type"
End If
End If

Exit_Sub:
Exit Sub

Error_Sub:
MsgBox err.Description
Resume Exit_Sub
End Sub

[f1 fan]

You really posted this in the wrong forum, but i am sure someone will move it for you.

DoCmd.Close acForm, Me.Name

My vb is rusty but i dont see acForm declared anywhere. What is it? Seems like its your problem.

What development tool are you using? Visual Studio? What version? Let me know and i can tell you how to debug. The second most important thing a developer HAS to know, is how to debug correctly. It saves hours of frustration and time, as no one can write a program bug free first time (unless it is a mickey mouse one that has no real value).

[aoude]

Thank you for getting back to me...I am creating a database using ACCESS 97.

[f1 fan]

oh wow. now you are pushing my memory limits :)
I still think the problem is the acForm. It isnt declared anywhere.

I think you can put a break point in access 97 on the line of code for debugging?
either that or put a message box on the line before to show the value of acForm or one of its properties. I think it is null

[aoude]

WOW you're fast...I will try to do what you have advised me...I will let you know what happens! Thanks!

[aoude]

I didn't what you advised but I keep getting the same message :(

[Comatose]

does it highlight or give you a line number?

[aoude]

it highlights

[Comatose]

mkay... and what line does it highlight?

[aoude]

it only highlights when I change something in the code...either put a line break etc...but if I just run the code I just get a message box 'object variable or with block variable not set'...I don't know how to check where the bug is exactly...I know this sounds really stupid...sorry!

[Comatose]

A Good Debugging Technique, is to add msgbox's throughout the code. So, add one right at very beginning that says "start" and then just after an if block, or something like that. That will give you a reference point, so that you can say "ok, well I got to this point, and then it errors out", and that will help to give you a better idea where the error is at. object variable or with block variable not set is a pretty standard error message meaning that an object is missing a reference... so this should give you a pretty good idea as to what is going on (once you use the msgbox's to figure out where in the code you are).

[aoude]

I will do what you've suggested...thank you for giving me some your time :)

[Comatose]

Let me know how it turns out...

[aoude]

You've been a great help...I used the MsgBox as you said and I found out that table = nothing, globalDatabase = nothing, and table.BOF and table.EOF = nothing. I thought that I declared all these and also set them correctly?!? I also created a globalDatabase module. I've been looking at the code and following all the steps in the book. BUt now that I know what the problem is I don't understand why?

[Comatose]

it's probably a problem with the set for table:

Set table = globalDatabase.OpenRecordset("SELECT * FROM IPTypeLookup WHERE IPTypeID='" & inputTypeID & "';", dbOpenForwardOnly)

I don't do a whole lot with databases, but I'd be willing to guess that access doesn't like the query. It might have a big problem assigning an object to your module, I don't know.

[aoude]

I think you're right...although I've used the same code in anther database and it doesn't give me any problems?!? I will try to take another approach...thanks a lot :)