VBA - GET / SET ??

[MVP - WannaB]

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.

 

[vbasean]

You really don't need the 'LBound' field if it is the value from the previous
record. What you would need is a 'Ranked' query to pull the value.

http://blogs.conchango.com/jamespip...a-value-from-the-previous-row-in-a-query.aspx

 

[Clif McIrvin]

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 McIrvin]

You really don't need the 'LBound' field if it is the value from the
previous
record. What you would need is a 'Ranked' query to pull the value.

Chris, what is the value of Lbound in the first row? [Or, where does the
initial value come from?] Doesn't that tend to require storing the
value?

http://blogs.conchango.com/jamespip...a-value-from-the-previous-row-in-a-query.aspx

Thanks for that link --- I'm keeping that one!

<remainder snipped>

 

[Clif McIrvin]

Are you intentionally trying to insult MVPs? You've succeeded. "MVP
WannaB"
as a display name says to everybody "My skills are at MVP level, so
please
evaluate me for the award".

Your post implies MVPs are clueless and don't know what get and set
are for,
don't know they shouldn't use reserved words for column names, and
don't know
that sorting data in a certain order requires an order by clause in
the query.

Chris, I disagree. I've never interpreted WannaB's display name that
way; rather as a future goal that is being worked toward.

 

[vbasean]

Chris,
I know you worked hard for your MVP and rightly so but have you concidered
that his handle implies that he 'aspires' to be an MVP one day. Which would
be flattery instead of insult...

 

[MVP - WannaB]

OH Chris, I regret that you misunderstand me, and I mean no offense. My
display name actually tells everyone that I wish I were bright enough to be
an MVP and that I admire and respect all MVPs such that I strive to be as
good as they already are. While I am hurt that you misunderstand my intent,
I do understand that everyone has there own interpretation of what they read.
I will consider a display name that show the admiration that I truly have
for all MVP's who have achieved such a level of accomplishment.

As for your thought that "my post implies MVPs are clueless" That is the
last thing that I intend to imply and I would not waste my time to post such
rubble.
==========================

 

[BruceM]

This brings up a question I have had about your MVP designation. I cannot
find your name listed at mvps.org, at least not as Chris O'C.

 

[Dirk Goldgar]

OH Chris, I regret that you misunderstand me, and I mean no offense. My
display name actually tells everyone that I wish I were bright enough to
be
an MVP and that I admire and respect all MVPs such that I strive to be as
good as they already are. While I am hurt that you misunderstand my
intent,
I do understand that everyone has there own interpretation of what they
read.
I will consider a display name that show the admiration that I truly have
for all MVP's who have achieved such a level of accomplishment.

As for your thought that "my post implies MVPs are clueless" That is the
last thing that I intend to imply and I would not waste my time to post
such
rubble.

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.

 

[MVP - WannaB]

Thanks Cliff, that's very helpful - I have too many meeting this afternoon,
but will use what you've provided here and post my results or more questions
as soon as I can.
1. Lbound and Ubound values are required.
2. The TAB order is set so they should go through each field sequentialy,
but they can jump around.
3. All rows are preset, no additional rows will(SHOULD) be added, this is
only to allow modifications to a table that is used to rank results from
another field in another table. and there is far too much already in place
to change the structure of what is currently in use.
THANK YOU SO MUCH!!!
=======================================

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

 

[Klatuu]

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.

 

[Clif McIrvin]

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.

Thanks, Dave, for the additional explanation. I've noticed a bit of
that myself.

 

[BruceM]

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

 

[BruceM]

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.

 

[Klatuu]

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.

 

[Clif McIrvin]

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

 

[BruceM]

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.

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 McIrvin]

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?"

 

[Klatuu]

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.

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
==========================================
==========================================

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 McIrvin]

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>