Selecting record in foreign table using VBA

[Jmeyering]

Is it possible to select a value from a foreign table using vba? The code is going to be executed automatically every month.



I can get the variable I want using a SQL statement, it is possible to pass a SQL value into a variable? For Instance the following SQL selects the value that I need, I know this doesn't work but hopefully you can understand what I mean.

    EmailAddress = "SELECT tblGuardians_RepPayee.Email FROM tblGuardians_RepPayee INNER JOIN tblClients ON tblGuardians_RepPayee.GuardianPayeeID = tblClients.RepPayee WHERE tblClients.ClientID=" & rst![ClientID] & ";"

I'm running reports based off tblPettyCashTransactions and need to the RepPayee Email address to email the report. My table struture is set up like this

tblGuardians_RepPayee
GuardianID
...
Email

tblClients:
GuardianId
...

tblPettyCashTransactions
ClientId
...

[pbaldy]

Sure; you'd have to open a recordset on the SQL or use DLookup(), which you may find simpler:

DLookup Usage Samples

Because of the join, you'd need to be looking up from a query rather than a table.

[Jmeyering]

Nailed, it Thanks So much!

[pbaldy]

No problemo!