Orderby doesnt go away

  • Thread starter Thread starter Ryan
  • Start date Start date
R

Ryan

I have a form with this on load event. "AllBalancesByFCandDBPD" is a
crosstab query.

Private Sub Form_Load()
Me.OrderBy = ""
Me.OrderByOn = False
Dim rst As Recordset
Dim db As Database
Dim q As QueryDef
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set qdf = db.QueryDefs("AllBalancesByFCandDBPD")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*Financial Class" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.lbl1.Caption = rst.Fields(i).Name
Me.frmFCDBTotal.ControlSource = rst.Fields(i).Name
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Case 2
Me.lbl3.Caption = rst.Fields(i).Name
Me.DB2.ControlSource = rst.Fields(i).Name
Case 3
Me.lbl4.Caption = rst.Fields(i).Name
Me.DB3.ControlSource = rst.Fields(i).Name
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
If Len(Me.lbl3.Caption) < 2 Then
Me.lbl3.Visible = False
Me.DB2.Visible = False
End If
If Len(Me.lbl4.Caption) < 2 Then
Me.lbl4.Visible = False
Me.DB3.Visible = False
End If
End Sub

Since a column my exist one time and not the next time, if a user right
clicks and sorts a-z or z-a on a column named C28, and then closes and opens
the form again and the results dont include a column named C28 I get this
error.

Run-time error '3070'
The Microsoft Jet database engine does not recognize " as a valid field name
or expression.

If I go into the form in design view, I see that the Order By propert is set
to AllBalancesByFCandDBPD.C28 DESC
No matter how I try, the only way to get rid of the value in the Order By
property is to open the form in design view and manually delete it. I have
tried these options
OnOpen and OnLoad events
Me.Orderby = ""
Me.Orderbyon = False
AND
Me.Form.Orderby = ""
Me.Form.Orderbyon = False
AND
Me.ControlSource.Oderby = ""
Me.ControlSource.Oderbyon = False

Please help!!!!
 
Me.OrderBy = ""
Me.OrderByOn = False
Click to expand...

hmm, have you tried setting the above code in the form's Unload event or
Close event? or instead you might remove the various Close options from the
form's Title bar and Menu bar, and running the following code from a "Close"
command button on the form, as

DoCmd.Close , , acSaveNo

the above command has always worked for me, to avoid having changes made to
the form itself (not data) by the user be saved from time to the next. but i
did have an op tell me recently in a post that it didn't work for his
situation, though i can't recall the details.

hth
 
I have tried the on unload and on close event, but those didnt work either.
Your suggestion with using a close button is how Im currently handeling the
situation, however, I had users that would still want to close access using
the applications close button while the form is still open. I have disabled
the application close for now, but it just doesnt make sense that I cant use
the on open, on load, on unload, or on close event to reset the orderby
property to nothing. I will give the acSaveNo a try and let you know how
that works.
 
After further research, I made some progress. If I add a button and set its
on click event to

Me.OrderBy = ""
Me.OrderByOn = True
DoCmd.Close

then the OrderBy will go away. This is the only way I can get it to work,
it still will not work in the OnUnload, OnClose, OnOpen, or OnLoad events of
the form. Any suggestions???
 
i should have asked you before: if you're using A2000 or newer, did you
turn OFF the Name Autocorrect setting when you created this database? if
not, suggest you open a new blank db, turn off Name Autocorrect from Tools |
Options | General tab. then compact the db. next, import all the objects
from your current db into the new one, then compact again. this is a good
move, even if it doesn't fix your form problem. (for more information, see
http://allenbrowne.com/bug-03.html.) to get rid of the form problem
entirely, you may have to build the form from scratch in the new db.

other than that, if you have users who want to close Access with one button
click, rather than closing the form and then Access, suggest you add a
command button to the form to quit the application. call the procedure that
runs on the Close button, and then add

Application.Quit acQuitSaveNone

hth
 
Back
Top