Loop Query Until Empty

[SomeDude]

Perhaps I'm going about this all wrong. If so, kindly tell me. I'm no stranger to SQL, but am still quite new to VBA (and don't have enough money to take a class).

I'm an accountant and I'd like to build my own personal financial database in Access. One thing I like about MS Money is the 'Forecast Cashflow' feature, which is based on a schedule of recurring transactions, amounts, next due date, and frequency. I want to replicate this.

So, I created a dummy database for the sake of learning the code behind this process.
I've got 2 tables (but no microphone ):
1) tbl_InitialPoint (which is my schedule of recurring transactions, amounts, frequency (in days))
2) tbl_Register (where I want forecasted transactions to wind up)
I've got 2 saved queries:
1) qry_MaxDate (looks for any transaction @ 'tbl_InitialPoint' and finds the last date of that transaction in 'tbl_Register'
SELECT MAX(tbl_Register.PostDate) AS LastDate, tbl_InitialPoint.Description
FROM tbl_InitialPoint INNER JOIN tbl_Register ON tbl_InitialPoint.Description = tbl_Register.Description
GROUP BY tbl_InitialPoint.Description;
2) qry_InsertTransactions (populates 'tbl_Register')
INSERT INTO tbl_Register ( PostDate, Description, Amount )
SELECT qry_MaxDate.LastDate + tbl_InitialPoint.Frequency AS DateSeries, tbl_InitialPoint.Description, tbl_InitialPoint.Amount
FROM tbl_InitialPoint INNER JOIN qry_MaxDate ON tbl_InitialPoint.Description = qry_MaxDate.Description
WHERE qry_MaxDate.LastDate + tbl_InitialPoint.Frequency <= [Forms]![HomePage]![DateHorizon];

And I've got a form called 'HomePage' that has
1) A textbox where I input a date horizon (this is where I want the eventual 'Forecast Cashflow' to stop
2) A button to start the process

It seems to me that qry_InsertTransactions needs to be run over and over (looped) until the query is empty. And it seems to me that this can only be done via VBA (not sure if I could structure my SQL to avoid VBA in this case, but I need/want to learn the vba to accomplish this).

I'm having trouble getting VBA to run qry_InsertTransactions a single time. I'm pretty sure if I could get the thing to run once I could get it to run through a loop. So, here's what I came up with. Can anyone tell me where I've gone wrong?

Public Sub InsertTransactionsBttn_Click()

Dim db As Database
Dim qdfNew As QueryDef
Dim qdfInsertTransactions As QueryDef

Set db = CurrentDb()
Set qdfInsertTransactions = CreateQueryDef("NewQueryDef", _
"INSERT INTO tbl_Register ( PostDate, Description, Amount )" & _
"SELECT qry_MaxDate.LastDate + tbl_InitialPoint.Frequency AS DateSeries, tbl_InitialPoint.Description, tbl_InitialPoint.Amount" & _
"FROM tbl_InitialPoint INNER JOIN qry_MaxDate ON tbl_InitialPoint.Description = qry_MaxDate.Description" & _
"WHERE qry_MaxDate.LastDate + tbl_InitialPoint.Frequency <= [Forms]![HomePage]![DateHorizon]")

Execute.QueryDef qdfInsertTransactions

End Sub

 

[plog]

I'm having trouble getting VBA to run qry_InsertTransactions a single time

What does that mean? Are you getting errors? If so, post the message. Or is it running without errors but not putting data into tbl_Register?

Without know that, here's what I would do--Divide and Conquer. Make sure your SQL works--create a query, paste the SQL into there and execute it. If it works, then the issue is something else in the VBA, if it doesn't, you've narrowed down the problem to the SQL.

 

[JHB]

One problem is, you are missing spaces when you go to a new line in your query.
Wrong:

"INSERT INTO tbl_Register ( PostDate, Description, Amount )" & _
"SELECT qry_MaxDate.LastDate + tbl_InitialPoint.Frequency AS DateSeries, tbl_InitialPoint.Description, tbl_InitialPoint.Amount" & _
"FROM tbl_InitialPoint INNER JOIN qry_MaxDate ON tbl_InitialPoint.Description = qry_MaxDate.Description" & _
"WHERE qry_MaxDate.LastDate + tbl_InitialPoint.Frequency <= [Forms]![HomePage]![DateHorizon]"

Correct:

"INSERT INTO tbl_Register ( PostDate, Description, Amount ) " & _
"SELECT qry_MaxDate.LastDate + tbl_InitialPoint.Frequency AS DateSeries, tbl_InitialPoint.Description, tbl_InitialPoint.Amount " & _
"FROM tbl_InitialPoint INNER JOIN qry_MaxDate ON tbl_InitialPoint.Description = qry_MaxDate.Description " & _
"WHERE qry_MaxDate.LastDate + tbl_InitialPoint.Frequency <= [Forms]![HomePage]![DateHorizon]"

 

[SomeDude]

JHB: Thanks. I added the spaces before each " per your example.

plog: You asked about errors I'm getting. When I press the button on my form I get a 'Compile error: Sub or Function not defined'. Debugger highlights 'Public Sub InsertTransactionsBttn_Click()' in yellow, and CreateQueryDef is highlighted. I've attached a screenshot.

 

[JHB]

You are missing the database object in connection with the CreateQueryDef.

Set db = CurrentDb()
    Set qdfInsertTransactions = [COLOR=Red][B]db.[/B][/COLOR]CreateQueryDef("NewQueryDef", _
.....

 

[SomeDude]

Thanks, JHB. I added the db. prefix.

I went back to click on the button on the HomePage and got a 'Run-time error 3012: Object 'NewQueryDef' already exists.' So, I got rid of the name (db.CreateQueryDef("", _).

Also, I think my syntax for the Execute command was incorrect, so I changed it to qdfInsertTransactions.Execute (""). I added the '("")' because I kept getting errors telling me I didn't have enough parameters.

So, now I'm getting a 'Run-time error 3421: Data type conversion error.' Does this have something to do with my changing 'db.CreateQueryDef("NewQueryDef", _' to 'db.CreateQueryDef("", _' ?

 

[JHB]

So, now I'm getting a 'Run-time error 3421: Data type conversion error.' Does this have something to do with my changing 'db.CreateQueryDef("NewQueryDef", _' to 'db.CreateQueryDef("", _' ?

This type of errors occurs normally if you are trying to put a wrong datatype in a field ex. text in a number field type. It could be this: "qry_MaxDate.LastDate + tbl_InitialPoint.Frequency"
If you are trying to add something to a date field, then look at the function DateAdd in the Help-file.
Get the query to run in the QBE-window first.

I went back to click on the button on the HomePage and got a 'Run-time error 3012: Object 'NewQueryDef' already exists.' So, I got rid of the name (db.CreateQueryDef("", _).

Why didn't you delete 'NewQueryDef' instead?

 

[SomeDude]

Sorry for the delay in responding...I've been wrapping up month-end.

In response to why I didn't just delete "NewQueryDef"...I'm that much of a newbie. Thank you for your patience. I am seriously trying to "get" this.

In an attempt to get around the data type conversion error and how you thought the + operator might be causing a problem, I created a new query (called 'qry_PrepTransactions', which is the same as 'qry_InsertTransactions', but without the INSERT INTO statement...a simple SELECT statement. Then I modified 'qry_InsertTransactions' to
INSERT INTO tbl_Register
SELECT *
FROM qry_PrepTransactions;
I'm not getting any vba errors with this, however, I am with my Execute statement. It seems like I'm seeing many variations on the execute statement in the help file and/or online, and I've tried 5 of them and noted the resulting error I get. I shouldn't have to invoke a recordset for this, should I? -I ask because I see that a lot in the help file and in online forums.

Can you tell which Execute statement has the correct syntax, and what I might be missing?

I couldn't imagine that there's really a data type conversion error...it seems like if that were really the case then I would get a similar error when running the Insert query by double-clicking on it in the Navigation pane. When I run the Insert query manually, it runs just fine...just as expected.

 

[JHB]

Post your database with some sample data, (zip it if you don't have post 10 post yet).

 

[SomeDude]

Here's the database I'm working with.

 

[JHB]

Now it is running.
Another way to do the same is to create a query and the run the query with the "DoCmd.OpenQuery" command. I've made an example for you.
The database with the corrected code and the example is attached.

 

[SomeDude]

JHB, thanks.

I noticed the DateAdd function, and the # prefix and suffix around [Forms]![HomePage]![DateHorizon]. I played around with the code just a bit and here is the end result:

Option Compare Database

Private Sub InsertTransactionsBttn_Click()

Dim db As DAO.Database
Dim qdfInsertTransactions As QueryDef

Set db = CurrentDb()
Set qdfInsertTransactions = db.CreateQueryDef("", _
"INSERT INTO tbl_Register ( PostDate, Description, Amount ) " & _
"SELECT DateAdd('d',[tbl_InitialPoint].[Frequency],[qry_MaxDate].[LastDate]) AS PostDate, tbl_InitialPoint.Description, tbl_InitialPoint.Amount " & _
"FROM tbl_InitialPoint INNER JOIN qry_MaxDate ON tbl_InitialPoint.Description = qry_MaxDate.Description " & _
"WHERE DateAdd('d',[tbl_InitialPoint].[Frequency],[qry_MaxDate].[LastDate]) <=#" & [Forms]![HomePage]![DateHorizon] & "#")

Do
qdfInsertTransactions.Execute
Loop Until qdfInsertTransactions.RecordsAffected = 0

End Sub

Thanks again!