Access Syntax Question

[JHB]

Somehow the contents of my original post vanished, so I am duplicating
the post in the hope that this time it will get through.

Hi:

In an Access Macro, I am attempting to set a conditional situation to
detect the end of a table. In the conditional column, i have created
the following statement:


IsError(Command[Records GoToNext])

The intent is that the action which follows will be executed IF an
error would be detected by attempting to move to the next record,
since you are at the end of the table. Sadly, there is an error in the
syntax (and possibly the logic) which makes it unaccptable to Access.

I am hoping that some kind soul will point out the error, and give me
the correct statement in a form that will work.

Thanks in advance

John Baker

 

[Guest]

Hi

I have not used error trapping in a macro – it “may†be possible though, not
sure.

For something like you desicbe you could use some simply code like this

Create a control – button or lable, call it CommandForward and put this
OnClick.

Private Sub CommandForward_Click()
On Error GoTo Err_CommandForward_Click
DoCmd.GoToRecord , , acNext
Exit_CommandForward_Click:
Exit Sub
Err_CommandForward_Click:
MsgBox "This is the last record", vbOKOnly, "message from JBH"
Resume Exit_CommandForward_Click
End Sub

This will move the record to the next record until it gets to the end and
then trap that error with the message “This is the last recordâ€

You can do the same the other way (CommandBack) by moving the record the
previous until it gets to the start and then give a message (trap the error),
like this

Private Sub CommandBack_Click()
On Error GoTo Err_CommandBack_Click
DoCmd.GoToRecord , , acPrevious
Exit_CommandBack_Click:
Exit Sub
Err_CommandBack_Click:
MsgBox "This is the first record", vbOKOnly, "message from JBH"
Resume Exit_CommandBack_Click
End Sub

Hope this helps

 

[JHB]

Hi

I have not used error trapping in a macro - it "may" be possible though, not
sure.

For something like you desicbe you could use some simply code like this

Create a control - button or lable, call it CommandForward and put this
OnClick.

Private Sub CommandForward_Click()
On Error GoTo Err_CommandForward_Click
DoCmd.GoToRecord , , acNext
Exit_CommandForward_Click:
Exit Sub
Err_CommandForward_Click:
MsgBox "This is the last record", vbOKOnly, "message from JBH"
Resume Exit_CommandForward_Click
End Sub

This will move the record to the next record until it gets to the end and
then trap that error with the message "This is the last record"

You can do the same the other way (CommandBack) by moving the record the
previous until it gets to the start and then give a message (trap the error),
like this

Private Sub CommandBack_Click()
On Error GoTo Err_CommandBack_Click
DoCmd.GoToRecord , , acPrevious
Exit_CommandBack_Click:
Exit Sub
Err_CommandBack_Click:
MsgBox "This is the first record", vbOKOnly, "message from JBH"
Resume Exit_CommandBack_Click
End Sub

Hope this helps

--
Wayne
Manchester, England.

Somehow the contents of my original post vanished, so I am duplicating
the post in the hope that this time it will get through.

In an Access Macro, I am attempting to set a conditional situation to
detect the end of a table. In the conditional column, i have created
the following statement:

IsError(Command[Records GoToNext])

The intent is that the action which follows will be executed IF an
error would be detected by attempting to move to the next record,
since you are at the end of the table. Sadly, there is an error in the
syntax (and possibly the logic) which makes it unaccptable to Access.

I am hoping that some kind soul will point out the error, and give me
the correct statement in a form that will work.

Thanks in advance

John Baker

Thank you..that is exactly what I was looking for.

Much Appreciated..

Good Luck to Manchester United!!

John Baker

 

[Steve Schapel]

JHB,

I do not agree with the idea of deliberately creating an error, and then
trying to base your functionality on the error. I liked your original
idea of detecting the last record. To do this in a macro, you would use
this Condition:
[RecordsetClone].[RecordCount]=[CurrentRecord]
There would be an equivalent way to do the same thing in VBA.