Solved Single Quotes in Criteria

[JithuAccess]

Hello Guys,

I want to display File Name from my Table tblFileBasic in to a Text in my Form frmFileRequest if the File Number in the text box of the Form matches the File Number in the Table.

This is my Code:

Dim db As Database
Dim rst As Recordset
Dim strsql As String

strsql = "Select File_Name from tblFileBasic where (tblFileBasic.File_Number = Forms!frmFileRequest!File_Number)"

Set db = CurrentDb
Set rst = db.OpenRecordset(strsql, dbOpenDynaset)

Text15 = rst.Fields(0)

I am getting Error Message like "Too Few Parameters.."

I think I should add a single quotes in where but I am not so sure how to add this. I tried Ampersand but it's not working.

Thanks

 

[cheekybuddha]

You need to concatenate the external value in to your SQL string:

' ...
strsql = "Select File_Name from tblFileBasic where tblFileBasic.File_Number = " & Forms!frmFileRequest!File_Number
' ...

 

[cheekybuddha]

If File_Number is a string datatype the, yes, you also need to add quotes:

' ...
strsql = "Select File_Name from tblFileBasic where tblFileBasic.File_Number = '" & Forms!frmFileRequest!File_Number & "'"
'                                                                             ^                                     ^^^^^
'                                                                             |                                     |||||
' ...

 

[JithuAccess]

If File_Number is a string datatype the, yes, you also need to add quotes:

' ...
strsql = "Select File_Name from tblFileBasic where tblFileBasic.File_Number = '" & Forms!frmFileRequest!File_Number & "'"
'                                                                             ^                                     ^^^^^
'                                                                             |                                     |||||
' ...

Thank you so much. It works

 

[plog]

I want to display File Name from my Table tblFileBasic in to a Text in my Form

Cheeky's post is a good lesson in how to build strings in VBA, but the method you have chosen to achieve the above objective is overkill. You can achieve the same thing with a DLookup as the control source of that text box.