Results 1 to 15 of 15
  1. #1
    David Huang is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2024
    Posts
    5

    Enter Value for Parameter - Form design question

    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

  2. #2
    madpiet is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Feb 2023
    Posts
    97
    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.

  3. #3
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,989
    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.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,648
    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
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    David Huang is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2024
    Posts
    5
    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.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,087
    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,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Join Date
    Apr 2017
    Posts
    1,643
    Quote Originally Posted by David Huang View Post
    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.

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,772
    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?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    madpiet is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Feb 2023
    Posts
    97
    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.

  10. #10
    David Huang is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2024
    Posts
    5
    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.

  11. #11
    madpiet is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Feb 2023
    Posts
    97
    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.

  12. #12
    David Huang is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2024
    Posts
    5
    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

  13. #13
    madpiet is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Feb 2023
    Posts
    97
    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.

  14. #14
    David Huang is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2024
    Posts
    5
    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

  15. #15
    madpiet is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Feb 2023
    Posts
    97
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 03-16-2020, 06:39 PM
  2. Enter Parameter Value in Access Form
    By Rudi Candra in forum Access
    Replies: 1
    Last Post: 07-22-2017, 05:49 AM
  3. Replies: 12
    Last Post: 12-07-2015, 07:10 PM
  4. Replies: 4
    Last Post: 10-29-2015, 01:46 PM
  5. Replies: 13
    Last Post: 01-10-2012, 09:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums