recognize EOF condition when using GoToRecord Action

[MLH]

If I'm using the following in a procedure...

DoCmd.GoToRecor d acDataForm, "FormName", acNext, 4

.... how can I recognize the EOF condition? Using GoToRecord,
I find myself lost when trying to determine when EOF is reached.

I suppose it's too much to wish for something like...
On EOF GoTo Exit_Label

[lyle fairfield]

Re: recognize EOF condition when using GoToRecord Action

For many years, VB experts have specifically recommended that GoTo
never be used. Like Kill and Open, it is as carry-over from a by-gone
age, and inherently dangerous.

In Northwinds MS writes it own Function to handle GoToRecord errors,
such as trying to pass beyond EOF:

Function TryToGoToRecord (Record As AcRecord) As Boolean
On Error GoTo ErrorHandler

DoCmd.GoToRecor d , , Record

Done:
Exit Function
ErrorHandler:
MsgBox Err.Description
Resume Done
End Function

But I think this is insufficient to handle the situation you are
describing. Regardless, I would recommend putting your code lines in
an error handling snippet, learning what error number arises on an EOF
error and doing some action when that error (number) occurs in your
error-handling block.

For even more advice than you want I may say that I discourage working
with recordsets except in very unusual conditions. We have beautiful
Access forms that allows us interaction with data. We have efficient
SQL that allows us to modify data painlessly and relatively safely.
And we have recordsets when we need to be slow, clumsy and vulnerable.

No, I don't hope to convert anyone. Obesity will disappear in North
America before the use of recordsets in inappropriate situations
disappears. But maybe there will be some newbie reading this who might
be influenced. I can hope.




On May 28, 10:08 am, MLH <C...@NorthStat e.netwrote:

If I'm using the following in a procedure...
>
DoCmd.GoToRecor d acDataForm, "FormName", acNext, 4
>
... how can I recognize the EOF condition? Using GoToRecord,
I find myself lost when trying to determine when EOF is reached.
>
I suppose it's too much to wish for something like...
On EOF GoTo Exit_Label

[Salad]

Re: recognize EOF condition when using GoToRecord Action

MLH wrote:

If I'm using the following in a procedure...
>
DoCmd.GoToRecor d acDataForm, "FormName", acNext, 4
>
... how can I recognize the EOF condition? Using GoToRecord,
I find myself lost when trying to determine when EOF is reached.
>
I suppose it's too much to wish for something like...
On EOF GoTo Exit_Label
>

I guess the error message is fine. If you need to, something like the
following will work.

Private Sub Command11_Click ()
On Error GoTo Err_Command11_C lick
Dim lngRec As Long
Dim rst As Recordset
Set rst = Me.RecordsetClo ne
rst.Bookmark = Me.Bookmark
lngRec = rst.AbsolutePos ition
rst.Close

If (Me.RecordsetCl one.RecordCount - lngRec) 4 Then
DoCmd.GoToRecor d , , acNext, 4
End If
Exit_Command11_ Click:
Exit Sub

Err_Command11_C lick:
MsgBox Err.Description
Resume Exit_Command11_ Click

End Sub

Hei No Te tiare

http://www.youtube.com/watch?v=rpmyn2uR6O8

[Salad]

Re: recognize EOF condition when using GoToRecord Action

lyle fairfield wrote:

For many years, VB experts have specifically recommended that GoTo
never be used. Like Kill and Open, it is as carry-over from a by-gone
age, and inherently dangerous.

Could your expand on your opinion of not using Kill? How else can one
delete a file? And with Open, if one wanted to write to a text file or
read a text file that isn't a typical CSV then what does one use?

I suppose someone could write malicious code with them but what if you
simply need to do a task that is non-malicious?

If it's recommended one not use Docmd.GoToRecor d, what options are there
(you aren't keen on Recordsets) to use?

>
In Northwinds MS writes it own Function to handle GoToRecord errors,
such as trying to pass beyond EOF:
>
Function TryToGoToRecord (Record As AcRecord) As Boolean
On Error GoTo ErrorHandler
>
DoCmd.GoToRecor d , , Record
>
Done:
Exit Function
ErrorHandler:
MsgBox Err.Description
Resume Done
End Function
>
But I think this is insufficient to handle the situation you are
describing. Regardless, I would recommend putting your code lines in
an error handling snippet, learning what error number arises on an EOF
error and doing some action when that error (number) occurs in your
error-handling block.
>
For even more advice than you want I may say that I discourage working
with recordsets except in very unusual conditions. We have beautiful
Access forms that allows us interaction with data. We have efficient
SQL that allows us to modify data painlessly and relatively safely.
And we have recordsets when we need to be slow, clumsy and vulnerable.
>
No, I don't hope to convert anyone. Obesity will disappear in North
America before the use of recordsets in inappropriate situations
disappears. But maybe there will be some newbie reading this who might
be influenced. I can hope.
>
>
>
>
On May 28, 10:08 am, MLH <C...@NorthStat e.netwrote:
>

>>If I'm using the following in a procedure...
>>
>>DoCmd.GoToRec ord acDataForm, "FormName", acNext, 4
>>
>>... how can I recognize the EOF condition? Using GoToRecord,
>>I find myself lost when trying to determine when EOF is reached.
>>
>>I suppose it's too much to wish for something like...
>>On EOF GoTo Exit_Label

>
>

[lyle fairfield]

Re: recognize EOF condition when using GoToRecord Action

I am sorry if it sounded as if I were suggesting the use of some
alternative to Kill and Open. I was trying to explain that VBA evolved
from ancient technologies, Quick Basic and Ruby, TTBOMK, and that it
contains archaic tools that use dos-like commands that are immediate
and can be catastrophic. Kill is one of these.
If I were using Kill for the deletion of anything but a temporary file
I would certainly write my own function which checked "Do you really
wanna", offered the option of backup, and check for file existence
before killing anything.

MS would serve us well by introducing a more modern alternative
scripting language for use with its Office programs. Such a language
might do what I describe above internally.

I did not recommemd that DoCmd.GoToRecor d not be used, (although I
believe I have never used it.) I recommended that explicitly declared
Recordsets not be used except in unusual circumstances such as
massive, recursive manipulation of data.
If we must use a recordset, Seek, FindFirst, Find (ADO), MoveNext,
setting the Filter (ADO) etc are all excellent alternatives to
DoCmd.GoToRecor d. After using them we could just check NoMatch or EOF
without risking attempt to move past end of file error.

Fifteen years ago I used Recordsets extensively with Access/Jet.
Before thart I used them even more extensivley in X-Base programs. And
I still use them with classic ASP. But not unless I have to.


On May 28, 11:32 am, Salad <o...@vinegar.c omwrote:

lyle fairfield wrote:

For many years, VB experts have specifically recommended that GoTo
never be used. Like Kill and Open, it is as carry-over from a by-gone
age, and inherently dangerous.

>
Could your expand on your opinion of not using Kill?  How else can one
delete a file?  And with Open, if one wanted to write to a text file or
read a text file that isn't a typical CSV then what does one use?
>
I suppose someone could write malicious code with them but what if you
simply need to do a task that is non-malicious?
>
If it's recommended one not use Docmd.GoToRecor d, what options are there
(you aren't keen on Recordsets) to use?
>
>
>
>
>

In Northwinds MS writes it own Function to handle GoToRecord errors,
such as trying to pass beyond EOF:

>

Function TryToGoToRecord (Record As AcRecord) As Boolean
    On Error GoTo ErrorHandler

>

    DoCmd.GoToRecor d , , Record

>

Done:
    Exit Function
ErrorHandler:
    MsgBox Err.Description
    Resume Done
End Function

>

But I think this is insufficient to handle the situation you are
describing. Regardless, I would recommend putting your code lines in
an error handling snippet, learning what error number arises on an EOF
error and doing some action when that error (number) occurs in your
error-handling block.

>

For even more advice than you want I may say that I discourage working
with recordsets except in very unusual conditions. We have beautiful
Access forms that allows us interaction with data. We have efficient
SQL that allows us to modify data painlessly and relatively safely.
And we have recordsets when we need to be slow, clumsy and vulnerable.

>

No, I don't hope to convert anyone. Obesity will disappear in North
America before the use of recordsets in inappropriate situations
disappears. But maybe there will be some newbie reading this who might
be influenced. I can hope.

>

On May 28, 10:08 am, MLH <C...@NorthStat e.netwrote:

>

>If I'm using the following in a procedure...

>

>DoCmd.GoToReco rd acDataForm, "FormName", acNext, 4

>

>... how can I recognize the EOF condition? Using GoToRecord,
>I find myself lost when trying to determine when EOF is reached.

>

>I suppose it's too much to wish for something like...
>On EOF GoTo Exit_Label

[Salad]

Re: recognize EOF condition when using GoToRecord Action

lyle fairfield wrote:

I am sorry if it sounded as if I were suggesting the use of some
alternative to Kill and Open. I was trying to explain that VBA evolved
from ancient technologies, Quick Basic and Ruby, TTBOMK, and that it
contains archaic tools that use dos-like commands that are immediate
and can be catastrophic. Kill is one of these.
If I were using Kill for the deletion of anything but a temporary file
I would certainly write my own function which checked "Do you really
wanna", offered the option of backup, and check for file existence
before killing anything.

Where I use kill is when I filecopy a file to a new folder for permanent
residence and then kill the original. By and large, it's a temp file.
But I would always ask for a confirmation otherwise.

MS would serve us well by introducing a more modern alternative
scripting language for use with its Office programs. Such a language
might do what I describe above internally.

Or perhaps move it to the Trashcan instead of wiping out the file.

I did not recommemd that DoCmd.GoToRecor d not be used, (although I
believe I have never used it.) I recommended that explicitly declared
Recordsets not be used except in unusual circumstances such as
massive, recursive manipulation of data.
If we must use a recordset, Seek, FindFirst, Find (ADO), MoveNext,
setting the Filter (ADO) etc are all excellent alternatives to
DoCmd.GoToRecor d. After using them we could just check NoMatch or EOF
without risking attempt to move past end of file error.

It's not a command in my repertoire either.

Fifteen years ago I used Recordsets extensively with Access/Jet.
Before thart I used them even more extensivley in X-Base programs. And
I still use them with classic ASP. But not unless I have to.
>
>
On May 28, 11:32 am, Salad <o...@vinegar.c omwrote:
>

>>lyle fairfield wrote:
>>

>>>For many years, VB experts have specifically recommended that GoTo
>>>never be used. Like Kill and Open, it is as carry-over from a by-gone
>>>age, and inherently dangerous.

>>
>>Could your expand on your opinion of not using Kill? How else can one
>>delete a file? And with Open, if one wanted to write to a text file or
>>read a text file that isn't a typical CSV then what does one use?
>>
>>I suppose someone could write malicious code with them but what if you
>>simply need to do a task that is non-malicious?
>>
>>If it's recommended one not use Docmd.GoToRecor d, what options are there
>>(you aren't keen on Recordsets) to use?
>>
>>
>>
>>
>>
>>

>>>In Northwinds MS writes it own Function to handle GoToRecord errors,
>>>such as trying to pass beyond EOF:

>>

>>>Function TryToGoToRecord (Record As AcRecord) As Boolean
>> On Error GoTo ErrorHandler

>>

>> DoCmd.GoToRecor d , , Record

>>

>>>Done:
>> Exit Function
>>>ErrorHandler :
>> MsgBox Err.Description
>> Resume Done
>>>End Function

>>

>>>But I think this is insufficient to handle the situation you are
>>>describing . Regardless, I would recommend putting your code lines in
>>>an error handling snippet, learning what error number arises on an EOF
>>>error and doing some action when that error (number) occurs in your
>>>error-handling block.

>>

>>>For even more advice than you want I may say that I discourage working
>>>with recordsets except in very unusual conditions. We have beautiful
>>>Access forms that allows us interaction with data. We have efficient
>>>SQL that allows us to modify data painlessly and relatively safely.
>>>And we have recordsets when we need to be slow, clumsy and vulnerable.

>>

>>>No, I don't hope to convert anyone. Obesity will disappear in North
>>>America before the use of recordsets in inappropriate situations
>>>disappears . But maybe there will be some newbie reading this who might
>>>be influenced. I can hope.

>>

>>>On May 28, 10:08 am, MLH <C...@NorthStat e.netwrote:

>>

>>>>If I'm using the following in a procedure...

>>

>>>>DoCmd.GoToR ecord acDataForm, "FormName", acNext, 4

>>

>>>>... how can I recognize the EOF condition? Using GoToRecord,
>>>>I find myself lost when trying to determine when EOF is reached.

>>

>>>>I suppose it's too much to wish for something like...
>>>>On EOF GoTo Exit_Label

>
>

[Rick Brandt]

Re: recognize EOF condition when using GoToRecord Action

Salad wrote:

lyle fairfield wrote:
>

>I am sorry if it sounded as if I were suggesting the use of some
>alternative to Kill and Open. I was trying to explain that VBA
>evolved from ancient technologies, Quick Basic and Ruby, TTBOMK, and
>that it contains archaic tools that use dos-like commands that are
>immediate and can be catastrophic. Kill is one of these.
>If I were using Kill for the deletion of anything but a temporary
>file I would certainly write my own function which checked "Do you
>really wanna", offered the option of backup, and check for file
>existence before killing anything.

>
Where I use kill is when I filecopy a file to a new folder for
permanent residence and then kill the original. By and large, it's a
temp file. But I would always ask for a confirmation otherwise.
>

>MS would serve us well by introducing a more modern alternative
>scripting language for use with its Office programs. Such a language
>might do what I describe above internally.

>
Or perhaps move it to the Trashcan instead of wiping out the file.

One of the regulars gave me a link to this code a few years ago (Douglas
perhaps?). It deletes files in a manner that puts them into the recycle
bin.

Option Compare Database
Option Explicit

Private Type SHFILEOPSTRUCT
hwnd As Long
wFunc As Long
pFrom As String
pTo As String
fFlags As Integer
fAnyOperationsA borted As Boolean
hNameMappings As Long
lpszProgressTit le As String
End Type

Private Declare Function SHFileOperation Lib "shell32.dl l" Alias
"SHFileOperatio nA" (lpFileOp As SHFILEOPSTRUCT) As Long

Private Const FO_DELETE = &H3
Private Const FOF_ALLOWUNDO = &H40
Private Const FOF_NOCONFIRMAT ION = &H10
Private Const FOF_SILENT = &H4

Public Sub Recycle(File As String, Optional Confirm As Boolean = False,
Optional SilentOnError As Boolean = True)

Dim FileOperation As SHFILEOPSTRUCT
Dim lReturn As Long
Dim sFileToDelete As String
Dim lFlags As Integer

' Set File to delete.
sFileToDelete = Trim(File) & vbNullChar

' Set flags.
lFlags = FOF_ALLOWUNDO
If Not Confirm Then
lFlags = lFlags Or FOF_NOCONFIRMAT ION
End If
If SilentOnError Then
lFlags = lFlags Or FOF_SILENT
End If

With FileOperation
.wFunc = FO_DELETE
.pFrom = sFileToDelete
.fFlags = lFlags
End With

' Send to recycle bin.
lReturn = SHFileOperation (FileOperation)

End Sub

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[lyle fairfield]

Re: recognize EOF condition when using GoToRecord Action

I expect there are many such procedures. Here's mine.

Public Sub MoveFileToRecyc leBin(ByVal FullPath$)
Dim Shell As Shell32.Shell
Dim RecycleBin As Folder
Set Shell = New Shell32.Shell
Set RecycleBin = Shell.NameSpace (ssfBITBUCKET)
RecycleBin.Move Here FullPath
End Sub

(requires a reference to Shell32.dll or late binding)

IMO, MS should modify Kill to incorporate the actions of these
procedures, or do something better.


On May 28, 4:38 pm, "Rick Brandt" <rickbran...@ho tmail.comwrote:

Salad wrote:

lyle fairfield wrote:

>

I am sorry if it sounded as if I were suggesting the use of some
alternative to Kill and Open. I was trying to explain that VBA
evolved from ancient technologies, Quick Basic and Ruby, TTBOMK, and
that it contains archaic tools that use dos-like commands that are
immediate and can be catastrophic. Kill is one of these.
If I were using Kill for the deletion of anything but a temporary
file I would certainly write my own function which checked "Do you
really wanna", offered the option of backup, and check for file
existence before killing anything.

>

Where I use kill is when I filecopy a file to a new folder for
permanent residence and then kill the original.  By and large, it's a
temp file. But I would always ask for a confirmation otherwise.

>

MS would serve us well by introducing a more modern alternative
scripting language for use with its Office programs. Such a language
might do what I describe above internally.

>

Or perhaps move it to the Trashcan instead of wiping out the file.

>
One of the regulars gave me a link to this code a few years ago (Douglas
perhaps?).  It deletes files in a manner that puts them into the recycle
bin.
>
Option Compare Database
Option Explicit
>
Private Type SHFILEOPSTRUCT
    hwnd As Long
    wFunc As Long
    pFrom As String
    pTo As String
    fFlags As Integer
    fAnyOperationsA borted As Boolean
    hNameMappings As Long
    lpszProgressTit le As String
End Type
>
Private Declare Function SHFileOperation Lib "shell32.dl l" Alias
"SHFileOperatio nA" (lpFileOp As SHFILEOPSTRUCT) As Long
>
Private Const FO_DELETE = &H3
Private Const FOF_ALLOWUNDO = &H40
Private Const FOF_NOCONFIRMAT ION = &H10
Private Const FOF_SILENT = &H4
>
Public Sub Recycle(File As String, Optional Confirm As Boolean = False,
Optional SilentOnError As Boolean = True)
>
Dim FileOperation As SHFILEOPSTRUCT
Dim lReturn As Long
Dim sFileToDelete As String
Dim lFlags As Integer
>
'  Set File to delete.
sFileToDelete = Trim(File) & vbNullChar
>
'  Set flags.
lFlags = FOF_ALLOWUNDO
If Not Confirm Then
    lFlags = lFlags Or FOF_NOCONFIRMAT ION
End If
If SilentOnError Then
    lFlags = lFlags Or FOF_SILENT
End If
>
With FileOperation
    .wFunc = FO_DELETE
    .pFrom = sFileToDelete
    .fFlags = lFlags
End With
>
'  Send to recycle bin.
lReturn = SHFileOperation (FileOperation)
>
End Sub
>
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com