How to order form assets by name

[Ezzz]

Initially all the assets in my form were ordered by the product Id. (Not set by me suspect it is some sort of default after i downloaded the database?)

In order to keep all similar assets together i used the forms "Order By" function in the forms properties box to select "Product Name".

This worked well until i tried to get clever and added a serial number search function using an unbound txt box and a command button. Although the search function works (ish - if there are more than one asset with the same serial number it will only find the first one?) my form has reverted to being Ordered by the product Id.

Is there a way to stop the form ordering by the Product Id and reset the form so it will order by the Product name?

Ezzz

[NeoPa]

I'm sure there is, but it would depend absolutely on what code is behind your Command Button. Command Buttons don't do anything on their own. They just give you a hook to hang your code on. We'd need an idea of what the code is doing before we can suggest a way to undo it.

[Ezzz]

Form Ordering

The cmd search coding behind the button is as below:-

Private Sub cmdSearch_Click()
    Dim SerialNumberRef As String
    Dim Search As String
    
'Check txtSearch for Null value or Nill Entry first.

    If IsNull(Me![txtsearch]) Or (Me![txtsearch]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
        Me![txtsearch].SetFocus
    Exit Sub
End If
'---------------------------------------------------------------
        
'Performs the search using value entered into txtSearch
'and evaluates this against values in SerialNumber
        
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("SerialNumber")
    DoCmd.FindRecord Me!txtsearch
        
    SerialNumber.SetFocus
    SerialNumberRef = SerialNumber.Text
    txtsearch.SetFocus
    Search = txtsearch.Text
        
'If matching record found sets focus in SerialNumber and shows msgbox
'and clears search control

    If SerialNumberRef = Search Then
        MsgBox "Match Found For: " & Search, , "Congratulations!"
        SerialNumber.SetFocus
        txtsearch = ""
        
    'If value not found sets focus back to txtSearch and shows msgbox
        Else
           MsgBox "Match Not Found For: " & Search & " - Please Try Again.", _
            , "Invalid Search Criterion!"
            txtsearch.SetFocus
    End If
End Sub

If you also know of a way to adapt it so it will continue to search for further assets with the same serial numbers that would be tops!!!

Ezzz

[NeoPa]

You're doing a Search/Find rather than a Filter. The difference being that the Filter approach removes all non-matching records from the displayed list. As I tend to use the Filter approach myself, I don't have much experience with the Search/Find one I'm afraid. It appears that this (Line #19) is where your reordering is taking place (I would guess).

I would attempt to set the .OrderBy parameter, to see if that fixes anything. If not, then you could consider the Filter approach instead, if that were compatible with your requirements of course.

If you also know of a way to adapt it so it will continue to search for further assets with the same serial numbers that would be tops!!!

The Filter approach would show all those records that match your criteria. This may be a more appropriate way for you from my reading of your situation. Only you can really decide that though.

[Ezzz]

Thanks for the info will have to investigate further.
Ezzz

[Ezzz]

Form Ordering

I have reset the Order By function and this corrects the form so it orders by the Product Name and continues to work fine until I use the search command whereby it reverts to being ordered by the Product Id and then remains that way until you go in to the forms properties and alter and reset the order by function? So it is the cmdSearch that upsets the form but I can't figure out what or why. Any help would be appreciated, if not I can always remove the search function.

Ezzz

[NeoPa]

If you refer back to post #4 you'll see I suggest it is probably line #19 that makes the change.

[Ezzz]

I've looked at line 19 but can't figure out why you think its there?
To be honest though I don't realy understand what most of it does.
Just wondered if anyone else might have any ideas or suggestions.

Ezzz

[NeoPa]

I've looked at line 19 but can't figure out why you think its there?

Mainly experience and an understanding of how things work under the hood.
Line #19 :

DoCmd.FindRecord Me!txtsearch

Generally such commands are providing functionality equivalent to items selectable from the menus. As such, they are typically (though not always) more involved than most VBA commands and methods. I would imagine that a search procedure would work most easily if the data were sorted by the item that is being searched, so I would expect the ordering to be done within this call, just before the search part itself - if that makes sense.

[NeoPa]

By the way, this is fairly straightforward to test for yourself.

Try the code with that line.

Close and reopen the form (to reset the test).

Try the code without that line.

If you see the data is resorted in the latter case then I'm wrong.

[Ezzz]

Tried your test with and without the line 19 and it makes no difference. In both case they revert to being sorted by the Product Id and not by the Product Name as I'd like.
I tried removing various parts of the "cmdSearch" and found that its line 17
DoCmd.ShowAllRe cords that is causing the problem? But why I don't know.
The wierd thing is that with line 17 removed the search function still works! and my form remains sorted the way i want, so all is well.

Ezzz

[NeoPa]

Checking the Help for ShowAllRecords I found :

Access Help:
Remarks
This method removes any existing filters that may exist on the current table, query, or form.

PS. Well done for taking it that step further and resolving the problem :)