Message Box to show delete query info.

[CutAndPaste]

I've created an append and delete query combination to move records between 2 dates to an archive database. I've also got a message box that pops up prior to this query executing to ask for confirmation from the user before this action is carried out. I'd like to include more information in the message box, something like "You are about to archive X records between dd/mm/yyyy and dd/mm/yyyy" Do you want to proceed?"

I'm guessing that I somehow reference the [StartDate] and [EndDate] fields on my form that filter the query, but am not so sure of the syntax of this within a message box. Also getting a count of the records is needed.

So part of the message box is going to look like:

"You are about to archive X records between "& Forms![frmReport]![StartDate]&" and "& Forms![frmReport]![EndDate]&" Do you want to proceed?"

Am I on the right track here? The Record Count is still evading me though...


[This message has been edited by CutAndPaste (edited 07-31-2001).]

 

[KDg]

Hi CAP,

your msgbox looks fine - if you're calling it from within the form you can use me.StartDate and me.EndDate instead, if you're using it in a seperate function then you may want pass the dates as parameters to make it re-usable. FOr Recordcount - this only knows about the last record that it's been to. This means that you need to do RecordSet.MoveLast if you have a select statement in order to find out how many records you have,

HTH

Drew

 

[Atomic Shrimp]

to get a count of the records you're moving, create a select query with the same criteria as your append/delete queries, then use Dcount to count the records in it, like this:

"You are about to archive "& Dcount("*","MyCountingQuery") & " records between "& Forms![frmReport]![StartDate]&" and "& Forms![frmReport]![EndDate]&" Do you want to proceed?"

 

[Atomic Shrimp]

I've just noticed that you are asking about how to use the contents of a form field as criteria in a query; this has been covered quite a few times on the board here before, but briefly:

In the Query Desgin Grid, right-click in the Criteria row (below the record date) and select 'build', then you can use the expression builder to find and reference the text box on your form; it will end up inserting a reference something like:

[Forms]![FormName]![FromDateTextBox]

You need to change this to something like:

>=[Forms]![FormName]![FromDateTextBox] And <=[Forms]![FormName]![ToDateTextBox]

 

[CutAndPaste]

Thanks for this, it was the DCount part that I was struggling with (I'm fine on using query parameters driven by forms and the Me. syntax).

I'll try this later today!

Cheers.

[This message has been edited by CutAndPaste (edited 08-01-2001).]