VBA Reference to Fields in Tables

[JHB]

Hi: I have a very simple question, and I should know the answer but
dont!

I have a Access 2003 VBA routine, (which someone else wrote) and
there are two arguments "address 1" and "Address 2". The routine
wainders off to Google Maps and comes back with the distance and time
between the two addresses. It is written in VBA. I need to be able to
set it up so that the two addresses are taken from two fields in a
table known as "addresses"

Addresses: From address.
Addresses: To address

The VBA present call is as follows:

Call GetDistanceAndTime("64 Hobbs Road, West Podunk, NY,", "299 South
Main Street, Pittsburgh, Pa")

Can someone tell me how to do that please?. I know its easy for
someone who knows, but I have had trouble finding the way to do it!

Thanks

John Baker

 

[Gloops]

Hello,

You did not say what you want to do of the results.
Also the name of the table, and of the two fields where you read the two
addresses.

 

[JHB]

Hello,

You did not say what you want to do of the results.
Also the name of the table, and of the two fields where you read the two
addresses.

The Table is "Addresses"
The fields are "from address" and "to address"

The results from Google are being parsed and time and distance
results (which are in the output) being teased out if it and shown as
two fields. That is being developed right now (by someone else).

Thanks


John Baker

 

[John W. Vinson]

Hi: I have a very simple question, and I should know the answer but
dont!

I have a Access 2003 VBA routine, (which someone else wrote) and
there are two arguments "address 1" and "Address 2". The routine
wainders off to Google Maps and comes back with the distance and time
between the two addresses. It is written in VBA. I need to be able to
set it up so that the two addresses are taken from two fields in a
table known as "addresses"

Addresses: From address.
Addresses: To address

The VBA present call is as follows:

Call GetDistanceAndTime("64 Hobbs Road, West Podunk, NY,", "299 South
Main Street, Pittsburgh, Pa")

Can someone tell me how to do that please?. I know its easy for
someone who knows, but I have had trouble finding the way to do it!

Thanks

John Baker

You'll have to change the Sub to a Function; a Function returns a value, a Sub
doesn't. The first line should be

Public Function GetDistanceAndTime(Address1 As String, _
Address2 As String) As Double

and at the end of the function somewhere you need a line

GetDistanceAndTime = <some expression returned by your calculation>

You can then call the function in several ways - one would be to type

Distance: GetDistanceAndTime([From Address], [To Address])

in a vacant Field cell; the square brackets are essential to tell Access that
these are fields in the query.

Or you could base a Form or Report on a query containing the address fields
and set the Control Source of a textbox to

= GetDistanceAndTime([From Address], [To Address])

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Gloops]

Hello,

I intended to propose a sub with a loop and a recordset, but ...
probably a query is more simple.

I wonder how you knew that GetDistanceAndTime was not already a
function. And how a same double can be both a distance and a time, so
what the unit is.

 

[John W. Vinson]

Hello,

I intended to propose a sub with a loop and a recordset, but ...
probably a query is more simple.

I wonder how you knew that GetDistanceAndTime was not already a
function. And how a same double can be both a distance and a time, so
what the unit is.

A Sub is called using the Call keyword; a Function is called just by
referencing it.

I have no idea whether Double is appropriate; I guessed, just because it seemd
a plausible choice for a distance.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Gloops]

John W. Vinson wrote, on 22nd Apr. 2012 23:58 UTC + 1 :

A Sub is called using the Call keyword; a Function is called just by
referencing it.

Oh, right. I have to admit I do not vary a lot on that, and ... a
certain time has gone since I learned that

I have no idea whether Double is appropriate; I guessed, just because it seemd
a plausible choice for a distance.

I guess a look at the Google reference can be useful there ...

 

[JHB]

John W. Vinson wrote, on 22nd Apr. 2012 23:58 UTC + 1 :


Oh, right. I have to admit I do not vary a lot on that, and ... a
certain time has gone since I learned that




I guess a look at the Google reference can be useful there ...

I thank you for all your help, but I have all the Public function
stuff.

What I need is simply to be able to refer to a table field in the
call :

Call GetDistanceAndTime("64 Hobbs Road, West Podunk, NY,", "299
South Main Street, Pittsburgh, Pa") ,

substituting the reference to the table and fields for the addresses.
As in [address]![Startingpoint] -- although I know that is not the way
to do it.

Your help is much appreciated.


Thanks

John Baker

 

[John W. Vinson]

John W. Vinson wrote, on 22nd Apr. 2012 23:58 UTC + 1 :


Oh, right. I have to admit I do not vary a lot on that, and ... a
certain time has gone since I learned that




I guess a look at the Google reference can be useful there ...

I thank you for all your help, but I have all the Public function
stuff.

What I need is simply to be able to refer to a table field in the
call :

Call GetDistanceAndTime("64 Hobbs Road, West Podunk, NY,", "299
South Main Street, Pittsburgh, Pa") ,

substituting the reference to the table and fields for the addresses.
As in [address]![Startingpoint] -- although I know that is not the way
to do it.

Your help is much appreciated.


Thanks

John Baker

Put a textbox on the Form named Startingpoint and another named Endingpoint,
bound to the appropriate table fields, and use

Call GetDistanceAndTime(Me![Startingpoint], Me![Endingpoint])

I presume that the VBA code will do something useful with the distance and
time, because I can't think of any way to have it display them on the form or
report without using a Function (or having the VBA code explicitly reference
the form or report).
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Gloops]

John W. Vinson wrote, on 24th Apr. 2012 22:41 UTC + 1 :

I presume that the VBA code will do something useful with the distance and
time

I think that is exactly the level of information that is provided in
this thread. It is not possible to give instructions for the integration
of a module without the documentation of it, especially in what form it
requires its input (from point, to point), and how to get the results
and what it represents.