Entering Parameter Values - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Entering Parameter Values

Samantha Waterman

2 years ago

Hello!

I just had a question about making parameter values. Are you able to add your own and still keep the pre-existing criteria needed?

So, my example: I've created a report that will bring up employees and their previous trainings - it works when I print preview it by asking for a parameter value "Forms!TrainingF!TrainingID" - and when I enter the TrainingID it brings up everything I need it to. My plan/idea is to have this report button on my Main Menu but I was wanting to make it say "Enter Training Number" to make it easier for my users who just want to print the report without having to go through all the forms just to get to it.

Is there a way to do that and still keep the criteria I have for it in the query I created to make this report? I hope that all makes sense - but as always, thank you all so much for all you do!!

Adam Schwanz @Reply

2 years ago

InputBox might cover it

On your forms button, just do something like this, the syntax might need to change if trainingID is a text

Dim X

X = Inputbox("Enter Training ID")

DoCmd.OpenReport "YourReport", acViewPreview, , "TrainingID=" & X

You would probably want to wrap that in an if statement too to check that X has a value before trying to open the report or it would error, which syntax would depend on if trainingID is a number or a short text

Samantha Waterman OP @Reply

2 years ago

First, thank you! Whether I can figure this out and make it work or not I still appreciate your help!

Second, I tried it how you wrote it, no dice - tried an IF statement as you also suggested, still no dice.

It just gives me an error "Compile Error: Argument Not Optional" and then highlights the **docmd.openreport...** line from your example.

And apologies for not stating this to begin with, but to answer your question: yes, my TrainingID is a number! (and if that is the whole problem with the error then please forgive my very limited knowledge on how to make the syntax different to account for that! I'm still very much a newbie! Lol)

	Richard Rost @Reply 2 years ago
Just make a field on your form that has the ID in it. Value From a Form

Samantha Waterman OP @Reply

2 years ago

Thank you, Richard! Yes, I do have all of that already and it works great thanks to you and your wonderful teaching skills! My users can enter or create an employee and then they are able to add a "training" specified to that employee and then from there, generate a report to print out with all of that information on it (just like your Invoice video). The query I made to bind to this report works perfect with the criteria specified in my original comment - - I was just wondering if it was possible to prompt my users to enter the "Training Number" (which would of course be the "TrainingID", but they wouldn't be aware of that as a front end user if that makes sense?) from the Main Menu basically to bypass opening all the forms just to get to it? Instead of them seeing the original Parameter Value if that makes sense too?

	Richard Rost @Reply 2 years ago
I'm not following.

	Samantha Waterman OP @Reply 2 years ago

	Richard Rost @Reply 2 years ago
I'm sorry... I'm still lost. I don't understand the nature of your tables and fields. Can you explain this to me as you would in a generic example to someone who doesn't understand your business... maybe use customers and orders like I do in my lessons?

Samantha Waterman OP @Reply

2 years ago

The above are screenshots from my original database - - so the question is, would I be able to make an intentional prompt if I put an additional "preview report" button on my main menu (have it say "Enter Violation Number") - - or would I have to make a whole different query just to do that? If that makes sense - - basically is an intentional prompt for an extra button possible from the same query without "adding" the prompt to the original button where it works without it?

Samantha Waterman OP @Reply

2 years ago

Yes, my apologies!

So let's say from your invoice video - - I have an invoice report that works fine with your invoice button where you put it on the Order form.
The question is, if put an extra invoice button on the Main Menu form - I know it will prompt for a parameter. Is there a way to put an "intentional parameter value" in the query used for the invoice button (like: [Enter Invoice Number]) without it making that same prompt on the Order form where it works?

Sorry, I'm trying to make it make sense like I can see it in my head!

Richard Rost @Reply

2 years ago

OK, so let's say you're trying to open up a customer report based on the current customer who's on the open customer form. Normally the query that the report is based on would look for Forms!CustomerF!CustomerID. I got that.

So now you want to add the ability to open the same customer report from the Main Menu WITHOUT having the customer form open. You want to prompt the user for the CustomerID instead, and then continue to open the same report.

Am I correct so far?

	Samantha Waterman OP @Reply 2 years ago
Yes, because I know that it will ask for the parameters because the Customer Form isn't open - - I just wanted to know if it was possible to make the parameter value prompt say "Enter Customer Number" instead of what it would normally say (Forms!CustomerF!CustomerID)

	Richard Rost @Reply 2 years ago
If I'm correct so far, you basically want the prompt that pops up to say something like "Enter Customer ID" instead of just "Forms!CustomerF!CustomerID"? Yes?

	Richard Rost @Reply 2 years ago
Haha we're typing at the same time. LOL. Hang on one min...

	Samantha Waterman OP @Reply 2 years ago
Lol, that's kinda cool - - but yes, I guess I'm asking if all of that is even a possibility? - without it asking for that same prompt where it works? (I feel like that would defeat the purpose of it being on the Customer form itself)

	Richard Rost @Reply 2 years ago
I know that it can be done with VBA. I'm trying to figure out a non-VBA solution for you. Hang tight for a few minutes.

	Samantha Waterman OP @Reply 2 years ago
Sure thing! And thank you so much! I appreciate all the help even though I'm horrible at explaining things! Lol! - - VBA or non-VBA, I'm learning it all at a snail's pace but once I understand it I can make it work!

	Richard Rost @Reply 2 years ago
OK, I futzed around with it for a few minutes - and even asked ChatGPT (which is normally useless with Access stuff) - and I can't come up with an easy NON-VBA solution, so I'll explain it in VBA for you... which isn't too hard. Easier to make a short video for you though... so stay tuned. Give me an hour.

	Samantha Waterman OP @Reply 2 years ago
Thank you so much, Richard! I truly appreciate it - take all the time you need, I know you and your awesome team are busy busy helping EVERYONE!! Lol so I can wait as long as needed!

	Richard Rost @Reply 2 years ago
Working on it now. :)

	Kevin Yip @Reply 2 years ago
My suggestion is just not use parameters. They create those prompts that you can't fully control, and the resulting VBA to get around it adds hassles to your design. It is so much simpler to just modify your form's or report's Filter property based on user inputs, which usually requires just one line of code: Me.Filter = "[CustomerID] = " & UserInput Me.FilterOn = True

	Richard Rost @Reply 2 years ago
Right but then you can no longer use the value on the CustomerF if it's open.

	Kevin Yip @Reply 2 years ago
Of course you can. E.g.: Me.Filter = "[CustomerID] = " & Forms!Form1!Field1

	Richard Rost @Reply 2 years ago
Right, but she's trying to avoid the user seeing "Forms!Form1!Field1". She want's a pretty prompt like "Enter the Customer ID." Going to show how to do it with an Input Box.

	Richard Rost @Reply 2 years ago
I tried for a while to figure out how to do it without VBA... and still not see the Form!Field parameter prompt.

	Kevin Yip @Reply 2 years ago
The need to make your own prompt is exactly why I suggested not using parameter queries, which require the exact prompt name used inside the query. Just use a regular query (for the record source), then prompt the user for input in your own way, then assign the user input to the form's ore report's filter.

	Richard Rost @Reply 2 years ago
Here you go, Samantha: Open Report 2 Ways

	Richard Rost @Reply 2 years ago
Which is exactly what I did... got rid of the parameter query and used a where condition in the Open Report. For the main menu button, I used an InputBox to ask the user for it. On the form, just supply the current ID. I was just trying to find a non-VBA method to BOTH use the form field if it's open OR ask a "friendly" prompt. Couldn't do it.

Samantha Waterman OP @Reply

2 years ago

I'm ecstatic to report that the VBA operates perfectly! I'm able to preview my report from my Main Menu just as I pictured it would work in my head once you taught me how! I'm so grateful to all of you for taking the time to try to understand and help me! - - Although I will probably have a lot more questions as I try to put all my legos together, so bear with me as I try to explain those too when they come! LOL - - I'm thankful either way!!!

Richard Rost @Reply

2 years ago

I'm glad everything worked out well for you.

Sometimes, the most challenging aspect for me is understanding the specific terms used by my students. I might not be familiar with your table names, field names, and how they connect, so if you have questions, try to help me help you... explain your database terminology in simple terms that I can relate to - like customers, contacts, orders - the concepts I cover in my videos. When I was a Developer, my biggest hurdle was grasping the customer's business to accurately translate it into database terms. Once we clear that hurdle, everything else tends to fall into place smoothly.

I actually wrote a whole article on this topic... All About Context

Samantha Waterman OP @Reply

2 years ago

Yes, I totally understand and agree - I think that a lot of the translation into the simple terms and concepts for questions that get asked when help is needed (at least from mine! Lol) lapse because the person needing help forgets that You have no idea what they're looking at! And in the same context what they're working on is all They see! But still, it's all appreciated and I'm still blown away that I got a whole video out of it! Lol so thanks again!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View