Code diversion

[John Sh]

I have a form with three buttons for a user selection. This determines the table sort order, a label caption and what controls are to be used on a separate form.
The first two selections work as expected but the third, "BoxNo", causes the program to go off track.
The selection form, "Choices" is opened "acDialog" from the main form's "onLoad" event which then calls getOrder() after "choices" closes.
If I put in a break point at the top of the sub it will step down to the third setorderby as expected.
If I comment out that line the code runs correctly but without the required table order.
There is no error detected in this code block.
"transfer" is a public variable that is set by the user selected option in "Choices".
I have retyped the code, shifted things around and drank copious amounts of coffee, all to no avail.
Moving the offending elseif statement to the top of the code block causes the same problem.
The code freezes at a docmd.close statement in another sub with runtime error 2585, sometimes requiring a complete shutdown and restart of Access.
At this point it would seem that the problem is in the "DoCmd.SetOrderBy "Boxno" line.

Private Sub getOrder()
    On Error GoTo Err_bugger
    DoCmd.SelectObject acForm, "NoGenus"
    If transfer = "Genus" Then
        DoCmd.SetOrderBy "Genus"
        Me.cboGenus.SetFocus
        Me.lblNGNE.Caption = "No Genus"
        Me.cboGenus.Dropdown
    ElseIf transfer = "Species" Then
        DoCmd.SetOrderBy "Speciesepithet"
        Me.lblNGNE.Caption = "No Epithet"
        Me.txtEpithet.SetFocus
    ElseIf transfer = "BoxNo" Then
        DoCmd.SetOrderBy "Boxno"
        Me.lblNGNE.Caption = "No Box Numbers"
        Me.txtBoxNo.SetFocus
    End If
    transfer = ""
    Exit Sub
Err_bugger:
    MsgBox Err.Number & Err.Description
End Sub

 

[arnelgp]

so the Main form is "NoGenus"?

instead of "Load" event, can you open "choices" form on Timer Event.
set the Timer Interval to 300 (on main form).
on main form timer event:

private sub form_timer()
'kill timer
me.timerinterval = 0
'open choices form
'docmd.openform "choices",,,,,acDialog
call getOrder(me)
end sub

change getOrder() to accept parameter:

Private Sub getOrder(f As Form)
    On Error GoTo Err_bugger
''    DoCmd.SelectObject acForm, "NoGenus"
    Select Case Transfer
    Case "Genus"
        with f
            .OrderBy = "Genus"
            .OrderByOn = True
            !cboGenus.SetFocus
            !lblNGNE.Caption = "No Genus"
            !cboGenus.Dropdown
        end with
    Case "Species"
        with f
            .OrderBy = "Speciesepithet"
            .OrderByOn = True
            !lblNGNE.Caption = "No Epithet"
            !txtEpithet.SetFocus
        end with
    Case "BoxNo" Then
        with f
            .OrderBy = "Boxno"
            .OrderByOn = True
            !lblNGNE.Caption = "No Box Numbers"
            !txtBoxNo.SetFocus
        end with
    End select
    transfer = ""
    Exit Sub
Err_bugger:
    MsgBox Err.Number & Err.Description
End Sub

 

[John Sh]

so the Main form is "NoGenus"?

instead of "Load" event, can you open "choices" form on Timer Event.
set the Timer Interval to 300 (on main form).
on main form timer event:

I tried that, it's worse. The "NoGenus" form opens to the wrong section before the "Choices" form opens and I end up in the same place as before

Can you explain why my code works for the first two options but not the third?

 

[arnelgp]

then move it back to the Load event.
i thought you only have code in the Load.

 

[John Sh]

then move it back to the Load event.
i thought you only have code in the Load.

I do appreciate you time and effort on this but so far none of it helps.
I had already removed the ontimer code and solved one problem but it doesn't fix the runaway code.
If you could explain why what appears to be perfectly normal code is doing this I might have a chance of fixing it, but I have absolutely no idea as to where else to look for the answer.
I would much prefer an explanation than some other code snippet that has exactly the same problem.
John

 

[arnelgp]

the question is do you have BoxNo
either in table or SQL query of your Recordsource?

I would much prefer an explanation than some other code snippet that has exactly the same problem

i am not good on explaining things, i thought that you can follow through the code
as in college, where you're prof give you some assignment you don't know and you are
resourceful enough to research for the meaning and answer.

 

[John Sh]

then move it back to the Load event.
i thought you only have code in the Load.

I've fixed it! I moved the setorderby command to the last line of each if---else if statement and now all the code works properly.
Never having been to college to learn coding, I am very much a teach myself and learn as I go type of person.
Thanks again for your efforts.
John