query looping

[Bob]

Hi,
I have a small database in Access 2002 which records commission payments
made to a loan broker.
The following query searches for any months that a commission payment
was not received for a particular Loan number. In this case Loan number 1.

SELECT tblDate.MonthYear
FROM tblDate
WHERE
MonthYear < Now()
AND Format ([MonthYear],"mmmm,yyyy")
Not In (
SELECT Format ([PaymentDate],"mmmm,yyyy")
FROM tblCommission
WHERE LoanNo =1);

I want to be able to loop through the Loan numbers running this query on
each number.
I understand loops and could probably write this in C++ or Java but I'm
not big on VBA.
Any assistance greatly appreciated.

Bob

 

[Bob]

Hi,
I have a small database in Access 2002 which records commission payments
made to a loan broker.
The following query searches for any months that a commission payment
was not received for a particular Loan number. In this case Loan number 1.

SELECT tblDate.MonthYear
FROM tblDate
WHERE
MonthYear < Now()
AND Format ([MonthYear],"mmmm,yyyy")
Not In (
SELECT Format ([PaymentDate],"mmmm,yyyy")
FROM tblCommission
WHERE LoanNo =1);

I want to be able to loop through the Loan numbers running this query on
each number.
I understand loops and could probably write this in C++ or Java but I'm
not big on VBA.
Any assistance greatly appreciated.

Bob

I think I should be using something like this but I'm only guessing
Dim myvalue As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryMissingCommission")
Set rst = qdf.OpenRecordset

rst.MoveFirst
Do While rst.EOF = False
myvalue = rst.Fields("LoanNo").Value
MsgBox myvalue
rst.MoveNext
Loop
rst.Close
qdf.Close
Set rst = Nothing
Set qdf = Nothing
dbs.Close
Set dbs = Nothing

When I run it from a command button on a form I get an error stating
that : Dim dbs As DAO.Database "User-defined type not defined"

Any Ideas ?

 

[Guest]

Hi Bob

You may need to add a reference (Tools, References in the VBA editor) to
Microsoft DAO 3.6 Object Library.

Regards
Glenn

Hi,
I have a small database in Access 2002 which records commission payments
made to a loan broker.
The following query searches for any months that a commission payment
was not received for a particular Loan number. In this case Loan number 1.

SELECT tblDate.MonthYear
FROM tblDate
WHERE
MonthYear < Now()
AND Format ([MonthYear],"mmmm,yyyy")
Not In (
SELECT Format ([PaymentDate],"mmmm,yyyy")
FROM tblCommission
WHERE LoanNo =1);

I want to be able to loop through the Loan numbers running this query on
each number.
I understand loops and could probably write this in C++ or Java but I'm
not big on VBA.
Any assistance greatly appreciated.

Bob

I think I should be using something like this but I'm only guessing
Dim myvalue As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryMissingCommission")
Set rst = qdf.OpenRecordset

rst.MoveFirst
Do While rst.EOF = False
myvalue = rst.Fields("LoanNo").Value
MsgBox myvalue
rst.MoveNext
Loop
rst.Close
qdf.Close
Set rst = Nothing
Set qdf = Nothing
dbs.Close
Set dbs = Nothing

When I run it from a command button on a form I get an error stating
that : Dim dbs As DAO.Database "User-defined type not defined"

Any Ideas ?

 

[Bob]

Hi Bob

You may need to add a reference (Tools, References in the VBA editor) to
Microsoft DAO 3.6 Object Library.

Regards
Glenn

:

Hi,
I have a small database in Access 2002 which records commission payments
made to a loan broker.
The following query searches for any months that a commission payment
was not received for a particular Loan number. In this case Loan number 1.

SELECT tblDate.MonthYear
FROM tblDate
WHERE
MonthYear < Now()
AND Format ([MonthYear],"mmmm,yyyy")
Not In (
SELECT Format ([PaymentDate],"mmmm,yyyy")
FROM tblCommission
WHERE LoanNo =1);

I want to be able to loop through the Loan numbers running this query on
each number.
I understand loops and could probably write this in C++ or Java but I'm
not big on VBA.
Any assistance greatly appreciated.

Bob

I think I should be using something like this but I'm only guessing
Dim myvalue As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryMissingCommission")
Set rst = qdf.OpenRecordset

rst.MoveFirst
Do While rst.EOF = False
myvalue = rst.Fields("LoanNo").Value
MsgBox myvalue
rst.MoveNext
Loop
rst.Close
qdf.Close
Set rst = Nothing
Set qdf = Nothing
dbs.Close
Set dbs = Nothing

When I run it from a command button on a form I get an error stating
that : Dim dbs As DAO.Database "User-defined type not defined"

Any Ideas ?

Thankyou, that solved that problem
I still need more help to get this going so I have stated a new thread
where I'll try to explain it better.