Passing a Query Field to a VBA variable

  • Thread starter Thread starter MAC
  • Start date Start date
M

MAC

I need to be able to pass the result of a query to a VBA
Variable. Currently, I have only been able to do this by
Changing the query into a make table query and then
Opening the table with VBA and assiging the variables.
There must be a better way.

As an example:
I have a Query that Counts the number of records with a
particular status. If the number of records with this
status is 0, I want to disable a button on a form.

Thanks!

Michael Carrillo
 
Hi Mike,

You can do this by using code such as the following
(using DAO). Hopefully I won't make any typo's:

Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("QueryName",dbOpenDynaset,
dbReadOnly)

Of course you would need to substitute the query name.
From here it should be the same as working with a
recordset drawn from a table (post back if you need
further help on that)

One note though, if you are only trying to count the
number of records in another table that meet certain
criteria, have you thought of using the Dcount()
function. Or, DFirst() to just see if any matches exist?

-Ted
 
Ted said:
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("QueryName",dbOpenDynaset,
dbReadOnly)
Click to expand...

No typos as far as I can see. Why use the dbs variable? Unless you use
it a lot in the same procedure. If you do, please don't forget to Unset
what you Set, and Close what you Open. Will help you keep from the
"Access Won't Close" bug.
One note though, if you are only trying to count the
number of records in another table that meet certain
criteria, have you thought of using the Dcount()
function. Or, DFirst() to just see if any matches exist?
Click to expand...

And, if it really is for that query, you can apply DLookup(), as in

yourVar = DLookup("queryfieldname","queryname")
 
I tried but got the following error:
run-time error '91':
object variable or with block variable not set.
This error message highlights the first line where I try
to 'Set rsCount=db911 .....'
Perhaps, I missed something. Here is the code I tested:

Private Sub Form_Load()
Dim stDocName As String
Dim intSBCct As Integer
Dim intSCCct As Integer

Dim db911 As Database
Dim rsCount As DAO.Recordset

Set dbs = CurrentDb
Set rsCount = db911.OpenRecordset("test1",
dbOpenDynaset, dbReadOnly)
intSBCct = rsCount.Fields("Count")
Set rsCount = Nothing
Set rsCount = db911.OpenRecordset("test2",
dbOpenDynaset, dbReadOnly)
intSCCct = rsCount.Fields("Count")

If intSBCct > 0 Then
Me![CommandSBC].Enabled = True
Else
Me![CommandSBC].Enabled = False
End If

If intSCCct > 0 Then
Me![CommandIntrado].Enabled = True
Else
Me![CommandIntrado].Enabled = False
End If

End Sub

Michael
 
I tried using the Dlookup, but got the following error:
run-time error '424':
object required
This error highlights the first 'If' statement.
Here is the code I tried to use.

Private Sub Form_Load()
Dim intSBCct As Integer
Dim intSCCct As Integer

If DLookup("Status", "test3") Is Null Then
intSBCct = 0
Else
intSBCct = DLookup("Status", "test3")
End If
'************ Also Tried
If (intSCCct = DLookup("status", "test4")) Is Null Then
intSCCct = 0
Else
intSCCct = DLookup("status", "test4")
End If

If intSBCct > 0 Then
Me![CommandSBC].Enabled = True
Else
Me![CommandSBC].Enabled = False
End If

If intSCCct > 0 Then
Me![CommandIntrado].Enabled = True
Else
Me![CommandIntrado].Enabled = False
End If

End Sub

I am not sure what object the error code is refering too.

Michael
 
Hi,
It's telling you you haven't set your db911 variable, which you haven't.
you've 'set' dbs (which is not declared at all).
So simply change Set dbs = CurrentDb to
Set db911 = CurrentDb

--
HTH
Dan Artuso, Access MVP


MAC said:
I tried but got the following error:
run-time error '91':
object variable or with block variable not set.
This error message highlights the first line where I try
to 'Set rsCount=db911 .....'
Perhaps, I missed something. Here is the code I tested:

Private Sub Form_Load()
Dim stDocName As String
Dim intSBCct As Integer
Dim intSCCct As Integer

Dim db911 As Database
Dim rsCount As DAO.Recordset

Set dbs = CurrentDb
Set rsCount = db911.OpenRecordset("test1",
dbOpenDynaset, dbReadOnly)
intSBCct = rsCount.Fields("Count")
Set rsCount = Nothing
Set rsCount = db911.OpenRecordset("test2",
dbOpenDynaset, dbReadOnly)
intSCCct = rsCount.Fields("Count")

If intSBCct > 0 Then
Me![CommandSBC].Enabled = True
Else
Me![CommandSBC].Enabled = False
End If

If intSCCct > 0 Then
Me![CommandIntrado].Enabled = True
Else
Me![CommandIntrado].Enabled = False
End If

End Sub

Michael
-----Original Message-----
Hi Mike,

You can do this by using code such as the following
(using DAO). Hopefully I won't make any typo's:

Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("QueryName",dbOpenDynaset,
dbReadOnly)

Of course you would need to substitute the query name.
From here it should be the same as working with a
recordset drawn from a table (post back if you need
further help on that)

One note though, if you are only trying to count the
number of records in another table that meet certain
criteria, have you thought of using the Dcount()
function. Or, DFirst() to just see if any matches exist?

-Ted

.
Click to expand...
Click to expand...
 
Geeez!
Sometime a person can't see the forest for the trees!

Many Thanks! It works Great!

Michael
-----Original Message-----
Hi,
It's telling you you haven't set your db911 variable, which you haven't.
you've 'set' dbs (which is not declared at all).
So simply change Set dbs = CurrentDb to
Set db911 = CurrentDb

--
HTH
Dan Artuso, Access MVP


"MAC" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
I tried but got the following error:
run-time error '91':
object variable or with block variable not set.
This error message highlights the first line where I try
to 'Set rsCount=db911 .....'
Perhaps, I missed something. Here is the code I tested:

Private Sub Form_Load()
Dim stDocName As String
Dim intSBCct As Integer
Dim intSCCct As Integer

Dim db911 As Database
Dim rsCount As DAO.Recordset

Set dbs = CurrentDb
Set rsCount = db911.OpenRecordset("test1",
dbOpenDynaset, dbReadOnly)
intSBCct = rsCount.Fields("Count")
Set rsCount = Nothing
Set rsCount = db911.OpenRecordset("test2",
dbOpenDynaset, dbReadOnly)
intSCCct = rsCount.Fields("Count")

If intSBCct > 0 Then
Me![CommandSBC].Enabled = True
Else
Me![CommandSBC].Enabled = False
End If

If intSCCct > 0 Then
Me![CommandIntrado].Enabled = True
Else
Me![CommandIntrado].Enabled = False
End If

End Sub

Michael
-----Original Message-----
Hi Mike,

You can do this by using code such as the following
(using DAO). Hopefully I won't make any typo's:

Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("QueryName",dbOpenDynaset,
dbReadOnly)

Of course you would need to substitute the query name.
From here it should be the same as working with a
recordset drawn from a table (post back if you need
further help on that)

One note though, if you are only trying to count the
number of records in another table that meet certain
criteria, have you thought of using the Dcount()
function. Or, DFirst() to just see if any matches exist?

-Ted

-----Original Message-----
I need to be able to pass the result of a query to a VBA
Variable. Currently, I have only been able to do this
by
Changing the query into a make table query and then
Opening the table with VBA and assiging the variables.
There must be a better way.

As an example:
I have a Query that Counts the number of records with a
particular status. If the number of records with this
status is 0, I want to disable a button on a form.

Thanks!

Michael Carrillo
.

.
Click to expand...
Click to expand...


.
Click to expand...
 
MAC said:
If DLookup("Status", "test3") Is Null Then
Click to expand...

No; that must be

if isnull(dlookup(-that thing-)) then
I am not sure what object the error code is refering too.
Click to expand...

Mislead by the Is keyword. You *can* write
if someobject is nothing then
or the like (don't pin me down on that please)
 
Back
Top