Help with code

Hi All,

Access 2002 using Windows XP

I am pretty new to writing code so please bear with me. I have some code
which exectues an append and a select query. Both queries require the user
to enter a date. Becaue of this, the same information has to be entered
twice which I would like to avoid.

Is there a way using code, that I can prompt the user to enter the date and
save their input as a variable? I'm assuming that by doing it this way, I
can run the SQL's from within the code and use the stored variable as the
SQL criteria rather than calling the stored queries?

Or maybe there's a completly better/easier way?

Any help on this would be much appreciated.

Regards,

Mark

re: Help with code

Provide a little unbound form where the user can enter the values.

In the Criteria row of your query, you can refer to:
[Forms].[Form1].[Textbox1]
or if you execute the query in code, you can concatenate the value of the
text box into the string.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark" <mark.reed75@ntlworld.com> wrote in message
news:x7ngd.148$NM.30@newsfe6-gui.ntli.net...[color=blue]
> Hi All,
>
> Access 2002 using Windows XP
>
> I am pretty new to writing code so please bear with me. I have some code
> which exectues an append and a select query. Both queries require the user
> to enter a date. Becaue of this, the same information has to be entered
> twice which I would like to avoid.
>
> Is there a way using code, that I can prompt the user to enter the date
> and save their input as a variable? I'm assuming that by doing it this
> way, I can run the SQL's from within the code and use the stored variable
> as the SQL criteria rather than calling the stored queries?
>
> Or maybe there's a completly better/easier way?
>
> Any help on this would be much appreciated.
>
> Regards,
>
> Mark
>
>
>[/color]

re: Help with code

"Mark" <mark.reed75@ntlworld.com> wrote:
[color=blue]
> Or maybe there's a completly better/easier way?[/color]

Hi Mark.

One approach would be to have your users input the reuired value into a
text box on a (dialogue) form and then reference the text box in your
stored query's criteria, ie in query grid's criteria line for the field in
question, enter [Forms]![MyForm]![txtMyTextBox] (where "MyForm" is the name
of the form and "txtMyTextBox" is the name of the text box).

If you enter this into both queries and call the queries from code whilst
"MyForm" is open, then that should work.

Regards,
Keith.
www.keithwilby.com

re: Help with code

Thankyou both for your quick response.

I understand what you are saying about using the unbound form to retrieve
the value but what I dont get how to do is open the form mid way through my
code when I need it, pause the code until the information has been input on
the form and then resume the code where I can then reference their input???

Thanks again,

Mark

"Mark" <mark.reed75@ntlworld.com> wrote in message
news:x7ngd.148$NM.30@newsfe6-gui.ntli.net...[color=blue]
> Hi All,
>
> Access 2002 using Windows XP
>
> I am pretty new to writing code so please bear with me. I have some code
> which exectues an append and a select query. Both queries require the user
> to enter a date. Becaue of this, the same information has to be entered
> twice which I would like to avoid.
>
> Is there a way using code, that I can prompt the user to enter the date
> and save their input as a variable? I'm assuming that by doing it this
> way, I can run the SQL's from within the code and use the stored variable
> as the SQL criteria rather than calling the stored queries?
>
> Or maybe there's a completly better/easier way?
>
> Any help on this would be much appreciated.
>
> Regards,
>
> Mark
>
>
>[/color]

re: Help with code

Lots of solutions, but try this:

In your code, before the queries are run:
DoCmd.OpenForm "MyForm", WindowMode:=acDialog
That pauses the code until the user dismisses the dialog.

Now in the Ok button of the form:
Me.Visible = False
This hides the dialog from the user, but leaves it open for your code to
read.

At the end of your code:
DoCmd.Close acForm, "MyForm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark" <mark.reed75@ntlworld.com> wrote in message
news:fIngd.31$zG6.27@newsfe4-gui.ntli.net...[color=blue]
> Thankyou both for your quick response.
>
> I understand what you are saying about using the unbound form to retrieve
> the value but what I dont get how to do is open the form mid way through
> my code when I need it, pause the code until the information has been
> input on the form and then resume the code where I can then reference
> their input???
>
> Thanks again,
>
> Mark
>
> "Mark" <mark.reed75@ntlworld.com> wrote in message
> news:x7ngd.148$NM.30@newsfe6-gui.ntli.net...[color=green]
>> Hi All,
>>
>> Access 2002 using Windows XP
>>
>> I am pretty new to writing code so please bear with me. I have some code
>> which exectues an append and a select query. Both queries require the
>> user to enter a date. Becaue of this, the same information has to be
>> entered twice which I would like to avoid.
>>
>> Is there a way using code, that I can prompt the user to enter the date
>> and save their input as a variable? I'm assuming that by doing it this
>> way, I can run the SQL's from within the code and use the stored variable
>> as the SQL criteria rather than calling the stored queries?
>>
>> Or maybe there's a completly better/easier way?
>>
>> Any help on this would be much appreciated.
>>
>> Regards,
>>
>> Mark[/color][/color]

re: Help with code

"Mark" <mark.reed75@ntlworld.com> wrote:
[color=blue]
> what I dont get how to do is open the form mid way through my
> code when I need it, pause the code until the information has been
> input on the form and then resume the code where I can then reference
> their input?[/color]

It's difficult to say without more info, but at a guess, can you initiate
the code from an OK button on the form you use to collect the user input?
It doesn't actually matter when you collect the user input as long as the
form is still open when the queries run. The code in your OK button could
be along the lines of:

Call MyProcedure 'This runs the queries
DoCmd.Close acForm, Me.Name

Regards,
Keith.

re: Help with code

I cannot thank you both enough. The code works exactly how I want it to now
and that is one less headache I have to deal with.

Thanks again,

Mark

"Mark" <mark.reed75@ntlworld.com> wrote in message
news:fIngd.31$zG6.27@newsfe4-gui.ntli.net...[color=blue]
> Thankyou both for your quick response.
>
> I understand what you are saying about using the unbound form to retrieve
> the value but what I dont get how to do is open the form mid way through
> my code when I need it, pause the code until the information has been
> input on the form and then resume the code where I can then reference
> their input???
>
> Thanks again,
>
> Mark
>
> "Mark" <mark.reed75@ntlworld.com> wrote in message
> news:x7ngd.148$NM.30@newsfe6-gui.ntli.net...[color=green]
>> Hi All,
>>
>> Access 2002 using Windows XP
>>
>> I am pretty new to writing code so please bear with me. I have some code
>> which exectues an append and a select query. Both queries require the
>> user to enter a date. Becaue of this, the same information has to be
>> entered twice which I would like to avoid.
>>
>> Is there a way using code, that I can prompt the user to enter the date
>> and save their input as a variable? I'm assuming that by doing it this
>> way, I can run the SQL's from within the code and use the stored variable
>> as the SQL criteria rather than calling the stored queries?
>>
>> Or maybe there's a completly better/easier way?
>>
>> Any help on this would be much appreciated.
>>
>> Regards,
>>
>> Mark
>>
>>
>>[/color]
>
>[/color]