Solved Object variable or With block variable not set Error

[Momma]

I get an "Object variable or With block not set" error on the following code. I did a Debug Compile and it compiled without any errors.
What could be the problem?

            Dim rs As DAO.Recordset
            Set rs = db.OpenRecordset("SELECT Count([pkFilename]) AS RecCount FROM TblDocument where [fileName] = filename1;")
            RecCount1 = rs![RecCount]
            rs.Close
            Set rs = Nothing

 

[June7]

What is filename1? Where does that parameter come from?

Your code works for me after I changed db to CurrentDb - but I get "missing parameter" error because of the embedded filename1 parameter. Need to concatenate variable inputs and use apostrophe delimiters if filename is text data type.

"SELECT Count([pkFilename]) AS RecCount FROM TblDocument where [fileName] = '" & filename1 & "'")

Or you could just use DCount() domain aggregate function

RecCount1 = DCount("*", "TblDocument", "filename='" & filename1 & "'")

You should have Option Explicit at top of every code module. Set your VBA editor to do that by default for new modules. Will have to manually add to existing.

 

[tvanstiphout]

I get an "Object variable or With block not set" error on the following code. I did a Debug Compile and it compiled without any errors.
What could be the problem?

            Dim rs As DAO.Recordset
            Set rs = db.OpenRecordset("SELECT Count([pkFilename]) AS RecCount FROM TblDocument where [fileName] = filename1;")
            RecCount1 = rs![RecCount]
            rs.Close
            Set rs = Nothing

Probably because "db" is not declared. Add this:
dim db as dao.database
set db=currentdb

 

[June7]

Actually, without declaring and setting db, the error is simply "object required".

 

[Momma]

What is filename1? Where does that parameter come from?

Your code works for me after I changed db to CurrentDb - but I get "missing parameter" error because of the embedded filename1 parameter. Need to concatenate variable inputs and use apostrophe delimiters if filename is text data type.

"SELECT Count([pkFilename]) AS RecCount FROM TblDocument where [fileName] = '" & filename1 & "'")

Or you could just use DCount() domain aggregate function

RecCount1 = DCount("*", "TblDocument", "filename='" & filename1 & "'")

You should have Option Explicit at top of every code module. Set your VBA editor to do that by default for new modules. Will have to manually add to existing.

Sorry, I declare it as follows:

    Dim Filename1 As String
    Filename1 = "SaleGuarantee-" & Contact & ".pdf"
    Dim Contact As String
    Contact = Forms!frmContactgrouplistbox!ContactList.Column(2, i)
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset("SELECT Count([pkFilename]) AS RecCount FROM TblDocument where [fileName] = filename1;")
    RecCount1 = rs![RecCount]
    rs.Close
    Set rs = Nothing

 

[The_Doc_Man]

When you DIM a variable, it is usually created with a default value. For string data types, the default is a zero-length (i.e. empty) string. It actually is legal to DIM variables out of order because the DIM statements are "gathered" together. They actually aren't executable. (Note that a REDIM statement IS executable.) So it doesn't matter that you defined the variables pretty much as you needed them. But it is considered slightly preferred form to DIM stuff before the first executable statement of any routine. Makes it easier to understand.

In this snippet, you assign a value to Contact AFTER you use it to assign a value to Filename1. Therefore, at the time that you define Filename1, you would get the string "SaleGaurantee-.pdf" which might not point to anything meaningful. Which means the recordset you defined would fail in its definition.

 

[June7]

Well, did you try the suggested corrections?

 

[sonic8]

Sorry, I declare it as follows:

You just confirmed @June7's assumption about the variable db not being declared.

 

[NauticalGent]

In addition to not setting your db Object to Currentdb, you have a couple of errors:

Filename1 = "SaleGuarantee-" & Contact & ".pdf"
Dim Contact As String
Contact = Forms!frmContactgrouplistbox!ContactList.Column(2, i)

You are trying to use the string 'Contact' as part of another string BEFORE you have populated it. Try this:

Dim db As DAO.Database
Dim Filename1 As String
Dim Contact As String
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Count([pkFilename]) AS RecCount FROM TblDocument where [fileName] = filename1;")

Contact = Forms!frmContactgrouplistbox!ContactList.Column(2, i)
Filename1 = "SaleGuarantee-" & Contact & ".pdf"

RecCount1 = rs![RecCount]
rs.Close
Set rs = Nothing
Set db = Nothing

However, this will still not work because your 2nd argument (row) in Contact = Forms!frmContactgrouplistbox!ContactList.Column(2,i) is not being populated. You need to declare 'i' as an integer and then populate it before you run this event.

 

[Uncle Gizmo]

Try This:-

Dim db As DAO.Database
Set db = CurrentDb()

Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("SELECT Count([pkFilename]) AS RecCount FROM TblDocument WHERE [fileName] = '" & filename1 & "';")
RecCount1 = rs![RecCount]
rs.Close
Set rs = Nothing

 

[Momma]

When you DIM a variable, it is usually created with a default value. For string data types, the default is a zero-length (i.e. empty) string. It actually is legal to DIM variables out of order because the DIM statements are "gathered" together. They actually aren't executable. (Note that a REDIM statement IS executable.) So it doesn't matter that you defined the variables pretty much as you needed them. But it is considered slightly preferred form to DIM stuff before the first executable statement of any routine. Makes it easier to understand.

In this snippet, you assign a value to Contact AFTER you use it to assign a value to Filename1. Therefore, at the time that you define Filename1, you would get the string "SaleGaurantee-.pdf" which might not point to anything meaningful. Which means the recordset you defined would fail in its definition.

Well, did you try the suggested corrections?

Sorry for the late reply. I end up using your 2nd suggestion and it worked.
The first one gives a run-time error, although it compiled.

            Dim rs As DAO.Recordset
            Dim db As DAO.Database
            Set db = CurrentDb
            Set rs = db.OpenRecordset("SELECT Count([pkFilename]) AS RecCount FROM TblDocument where [fileName] = '" & Filename1 & "'")
            RecCount1 = rs![RecCount]
            rs.Close
            Set rs = Nothing