CODEWITHSUNDEEP
sqlms-accessselect
FatBoySlim7
FatBoySlim7

Reputation: 232

MS ACCESS vba select

I have a table in MS Access with data that I'm trying to select. The table has a userID in it, so I'm only trying to select info with a particular user ID. What I'm doing though is passing the userID to a form in access, so I want to be able to reference the frm field in my query. When i run this code below it tells me theres too few parameters.

dim rs1 as DAO.recordset
strSQL = "Select * from [dbo_tempDetail] where [userID] = [Forms]![frmUserInfo]![txtCLIENTID];"

Set rs1 = CurrentDb.OpenRecordset(strSQL, , dbOpenDynamic)

rCount = rs1.RecordCount

what is wrong here

EDIT 2

strSQL = "Select * from [dbo_tempDetail] where userID = '" & [Forms]![frmUserInfo]![txtClientID] & "' "

Set rs1 = CurrentDb.OpenRecordset(strSQL, , dbOpenDynamic)

rCount = rs1.RecordCount

Upvotes: 1

Views: 543

Answers (1)

user4039065
user4039065

Reputation:

The SELECT statement must be a concatenated string using the form value(s). VBA might know what [Forms]![frmUserInfo]![txtCLIENTID] is, but the SELECT does not.

strSQL = "Select * from [dbo_tempDetail] where [userID] = " & _
             [Forms]![frmUserInfo]![txtCLIENTID] & ";"
'might have to quote the txtCLIENTID value
strSQL = "Select * from [dbo_tempDetail] where [userID] = '" & _
             [Forms]![frmUserInfo]![txtCLIENTID] & "';"

Upvotes: 4

Related Questions