VBA - GET / SET ??

You know how you can tell who's an MVP and not someone posing as an MVP or
under someone else's MVP name? You go to Microsoft's community website and
check for blue MVP icons next to their names. Only real MVPs get an MVP icon
next to their names when they post using their Microsoft MVP accounts.

Check out these threads for Jerry Whittle, Chris O'C, Tom Wickerath and Larry
Linson with official blue MVP icons next to our names in our posts in these
threads:

http://www.microsoft.com/office/comm...xp=&sloc=en-us


http://www.microsoft.com/office/comm...b-c95f48917f15


Chris
Microsoft MVP


BruceM wrote:
OK. I expect you understand the reason such questions arise is that your
MVP designation is by your word alone.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200810/1

The links you provided brought me to the web newsreader for these groups. I
see no icons other than the ones that are part of every message.

"Chris O'C via AccessMonster.com" u29189@uwe wrote in message
news:8c5a562bec1f0@uwe...
You know how you can tell who's an MVP and not someone posing as an MVP or
under someone else's MVP name? You go to Microsoft's community website
and
check for blue MVP icons next to their names. Only real MVPs get an MVP
icon
next to their names when they post using their Microsoft MVP accounts.

Check out these threads for Jerry Whittle, Chris O'C, Tom Wickerath and
Larry
Linson with official blue MVP icons next to our names in our posts in
these
threads:

http://www.microsoft.com/office/comm...xp=&sloc=en-us


http://www.microsoft.com/office/comm...b-c95f48917f15


Chris
Microsoft MVP


BruceM wrote:
OK. I expect you understand the reason such questions arise is that your
MVP designation is by your word alone.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200810/1

Normally I do see the MVP logo, but I don't see any on the link you provide.
I do see some in the second thread. Your name appears two different ways -
with and without. That indicates you log on from different locations.

I made it very plain I don't know if you are or are not, it is just that
there is a lot of doubt.

" I'd quickly lose my job because of the hostilities other MVPs caused,
though
I had nothing to do with them. Guilty by association.
"
Talk about offensive to MVP's

I also said in my post you are knowledgeable and usually helpful, but in
this case I stand by my statement that you are out of line. It is not the
first time I have seen you go weird for no apparent reason.


"Chris O'C via AccessMonster.com" u29189@uwe wrote in message
news:8c59d368df37c@uwe...
My MVP profile isn't public because my company would find out I'm an MVP.
I'd quickly lose my job because of the hostilities other MVPs caused,
though
I had nothing to do with them. Guilty by association.

But there's easy proof of who's an MVP. Only MVPs can log in with their
MVP
Windows Live ID and post with a blue MVP icon next to their names on
Microsoft's site. You can see Jerry Whittle, Chris O'C, Tom Wickerath and
Larry Linson with official blue MVP icons next to our names in our posts
in
these threads:

http://www.microsoft.com/office/comm...xp=&sloc=en-us


http://www.microsoft.com/office/comm...b-c95f48917f15


My boss hates MVPs, but even if an employee wasn't an MVP and said about
another employee "take his comments with a grain of salt. He is
knowledgable
and often helpful, but occasionally goes a bit off tilt now and then",
he'd
fire him immediately. Why? Because it undermines trust and goodwill
between
colleagues and isn't professional behavior.

Chris
Microsoft MVP


Klatuu wrote:
Clif,
I apologize for Chris. I did not read it that way at all.
Also, understand there is some doubt whether he actually is an MVP. He
claims he works for a company where in would frowned on for him to known
as
an MVP and therefore does not allow his profile to be published. That is
legitimate. You can hide your information so it is known only to
Microsoft,
but that is very rare indeed.

There has been discussion in the MVP private newgroups as to who he is and
whether is claim to be an MVP is real or not. He does not appear in the
private MVP sites that I know of. Notice he doesn't say in what disipline
he
received his award.

So the bottom line is, take his comments with a grain of salt. He is
knowledgable and often helpful, but occasionally goes a bit off tilt now
and
then.

--
Message posted via http://www.accessmonster.com

"BruceM" wrote in message
...
The links you provided brought me to the web newsreader for these
groups. I see no icons other than the ones that are part of every
message.

Bruce, that's what I thought too, until I scrolled all the way through
all three threads these two links brought up. That blue MVP icon Chris
is referring to does appear in the 2nd and third threads.

--
Clif


"Chris O'C via AccessMonster.com" u29189@uwe wrote in message
news:8c5a562bec1f0@uwe...
You know how you can tell who's an MVP and not someone posing as an
MVP or
under someone else's MVP name? You go to Microsoft's community
website and
check for blue MVP icons next to their names. Only real MVPs get an
MVP icon
next to their names when they post using their Microsoft MVP
accounts.

Check out these threads for Jerry Whittle, Chris O'C, Tom Wickerath
and Larry
Linson with official blue MVP icons next to our names in our posts in
these
threads:

http://www.microsoft.com/office/comm...xp=&sloc=en-us


http://www.microsoft.com/office/comm...b-c95f48917f15


Chris
Microsoft MVP


BruceM wrote:
OK. I expect you understand the reason such questions arise is that
your
MVP designation is by your word alone.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200810/1





--
Clif
Still learning Access 2003

Yes, I finally found some postings with the icon. I am still curious as to
why he refuses to say in what area he received the MVP designation. By
identifying himself as an MVP here he suggests this is his area of
expertise. In a sense I suppose the discipline doesn't matter if the
answers are helpful, as they often are, but even so I find the retricence
rather curious.

"Clif McIrvin" wrote in message
...
"BruceM" wrote in message
...
The links you provided brought me to the web newsreader for these groups.
I see no icons other than the ones that are part of every message.

Bruce, that's what I thought too, until I scrolled all the way through all
three threads these two links brought up. That blue MVP icon Chris is
referring to does appear in the 2nd and third threads.

--
Clif


"Chris O'C via AccessMonster.com" u29189@uwe wrote in message
news:8c5a562bec1f0@uwe...
You know how you can tell who's an MVP and not someone posing as an MVP
or
under someone else's MVP name? You go to Microsoft's community website
and
check for blue MVP icons next to their names. Only real MVPs get an MVP
icon
next to their names when they post using their Microsoft MVP accounts.

Check out these threads for Jerry Whittle, Chris O'C, Tom Wickerath and
Larry
Linson with official blue MVP icons next to our names in our posts in
these
threads:

http://www.microsoft.com/office/comm...xp=&sloc=en-us


http://www.microsoft.com/office/comm...b-c95f48917f15


Chris
Microsoft MVP


BruceM wrote:
OK. I expect you understand the reason such questions arise is that
your
MVP designation is by your word alone.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200810/1





--
Clif
Still learning Access 2003

"BruceM" wrote in message
...
Yes, I finally found some postings with the icon. I am still curious
as to why he refuses to say in what area he received the MVP
designation. By identifying himself as an MVP here he suggests this
is his area of expertise. In a sense I suppose the discipline doesn't
matter if the answers are helpful, as they often are, but even so I
find the retricence rather curious.

I suppose there are some questions we will never have full answers to. I
prefer to take things folks say at face value, until evidence strongly
suggests otherwise. I have been burned from time to time by this
philosophy, but not badly enough that I have seen the need to change it.

It seems to me that I have seen his sig in other forums that I lurk in;
and as here, his posts generally seem helpful and on target. Also, like
Klatuu, there have been posts that simply leave me scratching my head
and wondering, "Where did *that* come from?"

--
Clif
Still learning Access 2003

Hello and again Thank you very much for any assistance you can provide.
Based on guidance provided by Cliff, this is where I am at so far.
First I will attempt describe as best I can what you don't see.
The form I am working on is a sub-form of a primary form. The display format
is datasheet view, there are 4 columns, Lbound in row 1 will always be 0.
There are 11 records ID 1 - 11.
What I have written here seems to work in all situations that I have tested
except one (If a user make a change to Ubound in row2 then clicks on Ubound
in row4, Lbound in row3 never gets set to the correct value.

So my question; is there a way perhaps using the ID field to set the value
of Lbound of row3 when focus is lost on Ubound of row2?

======================
Private Sub Form_Open(Cancel As Integer)
On Error GoTo ProcError

Me.Ubound.SetFocus

ExitProc:
Exit Sub

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdEndDate_Click..."
Resume ExitProc
End Sub
======================
Private Sub Lbound_GotFocus()
On Error GoTo ProcError

If Me.ID = (Me.ctl_prvID + 1) Then
Me.Lbound = Me.ctl_prvUbound
End If

ExitProc:
Me.Ubound.SetFocus
Exit Sub

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdEndDate_Click..."
Resume ExitProc
End Sub
======================
Private Sub Ubound_LostFocus()
On Error GoTo ProcError

If Me.ID = 1 Then
Me.ctl_prvUbound = Me.Ubound
Else
If Me.ID = (Me.ctl_prvID + 1) Then
Me.Lbound = Me.ctl_prvUbound
End If
Me.ctl_prvUbound = Me.Ubound
Me.ctl_prvID = Me.ID
End If

ExitProc:
Exit Sub

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdEndDate_Click..."
Resume ExitProc
End Sub
==========================================
==========================================

"Clif McIrvin" wrote:

"MVP - WannaB" wrote in message
...
Hello, I'm not sure that subject line is exactly right for this but in
my
head it sounded close.
I have a form with 4 columns of data (ID, Lbound, Ubound, and
Description).
There are 11 rows of data that a user can modify, and because Lbound
in every
row needs to be the same as Ubound of the previous row I would like to
GET
the value of Ubound for each row and SET the value of Lbound. So the
user
only needs to enter or modify the value for Ubound and the
description. Is
GET and SET the right functions to use or am I getting VBA mixed up
with VB?
And can someone start me in the right direction to accomplish this?
Thanks
for your help in advance. I truly appreciate all your help and still
hope to
be able to help others one of these days.



In VBA GET and SET relate to properties of class objects --- different
subject altogether. (In the VBA editor, type class module into the help
search and have fun reading! Some developers write class modules, others
don't, from what I hear.)

Are the Lbound and Ubound values required?

Can the user 'jump around' in the 11 rows? That is, can you guarantee
that the user will always progress sequentially through the rows? In my
experience that's generally a bad assumption.

Assuming an initial data entry environment where the user always
proceeds sequentially 'down' the form something along these lines:

In the form (or Ubound control) AfterUpdate event set the default value
for Lbound [assuming that the next row hasn't been added to the table
yet.]

ctlLbound.DefaultValue = ctlUbound.Value

(DefultValue is always a string --- in some situations you need to
handle the data type conversion yourself.)

If the 11 rows are already present in the table the default value won't
help you. In this situation you can use the form's Current event to set
the Lbound value. I have also used the form's Before Insert event
instead of the current event so that I only modify a control's value if
the user begins typing a value into any control; the Current event will
fire anytime the current record pointer changes for any reason.

HTH
--
Clif
Still learning Access 2003

If I understand what you are saying, you need to update the entire recordset
of the subform each time you make a change. It may be that you need to
write a recordset processing routine to loop through all 11 records and
update the values any time you move from one row to the next.

In addition, I would suggest you change the names LBound and UBound. Both
are VBA intrinsic function uses to determine the first and last indexes in
an array.

"MVP - WannaB" wrote in message
...
Hello and again Thank you very much for any assistance you can provide.
Based on guidance provided by Cliff, this is where I am at so far.
First I will attempt describe as best I can what you don't see.
The form I am working on is a sub-form of a primary form. The display
format
is datasheet view, there are 4 columns, Lbound in row 1 will always be 0.
There are 11 records ID 1 - 11.
What I have written here seems to work in all situations that I have
tested
except one (If a user make a change to Ubound in row2 then clicks on
Ubound
in row4, Lbound in row3 never gets set to the correct value.

So my question; is there a way perhaps using the ID field to set the value
of Lbound of row3 when focus is lost on Ubound of row2?

======================
Private Sub Form_Open(Cancel As Integer)
On Error GoTo ProcError

Me.Ubound.SetFocus

ExitProc:
Exit Sub

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdEndDate_Click..."
Resume ExitProc
End Sub
======================
Private Sub Lbound_GotFocus()
On Error GoTo ProcError

If Me.ID = (Me.ctl_prvID + 1) Then
Me.Lbound = Me.ctl_prvUbound
End If

ExitProc:
Me.Ubound.SetFocus
Exit Sub

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdEndDate_Click..."
Resume ExitProc
End Sub
======================
Private Sub Ubound_LostFocus()
On Error GoTo ProcError

If Me.ID = 1 Then
Me.ctl_prvUbound = Me.Ubound
Else
If Me.ID = (Me.ctl_prvID + 1) Then
Me.Lbound = Me.ctl_prvUbound
End If
Me.ctl_prvUbound = Me.Ubound
Me.ctl_prvID = Me.ID
End If

ExitProc:
Exit Sub

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdEndDate_Click..."
Resume ExitProc
End Sub
==========================================
==========================================

"Clif McIrvin" wrote:

"MVP - WannaB" wrote in message
...
Hello, I'm not sure that subject line is exactly right for this but in
my
head it sounded close.
I have a form with 4 columns of data (ID, Lbound, Ubound, and
Description).
There are 11 rows of data that a user can modify, and because Lbound
in every
row needs to be the same as Ubound of the previous row I would like to
GET
the value of Ubound for each row and SET the value of Lbound. So the
user
only needs to enter or modify the value for Ubound and the
description. Is
GET and SET the right functions to use or am I getting VBA mixed up
with VB?
And can someone start me in the right direction to accomplish this?
Thanks
for your help in advance. I truly appreciate all your help and still
hope to
be able to help others one of these days.



In VBA GET and SET relate to properties of class objects --- different
subject altogether. (In the VBA editor, type class module into the help
search and have fun reading! Some developers write class modules, others
don't, from what I hear.)

Are the Lbound and Ubound values required?

Can the user 'jump around' in the 11 rows? That is, can you guarantee
that the user will always progress sequentially through the rows? In my
experience that's generally a bad assumption.

Assuming an initial data entry environment where the user always
proceeds sequentially 'down' the form something along these lines:

In the form (or Ubound control) AfterUpdate event set the default value
for Lbound [assuming that the next row hasn't been added to the table
yet.]

ctlLbound.DefaultValue = ctlUbound.Value

(DefultValue is always a string --- in some situations you need to
handle the data type conversion yourself.)

If the 11 rows are already present in the table the default value won't
help you. In this situation you can use the form's Current event to set
the Lbound value. I have also used the form's Before Insert event
instead of the current event so that I only modify a control's value if
the user begins typing a value into any control; the Current event will
fire anytime the current record pointer changes for any reason.

HTH
--
Clif
Still learning Access 2003

"MVP - WannaB" wrote in message
...
Hello and again Thank you very much for any assistance you can
provide.
Based on guidance provided by Cliff, this is where I am at so far.
First I will attempt describe as best I can what you don't see.
The form I am working on is a sub-form of a primary form. The display
format
is datasheet view, there are 4 columns, Lbound in row 1 will always be
0.
There are 11 records ID 1 - 11.
What I have written here seems to work in all situations that I have
tested
except one (If a user make a change to Ubound in row2 then clicks on
Ubound
in row4, Lbound in row3 never gets set to the correct value.

So my question; is there a way perhaps using the ID field to set the
value
of Lbound of row3 when focus is lost on Ubound of row2?


Glad I was able to get you started!

code snipped

(Take note of Klatuu's caution against using Access / VBA reserved words
as field names. Your use of Ubound and Lbound made sense in context, and
just slipped right past me. Chalk that one up to 'still learning'
grin. If your users are familiar with Ubound and Lbound there is no
reason you can't display those names in forms and reports; just use
different fieldnames in your database, and use the Description and
Caption properties to present the familiar names to the users.)

Is Klatuu correct that you might need cascading updates, or will it
always be the case that a change on row n will only require a change on
row n+1? If you need to allow for cascading updates you'll need a bit
more code.

Two possible tools come to mind (there very well could be other
techniques):

Search help and these forums for discussions on CurrentDb.Execute --
that will let you construct and execute an update query 'on the fly'.

Or, use the (sub)form's RecordsetClone property to work directly with
the records being displayed by your subform. (A search on RecordsetClone
should turn up good discussions, as well.)

With Me.RecordsetClone
(code to navigate to the record you need to modify, using recordset
Move or Seek methods. Info is available under the installed help Table
of Contents DAO Methods by Object)

after you have navigated to the record you need to change:

.Fields("LBound") = Me.ctl_prvUbound
.Update
End With

(you may need to me.refresh to display the change you just made)

From the help on the recordset property: However, unlike using the
RecordsetClone property, changing which record is current in the
recordset returned by the form's Recordset property also sets the
current record of the form.

There -- I've given you plenty of information to get into trouble with!
grin

--
Clif
Still learning Access 2003

"Dirk Goldgar" wrote:

For what it's worth, it never occurred to me to take any offense at your
usage, nor that it might be seen as anything but a sign of aspiration and
(maybe) shameless flattery. g I can see where Chris's concerns are coming
from, but I don't feel the same way.

Agreed.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/