Input Form (Query?)

[AJ]

I have a parameter query form (with 4 fields) that I would like to open and
have a user input 4 values and then I will run a VBA code I wrote.

Can I use the values from the form as global variables throughout my VBA code?

I set up a on click procedure but do not know how to capture the values in
the fields. I assume this is fairly easy but cannot seem to find the proper
syntax to do this.

Also is there a easy way to run a dlookup or query on the value to make sure
it is a valid input value?

Any examples would be great. Thanks.

 

[John W. Vinson]

I have a parameter query form (with 4 fields) that I would like to open and
have a user input 4 values and then I will run a VBA code I wrote.

Can I use the values from the form as global variables throughout my VBA code?

Well... you can but it's not a good idea. Globals lose their value if there is
a break or if the code is interrupted.

Why not just directly reference the Form as criteria?

=[Forms]![NameOfYourForm]![NameOfTheControl]

as a criterion in a Query will do the job.

I set up a on click procedure but do not know how to capture the values in
the fields. I assume this is fairly easy but cannot seem to find the proper
syntax to do this.

Also is there a easy way to run a dlookup or query on the value to make sure
it is a valid input value?

Ummm... "valid" in what sense? Returning records? Correct datatype? or what?

John W. Vinson [MVP]

 

[AJ]

Thanks John.
In referring to making sure the data is valid, lets say for example that
they are entering a departmentid, Can I run a select to make sure it exists
in my department table (in essence a valid departmentid)? If it does not I
can kick out a msgbox that makes them enter a different departemnt.
Thank again for all your help, you and all the other people in this forum
really do make programming a lot less stressful!!

I have a parameter query form (with 4 fields) that I would like to open and
have a user input 4 values and then I will run a VBA code I wrote.

Can I use the values from the form as global variables throughout my VBA code?

Well... you can but it's not a good idea. Globals lose their value if there is
a break or if the code is interrupted.

Why not just directly reference the Form as criteria?

=[Forms]![NameOfYourForm]![NameOfTheControl]

as a criterion in a Query will do the job.

I set up a on click procedure but do not know how to capture the values in
the fields. I assume this is fairly easy but cannot seem to find the proper
syntax to do this.

Also is there a easy way to run a dlookup or query on the value to make sure
it is a valid input value?

Ummm... "valid" in what sense? Returning records? Correct datatype? or what?

John W. Vinson [MVP]

 

[John W. Vinson]

Thanks John.
In referring to making sure the data is valid, lets say for example that
they are entering a departmentid, Can I run a select to make sure it exists
in my department table (in essence a valid departmentid)? If it does not I
can kick out a msgbox that makes them enter a different departemnt.

Well... you can do it a step better than that. Rather than making them type in
a department in a Textbox and slapping their hands if they get it wrong, use
an unbound Combo Box on your form, selecting only valid departments, and use
that control as the criterion.

Thank again for all your help, you and all the other people in this forum
really do make programming a lot less stressful!!

Glad to be able to help. Just so's you know we're all volunteers here (even
the occasional Microsoft employee is donating their time to answer in the
groups).

John W. Vinson [MVP]

 

[AJ]

Thanks John -
That works for one of the fields but the other field (departmentid was an
example) is going against a table that has approx. 20k rows so I think some
type of select to verify the field would work better.
Any thoughts?

 

[John W. Vinson]

Thanks John -
That works for one of the fields but the other field (departmentid was an
example) is going against a table that has approx. 20k rows so I think some
type of select to verify the field would work better.

About all I can suggest is opening the results form and checking to see if
there are any records in its RecordsetClone in its Load event - and cancel the
load if there are none, with an explanatory message.

A combo *can* have 64K records, but 20000 is way too big, I agree.

John W. Vinson [MVP]