Forms instead of <Variable>: [Prompt] in queries/reports

[Canderel]

Ok, so now this is where it's getting more complicated... (though I guess this is where you guys usually start)

When you have a query, and you type in your inputbox thingy like so:

PeriodEnd: [Enter period end date dd/mm/yyyy: ]

And you can have multiple, then it will prompt you for several of them.

Now this is not very tidy, so I'd rather do it on a form. How can I display the form as the query/report runs, and insert the values entered into the form as the inputs into my variables? (This case PeriodEnd)

-Canderel

Grrr... I can't get the image to display.

 

[KenHigg]

Just ref the text box on the form in the criteria part of the query. Something like:

Like forms!myFormName!myTextBoxName


Does this help?
Ken

 

[Canderel]

Looks like it will run fine... but I haven't tested it.

What if I have several? How does it know only to continue once the form is closed?

-Canderel

 

[KenHigg]

Here's how I see it working; You do the form with the text box for the criteria. Then you run the report with a button on the form. When the report is finished runnig, the focus goes back to the form.

???
Ken

 

[Mile-O]

Okay, this looks like you are trying to add an extra (and unnecessary) step into querying.

The pop up box you are referring to is a Parameter Box and for each time you need a parameter in a query you will be asked for it with the box that states Enter Parameter Value.

If you want to continue with this method (not advised) then you can go to the Query -> Parameters option in the query design grid and enter the name of the parameter and its data type.

i.e.

Enter Month - Integer

In your query you might [Enter Month] in two different places. AS you have defined a parameter you will only be asked once for this value. Don't define a parameter and you will be asked once for every instance in the query.

However, the better practice is to use forms, controls, and their events.

The best method is to set up your form for selecting all the criteria you want. Have the query premade with the parameters referring to your form in the following syntax:

[Forms]![frmMyForm]![txtMyTextBox]

I typically put a command button on these forms to let Access know when I want to open the query. On the code behind these buttons' Click event I test that the query has records and, if so, opens in another form (or report) and if not I give a message to the user.

 

[KenHigg]

Is this being done in another thread?

???
Ken

 

[Canderel]

I think I understand what you mean...

At the moment I want(ed) to do it this way.

• Guy Clicks on Query
• I open form with parameters
• He selects the parameters
• Query continues

You are suggesting...

• Guy opens form... he specifies the parameters... he clicks 'query' button.
• Query runs, has all the parameters it needs.

Seems simpler... You just need to get the user to not want to click on the query itself, but open the form...

Do I understand you correctly? Basically write a whole GUI for you database via forms?

 

[KenHigg]

Are you asking me or SJ?

Ken

 

[Mile-O]

Do I understand you correctly? Basically write a whole GUI for you database via forms?

Yes, that's exactly it. I never let my users near any of the database's objects - tables, queries, modules, reports, or form design. Too dangerous.

 

[Canderel]

Sorry Ken, yes, it sprouted from another thread.

But this was getting way off topic, so I decided to rather start a new thread.

Your advice seem to run along the same veins.

Basically, I had this great idea... and it seems that it's not quite the route Access was designed for. I'm going to keep bumping my head if I keep going this way.

But I am teaching myself... well, you guys are teaching me... as I go along.

At this point I should give you my appreciation for the polite and helpfulness of the whole forum. I've not often come to a forum where the people help you so readily - a matter of minutes generally - and with such an amazing degree of expertise and insight.

I hope to become a regular... if my job keeps turning to Access DB's then I sure would.

-Canderel

 

[Canderel]

Problem with whole GUI's is that most of these DB's that I have to do is just quick'n'dirty tools to extract data from the bigger oracle DB where I have no rights and access to.

And we have to hire an external company to design every little thing we want if we want anything.

The problem is also that the oracle DB's tables are getting big and the data is not always clean. We want to sort out the mess a little by tangling the bits of spaggethi on our side.

Dunno if it's a losing battle though. But I was recently appointed to help clear things, so I'll see what I can do.

-Canderel

 

[KenHigg]

That's cool, been there.

Sounds like you may be fixing to make the jump from a casual Access user to a beginng db developer...

The form front end stuff is right on where you don't let users have the direct access to the objects. I was wondering why you needed to let a user get to a query in the first place. If they need the data to analyze, I would suggest you do a routine to export a snapshot of the dataset to a spreadsheet.

Hope this helps...
Think I'll bail out of this one - SJ is much better than me at this stuff

Ken