recordset open error on SQL query

[Siuxia]

Hello all,
Been using these forums a lot over the last few days to help me with an Application I'm developing, but I have run in to a problem I can't seem to over come.

My SQL is pretty weak, and I have just about managed to pull the data I have needed using simple queries. I have had to move on to a JOIN query to accomplish something and it's all gone a bit pear shaped.
I used the SQL builder to create the query and running it through that it works and pulls the data I want but when I put it in to the VB code it keeps chucking errors at me.

Here is the code below:

Private Sub cboAllocatedTo_AfterUpdate()


Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset


strSQL = "SELECT tblAuthTypes.CanAllocate FROM tblAuthTypes INNER JOIN tblUsers ON tblAuthTypes.RecID = tblUsers.RecID WHERE (((tblUsers.UserName)=[Forms]![frmLogin].[txtusername]))"


rst.Open strSQL, cnn, adOpenDynamic, adLockReadOnly


If rst.EOF = True Then
    MsgBox "This is true"
Else
    MsgBox "This is false"
End If


End Sub

When I use this it come up with "No give value for one of more required parameters". When debugging it highlights the following line;

rst.Open strSQL, cnn, adOpenDynamic, adLockReadOnly

I have used similar code through out my application with no ill effects. Can anyone please shed some light on this.
Thanks in advance.

[Robeen]

I have used similar code through out my application with no ill effects.

Can you give us an example from your application where you've used similar code that works?

[Siuxia]

Here is a piece of working code, the only thing I can see that is different is the JOIN query on the first bit of code I posted.

Private Sub btnConfirm_Click()On Error GoTo send_err_handler


Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset


strSQL = "SELECT EMail FROM tblUsers WHERE UserName='" & Me.txtIssuer & "'"


rst.Open strSQL, cnn, adOpenDynamic, adLockReadOnly


strMsgBody = "The Engineering Job " & Chr(34) & Me.txtBriefDesc & Chr(34) & " that was created by you on " & Me.txtDateRaised & " has been marked as completed on " & Me.txtDateComp & "."


DoCmd.SendObject acSendNoObject, , , rst!EMail, , , "Job Number: " & Me.txtJobNumber & " marked as completed.", strMsgBody, True


send_err_handler:
'not sent error 2501
If Err.Number = 2501 Then
    MsgBox "Job not sent due to message window being closed!", vbInformation, "Emailing Error"
Else
    MsgBox Err.Number & " " & Err.Description, vbCritical, "Emailing Error"
End If
End Sub

[Robeen]

strSQL = "SELECT EMail FROM tblUsers WHERE UserName='" & Me.txtIssuer & "'"

The code in your first post does not have the parts that appear above in red. Have you tried duplicating that and seeing if it will work?

[Siuxia]

The code in your first post does not have the parts that appear above in red. Have you tried duplicating that and seeing if it will work?

You sir, are a star! It's now pulling the query, now to see if I can get it to do what I want it to do.
Thanks again.

[Robeen]

Happy to help!!
All the best.