Run Time Error - No Value Given

[RichardH]

Hi, I'm having a problem trying to run an sql statement where I get the error Run Time Error - No Value Given For One or More Required Parameters.
I know that the sql works but I don't know what I'm missing to get this to run. Can anyone help please? This is my code...

Dim cnn2 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim CnnStr As String

CnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
CnnStr = CnnStr + "User ID=Admin;"
CnnStr = CnnStr + " Data Source=C:\Initiatives\Database\Initiatives Tracker.mdb"

rs.ActiveConnection = CnnStr
strSQL = "SELECT tblUserDets.txtEmailAddress, tblUserDets.txtName FROM tblUserDets "
strSQL = strSQL + "INNER JOIN tblUserInitsFiltered ON tblUserDets.txtName = tblUserInitsFiltered.txtLastChangedBy "
strSQL = strSQL + "WHERE (((tblUserInitsFiltered.txtLastChangedBy)=Forms!frmAmend!txtOwner));"

rs.Open strSQL, CnnStr

thanks Richard

 

[Bat17]

Try:-

strSQL = strSQL + "WHERE (((tblUserInitsFiltered.txtLastChangedBy)=' " & Forms!frmAmend!txtOwner & " '));"


Peter

 

[RichardH]

Genius, it worked, cheers mate. Out of interest, what's the difference? Why would that work when it's concatenating the same details other than spaces?

 

[Mile-O]

Your version took in the literal reference to the form control so the query, in ADO, needs to have the parameter defined (Parameter object) whereas the solution uses the literal value of the form control so there would be no need for a Parameter object.