Use a Control on a SubForm to Set the OrderBy on Main Form

Question

I would like to know how to set the OrderBy on a main form to sort by a control on the subForm.  Is this possible?

Answer 1

No, not with an expression in the OrderBy property; it is reserved for fields in the current form's RecordSource only.

It's also a very unusual request. I'm not even sure if it is logical. Say I have an Order form with an OrderDetails subform. What does it really mean to sort the orders by "a control on the subform". Do you mean "a value in some control on the subform"? What if that value is 5, how would that sort the parent differently than if that value is 6?

---

-Tom. Microsoft Access MVP

Answer 2

I've never had a database give me as much trouble as this one...I just can't get this one done AT ALL!

I'm trying to do what I thought would be a simple invoices database but the forms, queries, reports, even the tables aren't working the way I want.  I have started over now 6 times!

What I was trying to do is show the Invoices form as a subform of the SubContractorForm.  Right now it is sorting by SubName.  So the first invoice showing is Invoice #59 for SubName "Arthur"

I would like to see invoice #1 first

This has to be the most frustrating database I have ever done...formulas that I have used in the past just simply won't run in this one.  Nothing I do is working :(

Answer 3

If "nothing works" starting over is no way to fix it. I call that "tinkering" and it will not get you to the finish line. You will need to understand what does not work and why, so you can learn.

> I would like to see invoice #1 first
That seems illogical. You cannot sort the contractors so invoice 1 is first, 2 is next etc. Example:
Say ContractorID=1 has invoices 1, 5, and 8. ContractorID=2 has invoices 2, 3, and 9. So when the form opens we show contractor 1, user clicks Next in navigation and we show contractor 2. Invoice 2 and 3 are in the subform among others. User clicks Next again. How do we now show contractor 1 again?

If you want something sorted by invoice number, create another form (maybe Continuous forms) to list the invoices in numeric order, and allow user to click to open the Contractor form on the correct record.

Or in its simplest version have a button on Contractor form which when clicked asks for an Invoice Number, and you then move the contractor form to the correct record using the Bookmark technique.

---

-Tom. Microsoft Access MVP

Answer 4

Thanks so much for your input.  I think this database just has me completely beat.  I did have the form set up differently in one of my previous attempts.

It's just that when I get the forms working, the reports don't work and vice versa.  I've tried so many different things - formulas I've used in previous databases aren't working in this one.  ComboBox filters aren't working.  I think I've just been staring at this too long (4 days straight) and I'm just not getting any further.

This is just so frustrating...I'm no expert but I have done more complex databases but this one just simply isn't doing anything right.  Attempt #4 is the closest I came to getting it to work but the report is incorrect.

Some of the subs work on more than one project; a new and unique order number is created per sub and per job.  But for each sub that is on more than one project the report is duplicating records.

For example SubID MATT02 is on Job number 20160602 and Job number 20160203.  The Order numbers for these are 20160602/77 and 20160203/235, respectively.  Invoice #100 was generated for SubID MATT02 for Order No 20160602/77.  Looking at it on the form the information is correct; however when I run the query which is the source for the report, Invoice #100 is listed twice - once for Order No. 20160203/235 and once for Order No. 20160602/77.

I thought the issue was the fact that the order number contained a symbol "/" but with or without it the report isn't working.

Answer 5

I would encourage you to spend a bit of time with the Northwind sample application.

Also, do not use MATT02 for the SubID. Why? Just like in Northwind and many other sample databases, ID values are mainly used as the glue behind the scenes, and never shown to the user nor is their value controlled by the user. Rather use AutoNumber for your PKs. You're allowed to have SubAbbrev field, text(10), unique index, required in addition to SubID.

20160602 for the Job Number is also a mistake. First off per my previous point you need to have a JobID autonumber PK. Your value seems to be a formatted date, so it really depends on OrderDate. That is a relational design error. If you wanted to display 20160602 somewhere, you could simply use the Format function on the OrderDate. So far you don't have a case for the JobNumber field.

20160602/77 is a mistake for another reason. It is the concatenation of two other values, so it is dependent on those values. There is rarely if ever a good reason for that in a proper relational database. Don't go there. Rather your OrderDetails table has an OrderID and a SubID.

Similar thing for invoice number.

Also, you need OrderDetails table, InvoiceDetails, Products. Again - see Northwind. It has its flaws (e.g. the use of spaces in object names) but the data model is not bad.

So start there. Do not create any forms and reports. Post your relationships diagram here for us to give feedback on.

And don't worry about that report query doubling up on some data. That is easily corrected once you have the right data model, and CERTAINLY not a reason to start over.

---

-Tom. Microsoft Access MVP

Answer 6

Thanks.  I went back to one of the previous versions and checked my relationships table and found an error.  Now I've fixed it the database is working a lot better.  Still needs some tweaking but the reports are working now.

I took out the "/" from the job number.  You're partially correct on the JobID - it is in part a date reference 20160602 is the second job started in June 2016.  It is a unique number generated by the accounting programme we use so as it is unique I used it as the primary key for the JobContracts table.  The Purchase Order is an autonumber that again is generated by the accounting programme so Purchase order 20160602/77 is the 77th order created for Job Number 20160602.  I decided to keep these to fields separated instead of concatenating them with the "/".

Same thing with the SubID MATT02 - again generated by the accounting programme; MATT02 is the second subcontractor with the surname Matthews. 

Using these as my primary keys was just a way of not getting confused and keeping the tables simple.  Neither of these fields are visible in the forms.  I was using them in the queries but as my database relationships had an error the queries weren't working.

I think I was just looking at this database too long and wasn't seeing the problems.  A much needed break helped.  Right now I am just doing finishing touches and formatting the forms and reports.

Thank you so much.  It really did help to have a second opinion as to where I was going wrong.

Answer 7

You're quite welcome. Indeed if the IDs are given by an external program, you may decide to keep them as the PKs in your tables. And keep your fingers crossed the accounting program will change only after you've taken your golden watch :-)

---

-Tom. Microsoft Access MVP

Answer 8

LOL!