Append Query Count

[jandrade15]

Hi Everyone,

I Have a query that appends data to another table. the criteria is the date field and it prompts for a parameter value dialog box. after I enter the date, I would like for a msgbox to appear and tell me how many records it will be appending. Im running a few queries simultaneously on a macro and I have the setwarnings on "off" mode.

Thanks for any help on guiding me in the right direction!

 

[HAMMAMABUARQOUB]

in the first place you should use a variable that stores the count of the records that match the date criteria, b4 even being apended, but how! use the forms ...as you usng macros to run simultaneous queries, use a line of one macro to open a form with and pass the count to it then use the same macro to dispaly the msg

 

[neileg]

I would use DCount() to get the number of records.

It's not a good idea to turn the warnings off in a macro since you can't implement your own error handling in a macro. This means you are flying blind.

 

[HAMMAMABUARQOUB]

I would use DCount() to get the number of records.

but Dcount() can be helpful if we have a table or query stored as a view.. not just a selection of unknown count records and non-name query...
this lead me to do so:
creat a form that takes its RecordSource from the SQL statement of the query talking about..
use the Form.RecordSetClone.RecordCount code to count...
regards

 

[neileg]

but Dcount() can be helpful if we have a table or query stored as a view.. not just a selection of unknown count records and non-name query...
this lead me to do so:
creat a form that takes its RecordSource from the SQL statement of the query talking about..
use the Form.RecordSetClone.RecordCount code to count...
regards

I don't understand. The OP doesn't say that this is an unnamed query. You can use the same parameters used for the query and use them in a Dcount statement before you run the query.

 

[jandrade15]

Thanks guys! i'll be giving this a shot right now and i'll post and let you know how it goes... Again Thanks for your input!

 

[jandrade15]

Ok im trying to make this work but im a little stuck.... here are some more specifics:
I hava a macro running two queries one to delete a temp table and one to append to that temp table. I have converted the macro to vb and im trying to insert the dcount function but im not sure how to make it work??? Below is the code I have:

Function Mail_Merge_EXP()
On Error GoTo Mail_Merge_EXP_Err

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Delete_Exp_Table", acViewNormal, acEdit
    DoCmd.OpenQuery "Expired_Data_Transfer", acViewNormal, acEdit
    
    DoCmd.SetWarnings True


Mail_Merge_EXP_Exit:
    Exit Function

Mail_Merge_EXP_Err:
    MsgBox Error$
    Resume Mail_Merge_EXP_Exit

End Function

How could I get the following to happen: Right after the "expired-data-transfer" query give me a record count msgbox for the records in the temp table where the data is being appended to? Sorry i'm a bit new at this, any general code that I may modify will help enormously!! Thanks a million!!!