Problem passing the value of a text box into sql select statement

[EdRev]

I'm new to Access programming and I hope somebody can help me with this code. I'm trying to query a table based on user's input.

Private Sub cmdAdd_Click()
Dim db As Database
Dim recordsource
Dim sqltxt As String
Dim areano As String
areano = Trim(txtAreaNo.Value)

sqltxt = "SELECT * from area_address where area_no = [areano] ;"
Set db = CurrentDb()

Set recordsource = db.OpenRecordset(sqltxt)

If Not (recordsource.EOF) Then
MsgBox ("Area already exist&quot
Else
frmAddArea.show
End If

End Sub

The sql doesn't work and when I change the [areano] into a string value, the else part of my if-else statement gave me a compilation error.

Any help will be greatly appreciated.

 

[Psychpt]

If areano is a number format then you should surround it with #
If it's a string format you should surround it with '

try:
sqltxt = "SELECT * FROM area_address WHERE area_no = " & "#" &areano & "#"
or:
sqltxt = "SELECT * FROM area_address WHERE area_no = '" & areano & "'"

hope this helps

 

[EdRev]

thanks Psychtpt,

it worked!

 

[Psychpt]

you're welcome!