Solved Single Quotes in Criteria (1 Viewer)

JithuAccess

Member
Local time
Yesterday, 18:47
Joined
Mar 3, 2020
Messages
320
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:

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

AWF VIP
Local time
Today, 01:47
Joined
Jul 21, 2014
Messages
2,394
You need to concatenate the external value in to your SQL string:
Code:
' ...
strsql = "Select File_Name from tblFileBasic where tblFileBasic.File_Number = " & Forms!frmFileRequest!File_Number
' ...
 

cheekybuddha

AWF VIP
Local time
Today, 01:47
Joined
Jul 21, 2014
Messages
2,394
If File_Number is a string datatype the, yes, you also need to add quotes:
Code:
' ...
strsql = "Select File_Name from tblFileBasic where tblFileBasic.File_Number = '" & Forms!frmFileRequest!File_Number & "'"
'                                                                             ^                                     ^^^^^
'                                                                             |                                     |||||
' ...
 

JithuAccess

Member
Local time
Yesterday, 18:47
Joined
Mar 3, 2020
Messages
320
If File_Number is a string datatype the, yes, you also need to add quotes:
Code:
' ...
strsql = "Select File_Name from tblFileBasic where tblFileBasic.File_Number = '" & Forms!frmFileRequest!File_Number & "'"
'                                                                             ^                                     ^^^^^
'                                                                             |                                     |||||
' ...


Thank you so much. It works
 

plog

Banishment Pending
Local time
Yesterday, 19:47
Joined
May 11, 2011
Messages
11,705
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.
 

Users who are viewing this thread

Top Bottom