Referencing Query in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that I want to execute and then reference in VBA. I have the
following code:

docmd.openquery "qryPAYMENTtotals",acNORMAL,acREADONLY)
pmttotals=query!PAYMENTtotals!paymenttotals


When the code gets to these two lines I start having problem. Actually the
second. I get a run time error 424. Object Required.

Can someone please point me in the correct direction.

Thanks,

Ray.
 
Ray said:
I have a query that I want to execute and then reference in VBA. I have the
following code:

docmd.openquery "qryPAYMENTtotals",acNORMAL,acREADONLY)
pmttotals=query!PAYMENTtotals!paymenttotals


When the code gets to these two lines I start having problem. Actually the
second. I get a run time error 424. Object Required.
Click to expand...


Displaying a query's data in datasheet view only allows a
user to view the data. To use the data in your VBA
procedure, you need to open a recordset:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("qryPAYMENTtotals", _
dbOpenSnapshot, dbReadOnly)
pmttotals=rs!paymenttotals
. . .
rs.Close : Set rs = Nothing
Set db = Nothing
 
I have a query that I want to execute and then reference in VBA. I have the
following code:

docmd.openquery "qryPAYMENTtotals",acNORMAL,acREADONLY)
pmttotals=query!PAYMENTtotals!paymenttotals

When the code gets to these two lines I start having problem. Actually the
second. I get a run time error 424. Object Required.

Can someone please point me in the correct direction.

Thanks,

Ray.
Click to expand...

If the query returns just one record, you do not have to actually run
the query. All you need is :

pmttotals = DLookUp("[PaymentTotals]","qryPayment")

If it returns lots of records, you can use a where clause to get the
correct value,
pmtTotals = DLookUp("[PaymentTotals]","qryPayment","[Paidto] = 'Smith
and Sons'")

or open a recordset within the code and search there.
 
Back
Top