Access 2010 VBA Loop query until query is null

Question

I've been using Access for about a year now, and have only recently begun learning about VBA. The books I've read and the help I've read online don't seem to fit quite what I need; or if they do, then I must be too much of a novice to notice.

My project is a personal financial database, and I'm working on a way to forecast cash flows. I've got

a table with recurring transactions called 'tbl_InitialPoint' (with columns Description, Amount, and Frequency (in days)),
a query that finds the last date of a particular Description in 'tbl_Register',
an append query to insert recurring transactions from 'tbl_InitialPoint' to '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];

What I have no idea about is the specific vba to run/loop this query x number of times until the query is null or empty.

Any ideas?

Philippe Grondier Philippe Grondier · Accepted Answer · 2013-10-01T03:45:33

I suppose here that you are using local Access tables and the corresponding DAO objects.

You'l have to use the queryDef object and its execute method.

Once the querydef executed, you can then test the recordsAffected property of your queryDef instance.

If this value is equal to zero, this will mean that no records have been inserted through your query.

EDIT: following your comments, you can see some queryDef related code here , where you'll be able to create a new querydef and list the properties of the object, including the recordsAffected property.

Access 2010 VBA Loop query until query is null

1 Answers