Help with code

[Mark]

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

[Allen Browne]

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" <ma*********@ntlworld.com> wrote in message
news:x7*************@newsfe6-gui.ntli.net...

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

[Keith Wilby]

"Mark" <ma*********@ntlworld.com> wrote:

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

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

[Mark]

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" <ma*********@ntlworld.com> wrote in message
news:x7*************@newsfe6-gui.ntli.net...

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

[Allen Browne]

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" <ma*********@ntlworld.com> wrote in message
news:fI*************@newsfe4-gui.ntli.net...

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" <ma*********@ntlworld.com> wrote in message
news:x7*************@newsfe6-gui.ntli.net...

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

[Keith Wilby]

"Mark" <ma*********@ntlworld.com> wrote:

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?

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.

[Mark]

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" <ma*********@ntlworld.com> wrote in message
news:fI*************@newsfe4-gui.ntli.net...

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" <ma*********@ntlworld.com> wrote in message
news:x7*************@newsfe6-gui.ntli.net...

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