ComboBox or SubForm?

[OldBirdman]

I have a form, bound to tblMain. I have tblSub, which is related to tblMain with a foreign key, all nice and proper database design.

I want to display the rows of tblSub in a control, but neither ComboBox nor SubForm seem to be easy to implement.

The primary purpose of this control is that when a row is clicked, then "strWork = strWork & xxx" where xxx is the correct field of the subform, or the correct column of the combobox.

I will also need to edit, delete, and add rows, both by the user, and with VBA.

ComboBox:
Use - when clicked, the .column contains the correct text
Update - Cannot know what to change, and will need another form to actually do any modification of the data. Note that this is not a "Not in List" issue as it may be an edit of a row that already exists, therefore not-in-list fires, but new row should not be added.

SubForm:
Use - there is no nice event for a row being clicked. OnCurrent fires for other reasons also, so the concatination creates errors. Will require OnClick for every field.
Update - easy, as all updatable fields can be displayed, unlocked, and enabled.

How should this problem be resolved?

[NeoPa]

I don't have a full solution for you OB, but with a subform, could you not add an unbound control (CheckBox, CommandButton, etc) that triggers the code instead of OnCurrent. Typically, just by clicking on the control, the desired record will be selected in the subform. Just an idea.

[OldBirdman]

Yes, of course I can do that. It is just that it takes space.

I have needed this solution in several databases, and others might also. I am fishing for ideas. Thank you for yours.

[NeoPa]

I hear you OB.

I'm not aware of a more built-in alternative. Let's see what some of the other experts can come up with.

[DonRayner]

Hey OB,

If you use a subform in data-sheet view you can use the forms click event to fire your code. When a user clicks on the record-selector the event will fire.

[OldBirdman]

Thanks for another suggestion. Yes, either continuous forms or datasheet view will (optionally) have record selectors. The click event fires if the record selector is clicked. It does not fire if any field is clicked.

A combobox or listbox will fire a click if any column is clicked, not just the left side.

A subform is easier to correct/update, because cboControl.column(2) = "xxx" produces an error, even though the control is bound. Subforms are harder to work with during design, however.

[OldBirdman]

I've decided that I need to go with a sub-form. I will have a click event on each textbox, and no record selectors, as space is a consideration. The subform will make it easier to add, delete, and update the records.

I used the wizard (access 2000) to create the subform control. I cannot display my records in the correct order.

1) Can I specify a sort order in design view? If yes, how?

2) From VBA, how do I change the sort order of a subform from code in the main form? From code in the subform?

Main form name = fMain; Sub-form name sfDetails

[NeoPa]

@OldBirdman
The order records are shown in a form depends on the RecordSource. A query can have the ORDER BY clause set which will give you your required sorting.

[NeoPa]

@OldBirdman
If you're talking about changing the sort order after the form has already been displayed, then each form has an OrderBy property. See Referring to Items on a Sub-Form for how to reference that from your main form. I don't know if a .Requery of the subform is required after changing the property.

[OldBirdman]

I've avoided using subforms because I can't make any changes without closing everything, doing my editing, and then restarting the program, but that is probably another thread.

So I'm really a beginner here. My subform (on the main form) has properties SourceObject and LinkChildFields which appear to be the determining factors as to what is displayed. I don't think these are what you refer to.

Within the subform itself, there is an OrderBy property. That seems to do nothing. Why not? So the only way is to work with the RecordSource property directly? This would therefore be true in both design view and VBA.

[FishVal]

Seems like your subform SourceObject is table/query. Isn't it?

[OldBirdman]

It was a Table, with only the table name in Record Source. That is the way the Wizard set me up. Seemed like the subform Order By property should allow me to display these records in my preferred order. Couldn't make that work.

Record Source is now a query "SELECT ... FROM ... ORDER BY Type, Location". That does work, and "If it ain't broke, don't fix it!" applies now. I will go that way.

But it does seem like if I wanted command buttons over each column, I should only have to change the Order By property and not have to run a query against the original table each time. Sort the 10 displayed records rather than SELECT from 50,000 records and then sort.

[NeoPa]

I believe the ordering is not applied unless you set OrderByOn to True in your code :

{Form}.OrderBy = "{Field1},{Field2}"
{Form}.OrderByOn = True

This property is not shown for forms in the Properties Window, so it's easy to miss.

[OldBirdman]

Private Sub Form_Load()
    Me.OrderBy = "Type DESC"
    Me.OrderByOn = True
End Sub

This code did not sort the records on the subform. As a matter of fact, the first time the subform was updated (i.e. when the first record was displayed on the main form), it showed zero records. Changing to the 2nd record then displayed all the records for the subform. But not sorted! returning to the first record displayed correctly.

Using VBA to set OrderByOn, and the property sheet to set OrderBy did not work either.

I always seem to have trouble using subforms. I guess this thread started when I tried to avoid using a subform. Referencing controls on a subform gives me a headache.

[NeoPa]

I'm afraid I haven't got an example to play with. I got the info from the help system. If I get some spare time I may see if I can knock something up, for curiosity's sake, but I don't use them much either :(

[OldBirdman]

Don't take your time. I'm going to use a query, not a table, to bind the form. This seems to work OK. At least for now I get predictable results and for this application the table is only a couple of thousand records, so efficiency not an issue.

But, if a form is bound to a dynaset, is there a guarantee that the records of the form are in the same order as the dynaset? For now, seems true!

[NeoPa]

I would have expected the answer to be "No". After all there is an OrderBy property for the form, but your experience seems to indicate otherwise :S

It doesn't add up for me, but maybe I'm missing something.

[OldBirdman]

If the dynaset is not sorted, or sorted in an order that is incorrect for the form, then it makes sense that the form have an OrderBy property to sort the dynaset. I would expect this to work without the need for VBA coding.

As the sort order is not guaranteed in a Select Query, why would we assume that a form's dynaset have a predefined order. Viewing the table in datasheet view from the tables tab of the database window does not display the stored order.

Assume I create a table, and load it by typing rows in datasheet view. Assume the autonumber key field is random, not sequential. The keys are now out of order, although if I close & reopen the datasheet view, the rows will appear ordered. I believe that here the order is the order entered.

Assume I compact/repair this database. What is the order of this table now? All I can assume is that it is not in the order I want for my form.

I need my data in my order, and for now I will bind my data to my form by using a Select query as a Record Source.