A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Attn: Marshall Barton



 
 
Thread Tools Display Modes
  #61  
Old October 28th, 2007, 04:49 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Attn: Marshall Barton

Tom wrote:
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?



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

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

--
Marsh
MVP [MS Access]
  #62  
Old October 28th, 2007, 05:11 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Attn: Marshall Barton

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



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.

--
Marsh
MVP [MS Access]
  #63  
Old October 28th, 2007, 06:02 PM posted to microsoft.public.access.reports
Tom
external usenet poster
 
Posts: 72
Default Attn: Marshall Barton

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


On Sun, 28 Oct 2007 09:46:02 -0600, Marshall Barton
wrote:

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.

--
Tom

  #64  
Old October 28th, 2007, 07:12 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Attn: Marshall Barton

Tom wrote:
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.

--
Marsh
MVP [MS Access]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:03 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.