Passing values from a Form to a Parameter Query

[wizzz_wizzz]

Hi All,



I'm trying to pass value from textbox in a form (frmStoreReceipt4) to a Query (qryOrderNumber). However, I am being prompted to enter in values for the parameters instead of taking the values entered in the textbox. I have thought adding this query criteria would do the trick

[Forms]![frmStoreReceipt4]![ORDER_NUMBER]

But it didn't work. I have also noticed that when my form (frmStoreReceipt4) is close, the query can run without prompting me to enter the parameters- however
the query did not show any results.

What have I done wrong? Appreciate any advice.

[Micron]

I'll jump in, but be warned - I never caught on to using split forms. Didn't seem as robust as a form subform design. For your problem, I think it's because a split form is a type of datasheet, and in such a form, a control is used multiple times as there are many records. Usually, the controls used to restrict the record set are in the header and the data "list" is in the detail section. You have it reversed.
Even if that is the issue, I don't see this query working. You have Date() as criteria in a date field, yet no records are in there for today's date. You will never return records unless the date there are dates in the table that match whatever day you run this on. Second, I think the field in question in your post is in the wrong query field.
Third, you have MANY fields that are begging to be numbers (quantities, line numbers, taxable amounts, currency...), yet you have set everything to text??

[wizzz_wizzz]

Hi Micron,

Thank you very much!! I am grateful that your comments and advices clearly pin-pointed all the weaknesses in my novice attempt, which are mostly through trial and error, and searching the internet. I have made some changes that you have pointed out and seem to get my query (qryOrderNumber) working.

I don't quite understand as I am still try to grasp MS Access concepts: "For your problem, I think it's because a split form is a type of datasheet, and in such a form, a control is used multiple times as there are many records. Usually, the controls used to restrict the record set are in the header and the data "list" is in the detail section. You have it reversed." I am greedy for further advice
>> Could you enlighten me further?

1)Basically, I have only one table (tblMain), which is to be used for receipt of incoming goods received. The idea of using split form is for the user to easily filter those
records not received to enter the quantiy, date received, etc. I have created some buttons ("All Outstanding", "Filter Today") as shortcut for filtering purposes.

2) Another requirement is that for the same "Order_Number", the "Invoice_Number, Exchange_Rate and Invoice Date" are the same. Hence, my idea now is to run qryOrderNumber to for ease of entry.

3)2) Another requirement is that new entries will have to be exported into an excel file. Hence, the user will filter those lines with goods just received and be exported into excel.

Appreciate any suggestions and advice.

[Micron]

I have made some changes that you have pointed out and seem to get my query (qryOrderNumber) working.

Not sure I understand its usefullness, given all the empty fields.

Could you enlighten me further?

Now that we have your original problem fixed and I'm back from vacation, I can see that the orientation (datasheet on the top) is an option you selected. It just looked weird to me, being a fan of having subforms on main forms as opposed to being split. It has nothing to do with your original issue (as mentioned, my experience with split forms is minimal). I prefer the look as shown here if it has to be a split form
https://support.office.com/en-us/art...b-86e79a1fbe1e

Re: 1)
- Again, I wouldn't have any quantities as text (you are)
- Code values (such as currency) may cause problems when they're free form text. Typos (e.g. SGD vs SGC) mean that all non conforming records will be omitted from query results when you design for what it's supposed to be. To prevent this, have such data in its own table and write either the unique id for the code or the actual value into the main table (I think most prefer the former, but it can make it more difficult to follow the bread crumbs when you're trouble shooting).
- I wouldn't mix sentence case as done in your form and I'd use datasheet captions (as shown)
- I don't allow control names and field names to be the same. If using a wizard to create a form, I rename all the controls using a somewhat generally excepted convention (and turn off "Name Autocorrect", which is best practice).
- I think you should take a look at normalization (see several information links below). SELDOM is one table really sufficient in a database. You seem to be mixing PO and invoice data with units, customer data (name, currency preference), part data (serial, description) and have no tables for customers, vendors, PO data, Invoice data, part data, etc.
2) While possible, your single table design is not the way to go. I presume the "Today!" button is in support of the goal.
3) Interesting that you would want to copy or move database data to Excel, unless it's for charting. I can't really think of why else.

Some changes made to your form design just to show another approach (consistent form, label, button, etc. sizing). Hopefully I have attached the correct file.
dbSOPOa.zip

Normalization is paramount. Diagramming maybe not so much for some people.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/index.php/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp

[wizzz_wizzz]

Hi Micron,

Wow, thank you very much for your advice, very detailed explanation and information for guidance, even taking the trouble to
make changes and improvements to my dB.

I have a much clearer picture now and must re-think my design.

Appreciate it very much

[Micron]

Glad to have helped!