problem when adding AND

[jwlnewsome]

got this code to get a record set and it works fine

Private Sub Combo177_AfterUpdate()
Dim db As Database
  Dim rs As Recordset
  Dim strSql As String
strSql = "SELECT [Email] FROM tblAdvisorEmail WHERE (((tblAdvisorEmail.Client)= [Client]));"
Set db = CurrentDb() ' Open pointer to current database
Set rs = db.OpenRecordset(strSql) ' Create recordset based on SQL
Do While Not rs.EOF
    MsgBox (rs![Email])
    rs.MoveNext
  Loop
  rs.Close
  db.Close
End Sub

but when i add an and statement i get an error.
the new code is below

Private Sub Combo177_AfterUpdate()
Dim db As Database
  Dim rs As Recordset
  Dim strSql As String
strSql = "SELECT [Email] FROM tblAdvisorEmail WHERE (((tblAdvisorEmail.Client)= [Client])AND ((tblAdvisorEmail.AdvisorName)=[ClaimsAdivsor]));"
Set db = CurrentDb() ' Open pointer to current database
Set rs = db.OpenRecordset(strSql) ' Create recordset based on SQL
Do While Not rs.EOF
    MsgBox (rs![Email])
    rs.MoveNext
  Loop
  rs.Close
  db.Close
End Sub

can anybody help

thanks in advance
john

 

[RuralGuy]

You didn't mention what error you were getting. Did you copy and paste the code or retype it. It wastes time to troubleshoot typo's and I can see what looks like typo's. How about deleting the duplicate post?

 

[jwlnewsome]

run time error 3061 two few parameters, expected 1

that the error message and i copied and pasted carnt see the typo myself could you point it out
thanks
john

 

[RuralGuy]

The typo is probably the cause of the error. You have:

strSql = "SELECT [email] FROM tblAdvisorEmail " & _
"WHERE (((tblAdvisorEmail.Client)= [Client])[B]AND[/B] " & _
"((tblAdvisorEmail.AdvisorName)=[[B]ClaimsAdivsor[/B]]));"

And it probably needs to be changed to:

strSql = "SELECT [email] FROM tblAdvisorEmail " & _
"WHERE (((tblAdvisorEmail.Client)= [Client]) " & _
"AND ((tblAdvisorEmail.AdvisorName)=[[B]ClaimsAdvisor[/B]]));"

 

[jwlnewsome]

tried that still getting the same error its doing my head in, i carnt see whats wrong and when i take the AND part out everything works fine.
thanks
john

 

[RuralGuy]

I got sidetracked and forgot my original thought, sorry. I'm going to assume you are trying to compare to controls on your form. If so then the controls need to be outside of the string. Was I correct in that the control name was misspelled? I also believe it is a text field and needs to be enclosed in quotes.

strSql = "SELECT [email] FROM tblAdvisorEmail " & _
"WHERE (((tblAdvisorEmail.Client)= " & Me.Client & ") " & _
"AND ((tblAdvisorEmail.AdvisorName)= " & _
Chr(34) & Me.ClaimsAdvisor & Chr(34) & "));"

It is very useful to name your controls differently than the fields to which they are bound. Access gets confused and does not know which object to reference. I usually preface the name with the type of control: txt for TextBox, cbo for ComboBox, etc.

 

[jwlnewsome]

yes it was a spelling error and yes im compering two enties on a form. im trying to narrow down the record set to just 1 email so i can put it somewhere else i have a table with 3 collums Client, AdvisorName and Email
and the form contains 2 combo boxes named Client and ClaimsAdvisor. the query should return an email depending on the 2 combo boxes.

i hope this explains what im trying to do, not to successfully i may add. i tried your last bit of code and got a different compile error data member not found . it highlighted

Private Sub Combo177_AfterUpdate()
Dim db As Database
  Dim rs As Recordset
  Dim strSql As String
strSql = "SELECT [email] FROM tblAdvisorEmail " & _
"WHERE (((tblAdvisorEmail.Client)= " & Me.Client & ") " & _
"AND ((tblAdvisorEmail.AdvisorName)= " & _
Chr(34) & [B][U]Me.ClaimsAdvisor [/U][/B]& Chr(34) & "));"
Set db = CurrentDb() ' Open pointer to current database
Set rs = db.OpenRecordset(strSql) ' Create recordset based on SQL
Do While Not rs.EOF
    MsgBox (rs![Email])
    rs.MoveNext
  Loop
  rs.Close
  db.Close
End Sub

thanks for the help

john (still going round in circles)

 

[RuralGuy]

Are you sure the name of the ComboBox is ClaimsAdvisor? The error indicates otherwise.

 

[jwlnewsome]

im going to start from scratch i may be some time
thanks for all your help but i need to just go away and come back to it with a clear head
laters, john

 

[RuralGuy]

I completely understand. I do that all of the time. Later!