By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,132 Members | 1,425 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,132 IT Pros & Developers. It's quick & easy.

Help for write VBA code using "Command" function and "GoToRecord" action

P: n/a
I working on a GIS project, with Access link which just need a little
routine in VBA, but I haven't knowledges in VBA language.

It's very simple, and it looks like that in a TPascal way :

....
Var
RecordNb : integer ;
....
{Command : function of Access}
....
{GoToRecord : method of Acces}

Begin
If Command=Null then
RecordNb:=1
Else
RecordNb:=Command;
GoToRecord(RecordNb);
End.

Could someone write me how it looks like in VBA ?
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
I don't understand what it is you are trying to do. Move to a record on a
form? In a Recordset? Automate Access from another application? What version
of Access? ADO or DAO?

Mike Storr
www.veraccess.com
"Clément Collin" <cl************@club-internet.fr> wrote in message
news:40**********************@news.club-internet.fr...
I working on a GIS project, with Access link which just need a little
routine in VBA, but I haven't knowledges in VBA language.

It's very simple, and it looks like that in a TPascal way :

...
Var
RecordNb : integer ;
...
{Command : function of Access}
...
{GoToRecord : method of Acces}

Begin
If Command=Null then
RecordNb:=1
Else
RecordNb:=Command;
GoToRecord(RecordNb);
End.

Could someone write me how it looks like in VBA ?

Nov 12 '05 #2

P: n/a
Thanks for your interest.

First, I am not a a professionnal computerist, furthermore I am french, what
can explain the unprecise way the issue is explained.

I try to clarify.

I work on the construction of an archaeological GIS. The softwares used are
ESRI Arcview 3.2 and MS Access 2003, and I don't know if it is ADO or DAO.
Arcview use a language called "Avenue" derived from "SmallTalk".

I want like you said to "Automate Access from other application" (from
Arcview). Precisely, I want to go to a specific record in a form when I
click on an object displaied in Arcview. For example : if I click on the
object which ID attribute is 10, it opens Access, and directly display the
form of the record which ID is 10.

To do that, I can make Arcview execute a shell command and send a parameter
: ID of the object (here C:\...\MSAccess.exe <DatabaseName> /cmd <ID of the
object>).

I've configure my database to open the wanted Form automatically. And I just
want to program a little Visual Basic macro to receive the parameter, and
use it to go automatically to the specified record, corresponding with the
ID of the object.

That's it. And I think if I could write in VB the program I've written in
Tpascal, I could work.

What do you think ? Have I to contract computerists ?

"Mike Storr" <no****@somewhere.con> escribió en el mensaje
news:mZ********************@news20.bellglobal.com. ..
I don't understand what it is you are trying to do. Move to a record on a
form? In a Recordset? Automate Access from another application? What version of Access? ADO or DAO?

Mike Storr
www.veraccess.com
"Clément Collin" <cl************@club-internet.fr> wrote in message
news:40**********************@news.club-internet.fr...
I working on a GIS project, with Access link which just need a little
routine in VBA, but I haven't knowledges in VBA language.

It's very simple, and it looks like that in a TPascal way :

...
Var
RecordNb : integer ;
...
{Command : function of Access}
...
{GoToRecord : method of Acces}

Begin
If Command=Null then
RecordNb:=1
Else
RecordNb:=Command;
GoToRecord(RecordNb);
End.

Could someone write me how it looks like in VBA ?


Nov 12 '05 #3

P: n/a
I'm not really familiar with the languages you are using, but perhaps using
the /cmd switch for the database you are working with would work. You could
pass a command line setting, ie. the record number as a string. Then when
the form opens, use the Command function to get that record number. Convert
the result to a number, then move to the desired record. Try placing this in
the OnOpen event of the form...

Dim recNum As Long
recNum = CLng(Command())
DoCmd.GoToRecord acActiveDataObject, "formName", acGoTo, recnum
"Clément Collin" <cl************@club-internet.fr> wrote in message
news:40**********************@news.club-internet.fr...
Thanks for your interest.


Nov 12 '05 #4

P: n/a
"Clément Collin" <cl************@club-internet.fr> wrote in message
news:40**********************@news.club-internet.fr...
Thanks for your interest.

First, I am not a a professionnal computerist, furthermore I am french, what can explain the unprecise way the issue is explained.

I try to clarify.

I work on the construction of an archaeological GIS. The softwares used are ESRI Arcview 3.2 and MS Access 2003, and I don't know if it is ADO or DAO.
Arcview use a language called "Avenue" derived from "SmallTalk".

I want like you said to "Automate Access from other application" (from
Arcview). Precisely, I want to go to a specific record in a form when I
click on an object displaied in Arcview. For example : if I click on the
object which ID attribute is 10, it opens Access, and directly display the
form of the record which ID is 10.

To do that, I can make Arcview execute a shell command and send a parameter : ID of the object (here C:\...\MSAccess.exe <DatabaseName> /cmd <ID of the object>).

I've configure my database to open the wanted Form automatically. And I just want to program a little Visual Basic macro to receive the parameter, and
use it to go automatically to the specified record, corresponding with the
ID of the object.

That's it. And I think if I could write in VB the program I've written in
Tpascal, I could work.

What do you think ? Have I to contract computerists ?



You can extract information from the database without opening it using
Access, but if you really do need Access to open and show a form, then you
could do this:

Assuming the name of the form is "frmMyForm" and the primary key is called
"ID" and is a long integer.

You need 3 steps:

1. Create and name it "AutoExec" In the design of the macro give it one
step which is to RunCode. In the function box at the bottom, write
=Startup()

2. Create a new module and name it "modStartup".
' ************************************************
Option Compare Database
Option Explicit

Public Function Startup()

On Error GoTo Err_Handler

Dim strCommand As String
Dim lngID As Long

strCommand = Nz(Command(), "")

If Len(strCommand) = 0 Then Exit Function

lngID = CLng(strCommand)

DoCmd.RunCommand acCmdAppMaximize

DoCmd.OpenForm "frmMyForm", , , , , , lngID

Exit_Handler:
Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

' ************************************************

3. In the form's code module, paste the following
' ************************************************
Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)

On Error GoTo Err_Handler

If IsNull(Me.OpenArgs) Then Exit Sub

With Me.RecordsetClone
.FindFirst "[ID]=" & Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
MsgBox "Record Not Found", vbCritical
Cancel = True
End If
End With

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

' ************************************************
HTH

Fletcher
Nov 12 '05 #5

P: n/a
Works perfectly. Very thanks to you. I'm saved.
(the only matter is that each time the shell executes, if Access is opened,
it opens a new Access Windows with a new copy of the same database)
Nov 12 '05 #6

P: n/a
Works perfectly. Very thanks to you. I'm saved.
(the only matter is that each time the shell executes, if Access is opened,
it opens a new Access Windows with a new copy of the same database)

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.