Attn: Marshall Barton

[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

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"

 

[Marshall Barton]

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.

 

[Marshall Barton]

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

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?

Actually, that one is my syntax issue. I should have used
the ControlSource property:

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

 

[Marshall Barton]

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

I forgot to chastize you for using reserved words for your
field names. "Name" is used by Acceess all over the place
and Sort and Field are highly suspect. A fairly safe
guideline is to never use a common word to name anything you
create.

Otherwise, your idea is perfectly valid and may very well be
easier to use than other approaches to your form's user
interface.

 

[Tom]

Marsh,

Thanks for the Reserved Word Tips. My bad

I changed the Field names in the Sort Table as well...thx.

When you mentioned adding some control source code to the On Open Event
Procedure, is this what you are referring to:

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

What a difference an Ampersand makes in the right place. Everything is now
working as I wanted with the choices for Report sorting and the sort is
working. I want to ponder it a bit more before putting into production.

You suggestion about Access Help is also a good one. I am very familiar
with the F1 Key or asking Mr Clipit a question. My challenge is I
sometimes don't "get" what I read. Other times, I am able to get enough
out of the Help to solve my questions or concerns. I may even Google from
time to time to find what I need. What I need to do is better understand
the VB language. I know what I want to do sometimes, but don't know the
language well enough to talk to the computer as you suggested below with my
Syntax errors. Would you have any suggestions for the type of class I
should take that would help someone like me?

Thx

Tom

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.

 

[Marshall Barton]

Thanks for the Reserved Word Tips. My bad

I changed the Field names in the Sort Table as well...thx.

When you mentioned adding some control source code to the On Open Event
Procedure, is this what you are referring to:

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

What a difference an Ampersand makes in the right place. Everything is now
working as I wanted with the choices for Report sorting and the sort is
working. I want to ponder it a bit more before putting into production.

You suggestion about Access Help is also a good one. I am very familiar
with the F1 Key or asking Mr Clipit a question. My challenge is I
sometimes don't "get" what I read. Other times, I am able to get enough
out of the Help to solve my questions or concerns. I may even Google from
time to time to find what I need. What I need to do is better understand
the VB language. I know what I want to do sometimes, but don't know the
language well enough to talk to the computer as you suggested below with my
Syntax errors. Would you have any suggestions for the type of class I
should take that would help someone like me?

Great news that it is working.

I wasn't really talking about using Help for expression
syntax. I was referring more to you not catching my mistake
of leaving out the group level's ControlSource property. A
definite lapse on my part, but the fact that you just copied
it without understanding the code should cause you concerns
about something.

As for recommending a course in VBA for Access, I don't even
know if such a thing is available. It may not be very
efficient for novices, but I like to just read a lot of
posts and, if I can relate to the question, try to work out
a solution, then compare that to the posted answers.
Google is also an excellent way to find all kinds of
information.