|
|
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!!!!
|
|
|
[Quoted Text] > Me.OrderBy = ""
> Me.OrderByOn = False
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
"Ryan" <Ryan[ at ]discussions.microsoft.com> wrote in message
news:803F44C7-0056-4106-A847-63E77EEC816E[ at ]microsoft.com...
> 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!!!!
|
|
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.
--
Please remember to mark this post as answered if this solves your problem.
"tina" wrote:
[Quoted Text] > > Me.OrderBy = ""
> > Me.OrderByOn = False
>
> 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
>
>
> "Ryan" <Ryan[ at ]discussions.microsoft.com> wrote in message
> news:803F44C7-0056-4106-A847-63E77EEC816E[ at ]microsoft.com...
> > 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!!!!
>
>
>
|
|
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???
--
Please remember to mark this post as answered if this solves your problem.
"Ryan" wrote:
[Quoted Text] > 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!!!!
|
|
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
"Ryan" <Ryan[ at ]discussions.microsoft.com> wrote in message
news:34E3C4C6-A88D-40F0-A492-15EBB3EBCA91[ at ]microsoft.com...
[Quoted Text] > 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???
> --
> Please remember to mark this post as answered if this solves your problem.
>
>
> "Ryan" wrote:
>
> > 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!!!!
|
|
|