Populating Hyperlink from vba

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dale - 19 Nov 2007 13:37 GMT

I am trying to run this code from a subroutine in vba... The filepath is a
constant string "strfpath". Currently I am getting a "missing operator"
error (where is it!!! the missing operator!!). Much appreciated if someone
could point me in the right direction. Thanks

strSql = "UPDATE tblpurchase_orders SET tblpurchase_orders.HLink =[Fname]#"
& strfpath & "[fname]#" & _
" WHERE (((tblpurchase_orders.HLink) Is Null) AND ((tblpurchase_orders.PO)
Is Null))"

Thanks again

Reply to this Message

Gary Walter - 19 Nov 2007 17:33 GMT

>I am trying to run this code from a subroutine in vba... The filepath is a
>constant string "strfpath". Currently I am getting a "missing operator"
[quoted text clipped - 5 lines]
> " WHERE (((tblpurchase_orders.HLink) Is Null) AND ((tblpurchase_orders.PO)
> Is Null))"

Hi Dale,

What is "[Fname]"?

Is this a user-supplied parameter?

In any case, if HLink needs a string,
you may be missing single quotes?

strSql = "UPDATE tblpurchase_orders SET " & _
"tblpurchase_orders.HLink = '[Fname]#" & strfpath & _
"[fname]#' " & _
" WHERE (((tblpurchase_orders.HLink) Is Null) " & _
"AND ((tblpurchase_orders.PO) Is Null))"

Copy above to clipboard.

Paste into Immediate Window.

Then ask for the string back....

?strSql

Is this what you wanted?

Test the result in Query Designer if not...

good luck,

gary

Reply to this Message

Gary Walter - 19 Nov 2007 18:45 GMT

Hi Dale,

On further thought...

If [Fname] is field in tblpurchase_orders,
then I think you want your SQL string to
end up as (if you did Debug.Print strSQL)...

if strfpath = "C:\" (does strfpath end with "\" ?)

UPDATE tblpurchase_orders
SET [HLink] = [Fname] & "#C:\" & [FName]
WHERE
([HLink] Is Null)
AND
([PO] Is Null);

does that look right?

if so...possibly try...

strSql= "UPDATE tblpurchase_orders " _
& "SET [HLink] = [Fname] & '#" & strfpath _
& "' & [FName] " _
& "WHERE ([HLink] Is Null) AND ([PO] Is Null);"

testing in Immediate Window:

strfpath="C:\"

strSql= "UPDATE tblpurchase_orders " _
& "SET [HLink] = [Fname] & '#" & strfpath _
& "' & [FName] " _
& "WHERE ([HLink] Is Null) AND ([PO] Is Null);"

?strSql
UPDATE tblpurchase_orders SET [HLink] = [Fname] & '#C:\' & [FName] WHERE
([HLink] Is Null) AND ([PO] Is Null);

Reply to this Message

Dale - 20 Nov 2007 02:52 GMT

Gary you are the best!!!!!!! You got it right away!!
This has eluded me and others in this newsgroup for a month now... Yes the
strfpath is the full path to the word doc file i.e.
strfpath="C:\somefolder\" And [Fname] as you guessed is a field in the
Purchase table (guess I need to be more explicit).
The code that worked if others find themselves needing this type of code:
strSql= "UPDATE tblpurchase_orders " _
& "SET [HLink] = [Fname] & '#" & strfpath _
& "' & [FName] " _
& "WHERE ([HLink] Is Null) AND ([PO] Is Null);"

Don't think I'd have ever come up with the single quotes..but obviously they
are needed

>>I am trying to run this code from a subroutine in vba... The filepath is a
>>constant string "strfpath". Currently I am getting a "missing operator"
[quoted text clipped - 44 lines]
> UPDATE tblpurchase_orders SET [HLink] = [Fname] & '#C:\' & [FName] WHERE
> ([HLink] Is Null) AND ([PO] Is Null);

Reply to this Message