Enter Value for Parameter - Form design question

[David Huang]

To cut to the chase, my question is:




Is it valid for a form to be based on a query which references something on the form?


I am getting the unwanted 'Enter Value for Parameter' pop-up, which I see a number of people on this forum are getting.


Some people are getting it consistently and are able to resolve the issue by changing the code in error. Others are changing something seemingly unrelated and the problem goes
away.


I am in the latter category, but the issue with an unknown resolution is that it puts doubt about whether to problem will re-appear in the future.


So I'm hoping that someone can tell me whether what I am doing is a proper design or not.


More details:


I'm doing what I think is quite common.


I've taken the Microsoft Inventory Management template, and modified it for my own needs.


In the header of the Inventory Transactions List screen, I've added a drop-down for the Inventory Items.


I've changed the form query so that it references that drop-down field, so the query only returns transactions for that item.


The form works fine, except it has now and then has come up with the Enter Parameter error. I've changed things around and saved the form, and the error goes away, but
then the form still works even it is changed back, so it seems like the act of changing something and saving the form is clearing the error, nothing has been fixed.


As to why I haven't gone with alternative solutions:
- filter vs query selection
I want the form to be able to cope with larger volumes, and my database developer's brain tells me I should minimize the rows returned from the database
- why not form/subform?
The honest answer is I didn't know what this is, but since I've started, I've added a couple of other tweaks including dynamically changing the name of the underlying query to one with
different criteria, such as all transactions modified today.


I'm thinking that in some instances the form is not loaded before the query executes. If I could get an explanation the sequence of what happens when a form opens,
that would also be helpful.


Thanks and Regards


David Huang

[madpiet]

Normally, if you want to filter a form, you do it from another form. Either that, or you set the form's recordsource after the dropdown selection is made (like in the AfterUpdate event of the unbound combobox).

One way of dealing with it is to create a small form, set it as dialog, and prompt the user for a value. Then in the code for the OK (vs Cancel) button, you'd set the recordsource or filter for the form you're opening. It's much easier to do that way, because you just create a filter like

strFilter = "[FirstName]='" & Me.cboFIrstNames & "'"

and then pass that in the Open event of the form. Can't remember the exact syntax since I haven't done it in so long, but if you look up DoCmd.OpenForm, the first argument is the form name, and then not long after that is the filter. That's where you'd put the strFilter argument.

I think the reason you're having the problem is that you're trying to filter the object and it's already open/rendered, and the filtering happens during the open event, but before the render.

[Minty]

I suspect you are doing the filtering in the open event, and probably should do it in the load event.

The normal method I do this is as follows:

Open the form empty - this reduces the time to load - so set the recordsource to something like
SELECT stuff FROM YourTable Where PrimaryKey_ID = 0

In the form load event set the filtering controls to the defaults you want.

Then apply the filtering as the final step to your load event.

[Micron]

Is it valid for a form to be based on a query which references something on the form?

I say no. After form open event occurs, load event occurs. That's when the form recordset should be populated by the query you are using. If your form control has a value in it at that time, you likely would be ok. If that gets delayed or is not present for any reason, you have a problem. If it's a combo, I don't see how it would work as a value would not have been chosen from its list. I think there are more reliable methods, like
- passing an open arg value in the form open command line or
- loading the records after user picks or enters a value in the control(s) that your query criteria needs or
- loading all records (not a fan of that idea unless recordset would be relatively small)then filtering based on same actions noted above

If I could get an explanation the sequence of what happens when a form opens,
that would also be helpful.

https://support.microsoft.com/en-us/...7-ce86553682f9

[David Huang]

Thanks to everyone who has replied so far. Your answers have all been informative and useful. I will do some more reading, and experiment with your suggestions.

[Gicu]

From what you are saying about changing and saving the form it seems like the enter parameter prompt might actually be associated with a form property such as Filter or OrderBy that gets saved with that parameter rather than the recordsource query itself. I think that if you open the form empty as suggested by Minty (or to the last viewed record by saving its unique ID in a local front-end settings table) then resetting its recordsource in the AfterUpdate of the combo (drop-down) you will get a nice and fast working form.

https://www.access-programmers.co.uk...e-help.309697/

Cheers,

[ArviLaanemets]

In the header of the Inventory Transactions List screen, I've added a drop-down for the Inventory Items.


I've changed the form query so that it references that drop-down field, so the query only returns transactions for that item.

I seriously advice to reconsider your design. P.e.:
Have an unbound form, where you have a combo to select item (e.g. cbbSelectItem). The combo has 2-column query as rowsource, like "SELECT ItemID, ItemName FROM tblItems ORDER BY 2 ASC". Set BoundColumn = 1 (i.e. when you select an item, the combo gets it's ID as value), and columnWidths AS "0,2.5" (i.e. item name is displayed).
Into this unbound form, add a continuous subform based on your Tranasactions List, and set links for subform as cbbSelectItem in unbound form, and ItemID for subform. In subform, the control for ItemID must be present, but at least disabled, or even better hidden (so user has no easy way to mess up all). Now, whenever the user selects a item in parent form, all transactions for this item are displayed in subform - without any coding needed. And whenever the user inserts a new entry into subform and saves the record, a new transaction for item selected in parent form is created - again without any VBA.

[Welshgasman]

Is it valid for a form to be based on a query which references something on the form

I would say No.
Bit of a 'what comes first, chicken or egg' situation?

Think about it, nothing is in the form, until the query supplies the data?

[madpiet]

Just wondering... are you thinking of the "Go to Record" thing where you select a record from a combobox, and once you've selected it, the form goes to that selected record? That's the closest thing I can think of that's similar to what you're describing. Could you please describe what your end goal was going into this? Kind of like this?

"I have a form for Inventory, and I want an easy way to find a specific record... one that I choose from a dropdown"?

If that's what you're trying to do, there's a wizard that does that. You basically drop a combobox on your form, and the wizard takes you through the rest of it.

[David Huang]

Hi madpiet


It is more for filtering than for going to a specific record.


I have a form for inventory, it comes from the Inventory template from Microsoft Featured Access templates


https://support.microsoft.com/en-au/...8-1afcfc91a9cb


The main screen is the Inventory Transactions List screen. It shows all the transactions in the database.


The behaviour I would like is similar to that for a form/subform. i.e master/detail.


The user selects an item and the transactions are displayed for that item.


I am thinking of converting it to a form/subform, as suggested by ArviLaanemets. I have implemented some functionality which doesn't neatly fit the form/subform model, but I'm willing to forgo this for a more standard design.

[madpiet]

AWSales_1.zipSorry, I tried arguing with that template and I gave up. Instead, I imported a bunch of data from AdventureWorks, so it's SalesOrderHeader and SalesOrderDetails. I figured that was a close enough match to what you were doing and easier than trying to figure out why something didn't work on something I didn't build. At any rate, after I started over, it got a lot easier. =)

Open the Sales_SalesOrderDetail form.
At the top is a combobox that says "Select a product"... so go crazy and pick one.
The form will be filtered so that only the SalesOrder line items containing that product will be shown.
if you want to see everything again, click the Clear Filter button.

Is that what you were looking for? <g>

Open the form in Design mode and look at the code behind the combobox and the button. Those two are the only things I wrote code for. (Makes it easier for you to find what I did if I didn't include a bunch of other irrelevant code, too!)

Holler if you have questions, but basically that's how you do it. I'm basically using the combobox to present values to choose from and then in the OnChange event, I'm applying the filter to the form. The button just clears the filters.

Hope that answers your question.

if not, post back.

[David Huang]

Thanks madpiet for the work. The form works well.

Using the filter property vs the query criteria is certainly an option. I just need to think about how much volume I want to support in the future.

David

[madpiet]

What do you mean about "how much volume [you] want to support"? I grabbed a bunch of data from SQL Server because (2) it was similar to what you were doing, and (2) it's always good to test against a lot of data so you can see how the database will perform as it gets larger.

[David Huang]

Hi madpiet,

My understanding is that in filtering using the filter properties, all the data is read in from the database, and the filtering is done by the form, whereas using SQL criteria would filter in the database, allow for the use of indexes, and return less rows to the form.

My background is in large systems development, so I naturally favor the sql criteria.

However, I can see that things can be done differently in Access. I'm realistically not going to be moving to large volumes in Access.

I can see also that the filter solution is more efficient when the user switches between different items, as the data does not need to be requeried.


David

[madpiet]

To be honest, I think what happens when you open a form and filter it, the RecordSource object and the filter get sort of merged together. Kind of like

SELECT <field1>, <field2>...
FROM <tableX>

and then the filter in the Open event gets tacked onto that. Maybe ask Albert Kallal. He would know for sure.