Looking for a little education on VBA code

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.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
--
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

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


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

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

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
Else '===added
' Employee number found
Me.Bookmark = rs.Bookmark '===changed
End If

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

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

Steve
-----------------------------
Artificial Intelligence is no match
for My Natural Stupidity

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


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

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

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
Else '===added
' Employee number found
Me.Bookmark = rs.Bookmark '===changed
End If

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

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

Steve
-----------------------------
Artificial Intelligence is no match
for My Natural Stupidity

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

hi dandgard,

thanks for this info - I use the "" alot in Excel (where I do not use any VBA or macros, makes it easier to email files without code attached). I haven't come across a usage for this info yet in Access, but since I'm still 'new' I'm sure that some opportunity will arise in the future.

again, thanks for responding 'cause it all helps in the long run...

Rick...

----- dandgard wrote: -----


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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/