Repeat Prompt

[accessewbie]

I need help identifying the best way to get the results I need. I have a table: it has a field labeled Customer Location and another field labeled Support Location. Basically we want our users to be prompted for the Customer Location and then it finds the match in the table to pull up the Support Location. I've got the query built that does the prompt and it works.
My problem is that the user will need to enter several locations and at the end see the full list. Right now, they get prompted and the answer appears. Is there a way to have a continous prompt until they want to stop? Or is there a way to do one prompt - get the answer - do another prompt - but this time have the answer listed with the previous answer? I hope this makes sense. Thanks for the help!

 

[lupins46]

You can't do it in the way you are describing.

You need to build a list of the required locations and then use the whole list in a single query.

The simplest way to build the list would be to create a table to contain it, and a continuous form to enter the values you want.
Then use this list table and your main table in a query and join them on the location value to get the records you need.

Obviously, you would also need a process to empty the list/table at the start of each session.

 

[accessewbie]

Could you lead me in the direction of any useful reading material or site to help me out? Your answer is helpful, but I'm not really sure where to start or what to do. I have the table but at a loss on beginning the form part.

 

[lupins46]

You can create the form using the form wizard.
Use the Autoform tabular option.

And as I said before, you will need a query to delete all existing records from the table before you open the form to enter a new list.

 

[accessewbie]

Let me just say I truly appreciate your patience. I am really learning here. So I read your suggestions and here's my comments.

The simplest way to build the list would be to create a table to contain it, and a continuous form to enter the values you want.
So I created a form based off my query that has the prompt for the user to enter the location. Is that correct? However, it prompts only once on the form so not sure how to get the continous prompt. Deafult view does show Continous Forms

Then use this list table and your main table in a query and join them on the location value to get the records you need.
-The table I created and the form? Confused here.

Obviously, you would also need a process to empty the list/table at the start of each session.
- Not sure what to do here either.

Again sorry if I'm missing the boat here.

 

[lupins46]

No.
You need to create a listo f required locations. That list goes in its own table, which I shall call RequiredLocations. It has one field, which I shall call rLocation.
To enter items in this table you create a continuous formm using the form wizard, and autoform tabular.

You now have two tables; RequiredLocations and the original table which I shall just call Locations.
You create a query based on these two tables. The tables should be joined rLocation to [Customer Location]. In the output grid you put one of these two fields and the Support location.

You run this query when you have entered the list of required locations. There are no prompts and no query criteria involved.

 

[accessewbie]

I think I understand the logic behind this all!! The user inputs into the form I made. Then they run the query which looks into the original table (Locations) and finds the match. It works and I thank you for your patience.


Now I have to ask how do I go about the part where you state, "And as I said before, you will need a query to delete all existing records from the table before you open the form to enter a new list"? I truly thank you for all the help and not getting frustrated!!!!

 

[lupins46]

You need another query.
Start a new query based on RequiredLocations.
Add any field to the output grid.
Choose Query>DeleteQuery from the menu.

That's it.

 

[accessewbie]

Now I guess I am being greedy by asking another question. We all make typos and I know that will be a problem for my team. So is there a way to have an error message or a line to read "location not found" if what they type in the form doesn't match up to the table? I first thought it would be an IIF statement in the query but that didn't work. Any ideas? I promise that's it for my requests.