Edit a Query in VBA...

[dodgyone]

I am using a Word document mail merge which uses a query in the DB. I use a command button to load up the Word doc but I firstly have to set the query criteria to the right date.

I have added an input box first which asks the user to enter their date. I would like to be able to take this value and update a field in the query in the criteria row and then save this (all without viewing the query design window). And finally open the Word doc which then uses this query to build the letters.

I've tried various querydefs etc but I'm getting a little confused...

 

[KyleS]

Hi,
The input box you mentioned, this is a standard VB input box and not something you've created? What you could do is set the criteria line = GetCriteria()

Then use this Fucntion:
Function GetCriteria() as String '(or whatever variable you need)
GetCriteria = InputBox("YOUR MESSAGE HERE&quot
End Function

Then take the input box out of your other code and this should work for you...It wil only ask once and the query will run... Kyle :

 

[B827]

As a simple solution could you put in the criteria of the field something like

[Please Enter the Date]

this will prompt on doing the mail merge? Sandy

 

[KyleS]

Sandy, that is a better solution if dodgyone only needs the date for the query, I was (for some reason which escapes me now...) thinking dodgyone would need the date for something else as well, so to do that without asking the question twice you could use a variation on this function...

But yes, the criteria prompt in the query would work just as well. Kyle :

 

[dodgyone]

Thanks for the suggestion but I already do that with a message encasd between []. What I wanted was to place the criteria in the query permanently rather relying on the user to enter a date in this fashion.

Is the smoething in querydefs for this?

Many thanks....