count records in recordsource so I can dynmaically create spreadsheet export

[qwertyjjj]

Hi
I need to export a form's data to Excel. I am doing this with the following code:
DoCmd.OutputTo acOutputForm, "LegalEntities", acFormatXLS, "C:\A.xls", False

I then open up the Excel spreadsheet and enter some formulas and rearrange the data as necessary. This is all done through VBA code.

Now, this all works fine for forms where I know how many rows of data the recordsource has as I can work out where I can insert cells, change forumlas, etc.

However, I now need to export the data from a form with drop downs, which means that sometimes the recordsource has 1 row...sometimes it has 50 rows.
So, to start adding forumlas to th spreadsheet I need to do it after all the data has been displayed.

I thought I could do this by counting the rows in the recordsource. Say, it had 50, I could then start editing Excel with my code at cell A52.

Any ideas?

 

[FoFa]

I think, if you have a recordset, you can do a move.last then check the rowcount property of the recordset for a count. (might be a different property) but it is there somewhere already.
You could use DCOUNT, but if you have a recordset already created, you don't really need to.

 

[qwertyjjj]

I am changing the recordsource many times depending on what is chosen from the drop downs.
Can I create a separate ecordset at the same time?
If so, maybe I could design some sort of function that tells me how many records are in the recordset each time?

 

[FoFa]

You could use DCOUNT with the same criteria and obtain a count also (it is a little slower), but changing the recordsource, you basically have a recordset no?

 

[qwertyjjj]

Okay, but I thought a recordset was different to a recordsource?
Doesn't the recordsource just refer to some SQL rather than actually retireving a dataset ?

 

[FoFa]

Yes it does, but if you don't retrieve it, what are you doing then?
If just dynamically populating say a combobox, there is also a property on the combobox (something index I think) that tells you how many items are populated at that time (base zero I think).

 

[qwertyjjj]

I'm not retrieving a dataset though, I'm just identifying which query to run for the form.
So in my VBA code I might have something like Form.Recordsource = "Query1"
From that, I don't think I can work out how many records it has ?