What is entered in a parameter pops up in the Form

[joe_madeira]

Hey,

I made a form using a field from a query that uses a parameter. When I enter this parameter, for example a date, how do I make the date appear on the form?
Is this possible?

Any help is much appreciated!

 

[boblarson]

1. I would not use parameter prompts in queries. Use a form for user input.

2. If using a form, you can then just reference the form and the control on the form and not have a problem (as long as the form remains open).

Read this for other good reasons why to avoid parameter prompts:
http://www.tek-tips.com/faqs.cfm?fid=6763

 

[joe_madeira]

I've only been using Microsoft Access for 2 weeks now and well I know no other way. My teacher in school taught us this.

How would I do this without using a parameter? What do I use? A query? A form? A combo box?

If you have the time can you explain to me how I would do this?

Thanks for the advice!

 

[boblarson]

Joe:

So, can you explain a bit as to what the query and form are meant to do and how you envision the interaction between the user and the form? Are you trying to open a form with just the records that are for a certain date? If so, you can have your criteria form with a text box on it to enter the date and then you can open the form in a click event on a button (using VBA is very flexible and we can help get you there if you wish) and you can simply filter the form by opening it like:

DoCmd.OpenForm "FormNameHere", acNormal, WhereCondition:="[DateFieldNameHereInBrackets]=#" & Me.YourTextBoxNameHere & "#"

Now to explain that part. You would put a button on your criteria form to open the form. And instead of following the wizard you would just click cancel when the wizard came up. Then while on the criteria form in design view you would click on the properties dialog and on the Events tab which is there. Clicking into the ON CLICK event property an ellipsis (...) should appear to the right. First, select [EVENT PROCEDURE] from the dropdown that happens when clicking into the click property and then click on the ellipsis and the VBA window should open to something like this (assuming your Command Button is named Command1):

Private Sub Command1_Click()
 
End Sub

You would put the code I gave you first off, in between those two lines. And you will need to change

FormNameHere

to the actual name of your form (it does need to be in the quotes like I have in the example).

And you would change

DateFieldNameHereInBrackets

to the actual name of the date field you have in the form you are opening that you want to limit the records to.

Also, the TextBoxNameHere needs to be changed to the name of the text box on the criteria form. And the criteria form should NOT have a record source and the text box should not be bound to a field.

And, the Octothorpes (#) are required for dates and the ME is a programming shortcut which refers to the current class object the code that the ME is used in is in(in this case the criteria form).

I hope that helps.

 

[joe_madeira]

boblarson,

Wow man thanks! The command button works fine its just when I enter a date into the criteria form text box it doesn't correspond to the date that comes up in the actual form i'm trying to open. For example, i type in " 29/11/2011" and when the button is pressed the Order form opens but only shows records from today. Even if i write "30/11/2011" its the same. I know I have records for these dates. What am i doing wrong?

BTW thank you so much! your better than my teacher in college.

Quick question: by not using parameters would that show sophistication to the examiners marking my project? Should I use both parameters and a criteria form for different things to show the depth of my knowledge?

Again, Thanks a bunch! took your time just to help me out =)
how do we change the world? one Act of Random Kindness.

 

[boblarson]

Oh, your dates need to be passed to Access in U.S. date format (thank Microsoft for that one). So modify your code to something like this:

DoCmd.OpenForm "FormNameHere", acNormal, WhereCondition:="[DateFieldNameHereInBrackets]=#" & Format(Me.YourTextBoxNameHere,"mm\/dd\/yyyy") & "#"

By the way, the \ in front of the / tells it to use the literal character, so as to avoid regional differences where the region settings on the computer might use the hyphen (-) in between.