By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,595 Members | 3,721 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,595 IT Pros & Developers. It's quick & easy.

Help with code

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
"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
Nov 13 '05 #3

P: n/a
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

Nov 13 '05 #4

P: n/a
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

Nov 13 '05 #5

P: n/a
"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.
Nov 13 '05 #6

P: n/a
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


Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.