recordsets ???

[Bob Wickham]

Hi,
About a week ago, Steve Schapel kindly helped me with some vba code and
the following is the result.

Private Sub cmdCommissionMissing_Click()
On Error GoTo Err_cmdCommissionMissing_Click

Dim dbs As Database
Dim rstLoans As DAO.Recordset
Dim rstMissed As DAO.Recordset
Dim strSql As String

Set dbs = CurrentDb

' call the function DeleteAll("tblMissingCommissionReport")

Call DeleteAll("tblMissingCommissionReport")

Set rstLoans = dbs.OpenRecordset("SELECT DISTINCT LoanNo FROM
tblCommission")


Do Until rstLoans.EOF
Set rstMissed = dbs.OpenRecordset("SELECT DISTINCT [MonthYear]" & _
" FROM tblDate" & _
" WHERE [MonthYear] < DateSerial(Year(Date()),
Month(Date()), 1)" & _
" AND Format([MonthYear],'mmmm,yyyy')" & _
" Not In (SELECT Format ([PaymentDate],'mmmm,yyyy')" & _
" FROM tblCommission" & _
" WHERE LoanNo =" & rstLoans!LoanNo & ")")

Do Until rstMissed.EOF
strSql = "INSERT INTO tblMissingCommissionReport (
LoanNumber, TheDate )" & _
" VALUES ( " & rstLoans!LoanNo & ", " &
CLng(rstMissed!MonthYear) & " )"
dbs.Execute strSql, dbFailOnError
rstMissed.MoveNext
Loop
rstMissed.Close
rstLoans.MoveNext
Loop
rstLoans.Close
Set rstMissed = Nothing
Set rstLoans = Nothing
Set dbs = Nothing

' call the sub RunMcrMissingCommissionReportPrintPreview()

Call RunMcrMissingCommissionReportPrintPreview


Exit_cmdCommissionMissing_Click:
Exit Sub

Err_cmdCommissionMissing_Click:
MsgBox Err.Description
Resume Exit_cmdCommissionMissing_Click

End Sub

As you can see, I have 2 recordsets, rstLoans and rstMissed.
I now wish to filter rstMissed a little further by adding something like:
" AND [MonthYear] > tblLoan.LoanStartDate" & _
" AND tblCommission.CommissionTypeID = 1" & _

after

" WHERE [MonthYear] < DateSerial(Year(Date()), Month(Date()), 1)" & _

and adding the appropriate tables in the FROM clause.

Even though this will run, it returns incorrect records and is obviously
the wrong way to go.

Do I need to declare some more recordsets and do this one step at a time.
If so, I am assuming something like :

" AND [rstMissed!MonthYear] > tblLoan.LoanStartDate" & _

OR is it a bit more complex than that and I would be better off with
separate queries written into functions that I could call where needed.

I just need some friendly advice on the best way to go before I spend a
week or two going round in circles (which I have been known to do)

Thanks

Bob Wickham

 

[Steve Schapel]

Bob,

What is tblLoans? Could it be that rstLoans could be defined like this...

Set rstLoans = dbs.OpenRecordset("SELECT LoanNo, LoanStartDate FROM
tblLoans")

or, perhaps evern better...

Set rstLoans = dbs.OpenRecordset("SELECT LoanNo, LoanStartDate FROM
tblLoans INNER JOIN tblCommission ON tblLoans.LoanNo =
tblCommission.LoanNo WHERE tblCommission.CommissionTypeID=1")

And then...

Set rstMissed = dbs.OpenRecordset("SELECT DISTINCT [MonthYear]" & _
" FROM tblDate" & _
" WHERE [MonthYear] > " &
CLng(rstLoans!LoanStartDate) & _
" AND [MonthYear] < DateSerial(Year(Date()),
Month(Date()), 1)" & _
" AND Format([MonthYear],'mmmm,yyyy')" & _
" Not In (SELECT Format ([PaymentDate],'mmmm,yyyy')" & _
" FROM tblCommission" & _
" WHERE LoanNo =" & rstLoans!LoanNo & ")")

Hope that might point you in a useful direction - I am a bit in the dark
as I still only have a very skeletal idea of your data structure and all
that.

--
Steve Schapel, Microsoft Access MVP

Hi,
About a week ago, Steve Schapel kindly helped me with some vba code and
the following is the result.

Private Sub cmdCommissionMissing_Click()
On Error GoTo Err_cmdCommissionMissing_Click

Dim dbs As Database
Dim rstLoans As DAO.Recordset
Dim rstMissed As DAO.Recordset
Dim strSql As String

Set dbs = CurrentDb

' call the function DeleteAll("tblMissingCommissionReport")

Call DeleteAll("tblMissingCommissionReport")

Set rstLoans = dbs.OpenRecordset("SELECT DISTINCT LoanNo FROM
tblCommission")


Do Until rstLoans.EOF
Set rstMissed = dbs.OpenRecordset("SELECT DISTINCT [MonthYear]" & _
" FROM tblDate" & _
" WHERE [MonthYear] < DateSerial(Year(Date()),
Month(Date()), 1)" & _
" AND Format([MonthYear],'mmmm,yyyy')" & _
" Not In (SELECT Format ([PaymentDate],'mmmm,yyyy')" & _
" FROM tblCommission" & _
" WHERE LoanNo =" & rstLoans!LoanNo & ")")

Do Until rstMissed.EOF
strSql = "INSERT INTO tblMissingCommissionReport ( LoanNumber,
TheDate )" & _
" VALUES ( " & rstLoans!LoanNo & ", " &
CLng(rstMissed!MonthYear) & " )"
dbs.Execute strSql, dbFailOnError
rstMissed.MoveNext
Loop
rstMissed.Close
rstLoans.MoveNext
Loop
rstLoans.Close
Set rstMissed = Nothing
Set rstLoans = Nothing
Set dbs = Nothing

' call the sub RunMcrMissingCommissionReportPrintPreview()

Call RunMcrMissingCommissionReportPrintPreview


Exit_cmdCommissionMissing_Click:
Exit Sub

Err_cmdCommissionMissing_Click:
MsgBox Err.Description
Resume Exit_cmdCommissionMissing_Click

End Sub

As you can see, I have 2 recordsets, rstLoans and rstMissed.
I now wish to filter rstMissed a little further by adding something like:
" AND [MonthYear] > tblLoan.LoanStartDate" & _
" AND tblCommission.CommissionTypeID = 1" & _

after

" WHERE [MonthYear] < DateSerial(Year(Date()), Month(Date()), 1)" & _

and adding the appropriate tables in the FROM clause.

Even though this will run, it returns incorrect records and is obviously
the wrong way to go.

Do I need to declare some more recordsets and do this one step at a time.
If so, I am assuming something like :

" AND [rstMissed!MonthYear] > tblLoan.LoanStartDate" & _

OR is it a bit more complex than that and I would be better off with
separate queries written into functions that I could call where needed.

I just need some friendly advice on the best way to go before I spend a
week or two going round in circles (which I have been known to do)

Thanks

Bob Wickham

 

[Bob Wickham]

Hope that might point you in a useful direction

Very useful direction, in fact, its almost perfect.
As I said last time, I'm trying to have a go rather than just "Ask and
wait for an answer"
I think I need to understand JOINS in order to proceed with my
understanding of databases.

And No!, it doesn't make any difference if you are an Aussie or a Kiwi.

Thanks again.

Bob Wickham