using combo box value withing a select query

[scoob8254]

hi, im creating a simple db and hit a prob, prob a simple answer but for some reason i cant figure it out, doh

i have a comobo box called comboGoToMark when i change the value in this box i want to change another value called MyGoogleMapURL which holds a url, the url has to include info from a table called tblMarks

i get the error " no value given for one or more required parameters"

am i going about this problem in the wrong way, any help appreciated as im fairly new to vba and sql etc

the code is below


Private Sub comboGoToMark_Change()
Dim dblglong As Double
Dim dblglat As Double
Dim strmarkname As String
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1
Dim mySQL As String
mySQL = "SELECT tblMarks.tblMarkName, tblMarks.tblLat, tblMarks.tblLong"
mySQL = mySQL + " FROM tblMarks"
mySQL = mySQL + " WHERE (((tblgoogle.tblSettingName)=me.comboGoToMark))"
myRecordSet.Open mySQL
dblglong = myRecordSet.Fields("tblLong").Value
dblglat = myRecordSet.Fields("tblLat").Value
strmarkname = myRecordSet.Fields("tblMarkName").Value

myRecordSet.Close

Set myRecordSet = Nothing
Set cnn1 = Nothing
MyGoogleMapURL = "http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=" + dblglat + "," + dblglong + "(" + strmarkname + ")"
End Sub

 

[pbaldy]

You have to concatenate the form reference. Since it looks like text (numeric would drop the single quotes):

mySQL = mySQL + " WHERE (((tblgoogle.tblSettingName)='" & me.comboGoToMark & "'))"

 

[scoob8254]

thanks for the reply, any help is greatly appreciated, still get the same error

no value given for one or more required parameters

 

[pbaldy]

Just noticed that the table in the FROM clause is different than in the WHERE clause.

 

[scoob8254]

jeez, your right,

corrected this, still get the same error

 

[pbaldy]

Okay, right before the recordset Open line, add

Debug.Print mySQL

which will let you examine the finished SQL string in the VBA immediate window. If you don't spot the problem, post that resulting SQL here.

 

[scoob8254]

seen the problem, made a typo on one of the field names, cant believe missed it as ive checked them time and time again, thanks for the help

 

[pbaldy]

No problemo; that Debug.Print is a valuable tool.

 

[scoob8254]

hello again, another prob with the same code, or nearly the same, the form didint look or work right so added a go button next to the combo box, everything seems to be fine till it gets to this part.

strurl1 = "[URL]http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q[/URL]=" + dblglat + "," + dblglong + "(" + strmarkname + ")"

then i get the error, runtime error 13, type mismatch.

the strurl1 variable hopefully after the above code should hold a url like this, btw below is just an example, i dont realy want to point out 10 downing st

http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=51.50335,-0.127721(Gordon Brown)

the complete code is below

Private Sub commandGo_Click()
Dim dblglong As Double
Dim dblglat As Double
Dim strmarkname As String
Dim strurl1 As String
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1
Dim mySQL As String
mySQL = "SELECT tblMarks.tblLat, tblMarks.tblLong"
mySQL = mySQL + " FROM tblMarks"
mySQL = mySQL + " WHERE (((tblMarks.tblMarkName)='" & Me.comboGoToMark & "'))"
Debug.Print mySQL
 
myRecordSet.Open mySQL
dblglong = myRecordSet.Fields("tblLong").Value
dblglat = myRecordSet.Fields("tblLat").Value
 
myRecordSet.Close
 
Set myRecordSet = Nothing
Set cnn1 = Nothing
 
strmarkname = Me.comboGoToMark
strurl1 = "[URL="http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q"][COLOR=#0066cc]http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q[/COLOR][/URL]=" + dblglat + "," + dblglong + "(" + strmarkname + ")"
WebBrowser0.Navigate [URL="http://www.access-programmers.co.uk/forums/=strurl1"]url:=strurl1[/URL]
End Sub

again any help appreciated

 

[scoob8254]

nm sorted