UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Open Record Set And Use Case Select To Initiate A Query Run, Access 2016    
 
   
Olaf123
post Yesterday, 06:15 PM
Post#1



Posts: 23
Joined: 9-June 16



Hello Everyone,
I am building a code that should take a listed below record set, look thru the records, find record xxxx then run query aaaa; find record yyyy then run query bbbb. I can't even test this code completely, us it gives me a run-time error 13- type miss match and highlights this: Set rst = db.OpenRecordset(strSQL).

Can someone help me in correcting the issue?

Thanks.
My code is listed below:


Dim db As Database
Dim rst As Recordset
Dim strPlanId As String
Dim intRecordCount As Integer
Dim strRecordType As String
Dim strSQL As String
Dim gSQL1 As String
Dim gSQL2 As String



strSQL = "SELECT ReportName.PlanID, ReportName.ReportNumber, ReportName.ReportingPeriod, ReportName.ReportDate FROM ReportName"

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)


Do While Not rst.EOF


intRecordCount = intRecordCount + 1
strRecordType = rst!Left([strPlanId], 5)

CheckRecordType:
Select Case strRecordType
Case "xxxx"
GoSub aaaa
Case "yyyy"
GoSub bbbb
End Select


aaaa:
gSQL1 = "INSERT INTO [AFilesYTD] ( [RECORD-ID], [Total Amount], [REPORT DATE] ) SELECT PayableMonthlyTotal.ReportId, First(PayableMonthlyTotal.[SumOfCURRENT-MONTH-AMOUNT1]) AS [FirstOfSumOfCURRENT-MONTH-AMOUNT1], CDate(Format([ReportDate],""0000-00-00"")) AS [Date] FROM PayableMonthlyTotal LEFT JOIN ReportName ON (PayableMonthlyTotal.Contract = ReportName.PlanID) AND (PayableMonthlyTotal.ReportId = ReportName.ReportNumber) GROUP BY PayableMonthlyTotal.ReportId, CDate(Format([ReportDate],""0000-00-00""));"

Return

bbbb:
gSQL2 = "INSERT INTO [BFilesYTD] ( [RECORD-ID], [Total Amount], [REPORT DATE] ) SELECT PayableMonthlyTotal.ReportId, First(PayableMonthlyTotal.[SumOfCURRENT-MONTH-AMOUNT1]) AS [FirstOfSumOfCURRENT-MONTH-AMOUNT1], CDate(Format([ReportDate],""0000-00-00"")) AS [Date] FROM PayableMonthlyTotal LEFT JOIN ReportName ON (PayableMonthlyTotal.Contract = ReportName.PlanID) AND (PayableMonthlyTotal.ReportId = ReportName.ReportNumber) GROUP BY PayableMonthlyTotal.ReportId, CDate(Format([ReportDate],""0000-00-00""));"

Return

rst.MoveNext
Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub
Go to the top of the page
 
theDBguy
post Yesterday, 06:54 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,505
Joined: 19-June 07
From: SunnySandyEggo


Hi. I think you actually have a few errors in there, but let's start with the first one you're getting and let's try to fix it. Try changing your one line to this and then let us know which error you get next.
CODE
Dim rst As DAO.Recordset

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th June 2019 - 09:52 PM