passing a textbox parameter to a query

[50ld13r]

hi again

seems after half a day searching im back here again. Basically want to load a listbox from a query that is based on the input from a text box. Its gettin late and iv got myself confused over dao,ado recordsets to name but a few. I think i am in the right area just dont know how to progress

currently i have a table with tblCustomers(id,name,areacode,phoneNum)

i want the user to type in the textbox the areacode and it to return all the values in a text box.

SQL stament i have written for this to work is:
Code:

SELECT ID,Name,PhoneNum
FROM tblCustomer
WHERE areacode=txtsearchareacode

sorry if there is a post on this already i have really looked for it but has bin a few hours now with no hope

Thanks for any direction

 

[50ld13r]

i want the user to type in the textbox the areacode and it to return all the values in a *text box.

*sorry should say listbox

 

[50ld13r]

what i have so far:

Private Sub cmdFind_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim phone As Integer

Dim sqlCustomer As String

Set db = CurrentDb()

phone = txtPhoneNum

sqlCustomer = "SELECT * FROM [tblCustomers] WHERE [PhoneNum]=800"

Set rs = db.OpenRecordset(sqlCustomer, dbOpenDynaset)

Do Until rs.EOF

lstRecords.AddItem rs![AccNum]
lstRecords.AddItem rs![name]
rs.MoveNext

Loop

where it has the 800 value i want the variable phone - when i replace 800 with phone it doesnt work. When debugging phone does equal what is in txtPhoneNum
In my lstRecords i want to add the account numbers and names to the list box until the recordset is empty

 

[MarkK]

Keep in mind that an SQL command is a string. To include the evaluation of a variable or expression in that string, that evaluation must occur outside the SQL statement.
Consider...

[COLOR="Green"]'the expression "CInt(Me.tbSomeNumber / 3.5)" is evaluated
'and the result concatenated to the string assigned to "sql"
'in this respect the SQL is 'parameterized'
[/COLOR]sql = _
  "SELECT intField, strField, datField " & _
  "FROM tbl " & _
  "WHERE intField = " & CInt(Me.tbSomeNumber / 3.5)

If the type of the field in the WHERE clause is a string, the data compared to that field must be in quotes...

[COLOR="Green"]'note the single quotes enclosing the value returned by Me.tbSomeText[/COLOR]
sql = _
  "SELECT intField, strField, datField " & _
  "FROM tbl " & _
  "WHERE strField = '" & Me.tbSomeText & "'"

For a date field...

  "WHERE datField = #" & Me.tbSomeDate & "#"

And for what you're doing you can assign an SQL statement directly to the RowSource property of the listbox control

Private Sub cmdFind_Click()
  With Me.lstRecords
    .RowSource = _
      "SELECT accNum, [name] " & _
      "FROM tblCustomers " & _
      "WHERE PhoneNum LIKE '" & Me.txtPhoneNum & "*'"
[COLOR="Green"]      'check out the 'LIKE' operator in access help[/COLOR]
    .Requery
  End With
End Sub

 

[50ld13r]

thanks again lagbot - works now - thansk also for adintonal information am an sql and vba newbie which can be testing at the best of times