SELECT...AS... Inline query

[Joshua Barnette]

I am currently working on a database where I need to be able to select
4 fields from a table with only 1 record.

The table name is prog_info_tbl, the fields are paddlin1, pcity,
pstate, pzip, the table key is info_id, which = 1.

What I am trying to accomplish is a button that when clicked can pull
this information and assign each field so that I can manipulate the
data (pull the spaces out and replace with "+"). I have the data
manipulation working for other fields, I just can't figure out the
query part.

I will paste my code below, I know you cannot run a select from
docmd.runsql, I want to show what I'm doing. I have tried to use ADO or
DAO, but always get an error about it not being recognized. Any help is
greatly appreciated.

Dim ch_add As String
Dim ch_city As String
Dim ch_state As String
Dim ch_zip As String
Dim dr_add As String
Dim dr_city As String
Dim dr_state As String
Dim dr_zip As String
Dim dr_link As String
Dim dr_SQL As String
dr_SQL = "select prog_info_tbl.ch_addlin1 as ch_add,
prog_info_tbl.ch_city as ch_city, prog_info_tbl.ch_state As ch_state,
prog_info_tbl.ch_zip as ch_zip from prog_info_tbl where
prog_info_tbl.info_id = 1;"
DoCmd.RunSQL ("dr_SQL")
ch_add = Replace(ch_add, " ", "+")
ch_city = Replace(ch_city, " ", "+")
ch_state = Replace(ch_state, " ", "+")
ch_zip = Replace(ch_zip, " ", "+")
dr_add = Me.addlin1.Value
dr_add = Replace(dr_add, " ", "+")
dr_city = Me.city.Value
dr_city = Replace(dr_city, " ", "+")
dr_state = Me.state.Value
dr_state = Replace(dr_state, " ", "+")
dr_zip = Me.zip.Value
dr_zip = Replace(dr_zip, " ", "+")
dr_link =
("http://www.mapquest.com/directions/...s=1&1y=US&1ffi=&1l=&1g=&1pl=&1v=&1n=&1pn=&1a="
& ch_add & "&1c=" & ch_city & "&1s=" & ch_state & "&1z=" & ch_zip &
"&2y=US&2ffi=&2l=&2g=&2pl=&2v=&2n=&2pn=&2a=" & dr_add & "&2c=" &
dr_city & "&2s=" & dr_state & "&2z=" & dr_zip)
FollowHyperlink (dr_link)

 

[Douglas J Steele]

You need to open a recordset, and then work with the values returned in the
recordset.

With DAO, it would be something like:

Dim rsCurr As DAO.Recordset
Dim dr_link As String
Dim dr_SQL As String

dr_SQL = "select prog_info_tbl.ch_addlin1 as ch_add, " & _
"prog_info_tbl.ch_city as ch_city, " & _
"prog_info_tbl.ch_state As ch_state, " & _
"prog_info_tbl.ch_zip as ch_zip " & _
"from prog_info_tbl " & _
"where prog_info_tbl.info_id = 1"

Set rsCurr = CurrentDB.OpenRecordset(dr_SQL)
If rsCurr.BOF = False And rsCurr.EOF = False Then
dr_link = "http://www.mapquest.com/directions/main.adp?" & _
"go=1&do=nw&rmm=1&un=m&cl=EN&ct=NA&rsres=1&" & _
"1y=US&1ffi=&1l=&1g=&1pl=&1v=&1n=&1pn=" & _
"&1a=" & Replace(Nz(rsCurr!ch_add, ""), " ", "+") & _
"&1c=" & Replace(Nz(rsCurr!ch_city, ""), " ", "+") & _
"&1s=" & Replace(Nz(rsCurr!ch_state, ""), " ", "+") & _
"&1z=" & Replace(Nz(rsCurr!ch_zip, ""), " ", "+") & _
"&2y=US&2ffi=&2l=&2g=&2pl=&2v=&2n=&2pn=" & _
"&2a=" & Replace(dr_add, " ", "+") & _
"&2c=" & Replace(dr_city, " ", "+") & _
"&2s=" & Replace(dr_state, " ", "+") & _
"&2z=" & Replace(dr_zip, " ", "+")
FollowHyperlink (dr_link)
End If

 

[Joshua Barnette]

Doug,
You solution worked, THANK YOU!!!! I struggled with this for days.
I very much appreciate this.

Can you recommend any good books or online tutorials for learning
DAO?

Thanks,
Joshua Barnette

 

[Douglas J Steele]

Any book by John Viescas should have a good section on it.