Help a with a simple VBA

[alexmb1]

I am a n00b with VBA and need some help. I have a form that lists data about hundreds of employees. in that form is an address column that is also in a residences table. I put links to google maps for each address in the residence table. I am trying to develop a button that on click will go into the residence table and return the link that is attached to the address of the employee on the form and then open that link.

here is what i have so far but i keep getting a 490 error:

Private Sub LookUpMap_Click()
Dim ds As DAO.Database
Dim rs As DAO.Recordset
Dim strMap As String
strMap = "(Select Google_Map From Residences Where Me.Permanent_Address = Address)"
Application.FollowHyperlink strMap, , True, True

End Sub

any help is appreciated

 

[gemma-the-husky]

strMap = "(Select Google_Map From Residences Where Me.Permanent_Address = Address)"

this cant be right. what is address? text or number

if text then you should have something like

strMap = "(Select Google_Map From Residences Where Me.Permanent_Address = " & chr(34) & Address & chr(34)

chr(34) is " - i find it easier to read.

 

[alexmb1]

address is a text field in the 'residence' table. 'Permanent_address' is a text field in 'employees' table. i am trying to use these as a way to reference the links and return those.

 

[alexmb1]

i tried your suggestion and i am still getting the 490 error that says "cannot open specified file"

here is what my code looks like now:


Private Sub LookUpMap_Click()
Dim ds As DAO.Database
Dim rs As DAO.Recordset
Dim strMap As String
strMap = "(Select Google_Map From Residences Where Me.Permanent_Address = " & Chr(34) & Address & Chr(34)

Application.FollowHyperlink strMap, , True, True
End Sub

 

[accessNator]

strMap = "(Select Google_Map From Residences Where Me.Permanent_Address = Address)"

address is a text field in the 'residence' table. 'Permanent_address' is a text field in 'employees' table. i am trying to use these as a way to reference the links and return those.

Your Select statement needs some work.
Your "WHERE" statement => Me.Permanent_Address will not work because you are trying to reference a control field with a "ME" syntax. The Me.Permanent_Address should be a column name in your "Residence" or "Residences" table. I am not sure which is the correct name based on your statement that I quoted from above.

Your = Address, indicates you are trying to reference the Address column in your "Residence" or "Residences" table

So, I am going to make assumptions here so it may point you to the correct direction.

I am assuming you want to use a form entry on your form called Permanent_Address

Please verify if your table name is Residence or Residences

So the Select statement should read:
strMap = "Select Google_Map From Residence Where Address = " & Me.Permanent_Address & ";"

 

[alexmb1]

the table is called 'residences' and the field in the form is called 'permanent_address'. ill try to see what happens with your code. hopefully i can get this figured out today.

 

[alexmb1]

the code seems to be pointing me in the right direction. when the dialog box comes up to warn about opening hyperlinks it is going to the correct location as it indicates. i still get the 490 error and when i choose to debug it highlights the last line of my code:

Application.FollowHyperlink strMap, , True, True



here is what it all looks like. am i missing something?

Private Sub Command45_Click()
Dim ds As DAO.Database
Dim rs As DAO.Recordset
Dim strMap As String

strMap = "Select Google Map From Residences Where Address = " & Me.Permanent_Address & ";"

Application.FollowHyperlink strMap, , True, True

End Sub

 

[hiền muội]

First: ds,rs you declare for what ?
Second: strMap in code is exactly a string "Select Google Map From Residences Where Address = your address---> it 's not a sql

---> Try following:
Private Sub Command45_Click()

Dim rs As DAO.Recordset
Dim strMap As String

strMap = "Select Google Map From Residences Where Address = '" & Me.Permanent_Address & "'"
set rs=currentDb.OpenRecordset(strMap)
Application.FollowHyperlink rs.Fields("Google Map").Value, , True, True

Set rs = Nothing
End Sub

 

[DCrake]

Has everyone missed the obvious

strMap = "Select Google Map From Residences Where Address = '" & Me.Permanent_Address & "'"

Google Map is the name of the field in the table and has a space in it, therefore it needs to be bracketed

strMap = "Select [B][Google Map][/B] From Residences Where Address = '" & Me.Permanent_Address & "'"

David

 

[gemma-the-husky]

this is changing

he had google_map with an underscore orginally
now its google map with a space, so it needs brackets

he originally also had Me.Permanent_Address as the predicate? for the where clause, but this has changed

the other thing that occurs to me is what datatype google_map might actually be?

so its not ever so clear ot me, exactly what the code will achieve.

 

[alexmb1]

ok its working now. i had to add the brackets to the code but it works just perfect now.

here is the final version i have

Private Sub Command45_Click()
Dim rs As DAO.Recordset
Dim strMap As String
strMap = "Select [Google Map] From Residences Where Address = '" & Me.Permanent_Address & "'"
Set rs = CurrentDb.OpenRecordset(strMap)
Application.FollowHyperlink rs.Fields("Google Map").Value, , True, True
Set rs = Nothing
End Sub

im learning slowly but starting to get the hang of it. couldnt have done it without all of your help though.

til next time

thanks