Looking for a little education on VBA code

[Rick]

As contributor “rkc†once pointed out to me, if I am to borrow code that someone else has written then I should at least understand what it does. Copying, pasting, and modifying text so that the code works on a current db is one thing – really understanding how the code works is something else again. I am trying to get educated by experience, trial and error, books, and this newsgroup. I’m bugged by not understanding this code and I don’t have my books with me right now and I’m not sure that I even have the right books for these questions, so I’m hoping that I can get some help here

I apologize in advance for the lengthy post and the ‘twenty questions’ format

If anyone has the time, I’d appreciate some explanations of the “how and why†of the following (commonly used) VBA code. I have looked up some of the words in the help files, but I’m still foggy on understanding the details of it. I’m adding what I think I understand and my questions after the code

Private Sub cboMyCombo_AfterUpdat
Dim rs As Objec
Set rs = Me.Recordset.Clon
rs.FindFirst “[MySubjectID] = “ & Str(Nz(Me![cboMyCombo], 0)
If Not rs.EOF Then Me.Bookmark = rs.Bookmar
End Su

My understanding is that in order for the form to display a particular record (selected by the combo box), Access must know where that record is currently located in the record source (a new ‘bookmark’ is created everytime the form is opened). The first two lines of the code create a new (virtual or temporary?) object (named ‘rs’) that is an exact duplicate of the forms record source. The third line locates the first record (and bookmark) that matches the value in the combo box. The last line sets the forms bookmark to match the bookmark of the virtual recordset (but only if the ‘findfirst’ action/method did not reach the end of the file)

I’m most confused about and would appreciate an explanation of
“[MySubjectID] = “ & Str(Nz(Me![cboMyCombo], 0))â€.

I see that the value of the combo is converted into a string and then the string is used for comparing to the field values. My questions

1. Why does it need to be a string? The bound column of the combo and the field are both formated as number/long integer. Couldn't value be matched to value
2. Why are the quotation marks enclosing the field name and the operator?
3. Why is the ampersand needed and/or what is its function here
4. Why the ‘Nz’?
5. Why a comma and then the zero? Is the zero indicating which column number of the combo's record source to search
6. Why is there a bang (!) between ‘Me’ and ‘[cboMyCombo]’ as opposed to a dot (.
7. I’ve read where it is sometimes useful to read VBA code right-to-left to understand it better, but how is one to know WHEN you should do so? (The Excel formulas I’ve worked with are easy to understand in comparison to VBA.
8. Feel free to offer any other information

TI

Rick..

 

[Douglas J. Steele]

1. Why does it need to be a string? The bound column of the combo and the
field are both formated as number/long integer. Couldn't value be matched
to value?

Actually, it doesn't need to be a string. It'll work perfectly well without
the Str function. Since you're concatenating the value to a string, though,
technically it's "more correct" to explicitly coerce it to a string.

2. Why are the quotation marks enclosing the field name and the operator?

What you're trying to do is create a WHERE clause (without the word WHERE in
it). WHERE clauses are strings, and the field name and operator are
constants that you want to put into the string.

3. Why is the ampersand needed and/or what is its function here?

As I implied above, you're creating a string by concatenating a constant
string to a value. The ampersand is the concatenation operator.

4. Why the 'Nz'?

If you haven't selected anything in your combo box, Me![cboMyCombo] is going
to return Null. The Nz function will convert that Null value to 0.

5. Why a comma and then the zero? Is the zero indicating which column
number of the combo's record source to search?

No, the , 0 in the Nz function is to ensure that the Null value is converted
to 0. (It's not really necessary, as that's the default). If you had comma
and then 9, then Null values would be converted to the number 9.

6. Why is there a bang (!) between 'Me' and '[cboMyCombo]' as opposed to a
dot (.)

Either will work, but the usual advice is use bang if it's something you
created (such as a control on a form) and a dot if it's something that's
part of the form. If you want to be totally correct, it should be
Me.Controls("cboMyCombo")

7. I've read where it is sometimes useful to read VBA code right-to-left to
understand it better, but how is one to know WHEN you should do so? (The
Excel formulas I've worked with are easy to understand in comparison to
VBA.)

Never heard this one, so I can't offer any advice.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

As contributor "rkc" once pointed out to me, if I am to borrow code that

someone else has written then I should at least understand what it does.
Copying, pasting, and modifying text so that the code works on a current db
is one thing - really understanding how the code works is something else
again. I am trying to get educated by experience, trial and error, books,
and this newsgroup. I'm bugged by not understanding this code and I don't
have my books with me right now and I'm not sure that I even have the right
books for these questions, so I'm hoping that I can get some help here.

I apologize in advance for the lengthy post and the 'twenty questions' format.

If anyone has the time, I'd appreciate some explanations of the "how and

why" of the following (commonly used) VBA code. I have looked up some of
the words in the help files, but I'm still foggy on understanding the
details of it. I'm adding what I think I understand and my questions after
the code.

Private Sub cboMyCombo_AfterUpdate
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[MySubjectID] = " & Str(Nz(Me![cboMyCombo], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

My understanding is that in order for the form to display a particular

record (selected by the combo box), Access must know where that record is
currently located in the record source (a new 'bookmark' is created
everytime the form is opened). The first two lines of the code create a new
(virtual or temporary?) object (named 'rs') that is an exact duplicate of
the forms record source. The third line locates the first record (and
bookmark) that matches the value in the combo box. The last line sets the
forms bookmark to match the bookmark of the virtual recordset (but only if
the 'findfirst' action/method did not reach the end of the file).

I'm most confused about and would appreciate an explanation of:
"[MySubjectID] = " & Str(Nz(Me![cboMyCombo], 0))".

I see that the value of the combo is converted into a string and then the

string is used for comparing to the field values. My questions:

1. Why does it need to be a string? The bound column of the combo and the

field are both formated as number/long integer. Couldn't value be matched
to value?

2. Why are the quotation marks enclosing the field name and the operator?
3. Why is the ampersand needed and/or what is its function here?
4. Why the 'Nz'?
5. Why a comma and then the zero? Is the zero indicating which column

number of the combo's record source to search?

6. Why is there a bang (!) between 'Me' and '[cboMyCombo]' as opposed to a dot (.)
7. I've read where it is sometimes useful to read VBA code right-to-left

to understand it better, but how is one to know WHEN you should do so? (The
Excel formulas I've worked with are easy to understand in comparison to
VBA.)

 

[Rick Willingham]

Douglas

Thanks for the really quick and explicit response! I must have overlooked the help files explanation that the 'FindFirst' method is looking for a string that works like a WHERE clause

Now that you've explained things, I can understand the line a little better.

So, if the same line might be written more simply as ' rs.FindFirst "[MySubjectID] =" & Me.[MyCombo] ', why isn't it?

Why is it "technically more correct" to coerce values to strings when concantenating the value to another string? Will Access or VBA unexpectedly 'hiccup' on a value within a string

Thanks again for your help

Rick..
----- Douglas J. Steele wrote: ----

1. Why does it need to be a string? The bound column of the combo and th
field are both formated as number/long integer. Couldn't value be matche
to value

Actually, it doesn't need to be a string. It'll work perfectly well withou
the Str function. Since you're concatenating the value to a string, though
technically it's "more correct" to explicitly coerce it to a string

2. Why are the quotation marks enclosing the field name and the operator

What you're trying to do is create a WHERE clause (without the word WHERE i
it). WHERE clauses are strings, and the field name and operator ar
constants that you want to put into the string

3. Why is the ampersand needed and/or what is its function here

As I implied above, you're creating a string by concatenating a constan
string to a value. The ampersand is the concatenation operator

4. Why the 'Nz'

If you haven't selected anything in your combo box, Me![cboMyCombo] is goin
to return Null. The Nz function will convert that Null value to 0

5. Why a comma and then the zero? Is the zero indicating which colum
number of the combo's record source to search

No, the , 0 in the Nz function is to ensure that the Null value is converte
to 0. (It's not really necessary, as that's the default). If you had comm
and then 9, then Null values would be converted to the number 9

6. Why is there a bang (!) between 'Me' and '[cboMyCombo]' as opposed to
dot (.

Either will work, but the usual advice is use bang if it's something yo
created (such as a control on a form) and a dot if it's something that'
part of the form. If you want to be totally correct, it should b
Me.Controls("cboMyCombo"

7. I've read where it is sometimes useful to read VBA code right-to-left t
understand it better, but how is one to know WHEN you should do so? (Th
Excel formulas I've worked with are easy to understand in comparison t
VBA.

Never heard this one, so I can't offer any advice

--
Doug Steele, Microsoft Access MV
http://I.Am/DougSteel
(No private e-mails, please

As contributor "rkc" once pointed out to me, if I am to borrow code tha

someone else has written then I should at least understand what it does
Copying, pasting, and modifying text so that the code works on a current d
is one thing - really understanding how the code works is something els
again. I am trying to get educated by experience, trial and error, books
and this newsgroup. I'm bugged by not understanding this code and I don'
have my books with me right now and I'm not sure that I even have the righ
books for these questions, so I'm hoping that I can get some help herewhy" of the following (commonly used) VBA code. I have looked up some o
the words in the help files, but I'm still foggy on understanding th
details of it. I'm adding what I think I understand and my questions afte
the code.

Private Sub cboMyCombo_AfterUpdate

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[MySubjectID] = " & Str(Nz(Me![cboMyCombo], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

My understanding is that in order for the form to display a particular

record (selected by the combo box), Access must know where that record is
currently located in the record source (a new 'bookmark' is created
everytime the form is opened). The first two lines of the code create a new
(virtual or temporary?) object (named 'rs') that is an exact duplicate of
the forms record source. The third line locates the first record (and
bookmark) that matches the value in the combo box. The last line sets the
forms bookmark to match the bookmark of the virtual recordset (but only if
the 'findfirst' action/method did not reach the end of the file).

I'm most confused about and would appreciate an explanation of:

"[MySubjectID] = " & Str(Nz(Me![cboMyCombo], 0))".

I see that the value of the combo is converted into a string and then the string is used for comparing to the field values. My questions:
1. Why does it need to be a string? The bound column of the combo and the

field are both formated as number/long integer. Couldn't value be matched
to value?

2. Why are the quotation marks enclosing the field name and the operator?
3. Why is the ampersand needed and/or what is its function here?
4. Why the 'Nz'?
5. Why a comma and then the zero? Is the zero indicating which column

number of the combo's record source to search?

6. Why is there a bang (!) between 'Me' and '[cboMyCombo]' as opposed to a dot (.)
7. I've read where it is sometimes useful to read VBA code right-to-left

to understand it better, but how is one to know WHEN you should do so? (The
Excel formulas I've worked with are easy to understand in comparison to
VBA.)

 

[Rick]

Nevermind, I tried my 'simplified' line and I can cause an error by deleting the entry in the combo box. I guess that without the Str and Nz functions to insure that there is always something added to the string, it'll crash

Thank again

Rick..

----- Rick Willingham wrote: ----

Douglas

Thanks for the really quick and explicit response! I must have overlooked the help files explanation that the 'FindFirst' method is looking for a string that works like a WHERE clause

Now that you've explained things, I can understand the line a little better.

So, if the same line might be written more simply as ' rs.FindFirst "[MySubjectID] =" & Me.[MyCombo] ', why isn't it?

Why is it "technically more correct" to coerce values to strings when concantenating the value to another string? Will Access or VBA unexpectedly 'hiccup' on a value within a string

Thanks again for your help

Rick..
----- Douglas J. Steele wrote: ----

1. Why does it need to be a string? The bound column of the combo and th
field are both formated as number/long integer. Couldn't value be matche
to value

Actually, it doesn't need to be a string. It'll work perfectly well withou
the Str function. Since you're concatenating the value to a string, though
technically it's "more correct" to explicitly coerce it to a string

2. Why are the quotation marks enclosing the field name and the operator

What you're trying to do is create a WHERE clause (without the word WHERE i
it). WHERE clauses are strings, and the field name and operator ar
constants that you want to put into the string

3. Why is the ampersand needed and/or what is its function here

As I implied above, you're creating a string by concatenating a constan
string to a value. The ampersand is the concatenation operator

4. Why the 'Nz'

If you haven't selected anything in your combo box, Me![cboMyCombo] is goin
to return Null. The Nz function will convert that Null value to 0

5. Why a comma and then the zero? Is the zero indicating which colum
number of the combo's record source to search

No, the , 0 in the Nz function is to ensure that the Null value is converte
to 0. (It's not really necessary, as that's the default). If you had comm
and then 9, then Null values would be converted to the number 9

6. Why is there a bang (!) between 'Me' and '[cboMyCombo]' as opposed to
dot (.

Either will work, but the usual advice is use bang if it's something yo
created (such as a control on a form) and a dot if it's something that'
part of the form. If you want to be totally correct, it should b
Me.Controls("cboMyCombo"

7. I've read where it is sometimes useful to read VBA code right-to-left t
understand it better, but how is one to know WHEN you should do so? (Th
Excel formulas I've worked with are easy to understand in comparison t
VBA.

Never heard this one, so I can't offer any advice

--
Doug Steele, Microsoft Access MV
http://I.Am/DougSteel
(No private e-mails, please

As contributor "rkc" once pointed out to me, if I am to borrow code tha

someone else has written then I should at least understand what it does
Copying, pasting, and modifying text so that the code works on a current d
is one thing - really understanding how the code works is something els
again. I am trying to get educated by experience, trial and error, books,
and this newsgroup. I'm bugged by not understanding this code and I don't
have my books with me right now and I'm not sure that I even have the right
books for these questions, so I'm hoping that I can get some help here.why" of the following (commonly used) VBA code. I have looked up some of
the words in the help files, but I'm still foggy on understanding the
details of it. I'm adding what I think I understand and my questions after
the code.

Private Sub cboMyCombo_AfterUpdate

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[MySubjectID] = " & Str(Nz(Me![cboMyCombo], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

My understanding is that in order for the form to display a particular

record (selected by the combo box), Access must know where that record is
currently located in the record source (a new 'bookmark' is created
everytime the form is opened). The first two lines of the code create a new
(virtual or temporary?) object (named 'rs') that is an exact duplicate of
the forms record source. The third line locates the first record (and
bookmark) that matches the value in the combo box. The last line sets the
forms bookmark to match the bookmark of the virtual recordset (but only if
the 'findfirst' action/method did not reach the end of the file).

I'm most confused about and would appreciate an explanation of:

"[MySubjectID] = " & Str(Nz(Me![cboMyCombo], 0))".

I see that the value of the combo is converted into a string and then the string is used for comparing to the field values. My questions:
1. Why does it need to be a string? The bound column of the combo and the

field are both formated as number/long integer. Couldn't value be matched
to value?

2. Why are the quotation marks enclosing the field name and the operator?
3. Why is the ampersand needed and/or what is its function here?
4. Why the 'Nz'?
5. Why a comma and then the zero? Is the zero indicating which column

number of the combo's record source to search?

6. Why is there a bang (!) between 'Me' and '[cboMyCombo]' as opposed to a dot (.)
7. I've read where it is sometimes useful to read VBA code right-to-left

to understand it better, but how is one to know WHEN you should do so? (The
Excel formulas I've worked with are easy to understand in comparison to
VBA.)

 

[Marc]

Private Sub cboMyCombo_AfterUpdate
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[MySubjectID] = " & Str(Nz(Me![cboMyCombo], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

My understanding is that in order for the form to display a particular

record (selected by the combo box), Access must know where that record is
currently located in the record source (a new 'bookmark' is created
everytime the form is opened). The first two lines of the code create a new
(virtual or temporary?) object (named 'rs') that is an exact duplicate of
the forms record source. The third line locates the first record (and
bookmark) that matches the value in the combo box. The last line sets the
forms bookmark to match the bookmark of the virtual recordset (but only if
the 'findfirst' action/method did not reach the end of the file).
Hi,
Seeing some really picky stuff was being discussed, and Rick seems to want
exactness, I thought I would just point out:
a) rs is a temporary dataset, and should be closed and set to nothing.
b) the fourth line moves the forms recordset to the record pointed to by the
temporary recordset.
Marc

 

[Rick]

----- Marc wrote: ----


a) rs is a temporary dataset, and should be closed and set to nothing
b) the fourth line moves the forms recordset to the record pointed to by th
temporary recordset
Mar

Hi Marc

Thanks for responding.

On your point b), I gathered that was what was happening with the 'bookmark' method, I guess I just didn't express that as well as you did.

On your point a), my uncertainty was based on the mechanics of duplicating the data set. Was a duplicate table being created and then deleted, making it temporary or was the duplicate made 'virtually' where it would never show up in the db window.

After reading your comment, I checked the db window and could not see any new object named rs so I guess that rs is a virtual object occupying memory space. I had assumed that the object would delete itself when the form closed, but your comment that it needs to be closed and set to nothing makes me wonder about my assumption. Does the rs object stay in memory until the program closes? Or is it only until the form closes?

If it is residing in memory, then how/where would I go about closing and setting it to nothing? Would I add code to the form's Close event? Or add another line at the end of the control's afterupdate event code? How would that line read? Possibly ' Set rs = Null ' or ' Delete rs '

Thanks for joining this discussion and helping me out

Rick..

 

[Rick]

----- Marc wrote: ----

Seeing some really picky stuff was being discussed, and Rick seems to wan
exactness, I thought I would just point out
Mar

Hi again Marc

I wasn't trying to be picky. It was more like "What forest? All I see are trees!". To me, that third line looked like a lot of separate formulas (instead of formulas building a single WHERE clause string) and I didn't understand the specifics of what was happening even though I got the jist of what it was doing.

BTW, if anyone else reads my posts and sees really embarassingly stupid/obvious/picky questions from me, please forgive my ignorance. Trying to teach one's self is bound to lead to a few misunderstandings. I don't have a relationship with anyone that knows or uses Access, so I come here for not only for education and information, but also a little validation that I'm on the right path with my (first) project. If that behaviour is not proper for this venue, then let me know

Thanks

Rick...

 

[Marc]

On your point a), my uncertainty was based on the mechanics of duplicating

the data set. Was a duplicate table being created and then deleted, making
it temporary or was the duplicate made 'virtually' where it would never show
up in the db window.

After reading your comment, I checked the db window and could not see any

new object named rs so I guess that rs is a virtual object occupying memory
space. I had assumed that the object would delete itself when the form
closed, but your comment that it needs to be closed and set to nothing makes
me wonder about my assumption. Does the rs object stay in memory until the
program closes? Or is it only until the form closes?

If it is residing in memory, then how/where would I go about closing and

setting it to nothing? Would I add code to the form's Close event? Or add
another line at the end of the control's afterupdate event code? How would
that line read? Possibly ' Set rs = Null ' or ' Delete rs '?

Thanks for joining this discussion and helping me out.

Rick...

Hi
It is only created in memory, and you need first the close statement and
then the null statement. I'm not sure of the exact technical description,
but the rs is an object, not a variable. In Access and other products,
objects can have a different scope to local variables. Therefore if you are
finished with an object you need to explicitly say so.

Marc

 

[Marc]

Hi again Marc,

I wasn't trying to be picky. It was more like "What forest? All I see

are trees!". To me, that third line looked like a lot of separate formulas
(instead of formulas building a single WHERE clause string) and I didn't
understand the specifics of what was happening even though I got the jist of
what it was doing.

BTW, if anyone else reads my posts and sees really embarassingly

stupid/obvious/picky questions from me, please forgive my ignorance. Trying
to teach one's self is bound to lead to a few misunderstandings. I don't
have a relationship with anyone that knows or uses Access, so I come here
for not only for education and information, but also a little validation
that I'm on the right path with my (first) project. If that behaviour is
not proper for this venue, then let me know.
Hi
Sorry to cause offence Rick. It was actually a compliment, at trying to get
all the concepts straight from the start rather than just in time or not at
all is unusual.

I don't have a lot of people to discuss my programming with either, so I
understand that. You're just fine.
Marc

 

[Rick]

Hi Marc

Thanks again for all of your responses

To the experts,with reference to what Marc wrote...

----- Marc wrote: ----
It is only created in memory, and you need first the close statement an
then the null statement. I'm not sure of the exact technical description
but the rs is an object, not a variable. In Access and other products
objects can have a different scope to local variables. Therefore if you ar
finished with an object you need to explicitly say so

Question 1) How/where exactly should I close then null the rs object (in this discussion) so that it is not taking up memory
Question 2) Is it really needed? I haven't notice here or in my books that it is needed, but if the record source is large enough the memory usage could slow down processing speed

TI

Rick..

<snip>> On your point a), my uncertainty was based on the mechanics of duplicatin
the data set. Was a duplicate table being created and then deleted, makin
it temporary or was the duplicate made 'virtually' where it would never sho
up in the db windownew object named rs so I guess that rs is a virtual object occupying memor
space. I had assumed that the object would delete itself when the for
closed, but your comment that it needs to be closed and set to nothing make
me wonder about my assumption. Does the rs object stay in memory until th
program closes? Or is it only until the form closessetting it to nothing? Would I add code to the form's Close event? Or ad
another line at the end of the control's afterupdate event code? How woul
that line read? Possibly ' Set rs = Null ' or ' Delete rs '


Mar

 

[Marc]

Hi Marc,

Thanks again for all of your responses.

To the experts,with reference to what Marc wrote....

----- Marc wrote: -----
It is only created in memory, and you need first the close statement and
then the null statement. I'm not sure of the exact technical description,
but the rs is an object, not a variable. In Access and other products,
objects can have a different scope to local variables. Therefore if you are
finished with an object you need to explicitly say so.

Question 1) How/where exactly should I close then null the rs object (in

this discussion) so that it is not taking up memory?

Question 2) Is it really needed? I haven't notice here or in my books

that it is needed, but if the record source is large enough the memory usage
could slow down processing speed?1) Here is an example. Although the example is in Vb as opposed to VBA, the
same syntax and reasons apply.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthopenx.asp
2) Here is the exact reasons why you should do it and when
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthclose.asp

Marc

 

[Rick]

Hi Marc,

Thank you for locating this info, it's really helpful.

Rick...

----- Marc wrote: -----

Hi Marc,
It is only created in memory, and you need first the close statement and
then the null statement. I'm not sure of the exact technical description,
but the rs is an object, not a variable. In Access and other products,
objects can have a different scope to local variables. Therefore if you are
finished with an object you need to explicitly say so.

this discussion) so that it is not taking up memory?

Question 2) Is it really needed? I haven't notice here or in my books

that it is needed, but if the record source is large enough the memory usage
could slow down processing speed?1) Here is an example. Although the example is in Vb as opposed to VBA, the
same syntax and reasons apply.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthopenx.asp
2) Here is the exact reasons why you should do it and when
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthclose.asp

Marc

 

[John Vinson]

Question 1) How/where exactly should I close then null the rs object (in this discussion) so that it is not taking up memory?

Just close it when you're done with it.

Question 2) Is it really needed? I haven't notice here or in my books that it is needed, but if the record source is large enough the memory usage could slow down processing speed?

Objects should - and *usually* do - cease to exist when they "go out
of scope", i.e. when you exit the sub or function where they are set.

Some versions of Access have been plagued with bugs whereby objects
declared in a sub persist even when the sub exits. Many experienced
Access developers (me amongst them) have been burned so often by this
bug (which can cause Access to stay open in the command bar even when
you explicitly close the database) that we habitually use the sequence

Dim rs As DAO.Recordset
....
Set rs = <some recordset open operation>
<use rs>
rs.Close <if you opened it, close it>
Set rs = Nothing <and put it away>

 

[Rick]

Hi John

Thank you for this.

Before Marc brought it up, I hadn't really thought about it. I guess that even though the rs object *should* cease to exist when the sub ends or the form closes, it is technically correct and safer to control the action explicitly

I don't recall where I originally learned of this code, but I've done some more searching and found a "NoMatch" that you had suggested to another poster here. Between that and the information Marc pointed me to, the code has changed from

Private Sub cboSelect_AfterUpdate(
' Find the record that matches the control
Dim rs As Objec
Set rs = Me.Recordset.Clon
rs.FindFirst "[InstallerID] = " & Str(Nz(Me![cboSelect], 0)
If Not rs.EOF Then Me.Bookmark = rs.Bookmar
End Su

to

Private Sub cboSelect_AfterUpdate(
' Find the record that matches the control
On Error GoTo ErrorHandler_cboSelec

Dim rs As DAO.Recordse

Set rs = Me.Recordset.Clon

rs.FindFirst "[InstallerID] = " & Str(Nz(Me![cboSelect], 0)
If rs.NoMatch The
MsgBox "Employee number " & Str(Nz(Me![cboSelect], 0)) & " does not exist!"
& Chr(13) & Chr(10) & "If you would like to create a new Installer record, click the 'New' button below."
, vbOKOnl
End I
If Not rs.EOF Then Me.Bookmark = rs.Bookmar
rs.Clos
Set rs = Nothin
Exit Su

ErrorHandler_cboSelect
If Not rs Is Nothing The
rs.Clos
Set rs = Nothin
End I
End Su

The message box appears everytime there is not a match (of course). I would like to not have it appear if the combo box is null or 0. I have tried placing the following immediately after the "findfirst" line
If Me.cboSelect = Null The
Exit Su
End I
If Me.cboSelect = 0 The
Exit Su
End I
and do not get the results I want. Could you take the time to explain the correct method and/or why this doesn't work

Thank you

Rick..

----- John Vinson wrote: ----

On Thu, 15 Apr 2004 17:56:06 -0700, "Rick

Question 1) How/where exactly should I close then null the rs object (in this discussion) so that it is not taking up memory

Just close it when you're done with it

Question 2) Is it really needed? I haven't notice here or in my books that it is needed, but if the record source is large enough the memory usage could slow down processing speed

Objects should - and *usually* do - cease to exist when they "go ou
of scope", i.e. when you exit the sub or function where they are set

Some versions of Access have been plagued with bugs whereby object
declared in a sub persist even when the sub exits. Many experience
Access developers (me amongst them) have been burned so often by thi
bug (which can cause Access to stay open in the command bar even whe
you explicitly close the database) that we habitually use the sequenc

Dim rs As DAO.Recordse
...
Set rs = <some recordset open operation><use rs
rs.Close <if you opened it, close it
Set rs = Nothing <and put it away

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=publi

 

[SteveS]

Hi John,

Thank you for this.

Before Marc brought it up, I hadn't really thought about it. I guess that even though the rs object *should* cease to exist when the sub ends or the form closes, it is technically correct and safer to control the action explicitly.

I don't recall where I originally learned of this code, but I've done some more searching and found a "NoMatch" that you had suggested to another poster here. Between that and the information Marc pointed me to, the code has changed from:

Private Sub cboSelect_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[InstallerID] = " & Str(Nz(Me![cboSelect], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

to:

Private Sub cboSelect_AfterUpdate()
' Find the record that matches the control.
On Error GoTo ErrorHandler_cboSelect

Dim rs As DAO.Recordset

Set rs = Me.Recordset.Clone

rs.FindFirst "[InstallerID] = " & Str(Nz(Me![cboSelect], 0))
If rs.NoMatch Then
MsgBox "Employee number " & Str(Nz(Me![cboSelect], 0)) & " does not exist!" _
& Chr(13) & Chr(10) & "If you would like to create a new Installer record, click the 'New' button below." _
, vbOKOnly
End If
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
rs.Close
Set rs = Nothing
Exit Sub

ErrorHandler_cboSelect:
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
End Sub

The message box appears everytime there is not a match (of course). I would like to not have it appear if the combo box is null or 0. I have tried placing the following immediately after the "findfirst" line:
If Me.cboSelect = Null Then
Exit Sub
End If
If Me.cboSelect = 0 Then
Exit Sub
End If
and do not get the results I want. Could you take the time to explain the correct method and/or why this doesn't work?

Thank you,

Rick...

Rick,

To test for a null, you need to use IsNull(Me.cboSelect).


<snip>
Dim rs As DAO.Recordset

'do the test before setting the recordset object
' so you don't have to set it to nothing

If IsNull(Me.cboSelect) OR Me.cboSelect = 0 Then
Exit Sub
End If

Set rs = Me.Recordset.Clone
<snip>


BTW, looking at the line:

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

if you get to the above line, it means there was a match found by
rs.FindFirst().

rs.EOF will always be FALSE (because a match was found)
and Not rs.EOF will always be TRUE,
so the line *could* just be written as:

Me.Bookmark = rs.Bookmark



HTH

 

[Rick]

Hi SteveS

Thank you for this. I tried to post a 'thank you' back around the 20th but nothing I was posting was going up on the site

With the bookmark EOF stuff you suggested, I tried that and started getting error message. It could be that when I did certain user actions (delete what's in the combo box) the code located the end of file and then... I don't know. Anyway, I put it back to the original and now it's fine. (If it ain't broke...

----- SteveS wrote: ----

Rick

To test for a null, you need to use IsNull(Me.cboSelect)


<snip
Dim rs As DAO.Recordse

'do the test before setting the recordset objec
' so you don't have to set it to nothin

If IsNull(Me.cboSelect) OR Me.cboSelect = 0 The
Exit Su
End I

Set rs = Me.Recordset.Clon
<snip


BTW, looking at the line

If Not rs.EOF Then Me.Bookmark = rs.Bookmar

if you get to the above line, it means there was a match found by
rs.FindFirst()

rs.EOF will always be FALSE (because a match was found
and Not rs.EOF will always be TRUE
so the line *could* just be written as

Me.Bookmark = rs.Bookmar



HT

 

[SteveS]

Rick

With the bookmark EOF stuff you suggested, I tried that and started getting error message. It could be that when I did >certain user actions (delete what's in the combo box) the code located the end of file and then... I don't know. Anyway, I >put it back to the original and now it's fine. (If it ain't broke...

My fault... I forgot to paste in code I modified

BTW, looking at the line
and Not rs.EOF will always be TRUE
so the line *could* just be written as

This is the code I *was* going to paste in

'----- snip ------------
Set rs = Me.RecordsetClone ' only one dot her

rs.FindFirst "[InstallerID] = " & Str(Nz(Me![cboSelect], 0)
If rs.NoMatch The
MsgBox "Employee number " & Str(Nz(Me![cboSelect], 0)) & " does not exist!"
& Chr(13) & Chr(10) & "If you would like to create a new Installer record, click the 'New' button below."
, vbOKOnl
Else '<<===adde
' Employee number foun
Me.Bookmark = rs.Bookmark '<<===change
End I

rs.Clos
Set rs = Nothing
'----- snip ------------

Like the saying goes: If it ain't broke, break it, then fix it..........

Stev

 

[Rick]

Hi SteveS

Thanks for this. I'll try it out later

Rick..

----- SteveS wrote: ----

Rick

With the bookmark EOF stuff you suggested, I tried that and started getting error message. It could be that when I did >certain user actions (delete what's in the combo box) the code located the end of file and then... I don't know. Anyway, I >put it back to the original and now it's fine. (If it ain't broke...

My fault... I forgot to paste in code I modified

BTW, looking at the line
and Not rs.EOF will always be TRUE
so the line *could* just be written as

This is the code I *was* going to paste in

'----- snip ------------
Set rs = Me.RecordsetClone ' only one dot her

rs.FindFirst "[InstallerID] = " & Str(Nz(Me![cboSelect], 0)
If rs.NoMatch The
MsgBox "Employee number " & Str(Nz(Me![cboSelect], 0)) & " does not exist!"
& Chr(13) & Chr(10) & "If you would like to create a new Installer record, click the 'New' button below."
, vbOKOnl
Else '<<===adde
' Employee number foun
Me.Bookmark = rs.Bookmark '<<===change
End I

rs.Clos
Set rs = Nothing
'----- snip ------------

Like the saying goes: If it ain't broke, break it, then fix it..........

Stev

 

[dandgard]

just another note here. The return from the Nz function need not be
numeric value, it can also be a string - specifically sometimes yo
don't want a value but a "" (empty string) instead of some value

 

[dandgard]

just another note here. The return from the Nz function need not be
numeric value, it can also be a string - specifically sometimes yo
don't want a value but a "" (empty string) instead of some value