VBA Error: run-time error 3061: too few parameters. Expected 2.

A

Arcadia

Registered User.
Local time
Today, 09:26
Joined
Jan 25, 2013
Messages
66
I have a problem with my VBA code and really hope someone can help me out here :rolleyes:

In Access i have made a report with a button that has to open a e-mail message but when i press the button i got the following error:

run-time error 3061:
Too few parameters. Expected 2.

And when i check out my VBA code i see that the following line is highlighted in yellow.

Set rst = qry.OpenRecordset

I know the problem does lay with the query this code is linked with because it has a parameter.. because when i link it with a query without a parameter it just works.

Does someone know how i can solve this problem the best? :o

Code: 
Private Sub Command18_Click()
Dim objOutlook As Outlook.Application
          Dim objOutlookMsg As Outlook.MailItem
          Dim objOutlookRecip As Outlook.Recipient
          Dim objOutlookAttach As Outlook.Attachment
          Dim qry As QueryDef
          Dim rst As Recordset
        


          ' Create the Outlook session.
          Set objOutlook = CreateObject("Outlook.Application")

          ' Create the message.
          Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

          With objOutlookMsg
              ' Add the To recipient(s) to the message.
              Set objOutlookRecip = .Recipients.Add("name@email.com")
              objOutlookRecip.Type = olTo

              ' Add the CC recipient(s) to the message.
              Set objOutlookRecip = .Recipients.Add("Michael Suma")
              objOutlookRecip.Type = olCC

             ' Add the BCC recipient(s) to the message.
              Set objOutlookRecip = .Recipients.Add("Andrew Fulr")
              objOutlookRecip.Type = olBCC

             ' Set the Subject, Body, and Importance of the message.
             .Subject = "Training dates"
            
             
             .BodyFormat = olFormatHTML
        
             .HTMLBody = "<p><strong>Dear Colleague,</strong></p>welcome to our Training offering for Region Europe. We, the Training Team are very excited about the upcoming year - we will have new products blabla, updated training curricula as well as a much better training infrastructure.<p>Please see the attachment for the dates! <p><p><p><p><b>With Kind regards,</strong></b> <p> Name <br> <p><b> Below you can see the upcoming training schedule: </b><br> "
             
              Set qry = CurrentDb.QueryDefs("TrainingCalender Query")
              [B]Set rst = qry.OpenRecordset[/B]
                
              Do Until rst.EOF
                    .HTMLBody = .HTMLBody & " <TABLE BORDER=4 RULES=NONE FRAME=BOX> " & " " & " <br>  <p> " & " Training: " & rst![Title] & " " & " <br> " & " Start Time: " & rst![Start Time] & " " & " <br> " & " Place: " & rst![Location] & " </table> "
                    rst.MoveNext
             Loop
             .Importance = olImportanceHigh  'High importance

             ' Add attachments to the message.
             If Not IsMissing(AttachmentPath) Then
                 Set objOutlookAttach = .Attachments.Add("L:\Public\Access\PDF\Trainingen.pdf")
             End If

             ' Resolve each Recipient's name.
             For Each objOutlookRecip In .Recipients
                 objOutlookRecip.Resolve
             Next

             ' Should we display the message before sending?
             If DisplayMsg Then
                 .Display
             Else
                 .Save
                 .Display
             End If
          End With
          Set objOutlook = Nothing
End Sub
 
The problem is because the Saved/precompiled query "TrainingCalender Query" expects two parameters to be provided in order to run the Query. You now have two options, either Edit the Query Def, by using the QueryDefs collection object or You can use a run time query.
 
pr2-eugin said:
The problem is because the Saved/precompiled query "TrainingCalender Query" expects two parameters to be provided in order to run the Query. You now have two options, either Edit the Query Def, by using the QueryDefs collection object or You can use a run time query.
Click to expand...


Ok i did that with the Query Def but when i run it i recieve the following error:

Run-time error 2482:
Microsoft Access cannot find the name ' van: dd-mm-yy' you entered in the expression.
Code: 
Private Sub Command18_Click()
Dim objOutlook As Outlook.Application
          Dim objOutlookMsg As Outlook.MailItem
          Dim objOutlookRecip As Outlook.Recipient
          Dim objOutlookAttach As Outlook.Attachment
          Dim qry As QueryDef
          Dim rst As Recordset
          
         Dim db As DAO.Database
         Dim qdf As DAO.QueryDef
         Dim prm As Parameter


          ' Create the Outlook session.
          Set objOutlook = CreateObject("Outlook.Application")

          ' Create the message.
          Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

          With objOutlookMsg
              ' Add the To recipient(s) to the message.
              Set objOutlookRecip = .Recipients.Add("person@mail.com")
              objOutlookRecip.Type = olTo

              ' Add the CC recipient(s) to the message.
              Set objOutlookRecip = .Recipients.Add("Michael Suyma")
              objOutlookRecip.Type = olCC

             ' Add the BCC recipient(s) to the message.
              Set objOutlookRecip = .Recipients.Add("Andrew Fulr")
              objOutlookRecip.Type = olBCC

             ' Set the Subject, Body, and Importance of the message.
             .Subject = "Training dates"
            
             
             .BodyFormat = olFormatHTML
        
             .HTMLBody = "<p><strong>Dear Colleague,</strong></p>welcome to our  We, the Training Team are very excited about the upcoming year - we will have new products  updated training curricula as well as a much better training infrastructure.<p>Please see the attachment for the dates! <p><p><p><p><b>With Kind regards,</strong></b> <p> Name<br> <p><b> Below you can see the upcoming training schedule: </b><br> "
             

                
    Set db = CurrentDb
    Set qdf = db.QueryDefs("TrainingCalender Query")
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next prm
    Set rst = qdf.OpenRecordset(dbOpenDynaset)
    

              Do Until rst.EOF
                    .HTMLBody = .HTMLBody & " <TABLE BORDER=4 RULES=NONE FRAME=BOX> " & " " & " <br>  <p> " & " Training: " & rst![Title] & " " & " <br> " & " Start Time: " & rst![Start Time] & " " & " <br> " & " Place: " & rst![Location] & " </table> "
                    rst.MoveNext
             Loop
             .Importance = olImportanceHigh  'High importance

             ' Add attachments to the message.
             If Not IsMissing(AttachmentPath) Then
                 Set objOutlookAttach = .Attachments.Add("L:\Public\Access\PDF\Trainingen.pdf")
             End If

             ' Resolve each Recipient's name.
             For Each objOutlookRecip In .Recipients
                 objOutlookRecip.Resolve
             Next

             ' Should we display the message before sending?
             If DisplayMsg Then
                 .Display
             Else
                 .Save
                 .Display
             End If
          End With
          Set objOutlook = Nothing
End Sub
 
Last edited:
Which method did you go for and what changes have you made? Show the code you have now :rolleyes:
 
pr2-eugin said:
Which method did you go for and what changes have you made? Show the code you have now :rolleyes:
Click to expand...

Sorry i forgot the code.

Code: 
Private Sub Command18_Click()
Dim objOutlook As Outlook.Application
          Dim objOutlookMsg As Outlook.MailItem
          Dim objOutlookRecip As Outlook.Recipient
          Dim objOutlookAttach As Outlook.Attachment
          Dim qry As QueryDef
          Dim rst As Recordset
          
         Dim db As DAO.Database
         Dim qdf As DAO.QueryDef
         Dim prm As Parameter


          ' Create the Outlook session.
          Set objOutlook = CreateObject("Outlook.Application")

          ' Create the message.
          Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

          With objOutlookMsg
              ' Add the To recipient(s) to the message.
              Set objOutlookRecip = .Recipients.Add("person@mail.com")
              objOutlookRecip.Type = olTo

              ' Add the CC recipient(s) to the message.
              Set objOutlookRecip = .Recipients.Add("Michael Syama")
              objOutlookRecip.Type = olCC

             ' Add the BCC recipient(s) to the message.
              Set objOutlookRecip = .Recipients.Add("Andrew Fler")
              objOutlookRecip.Type = olBCC

             ' Set the Subject, Body, and Importance of the message.
             .Subject = "Training dates"
            
             
             .BodyFormat = olFormatHTML
        
             .HTMLBody = "<p><strong>Dear Colleague,</strong></p>welcome to our we updated training curricula as well as a much better training infrastructure.<p>Please see the attachment for the dates! <p><p><p><p><b>With Kind regards,</strong></b> <p> Name <br> <p><b> Below you can see the upcoming training schedule: </b><br> "
             

                
    Set db = CurrentDb
    Set qdf = db.QueryDefs("TrainingCalender Query")
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next prm
    Set rst = qdf.OpenRecordset(dbOpenDynaset)
    

              Do Until rst.EOF
                    .HTMLBody = .HTMLBody & " <TABLE BORDER=4 RULES=NONE FRAME=BOX> " & " " & " <br>  <p> " & " Training: " & rst![Title] & " " & " <br> " & " Start Time: " & rst![Start Time] & " " & " <br> " & " Place: " & rst![Location] & " </table> "
                    rst.MoveNext
             Loop
             .Importance = olImportanceHigh  'High importance

             ' Add attachments to the message.
             If Not IsMissing(AttachmentPath) Then
                 Set objOutlookAttach = .Attachments.Add("L:\Public\Access\PDF\Trainingen.pdf")
             End If

             ' Resolve each Recipient's name.
             For Each objOutlookRecip In .Recipients
                 objOutlookRecip.Resolve
             Next

             ' Should we display the message before sending?
             If DisplayMsg Then
                 .Display
             Else
                 .Save
                 .Display
             End If
          End With
          Set objOutlook = Nothing
End Sub
 
Can you explain what this line is supposed to do in your code?
Code: 
        Set qdf = db.QueryDefs("TrainingCalender Query")
        For Each prm In qdf.Parameters
            [COLOR=Red][B]prm.Value = Eval(prm.Name)[/B][/COLOR]
        Next prm
        [COLOR=Red][B]Set rst = qdf.OpenRecordset(dbOpenDynaset)[/B][/COLOR]
 
pr2-eugin said:
Can you explain what this line is supposed to do in your code?
Code: 
        Set qdf = db.QueryDefs("TrainingCalender Query")
        For Each prm In qdf.Parameters
            [COLOR=Red][B]prm.Value = Eval(prm.Name)[/B][/COLOR]
        Next prm
        [COLOR=Red][B]Set rst = qdf.OpenRecordset(dbOpenDynaset)[/B][/COLOR]
Click to expand...

I thought to find the parameter and open the query.
 
Okay if you run the Query from the Query window, you will be prompted to enter two values? The value you enter in those two parameters are the ones you need to supply.

You have written a code to find the parameter but you have not passed an actual value for the Query to execute. How about pasting the raw SQL of the Query?
 
pr2-eugin said:
Okay if you run the Query from the Query window, you will be prompted to enter two values? The value you enter in those two parameters are the ones you need to supply.

You have written a code to find the parameter but you have not passed an actual value for the Query to execute. How about pasting the raw SQL of the Query?
Click to expand...

Code: 
SELECT TrainingCalender.Title, TrainingCalender.Location, TrainingCalender.[Start Time], TrainingCalender.[End Time], TrainingCalender.Description, [Training description].[Training description]
FROM TrainingCalender LEFT JOIN [Training description] ON TrainingCalender.Title = [Training description].Title
WHERE (((TrainingCalender.[Start Time])>=[van: dd-mm-yy]) AND ((TrainingCalender.[End Time])<=[tot: dd-mm-yy]));
 
What is the Data type of Start Time and End Time? I hope it is Date/Time?
 
pr2-eugin said:
What is the Data type of Start Time and End Time? I hope it is Date/Time?
Click to expand...

Yes it is the start date en end date, and it depends on what the user fills in because when you open the report the parameter shows up and the user can fill in a start and end date and the you will see al trainings days in that period of time.

I want to put the current output info that show up in the report in the mail. But the parameter setup in the query blocks it for me and i can't delete it because i need it so users can fill in the parameter when they open the report.
 
Change your Query as,
Code: 
[COLOR=Red][B]PARAMETERS [van: dd-mm-yy] DateTime, [tot: dd-mm-yy] DateTime;[/B][/COLOR]
SELECT TrainingCalender.Title, TrainingCalender.Location, TrainingCalender.[Start Time], TrainingCalender.[End Time], TrainingCalender.Description, [Training description].[Training description]
FROM TrainingCalender LEFT JOIN [Training description] ON TrainingCalender.Title = [Training description].Title
WHERE (((TrainingCalender.[Start Time])>=[van: dd-mm-yy]) AND ((TrainingCalender.[End Time])<=[tot: dd-mm-yy]));
Then the code you should be using would be,
Code: 
Private Sub Command18_Click()
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    Dim qry As QueryDef
    Dim rst As Recordset

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim prm As Parameter

    [COLOR=Green]' Create the Outlook session.[/COLOR]
    Set objOutlook = CreateObject("Outlook.Application")

    [COLOR=Green]' Create the message.[/COLOR]
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    With objOutlookMsg
       [COLOR=Green] ' Add the To recipient(s) to the message.[/COLOR]
        Set objOutlookRecip = .Recipients.Add("person@mail.com")
        objOutlookRecip.Type = olTo

       [COLOR=Green] ' Add the CC recipient(s) to the message.[/COLOR]
        Set objOutlookRecip = .Recipients.Add("Michael Syama")
        objOutlookRecip.Type = olCC

       [COLOR=Green] ' Add the BCC recipient(s) to the message.[/COLOR]
        Set objOutlookRecip = .Recipients.Add("Andrew Fler")
        objOutlookRecip.Type = olBCC

        [COLOR=Green]' Set the Subject, Body, and Importance of the message.[/COLOR]
        .Subject = "Training dates"

        .BodyFormat = olFormatHTML

        .HTMLBody = "<p><strong>Dear Colleague,</strong></p>" & _
                    "Welcome to our we updated training curricula as well as a much better training infrastructure." & _
                    "<p>Please see the attachment for the dates! </p>" & _
                    "<b><strong>With Kind regards,</strong></b>" & _
                    "<p> Name <br> </p>" & _
                    "<b> Below you can see the upcoming training schedule: </b><br> "

        Set db = CurrentDb
        Set qdf = db.QueryDefs("TrainingCalender Query")
        [COLOR=Red][B]qdf.Parameters("[van: dd-mm-yy]") = Format(InputBox("van: dd-mm-yy", "Start Date", Format(Date, "dd-mm-yy")), "\#mm\/dd\/yyyy\#")
        qdf.Parameters("[tot: dd-mm-yy]") = Format(InputBox("tot: dd-mm-yy", "End Date", Format(Date, "dd-mm-yy")), "\#mm\/dd\/yyyy\#")
        Set rst = qdf.OpenRecordset()[/B][/COLOR]

        Do Until rst.EOF
            .HTMLBody = .HTMLBody & " <TABLE BORDER=4 RULES=NONE FRAME=BOX> " & " " & _
                        " <br>  <p> " & " Training: " & rst![Title] & " " & " <br> " & _
                        " Start Time: " & rst![Start Time] & " " & " <br> " & " Place: " & rst![Location] & " </table> "
            rst.MoveNext
        Loop
        .Importance = olImportanceHigh  'High importance

        [COLOR=Green]' Add attachments to the message.[/COLOR]
        If Not IsMissing(AttachmentPath) Then _
            Set objOutlookAttach = .Attachments.Add("L:\Public\Access\PDF\Trainingen.pdf")

        [COLOR=Green]' Resolve each Recipient's name.[/COLOR]
        For Each objOutlookRecip In .Recipients
            objOutlookRecip.Resolve
        Next

        [COLOR=Green]' Should we display the message before sending?[/COLOR]
        If DisplayMsg Then 
            .Display
        Else
            .Save
            .Display
        End If
    End With
    Set objOutlook = Nothing
End Sub
 
pr2-eugin said:
Change your Query as,
Code: 
[COLOR=Red][B]PARAMETERS [van: dd-mm-yy] DateTime, [tot: dd-mm-yy] DateTime;[/B][/COLOR]
SELECT TrainingCalender.Title, TrainingCalender.Location, TrainingCalender.[Start Time], TrainingCalender.[End Time], TrainingCalender.Description, [Training description].[Training description]
FROM TrainingCalender LEFT JOIN [Training description] ON TrainingCalender.Title = [Training description].Title
WHERE (((TrainingCalender.[Start Time])>=[van: dd-mm-yy]) AND ((TrainingCalender.[End Time])<=[tot: dd-mm-yy]));
Then the code you should be using would be,
Code: 
Private Sub Command18_Click()
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    Dim qry As QueryDef
    Dim rst As Recordset

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim prm As Parameter

    [COLOR=Green]' Create the Outlook session.[/COLOR]
    Set objOutlook = CreateObject("Outlook.Application")

    [COLOR=Green]' Create the message.[/COLOR]
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    With objOutlookMsg
       [COLOR=Green] ' Add the To recipient(s) to the message.[/COLOR]
        Set objOutlookRecip = .Recipients.Add("person@mail.com")
        objOutlookRecip.Type = olTo

       [COLOR=Green] ' Add the CC recipient(s) to the message.[/COLOR]
        Set objOutlookRecip = .Recipients.Add("Michael Syama")
        objOutlookRecip.Type = olCC

       [COLOR=Green] ' Add the BCC recipient(s) to the message.[/COLOR]
        Set objOutlookRecip = .Recipients.Add("Andrew Fler")
        objOutlookRecip.Type = olBCC

        [COLOR=Green]' Set the Subject, Body, and Importance of the message.[/COLOR]
        .Subject = "Training dates"

        .BodyFormat = olFormatHTML

        .HTMLBody = "<p><strong>Dear Colleague,</strong></p>" & _
                    "Welcome to our we updated training curricula as well as a much better training infrastructure." & _
                    "<p>Please see the attachment for the dates! </p>" & _
                    "<b><strong>With Kind regards,</strong></b>" & _
                    "<p> Name <br> </p>" & _
                    "<b> Below you can see the upcoming training schedule: </b><br> "

        Set db = CurrentDb
        Set qdf = db.QueryDefs("TrainingCalender Query")
        [COLOR=Red][B]qdf.Parameters("[van: dd-mm-yy]") = Format(InputBox("van: dd-mm-yy", "Start Date", Format(Date, "dd-mm-yy")), "\#mm\/dd\/yyyy\#")
        qdf.Parameters("[tot: dd-mm-yy]") = Format(InputBox("tot: dd-mm-yy", "End Date", Format(Date, "dd-mm-yy")), "\#mm\/dd\/yyyy\#")
        Set rst = qdf.OpenRecordset()[/B][/COLOR]

        Do Until rst.EOF
            .HTMLBody = .HTMLBody & " <TABLE BORDER=4 RULES=NONE FRAME=BOX> " & " " & _
                        " <br>  <p> " & " Training: " & rst![Title] & " " & " <br> " & _
                        " Start Time: " & rst![Start Time] & " " & " <br> " & " Place: " & rst![Location] & " </table> "
            rst.MoveNext
        Loop
        .Importance = olImportanceHigh  'High importance

        [COLOR=Green]' Add attachments to the message.[/COLOR]
        If Not IsMissing(AttachmentPath) Then _
            Set objOutlookAttach = .Attachments.Add("L:\Public\Access\PDF\Trainingen.pdf")

        [COLOR=Green]' Resolve each Recipient's name.[/COLOR]
        For Each objOutlookRecip In .Recipients
            objOutlookRecip.Resolve
        Next

        [COLOR=Green]' Should we display the message before sending?[/COLOR]
        If DisplayMsg Then 
            .Display
        Else
            .Save
            .Display
        End If
    End With
    Set objOutlook = Nothing
End Sub
Click to expand...


Thanks but when i insert the Start date i get the following error message:

Run-time error 3421:
Data type conversion error.

And the following line is selected in yellow:

qdf.Parameters("[van: dd-mm-yy]") = Format(InputBox("van: dd-mm-yy", "Start Date", Format(Date, "dd-mm-yy")), "\#mm\/dd\/yyyy\#")
 
Last edited:
Use a form to capture the dates instead of an Input Box. You can't trap the Cancel key on an Input Box.
 
vbaInet said:
You can't trap the Cancel key on an Input Box.
Click to expand...
Even if you use StrPtr? It is two or three lines of more code but still is possible I think? :confused:

Either way, using Form controls is ideal. This way the input entered can actually be self validated if the control's Format is set to Short date !
 
pr2-eugin said:
Even if you use StrPtr? It is two or three lines of more code but still is possible I think? :confused:
Click to expand...
You can go ahead and try ;)

You'll need a couple of APIs to do this, but then again what's the point on wasting your time on that when you can create your own!
 
I still get the error message :(

How can the code be fixed?
Or is there another possibility :confused:
 
Last edited:
Please upload a Stripped DB.

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)
 
pr2-eugin said:
Please upload a Stripped DB.

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)
Click to expand...


I cannot do that because the database form is connected to SharePoint is there another way to solve it?
 
You must log in or register to reply here.

Similar threads

E
SendKeys "%{S}" Not Working anymore?
Replies
7
Views
428
Gasman
Gasman
T
Attache to email from attachment Field
Replies
2
Views
224
theinviter
T
A
Solved Auto email error needed if attachment file doesn't exist or is named incorrectly
Replies
3
Views
508
theDBguy
theDBguy
K
DLookup to return different value on MoveNext
Replies
5
Views
346
ebs17
E
B
DoCmd.RunCommand acCmdPaste stopped working, think office 365 update
Replies
3
Views
402
bignose2
B

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom