Interesting probs: binding unbound subform to main, and sorting sub

[Accesser]

Happy New Year!

I have an unbound subform that has 4 list boxes (combo boxes that are limited to their list). This subform is to basically be used as a summary subform, in which users should be able to view the records they may have just entered into 1-4 other dialog forms; the record sources being 4 underlying tables/queries linked to the 4 list boxes.

The summary subform is based on a table in which various detail records (their IDs) from the 4 tables can be combined in many-to-many relationships. Therefore, data must be entered into 1 or more of the 4 tables BEFORE being viewed or selected in the list boxes on the subform.

When users open up the main form, they will enter some data and then click cmd buttons to open up the dialog forms in which to enter data into 1 or more of the 4 underlying tables. When the dialog forms are closed, the data is requeried, visible in the 1-4 listboxes on the subform, and available to be selected in any combination.

Here are the forms and primary keys (PKs are autonumber fields):

Main form: frmCompany (source = tblCompany, PK = CompanyID & DetailID)
Subform: sfrmDetail (source = tblDetail, PK = DetailID)
Dialog forms:
frmContracts (source = tblContracts, PK = ContractID)
frmProperty (source = tblProperty, PK = PropertyID)
frmJobs (source = tblJobs, PK = JobID)
frmMaterials (source = tblMaterials, PK = MaterialID)

The very 1st record in tblDetail is a preset default record (automatically applied to every new company entered. If other detail data is available for the new company, users must change the combination of detail IDs and apply it to the company, overwriting the default detail record).

The default detail record is as follows:

DetailID = 1 (unique autonumber)
ContractID = 1 (1 = unknown/not available)
PropertyID = 1 “
JobID = 1 “
MaterialID = 1 “

Basically, users will first enter a company’s data, then enter or select existing data in 1 or more of the dialog forms or subform list boxes, then click a button on the subform that “applies the combination of detail records” to the company. When the Apply Detail to Company button is pressed, the DetailID on the main form is replaced by the DetailID on the subform.

Problem #1

When users navigate through the companies on the main form, I’d like for the DetailID on the subform, and it’s corresponding combination of detail records selected in the listboxes, to change according to the company. How would this be possible?

Problem #2

I’ve made buttons in the subform to allow users to sort in various ways. These include:

Sort by most recent detail record added
Sort by the most selected detail record
Reset sort (order by DetailID)
Sort alphabetically by Contract
Sort alphabetically by Property
Sort alphabetically by Job
Sort alphabetically by Material

I don’t know how to code the 2nd sort above: “Sort by the most selected detail record.” Basically, I’d like for the combinations of detail records most frequently selected in the subform and applied to the main form to be at the top of the sort. How would this be possible? The problem is that the DetailIDs per Company are actually stored in the main form (tblCompany)--not the subform (tblDetail). Each combination of detail records in tblDetail will be unique.

Any helpful comments/codes would be extreeeeeemly appreciated.

 

[TelcoBT]

First, I'm not in a position to do any testing on this, so take it with a pinch of salt, but here goes.

I think that you would need to create a SQl statement for the rowsource of the combo, which relates to the id's in use, and orders them. The SQL would need to have access to the number of DetailId's in use in the company table - so I would produce a query similar to this

qrySelectionCount = select count(detailId) as total, detailId from tblCompanies group by detailId

Next, build the SQL for the row source,

select * from tblDetail left join qrySelectionCount on detailId order by qrySelectionCount.total

a left join ensures that any detailId's not yet selected will still appear in the output recordset.

This count would change as more id's are selected, so your combo could become out of date during any user session, but it will always be OK on restarting. You could, I suppose refresh the rowsource appropriately if a lot of id's are selected during any user session.