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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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.) |
#2
|
|||
|
|||
Looking for a little education on VBA code
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 |
#3
|
|||
|
|||
Looking for a little education on VBA code
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 |
#4
|
|||
|
|||
Looking for a little education on VBA code
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/ |
#5
|
|||
|
|||
Looking for a little education on VBA code
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/ |
#6
|
|||
|
|||
Looking for a little education on VBA code
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/ |
Thread Tools | |
Display Modes | |
|
|