Open web page from SQL statement 1

[hansretallick]

I am trying to open a web page using four fields selected from a table as part of the URL. I think I have tied myself in knots with inverted commas, and I would be grateful if anyone can point me in the right direction.

My VBA code is as follows:

Dim MyHyperlink As String

MyHyperlink = DoCmd.RunSQL "SELECT tblClients.Ref, (

http://www.epcgraph.co.uk/epc.png?

[tblEPC.Field1] & "," & [tblEPC.Field2] & "," & [tblEPC.Field3] & "," & [tblEPC.Field4] ) AS MyHyperlink " & _
"FROM (tblHIP INNER JOIN tblClients ON tblHIP.PropID=tblClients.ID) LEFT JOIN tblEPC ON tblHIP.HIPID=tblEPC.HIPID " & _
"WHERE (((tblClients.Ref)=Forms!frmEPCAdministration!Combo1995))"

DoCmd.RunSQL MyHyperlink

Application.FollowHyperlink _

MyHyperlink

 

[jrbarnett]

You need to build the Hyperlink part of the SQL string in a variable, you can then pass this to the Application.FollowHyperlink command and also embed it in the SQL string, for example:

Dim MyHyperlink As String
Dim MySQL As String

MyHyperlink = "[URL unfurl="true"]http://www.epcgraph.co.uk/epc.png?[/URL]  [tblEPC.Field1] & "," & [tblEPC.Field2] & "," & [tblEPC.Field3] & "," & [tblEPC.Field4]"
        
MySQL = DoCmd.RunSQL "SELECT tblClients.Ref " & MyHyperLink &  " & _
"FROM (tblHIP INNER JOIN tblClients ON tblHIP.PropID=tblClients.ID) LEFT JOIN tblEPC ON tblHIP.HIPID=tblEPC.HIPID  " & _
        "WHERE ((tblClients.Ref)= Forms!frmEPCAdministration!Combo1995))"
        
DoCmd.RunSQL MySQL
        
Application.FollowHyperlink MyHyperlink

John

 

[Remou]

What do these fields contain?
[tblEPC.Field1] & "," & [tblEPC.Field2] & "," & [tblEPC.Field3] & "," & [tblEPC.Field4]

The normal format is, say:

http://www.tek-tips.com/threadminder.cfm?pid=702

That is, no commas.

After that, you need a recordset, unless you can use DlookUp, you cannot use RunSQL with a select query.

 

[hansretallick]

Thanks very much John, unfortunately the code still doesn't work - it throws up a compile error - expected - end of statement error message.

 

[hansretallick]

Remou - thanks for your reply. The field contain any integer between 01 and 99 and the web page expects the URL to be in format

http://www.epcgraph.co.uk/epc.png?xx,xx,xx,xx

(I have tested it and it works with manually entered integers).

 

[Remou]

Dim MyHyperlink As String
Dim MySQL As String
'Reference: Microsoft DAO x.x Object Library
Dim rs As DAO.Recordset

'Assuming Clients Ref is numeric        
MySQL "SELECT tblClients.Ref, tblEPC.Field1, tblEPC.Field2, tblEPC.Field3, tblEPC.Field4 FROM (tblHIP INNER JOIN tblClients ON tblHIP.PropID=tblClients.ID) LEFT JOIN tblEPC ON tblHIP.HIPID=tblEPC.HIPID  " & _
"WHERE tblClients.Ref= " & Forms!frmEPCAdministration!Combo1995

Set rs=CurrentDB.Openrecordset(MySQL)
        
MyHyperlink = "[URL unfurl="true"]http://www.epcgraph.co.uk/epc.png?"[/URL] & rs!Field1 & "," & rs!Field2 & "," & rs!Field3 & "," & rs!Field4
        
Application.FollowHyperlink MyHyperlink

 

[hansretallick]

Hi Remou
It nearly works - that is to say the only error is that it now doesn't like the combo - tblClients.Ref is actually text based rather than numeric

 

[Remou]

That would be:

<...>
"WHERE tblClients.Ref= '" & Forms!frmEPCAdministration!Combo1995 & "'"

 

[hansretallick]

I'm afraid it still isn't happy. I am now getting error "Compile error 0- expect sub, function or property" with !Combo1995 Highlighted.

 

[Remou]

It's late

[tt]MySQL[red]=[/red] "SELECT tblClients.Ref, tblEPC.Field1, tblEPC.Field2, tblEPC.Field3, tblEPC.Field4 FROM (tblHIP INNER JOIN tblClients ON tblHIP.PropID=tblClients.ID) LEFT JOIN tblEPC ON tblHIP.HIPID=tblEPC.HIPID " & _
"WHERE tblClients.Ref= '" & Forms!frmEPCAdministration!Combo1995 & "'"[/tt]

 

[hansretallick]

Stunning Remou - I's late here as well - I should have noticed the = as well. Many thanks - it works perfectly.

 

[jrbarnett]

Make it:

MyHyperlink = "

http://www.epcgraph.co.uk/epc.png?"

& [tblEPC.Field1] & "," & [tblEPC.Field2] & "," & [tblEPC.Field3] & "," & [tblEPC.Field4]"

and that should fix the link, sorry for the typo.

I'm a bit confused though - items with a .png extensions are graphics files (Portable Network Graphics files) - are you sure this shouldn't have a different extension?

John