Sort by form. Two criteria or more.

[EmergentGuru]

Hello, I have a Mainform where I have a dropdown list of categories " For example " Chemical Safety" Biological Safety" etc. I then also have a dropdown in a Subform with a list of inspection question numbers. Each inspection question number is in chronological order and is related to a certain Category. My original intention was to filter by form in the query basing the numbers showing in the subform based on the criteria chosen in the Mainform Dropdown list (For example Chemical Safety). I then realized that it is possible that the user would need all inspection question options when entering the data (rarely but possible). Is there a way that I could sort by form in respect? For example, could I make the dropdown query for the number have to tiers. 1st teir would sort by Category and then the next tier would show the remaining inspection question numbers in chronological order. For example can I make a criterial filter by form "and" something else? (Such as show remaining)

 

[MarkK]

Check out the OrderBy and OrderByOn properties of the Access.Form object. You can set those items to easily sort the data in a form, and you can sort on multiple fields. Code example:

   Me.OrderBy = "Lastname, FirstName, DateOfBirth DESC"
   Me.OrderByOn = True

You can also order the records in a form using the RecordSource property, which you can set to the name of a query, or the text of a SQL statement, which will then honor the ORDER BY clause in that SQL.

 

[EmergentGuru]

ok right now I have the dropdown dependent on a query. Where do I find the SQL for the form object. FYI, the dropdown search that I am using is unbound and it auto populates the number in a bound field for the "Inspection Number" when chosen. Where do I go to effect the SQL that you mention above. Should I go in the ubound dropdown or the bound text boxe previously discussed. Thanks

 

[MarkK]

You've used the terms 'filter by form' and 'sort by form' in your description of what you want to do, so I assumed your form was bound, but maybe we need to back up a little to understand the overall architecture of your problem.

The assumption I make when you say Mainform and Subform, is that all forms are bound, that the Mainform shows a single record (from a single table), and that the subform shows many child records (also from a single, related table). In addition, the subform records each contain a foreign key that is the primary key of the parent record, which is how the child record are related to the parent. Does that sound right?

If not, then can you describe the relationship between your mainform, and your subform.

As far as SQL goes, a bound form has SQL--or the name of a query--in its RecordSource property, and that is how that form gets it's data.

A control, like a listbox or combo, has a RowSource property, also SQL--or the name of a query--and that is how that control gets its data.

 

[EmergentGuru]

Your assumption is exactly right. Below is the query that I am using in the combo box to sort/filter. This particular query is separate actual query but based off of a union query (The SQL is below). With that said where would I put the additional SQL that you stated on your previous post. Hopefully the fact that it is based off of a Union query it wont be a problem.

SELECT UnionQuestionQry.ID, UnionQuestionQry.QuestionNumStr, UnionQuestionQry.Category, UnionQuestionQry.Question, UnionQuestionQry.SubCategory, UnionQuestionQry.RegulatoryField, UnionQuestionQry.RegulatoryNum, UnionQuestionQry.CategoryNumber, UnionQuestionQry.QuestionNumber, UnionQuestionQry.EMOBApplicable, UnionQuestionQry.TypeOfQuestion, UnionQuestionQry.SupplyChainApplicability, UnionQuestionQry.LaboratoryApplicability, UnionQuestionQry.ManufacturingApplicability, UnionQuestionQry.FacilitiesApplicability, UnionQuestionQry.AdministrativeApplicability, UnionQuestionQry.QAApplicability, UnionQuestionQry.ProtectiveServicesApplicability, UnionQuestionQry.UsedOrNotUsed, UnionQuestionQry.[Leading Question], UnionQuestionQry.Reference
FROM UnionQuestionQry
WHERE (((UnionQuestionQry.UsedOrNotUsed)="yes"))
ORDER BY UnionQuestionQry.Category, UnionQuestionQry.QuestionNumber;

The idea would be to use this query to sort/filter data in the ChecklisNumCbo (Sub) in the Findings Form (Sub). Based on the categoryCbo box (Main) the in the AssessmentFrm (Main). The category chosen in CateboryCbo (Main) would show first in the ChecklistNumCbo (sub), then the next sort tier would be "Question number Field" that would be in chronological order for example. See below

Example
2.1 Biological Safety
2.2 Biological Safety
2.3 Biological Safety
2.4 Biological Safety
1.1 Chemical Safety
1.2 Chemical Safety
1.3 Chemical Safety
1.4 Chemical Safety
3.1 Electrical Safety
3.2 Electrical Safety
3.3 Electrical Safety
3.4 Electrical Safety

The idea here being the person is going out to do a biological safety inspection and would most likely always choose the Biological Safety numbers because that is the focus of an inspection. But there is the possibility that a person may detect other safety findings (chemical or electrical in nature) that he/she would have to put into the Findings Table. Therefore you have easy access to the relevant topic but have the option to scroll down for other onsey twoseys if needed. Otherwise I have them all listed but it would entail large scrolling potential because there is probably 300+ options.

 

[MarkK]

Oh, maybe you are trying to describe what are called "cascading combos?" Search that term and see if you find what you are looking for. In that scenario you make a selection in combo 1, which filters combo2 to only show child records of combo1. and it is from combo 2 that you make your final selection.

If so, there is a lot written about this already. See if that's it.

 

[EmergentGuru]

I got it to work (round about and in a way that Is probably better). I did use the cascading combos/filter by form in the query for the combo. I did not get the specific solution i originally asked for but, I just moved the Main Filter cbo to the subform so both cbos were on the same subform line item and did the cascading cbo box. Thanks