Error 3061 Too few parameters Expected1.

[Alhakeem1977]

Hi,
Trying to solve this issue with no success, to generate an email body with a table.

Below is my vba code and the query below the code:

When I remove the parameter from the query it works fine but with parameter, it dosesn't Error 3061

Private Sub cmdOpenEmail_Click()
On Error GoTo ErrorHandler

Dim strMsg As String
Dim iResponse As Integer

DoCmd.Beep
   strMsg = "Are you sure you want to proceed?" & Chr(10)
      strMsg = strMsg & ""
   iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Notice")
   If iResponse = vbNo Then
   Me.Undo
    
  DoCmd.Close acForm, "frmFileReqA", acSaveNo

  Else
 
  Me.Dirty = False

 '''''''''''''''''' tools -> refrence -> Microsoft outlook
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim mailbody As String
Dim rs As DAO.Recordset
' <br> used to insert a line ( press enter)
' create a table using html
' check the link below to know more about html tables
' http://www.w3schools.com/html/html_tables.asp
' html color code
'http://www.computerhope.com/htmcolor.htm or http://html-color-codes.info/
'bg color is used for background color
' font color is used for font color
'<b> bold the text  http://www.w3schools.com/html/html_formatting.asp
'    is used to give a single space between text
'<p style="font-size:15px">This is some text!</p> used to reduce for font size

'********************* created header of table
   mailbody = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
   "<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">A/C No. </p></Font></TD>" & _
   "<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">Customer's Name </p></Font></TD>" & _
      "</TR>"
      

Set rs = CurrentDb.OpenRecordset("qryFileReqEmail", dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF

              mailbody = mailbody & "<TR>" & _
               "<TD ><center>" & rs.Fields![Account Number].Value & "</TD>" & _
               "<TD><center>" & rs.Fields![Customer].Value & "</TD>" & _
                                   "</TR>"

rs.MoveNext
Loop
rs.Close

' <br> used to insert a line ( press enter)
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = "#########@###.com"
.Cc = ""
.Subject = "Account File/s Physical Retrieval Request"
.HTMLBody = "Dear DMS <br><br> ""Kindly arrange to provide me with the physical Account file/s as per the below details: <br><br> " & mailbody & _
"</Table><br> <br>Your assistance in this matter would be highly appreciated.<br> <br>Regards, <br> <br> " & [Forms]![NavigationForm]![txtUserName] & ""
.Display
'.Send
 MsgBox "Your email has been generated successfully!"
End With
  
 
DoCmd.Close acForm, "frmFileReqA", acSaveNo
End If
Cleanup:
  Exit Sub

ErrorHandler:
  Select Case Err.Number
    Case 2501
      MsgBox "Email message was Cancelled."
    Case Else
      MsgBox Err.Number & ": " & Err.Description
  End Select
  Resume Cleanup

End Sub

SELECT Customerstbl.FID, [Branch] & "-" & [AccountNumber] AS [Account Number], Customerstbl.Customer, [Userlogin] & " - " & [UserName] AS [Requested by], tblFileReq.ReqPk, tblFileReq.IsActive
FROM tblUser INNER JOIN (Customerstbl INNER JOIN tblFileReq ON Customerstbl.FID = tblFileReq.[AccountNo]) ON tblUser.UserID = tblFileReq.ReqID
WHERE (((tblFileReq.IsActive)=True) AND ((tblFileReq.ReqID)=[Forms]![NavigationForm]![txtID]))
ORDER BY [Branch] & "-" & [AccountNumber], Customerstbl.AccountNumber;

Thanks a lot in acvance!

 

[theDBguy]

Hi. Have I told you to try out Leigh’s Generic Recordset?

 

[Alhakeem1977]

Hi. Have I told you to try out Leigh’s Generic Recordset?

Thank you so much for your earliest response.
Unfortunately, I do not know how to use the function in the link, how do I link it to the data to be placed inside the email body as a table with some fixed text?

Sent from my HUAWEI NXT-L29 using Tapatalk

 

[Alhakeem1977]

Hi. Have I told you to try out Leigh’s Generic Recordset?

Sorry I did not mention that I am referring to Microsoft Access

Sent from my HUAWEI NXT-L29 using Tapatalk

 

[essaytee]

With the form "NavigationForm" open, what happens when you run the query directly, do you still get the error message? By running directly, I mean double-clicking on the query name from the left-hand panel.

 

[essaytee]

Similar to theDBGuy's link, check out QueryDefs.

 

[gemma-the-husky]

Am I right in thinking that this problem is caused by the resolution of the form reference. This bit.

((tblFileReq.ReqID)=[Forms]![NavigationForm]![txtID]))


I tend to get around this by using a function, but you could use tempvars

Not sure how you reference tempvars offhand, but this sort of thing

tempvars.txtid = [Forms]![NavigationForm]![txtID]

then in your query

((tblFileReq.ReqID)=tempvars.txtid]))

 

[theDBguy]

I tend to get around this by using a function,

Me too, like the one I linked to earlier.

 

[Gasman]

I use this syntax

tempvars("txtID")=231

though

tempvars!txtid = 221

works just as well.

Am I right in thinking that this problem is caused by the resolution of the form reference. This bit.

((tblFileReq.ReqID)=[Forms]![NavigationForm]![txtID]))


I tend to get around this by using a function, but you could use tempvars

Not sure how you reference tempvars offhand, but this sort of thing

tempvars.txtid = [Forms]![NavigationForm]![txtID]

then in your query

((tblFileReq.ReqID)=tempvars.txtid]))

 

[pbaldy]

Another simple solution is wrapping the form reference in the Eval() function:

tblFileReq.ReqID=Eval('[Forms]![NavigationForm]![txtID]')

The recordset should be able to open it with that in place.

 

[Alhakeem1977]

This is to confirm that the issue is solved with Eval() function.

Another simple solution is wrapping the form reference in the Eval() function:

tblFileReq.ReqID=Eval('[Forms]![NavigationForm]![txtID]')

The recordset should be able to open it with that in place.

Very cool, the code works great.

tblFileReq.ReqID=Eval('[Forms]![NavigationForm]![txtID]')

I honestly do not know how to thank you all, especially pbaldy.

Thank you all for your quick response.

 

[Alhakeem1977]

Am I right in thinking that this problem is caused by the resolution of the form reference. This bit.

((tblFileReq.ReqID)=[Forms]![NavigationForm]![txtID]))


I tend to get around this by using a function, but you could use tempvars

Not sure how you reference tempvars offhand, but this sort of thing

tempvars.txtid = [Forms]![NavigationForm]![txtID]

then in your query

((tblFileReq.ReqID)=tempvars.txtid]))

Thank you so much for your response, for sure the Tempvars will solve the issue, all of you are experts.

The Eval function solves the issue.

Thanks again.

 

[pbaldy]

Happy to help! There are certainly many ways to skin the proverbial cat. I hadn't seen Leigh's function before. Nice, as I would expect from him.

 

[theDBguy]

Happy to help! There are certainly many ways to skin the proverbial cat. I hadn't seen Leigh's function before. Nice, as I would expect from him.

And of course, one of its advantages is it doesn't require any of the existing queries to be changed at all. It just works! Cheers!