Attn: Marshall Barton

Tom wrote:

On Wed, 24 Oct 2007 23:55:27 -0500, Marshall Barton wrote:
Tom wrote:
The code is working perfectly. In the Reports I want the sort, I placed
=StandardizePartNum([Comp Part]) in the Sorting & Grouping area. This
sorts the field Comp Part (the competitor's part) as indicated.

The field for my company's part is RayPart. For my other Brands the
database supports, my part could be Federated, Aimco or Spicer. What if I
wanted to give the user a choice of which field to sort at Report run time?
Could the Comp Part be replaced by a user selection using a Check Box or
Drop Down List that places the choice in a Table that remembers the choice?

I am currently using Check Boxes to accommodate user selections for other
possible options in the database. Here is some of the code in use:

This code checks the user's last selection and restores it in the Check
Box:

Private Sub form_open(Cancel As Integer)
Dim db As Database
Dim rsRay As Recordset
Dim rsSilver As Recordset
Dim rsAimco As Recordset
Dim rsSpicer As Recordset
Dim intRaySetting As Integer
Dim intSilverSetting As Integer
Dim intAimcoSetting As Integer
Dim intSpicerSetting As Integer
Set db = CurrentDb
Set rsRay = db.OpenRecordset("Raymold Option Status")
rsRay.MoveFirst
intRaySetting = rsRay!Setting
RaymoldOpt.Value = intRaySetting
rsRay.Close
'Added Code
Set rsSilver = db.OpenRecordset("Silver Option Status")
rsSilver.MoveFirst
intSilverSetting = rsSilver!Setting
SilverOpt.Value = intSilverSetting
rsSilver.Close
'Added Code
Set rsAimco = db.OpenRecordset("Aimco Option Status")
rsAimco.MoveFirst
intAimcoSetting = rsAimco!Setting
AimcoOpt.Value = intAimcoSetting
rsAimco.Close
'Added Code
Set rsSpicer = db.OpenRecordset("Spicer Option Status")
rsSpicer.MoveFirst
intSpicerSetting = rsSpicer!Setting
SpicerOpt.Value = intSpicerSetting
rsSpicer.Close
End Sub

This code is how the user selects a particular option and stores the choice
as a 1 or a 2 in the applicable Option Status Table:

Private Sub optRayOff_GotFocus()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Raymold Option Status")
rs.MoveFirst
rs.Edit
rs!Setting = "1"
rs.Update
rs.Close
RaymoldOpt.Value = "1"
End Sub
Private Sub optRayOn_GotFocus()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Raymold Option Status")
rs.MoveFirst
rs.Edit
rs!Setting = "2"
rs.Update
rs.Close
RaymoldOpt.Value = "2"
End Sub

Is there a way for the user to select and store one of five possible sort
columns and pass the choice to this line =StandardizePartNum([Comp Part])?


Whoa, you should explain what all those recordsets are for
and why it looks like you have separate tables for each
choice. It looks like there is only one record (and one
field?) in those tables. If that's a fair assumption, then
didn't you combine all that into one table, probably with
just a single value for the selected brand.

I also don't understand what the GotFocus event has to do
with much of anything. Normally, the check box's
AfterUpdate event would be more appropriate for saving the
setting. Why you have two check boxes for optRayOff and
optRayOn is beyond me. I would think that you would have a
cleaner UI and less code if you used either a combo box or
an option group.

After you get all that sorted out, you can use the report's
Open event to set the Sorting and grouping expression.

Assuming you have one table with the selected brand/field,
the code in the report's Open event procedure would be
something along these lines:

Me.GroupLevel(0) = "=StandardizePartNum([" & _
& DLookup("Setting", "Settings") & "])"

That probably won't do any more for you than give you a
vague idea of how to set a report's sorting when the report
is opened, but I am really lost as far as how you have
designed your tables.


The Option Status Tables are used to store the status of a user selection.
The selection is designed to stay or stick or be remembered in the same
session and restore the last setting when a new session is started, thus
the four Tables. Each Table has one field named Setting and one record
with a number 1 or 2 stored in it. The Option is either ON (2) or OFF (1).
When the Option is toggled, there is some code run (that I took out of my
sample) that manipulates the field names in the master interchange Tables
along with changing the Option Selection and storing the result in the
Option Table.

The Check Boxes for On and Off are on a Form as part of an Option Group.
When the Form is opened, the Form_Open Code checks the last status of the
four option boxes by looking in the Option Status Tables and restores the
Check Mark in the appropriate Check Box. When the user selects the
alternate box in the Option Group, that is what triggers the Got Focus
attribute for the Check Box and runs the attached code.

I hope I didn't confuse you. I only offered those bits of code as
something I am already using for a user selection.

OK, if I had these Descriptive Names and Field choices for Report sorting:

Competitive Part - Comp Part
Raybestos Part - RayPart
Federated Part - Federated
Spicer Chassis Part - Spicer
Aimco Part - Aimco

What would you recommend for a UI to select one of the choices and store
the result in the Table Setting for use in the above Me String you posted
above? Maybe a Drop Down Box showing the Five Descriptive Names on the
left that are tied to the 5 Field names on the left and those Field Names
are stored in the Table that the Me. code looks for during its DLookup?


I say again, from what I can tell, you only want one option
ar a time and the only place that information is used is to
sort the report. This tells me that you only need one,
single row table with one field that contains the name of
the field you want to sort. This can be accomplished most
cleanly and easily using a combo box.

In any case, once you figure out which field in the report's
record source table/query you want to sort by, the line of
code I posted earlier shows you how to tell the report the
way you want the report sorted
..
--
Marsh
MVP [MS Access]

Marsh,

When I run this code from the On Open Event Procedure,

Private Sub Report_Open(Cancel As Integer)
Me.GroupLevel(0) = "=StandardizePartNum([" & _
DLookup("Setting", "Settings") & "])"
End Sub

I get a Runtime error #2464 There is no sorting or grouping field or
expression defined for the goup level number you used.

What am I missing?

Thx


Me.GroupLevel(0) = "=StandardizePartNum([" & _
& DLookup("Setting", "Settings") & "])"

--
Tom

Tom wrote:

Marsh,

When I run this code from the On Open Event Procedure,

Private Sub Report_Open(Cancel As Integer)
Me.GroupLevel(0) = "=StandardizePartNum([" & _
DLookup("Setting", "Settings") & "])"
End Sub

I get a Runtime error #2464 There is no sorting or grouping field or
expression defined for the goup level number you used.


You must precreate an entry in the Sorting and Grouping
window. I thought you said you had already done that??

Once you get the report sorting as you said it was, then add
the code to change it to the use the field from the settings
table. (Double check to make sure the record in the
Settings table has the name of the field you want to use.)

--
Marsh
MVP [MS Access]

Marsh,

I did have this line =StandardizePartNum([Comp Part]) in the Sorting and
Grouping Window and it is working perfectly, but removed it thinking it was
redundant with the new code string you sent. It is now back and I have
tried to modify it per your example.

I have a Table Settings with a single Field named Setting. It has one row
with the entry Comp Part.

OK, based on your second paragraph, I don't need anything in the On Open
event. All I need to do is mod the code to look in the Settings Table.

This string is now in the Sorting and Grouping Window of my test report:

=StandardizePartNum([" & DLookup("Setting", "Settings") & "])

Now, when I run the report, it is asking for a Parameter Value for
" & DLookup("Setting", "Settings") & "

This makes sense, but I must have the syntax off.

Your original code string has two ampersands. The VB compiler barks at me
for some reason so I removed one of the ampersands that preceded DLookup in
the string.

I am very frustrated and confused at this point.

Tom



On Fri, 26 Oct 2007 12:04:39 -0500, Marshall Barton
wrote:

Tom wrote:

Marsh,

When I run this code from the On Open Event Procedure,

Private Sub Report_Open(Cancel As Integer)
Me.GroupLevel(0) = "=StandardizePartNum([" & _
DLookup("Setting", "Settings") & "])"
End Sub

I get a Runtime error #2464 There is no sorting or grouping field or
expression defined for the goup level number you used.


You must precreate an entry in the Sorting and Grouping
window. I thought you said you had already done that??

Once you get the report sorting as you said it was, then add
the code to change it to the use the field from the settings
table. (Double check to make sure the record in the
Settings table has the name of the field you want to use.)
--
Tom

Well, Tom, I would never have tried that, but I can't argue
with your thinking. Your syntax needs a little help though:

Try this:
=StandardizePartNum(DLookup("Setting", "Settings"))

OTOH, I suspect that might call DLookup many, many times and
be unacceptably slow. If it is, go back to the code in the
Open event.
--
Marsh
MVP [MS Access]

Tom wrote:
I did have this line =StandardizePartNum([Comp Part]) in the Sorting and
Grouping Window and it is working perfectly, but removed it thinking it was
redundant with the new code string you sent. It is now back and I have
tried to modify it per your example.

I have a Table Settings with a single Field named Setting. It has one row
with the entry Comp Part.

OK, based on your second paragraph, I don't need anything in the On Open
event. All I need to do is mod the code to look in the Settings Table.

This string is now in the Sorting and Grouping Window of my test report:

=StandardizePartNum([" & DLookup("Setting", "Settings") & "])

Now, when I run the report, it is asking for a Parameter Value for
" & DLookup("Setting", "Settings") & "

This makes sense, but I must have the syntax off.

Your original code string has two ampersands. The VB compiler barks at me
for some reason so I removed one of the ampersands that preceded DLookup in
the string.


On Fri, 26 Oct 2007 12:04:39 -0500, Marshall Barton wrote:

Tom wrote:
When I run this code from the On Open Event Procedure,

Private Sub Report_Open(Cancel As Integer)
Me.GroupLevel(0) = "=StandardizePartNum([" & _
DLookup("Setting", "Settings") & "])"
End Sub

I get a Runtime error #2464 There is no sorting or grouping field or
expression defined for the goup level number you used.


You must precreate an entry in the Sorting and Grouping
window. I thought you said you had already done that??

Once you get the report sorting as you said it was, then add
the code to change it to the use the field from the settings
table. (Double check to make sure the record in the
Settings table has the name of the field you want to use.)

Marsh,

One point I am not clear on is do I need the string in the Sorting and
Grouping Window AND the On Open event procedure if I use the On Open Event
to make the decision.

Thx

Tom


On Fri, 26 Oct 2007 17:58:02 -0500, Marshall Barton
wrote:

Well, Tom, I would never have tried that, but I can't argue
with your thinking. Your syntax needs a little help though:

Try this:
=StandardizePartNum(DLookup("Setting", "Settings"))

OTOH, I suspect that might call DLookup many, many times and
be unacceptably slow. If it is, go back to the code in the
Open event.
--
Tom

Yes, you need something/anything in Sorting and Grouping in
design view so the GroupLevel exists when you try to modify
it in the open event. I usually put an example expression
such as your original
=StandardizePartNum([Comp Part])
just as a reminder of what kind of thing I am doing. On
some ocassions I use something like
="Changed in Open event"
--
Marsh
MVP [MS Access]


Tom wrote:
One point I am not clear on is do I need the string in the Sorting and
Grouping Window AND the On Open event procedure if I use the On Open Event
to make the decision.


On Fri, 26 Oct 2007 17:58:02 -0500, Marshall Barton wrote:

Well, Tom, I would never have tried that, but I can't argue
with your thinking. Your syntax needs a little help though:

Try this:
=StandardizePartNum(DLookup("Setting", "Settings"))

OTOH, I suspect that might call DLookup many, many times and
be unacceptably slow. If it is, go back to the code in the
Open event.

Marsh,

Am trying your suggestion below. I have this string in the On Open event
procedu

Private Sub Report_Open(Cancel As Integer)
Me.GroupLevel(0) = "=StandardizePartNum([" & DLookup("Setting",
"Settings") & "])"
End Sub

I now get a 438 error: Object doesn't support this property or method
(Error 438)

Do I have a syntax issue again?

Thx





On Sat, 27 Oct 2007 11:48:26 -0500, Marshall Barton
wrote:

Yes, you need something/anything in Sorting and Grouping in
design view so the GroupLevel exists when you try to modify
it in the open event. I usually put an example expression
such as your original
=StandardizePartNum([Comp Part])
just as a reminder of what kind of thing I am doing. On
some ocassions I use something like
="Changed in Open event"
--
Tom

Marsh,

I have also been experimenting with a Combo Box for the sort choices.

I created a Combo Box on a Form. The Form is named Main Form. The Combo
Box is named Sort. So the reference should be Forms![Main Form]!Sort.

The Row Source Type is Table/Query.
The Row Source is "Select [Sort].[Name],[Sort].[Field] From [Sort]"

In the Combo Box Properties, I indicated there are 2 columns, column width
is 1";0" and the Bound column is 2 (Field). I have set the Default Value
to Comp
Part

I created a Table called Sort. The field names are Name and Field.

Name Field
Competitive Part Comp Part
Raybestos Part RayPart
Spicer Chassis Part Spicer
Aimco Part Aimco
Federated Part Federated

Private Sub Report_Open(Cancel As Integer)
Me.GroupLevel(0) = "=StandardizePartNum([ Forms![Main Form]!Sort ])"
End Sub

Would this string work as well? I should be able to pass a Combo Box to a
string, yes?

Thx


On Sat, 27 Oct 2007 11:48:26 -0500, Marshall Barton
wrote:

Yes, you need something/anything in Sorting and Grouping in
design view so the GroupLevel exists when you try to modify
it in the open event. I usually put an example expression
such as your original
=StandardizePartNum([Comp Part])
just as a reminder of what kind of thing I am doing. On
some ocassions I use something like
="Changed in Open event"
--
Tom

Tom wrote:
I have also been experimenting with a Combo Box for the sort choices.

I created a Combo Box on a Form. The Form is named Main Form. The Combo
Box is named Sort. So the reference should be Forms![Main Form]!Sort.

The Row Source Type is Table/Query.
The Row Source is "Select [Sort].[Name],[Sort].[Field] From [Sort]"

In the Combo Box Properties, I indicated there are 2 columns, column width
is 1";0" and the Bound column is 2 (Field). I have set the Default Value
to Comp
Part

I created a Table called Sort. The field names are Name and Field.

Name Field
Competitive Part Comp Part
Raybestos Part RayPart
Spicer Chassis Part Spicer
Aimco Part Aimco
Federated Part Federated

Private Sub Report_Open(Cancel As Integer)
Me.GroupLevel(0) = "=StandardizePartNum([ Forms![Main Form]!Sort ])"
End Sub

Would this string work as well? I should be able to pass a Combo Box to a
string, yes?


Yes, you can pass a combo box's value. BUT, as with
everything else in computing, you must pay careful attention
to details, so No, that string will not work.

There's also the issue that I forgot to use the Control
Source property in my example code. While I should not
abbreviate an example like that, you should check VBA Help
on anything you are not familiar with in code you want to
use.

Your string has extra spaces inside the [ ], but that won't
matter because you can not put a reference inside [ ]. [ ]
can only enclose a name. If you would only use names with
letters and digits, you would not have to worry about using
[ ] to hide spaces and other syntax confusing characters.

I think you were trying to use:

Me.GroupLevel(0).ControlSource = _
"=StandardizePartNum(Forms![Main Form]!Sort)"

But, as I said before, that will be less efficient and you
should use this instead:

Me.GroupLevel(0).ControlSource = _
"=StandardizePartNum([" & Forms![Main Form]!Sort & "])"

Note the difference is that your expression sets the
GroupLevel expression to:
=StandardizePartNum(Forms![Main Form]!Sort)
so the combo box reference might be evaluated for every row
in the report's record source.

The statement I recommended sets it to:
=StandardizePartNum([Comp Part])
and the combo box reference was evaluated just once in the
Open event so it does not have to be evaluated in the
sorting operation.

--
Marsh
MVP [MS Access]