How to Create Parameter in Access Form

[Deniese]

Greetings,

I’m new to Access and would appreciate any help that anyone can give me on
this.

I have a text box in my form that that I set the control source to pull data
(3-digit control numbers) from the field “Control_No†from my “controlsâ€
table.

I would like to create a parameter (Between [Enter Starting Control No] AND
[Enter Ending Control No]) so that when a user opens my form they will be
prompted to enter the starting and ending control numbers (3-digit numbers)
and the form will only return records that include the numbers that the user
specified.

I have spent a few days researching this on the Web to no avail so any
advice would be greatly appreciate.

Many Thanks,

Deniese

 

[Allen Browne]

In your Controls table, is Control_No a Number field? Or is it a Text field?

If it is a number field, create a query.
In the Criteria row under Control_No, enter:
Between [Enter Starting Control No] AND [Enter Ending Control No]
To be certain Access understands this correctly, choose Parameters on the
Query menu. Access opens the parameter dialog. Enter 2 rows:
[Enter Starting Control No] Long
[Enter Ending Control No] Long
Save the query. Test. It will ask for the parameter.

Now open your form in design view, and set its RecordSource property to the
name of the query you just saved.

 

[Deniese]

Hi Allen,

Many thanks for your response.

Before I posted my initial question, I did create a query as you suggested
but hadn’t chosen Parameters on the Query menu as you suggested and when I
ran the query it did request the parameters, however, my problem was getting
it to work with the form.

I forgot that I had to change from a number field to a text field because
there were a few records that had alphanumeric values in that field. I
switched back to a number field to test and followed your instructions
exactly, however, when the query requested the parameters, it now requested
them twice before returning the values. I don’t know if it matters, but in
the parameter dialog box Access automatically appended extra brackets so the
query parameters now look like this:

[[Enter Starting Control No]] Long Integer
[[Enter Ending Control No]] Long Integer

In the Control Source (I didn’t see a Record Source as you mentioned)
property of the form I used the expression builder to tie the "control_no"
field to the “parameter_query†I just created. Once I exited expression
builder it stripped the brackets so Control Source now states
“=Parameter_Query!Control_Noâ€. When I opened the form it didn’t request any
parameters, and it displayed “#Name?†in the control_no field. This is the
same problem I was having before. I know the brackets are important as it
made a difference in something else I was having a problem with, but I’m not
sure where they should go. I’ve seen them like
[Parameter_Query]![Control_No] and [Parameter_Query!Control_No], however,
enclosed in double brackets as I mentioned earlier is new to me.

Anyway, it looks like I need this to work with a text box so I’m not sure if
the instructions would be similar or more complex, but I’d really appreciate
it if you could help me out with this. I’ve already asked three people that
know Access at the college that I attend and they weren’t able to assist me.

Thanks Much,

Deniese

In your Controls table, is Control_No a Number field? Or is it a Text field?

If it is a number field, create a query.
In the Criteria row under Control_No, enter:
Between [Enter Starting Control No] AND [Enter Ending Control No]
To be certain Access understands this correctly, choose Parameters on the
Query menu. Access opens the parameter dialog. Enter 2 rows:
[Enter Starting Control No] Long
[Enter Ending Control No] Long
Save the query. Test. It will ask for the parameter.

Now open your form in design view, and set its RecordSource property to the
name of the query you just saved.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I’m new to Access and would appreciate any help that anyone can give me on
this.

I have a text box in my form that that I set the control source to pull
data
(3-digit control numbers) from the field “Control_No†from my “controlsâ€
table.

I would like to create a parameter (Between [Enter Starting Control No]
AND
[Enter Ending Control No]) so that when a user opens my form they will be
prompted to enter the starting and ending control numbers (3-digit
numbers)
and the form will only return records that include the numbers that the
user
specified.

I have spent a few days researching this on the Web to no avail so any
advice would be greatly appreciate.

 

[Allen Browne]

The data type does matter. You don't need to declare the parameters if the
field is Text, because that's the default type. You do need to delcare them
for Number or Date fields, so Access doesn't treat them as text.

The extra square brackets are a worry. Might be why you're being asked
twice.

RecordSource is a propety of the *form*, not control. (The title bar of the
Properties box will read "Form" when you are setting the properties of the
form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Many thanks for your response.

Before I posted my initial question, I did create a query as you suggested
but hadn’t chosen Parameters on the Query menu as you suggested and when
I ran the query it did request the parameters, however, my problem was
getting
it to work with the form.

I forgot that I had to change from a number field to a text field because
there were a few records that had alphanumeric values in that field. I
switched back to a number field to test and followed your instructions
exactly, however, when the query requested the parameters, it now
requested
them twice before returning the values. I don’t know if it matters, but
in
the parameter dialog box Access automatically appended extra brackets so
the
query parameters now look like this:

[[Enter Starting Control No]] Long Integer
[[Enter Ending Control No]] Long Integer

In the Control Source (I didn’t see a Record Source as you mentioned)
property of the form I used the expression builder to tie the "control_no"
field to the “parameter_query†I just created. Once I exited expression
builder it stripped the brackets so Control Source now states
“=Parameter_Query!Control_Noâ€. When I opened the form it didn’t request
any
parameters, and it displayed “#Name?†in the control_no field. This is
the
same problem I was having before. I know the brackets are important as it
made a difference in something else I was having a problem with, but I’m
not
sure where they should go. I’ve seen them like
[Parameter_Query]![Control_No] and [Parameter_Query!Control_No], however,
enclosed in double brackets as I mentioned earlier is new to me.

Anyway, it looks like I need this to work with a text box so I’m not sure
if
the instructions would be similar or more complex, but I’d really
appreciate
it if you could help me out with this. I’ve already asked three people
that
know Access at the college that I attend and they weren’t able to assist
me.

Thanks Much,

Deniese

In your Controls table, is Control_No a Number field? Or is it a Text
field?

If it is a number field, create a query.
In the Criteria row under Control_No, enter:
Between [Enter Starting Control No] AND [Enter Ending Control No]
To be certain Access understands this correctly, choose Parameters on the
Query menu. Access opens the parameter dialog. Enter 2 rows:
[Enter Starting Control No] Long
[Enter Ending Control No] Long
Save the query. Test. It will ask for the parameter.

Now open your form in design view, and set its RecordSource property to
the
name of the query you just saved.

I’m new to Access and would appreciate any help that anyone can give
me on this.

I have a text box in my form that that I set the control source to pull
data
(3-digit control numbers) from the field “Control_No†from my
“controlsâ€
table.

I would like to create a parameter (Between [Enter Starting Control No]
AND
[Enter Ending Control No]) so that when a user opens my form they will
be prompted to enter the starting and ending control numbers (3-digit
numbers)
and the form will only return records that include the numbers that the
user
specified.

I have spent a few days researching this on the Web to no avail so any
advice would be greatly appreciate.

 

[Deniese]

I get it now, I needed to put my select statement in the report properties
instead of the control properties. Everything works now. One million thank
yous Allen.

Deniese

The data type does matter. You don't need to declare the parameters if the
field is Text, because that's the default type. You do need to delcare them
for Number or Date fields, so Access doesn't treat them as text.

The extra square brackets are a worry. Might be why you're being asked
twice.

RecordSource is a propety of the *form*, not control. (The title bar of the
Properties box will read "Form" when you are setting the properties of the
form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Many thanks for your response.

Before I posted my initial question, I did create a query as you suggested
but hadn’t chosen Parameters on the Query menu as you suggested and when
I ran the query it did request the parameters, however, my problem was
getting
it to work with the form.

I forgot that I had to change from a number field to a text field because
there were a few records that had alphanumeric values in that field. I
switched back to a number field to test and followed your instructions
exactly, however, when the query requested the parameters, it now
requested
them twice before returning the values. I don’t know if it matters, but
in
the parameter dialog box Access automatically appended extra brackets so
the
query parameters now look like this:

[[Enter Starting Control No]] Long Integer
[[Enter Ending Control No]] Long Integer

In the Control Source (I didn’t see a Record Source as you mentioned)
property of the form I used the expression builder to tie the "control_no"
field to the “parameter_query†I just created. Once I exited expression
builder it stripped the brackets so Control Source now states
“=Parameter_Query!Control_Noâ€. When I opened the form it didn’t request
any
parameters, and it displayed “#Name?†in the control_no field. This is
the
same problem I was having before. I know the brackets are important as it
made a difference in something else I was having a problem with, but I’m
not
sure where they should go. I’ve seen them like
[Parameter_Query]![Control_No] and [Parameter_Query!Control_No], however,
enclosed in double brackets as I mentioned earlier is new to me.

Anyway, it looks like I need this to work with a text box so I’m not sure
if
the instructions would be similar or more complex, but I’d really
appreciate
it if you could help me out with this. I’ve already asked three people
that
know Access at the college that I attend and they weren’t able to assist
me.

Thanks Much,

Deniese

In your Controls table, is Control_No a Number field? Or is it a Text
field?

If it is a number field, create a query.
In the Criteria row under Control_No, enter:
Between [Enter Starting Control No] AND [Enter Ending Control No]
To be certain Access understands this correctly, choose Parameters on the
Query menu. Access opens the parameter dialog. Enter 2 rows:
[Enter Starting Control No] Long
[Enter Ending Control No] Long
Save the query. Test. It will ask for the parameter.

Now open your form in design view, and set its RecordSource property to
the
name of the query you just saved.


I’m new to Access and would appreciate any help that anyone can give
me on this.

I have a text box in my form that that I set the control source to pull
data
(3-digit control numbers) from the field “Control_No†from my
“controlsâ€
table.

I would like to create a parameter (Between [Enter Starting Control No]
AND
[Enter Ending Control No]) so that when a user opens my form they will
be prompted to enter the starting and ending control numbers (3-digit
numbers)
and the form will only return records that include the numbers that the
user
specified.

I have spent a few days researching this on the Web to no avail so any
advice would be greatly appreciate.