Populating Hyperlink from vba

  • Thread starter Thread starter Dale
  • Start date Start date
D

Dale

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
 
Dale said:
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))"
Click to expand...

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
 
Dale said:
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))"
Click to expand...
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);
 
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

Gary Walter said:
Dale said:
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))"
Click to expand...
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);
Click to expand...
 
Back
Top