Getting current selected record value in access subform

2
votes

I have 2 subforms in access 2010 database; Based on selection of subform1 field value , the vba program will run the subform2 output which common text fields in subform 1 and 2 as "supplier_name". So, I tried the "on double click" event on subform1 to write the currentRecord method see below,

Private Sub Supplier_name_DblClick(Cancel As Integer)
    strSQL = "Select * from [Query1] where"
    strSQL = strSQL & "[Supplier_name] ="'" & "Me!current record![Supplier_name]" &"'"
    Form![Mainform]![Subform2].Form.RecordSource = strSQL
End Sub

I am getting Run-time error 3075 at the 2nd line; Syntax error (missing operator) in query expression '[Supplier_name] =Me!current record![Supplier_name]' Please help

2
ms-accessvba
"I am stuck at the second line" is not very detailed. Do you get an error message? Does it crash? Do you mean the second line of code or the second row in a table? - Tilman Hausherr
I need the syntax to be complete in the line [Supplier_name] ="'" & "Me!current record![Supplier_name]" &"'" - Neelakandan K
So your question is how to make the SQL query perfect? If so, then please edit your question (not everybody reads the comments). There's also a typo where you wrote "stnSQL". And avoid SQL injections. xkcd.com/327 - Tilman Hausherr
Yes.. I have corrected my question. - Neelakandan K

2 Answers

1
votes

Thanks it worked for me but without the currentrecord property

strSQL = strSQL & "[Supplier_name] ='" & Me![Supplier_name] & "'"
0
votes

You are missing a space after the where in the second row of your code:

strSQL = "Select * from [Query1] where "

and there is also a problem with your " and ' chars in the third line:

strSQL = strSQL & "[Supplier_name] ='" & Me!current record![Supplier_name] & "'"