Code not working... Please Help!

G

Gina Whipp

Getting message on 'If rst.RecordCount > 3 Then' <Object variable or With
block variable not set>

Dim rst As DAO.Recordset
Dim RecordCount As DAO.Database
Dim PagesToInsert As Integer

If rst.RecordCount > 3 Then
PagesToInsert = Round((rst.RecordCount) + 0.5 / 1)
End If

MsgBox "Please insert" & PagesToInsert & "pages to print your form.",
vbOKOnly, "Printer Attention Required"


Thanks in advance....
Gina Whipp
 
D

Douglas J. Steele

While you've declared rst to be a recordset, you haven't instantiated it.

What is that recordset supposed to represent? You have to tell Access...
 
V

Van T. Dinh

.... and I am not sure why you declared RecordCount as a Database Object?

Shouldn't it be Long?
 
G

Gina Whipp

I did fix the RecordCount but still not working... I guess I don't
understand... Everything I try to Set comes up with this not available in
..mdb, so I'm going to try something else...

Thanks anyway...
 
P

Paul Johnson

Gina,

You shouldn't give up yet. You need to set your recordset object with the
name of a table:, or a select query SQL statement, like

Set rst = CurrentDb.OpenRecordSet("SELECT * FROM [MyTable] WHERE [SomeField]
= SomeNumericValue")

You can create the SQL statement by concatenating pieces in your code, like

Set rst = CurrentDb.OpenRecordSet("SELECT * FROM [MyTable] WHERE [SomeField]
= " & SomeNumericValue )

or

Set rst = CurrentDb.OpenRecordSet("SELECT * FROM [MyTable] WHERE [SomeField]
= '" & SomeTextValue & "'")

Adapt this to your table design and WHERE criteria. This is just a rough
example, so you can see which objects to use.

The OpenRecordSet method can just have a table name ("MyTable"), or it can
have a SQL string that is complex, with aggregate functions, joins, "where"
conditions, "order by" settings, whatever you can put into a query. If you
can put it into a query design window, you can open a recordset object.
Likewise, the VBA recordset will only be editable (updateable) if the same
recordset is editable in a GUI query.

Okay, you've read this far. My personal opinion is that you want to get
straight to the point. Since you just need a quick count, and you're not
accessing the recordset for anything else, get the value and let it go,
without creating a recordset object to reside in memory. I would do this:

Dim intRecordCount as Integer

intRecordCount = _
CurrentDb.OpenRecordSet( _
"SELECT Count(1) " & _
"FROM [MyTable] " & _
"WHERE [SomeField] = " & _
SomeNumericValue).fields(0)

If intRecordCount > 3 Then _
MsgBox "Please insert " & _
Round((intRecordCount) + 0.5 / 1) & _
" pages to print your form.", _
vbOKOnly + vbExclamation, _
"Printer Attention Required"

That was a complex way to get the count, because we were talking about
opening recordset objects. If anyone else is reading, they'll probably be
happy to add that the dCount function is perfectly suited to your needs.
And it's simpler to use. Let's cover that:

intRecordCount = dCount(1,"MyTable", _
"SomeField = '" & SomeTextValue & "'")

etc.

HTH

Paul Johnson
 
G

Gina Whipp

Paul,

Thank you for explaining recordset objects. It is an area I am unfamilar
with... I actually started reading about it. But since all I needed was
the page count I came up with something alot simpler:

Since all I wanted was the total number of pages to show in a message box.
I put a text boxe 'txtPage' control source is Pages. Then a message show
prior to print informing you of how many pages to print!

MsgBox "Please insert " & Me.txtPage & " pages to print your form.",
vbOKOnly, "Printer Attention Required"

But a big THANK YOU for taking the time to explain my problem and what I was
doing wrong. I guess I still have alot to learn! :cool:

Gina

Paul Johnson said:
Gina,

You shouldn't give up yet. You need to set your recordset object with the
name of a table:, or a select query SQL statement, like

Set rst = CurrentDb.OpenRecordSet("SELECT * FROM [MyTable] WHERE
[SomeField]
= SomeNumericValue")

You can create the SQL statement by concatenating pieces in your code,
like

Set rst = CurrentDb.OpenRecordSet("SELECT * FROM [MyTable] WHERE
[SomeField]
= " & SomeNumericValue )

or

Set rst = CurrentDb.OpenRecordSet("SELECT * FROM [MyTable] WHERE
[SomeField]
= '" & SomeTextValue & "'")

Adapt this to your table design and WHERE criteria. This is just a rough
example, so you can see which objects to use.

The OpenRecordSet method can just have a table name ("MyTable"), or it can
have a SQL string that is complex, with aggregate functions, joins,
"where"
conditions, "order by" settings, whatever you can put into a query. If
you
can put it into a query design window, you can open a recordset object.
Likewise, the VBA recordset will only be editable (updateable) if the same
recordset is editable in a GUI query.

Okay, you've read this far. My personal opinion is that you want to get
straight to the point. Since you just need a quick count, and you're not
accessing the recordset for anything else, get the value and let it go,
without creating a recordset object to reside in memory. I would do this:

Dim intRecordCount as Integer

intRecordCount = _
CurrentDb.OpenRecordSet( _
"SELECT Count(1) " & _
"FROM [MyTable] " & _
"WHERE [SomeField] = " & _
SomeNumericValue).fields(0)

If intRecordCount > 3 Then _
MsgBox "Please insert " & _
Round((intRecordCount) + 0.5 / 1) & _
" pages to print your form.", _
vbOKOnly + vbExclamation, _
"Printer Attention Required"

That was a complex way to get the count, because we were talking about
opening recordset objects. If anyone else is reading, they'll probably be
happy to add that the dCount function is perfectly suited to your needs.
And it's simpler to use. Let's cover that:

intRecordCount = dCount(1,"MyTable", _
"SomeField = '" & SomeTextValue & "'")

etc.

HTH

Paul Johnson

Gina Whipp said:
I did fix the RecordCount but still not working... I guess I don't
understand... Everything I try to Set comes up with this not available
in
.mdb, so I'm going to try something else...

Thanks anyway...
Click to expand...
Click to expand...
 
D

Doug Munich

You still need to address Doug Steele's advice, that you have not indicated
what set of records rst is meant to be. You need to set rst to the query
that you want to get a recordcount from. Something like:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim PagesToInsert As Integer

Set db = CurrentDb()
Set rst = db.OpenRecordset("qryDataToPrint")
rst.MoveLast

If rst.RecordCount > 3 Then
PagesToInsert = Round((rst.RecordCount) + 0.5 / 1)
Else
PagesToInsert = 1
End If
MsgBox "Please insert " & PagesToInsert & " pages to print your form.",
vbOKOnly, "Printer Attention Required"


Doug
 
Top