Solved

Error#3061 was generated by DAO

Posted on 2000-05-01
12
747 Views
Last Modified: 2008-02-01
I am now developing a workable Access2000 program, however, when it access a recordset, which is refers to a select query, it prompts out "
 "Error# 3061 was generated by DAO.
  Database too few parameters. Expected 1."

Did anyone knows what should I do? The query itself could be work property... The function I am writing for is to retrieve data from my tables, then send it to a mail.

My email address is manc@tce.com, are there any expert can help me through?

Regards,
MelodyGarden
0
Comment
Question by:MelodyGarden
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 4

Expert Comment

by:arcusd
ID: 2768224
coud u post the code that generates the error..
0
 
LVL 10

Expert Comment

by:paasky
ID: 2768879
Hello MelodyGarden,

I assume you have a parameter in your query and you don't assing a value to it before opening your query as recordset.

Here's an example of parameter query:
PARAMETERS [Enter ID:] Text;
SELECT *
FROM table1
WHERE f2=[Enter ID:]

And opening it as recordset:

Public Function OpenQuery(ID As String)
    Dim qry As QueryDef
    Dim rst As Recordset
   
    Set qry = CurrentDb.QueryDefs("Query1")
    qry.Parameters(0).Value = ID
    Set rst = qry.OpenRecordset
   
    If Not (rst.BOF And rst.EOF) Then
        rst.MoveFirst
        Debug.Print rst.Fields(0)
    End If
    Set qry = Nothing
    Set rst = Nothing
   
End Function

Hope this helps,
Paasky

0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2769075
You'll need to post the SQL of the query before we can really diagnose it, but I'd guess you have the query prompt for criteria, but opening it in DAO doesn't fire the prompt, so the parameter is missing.

Wes
0
 
LVL 2

Expert Comment

by:oui_li
ID: 2769427
That error is because there is an ERROR in your SQL. To debug easilly, copy your SQL and Paste it into a new query, Try to run the query. Access will show you where the error is.
0
 
LVL 5

Expert Comment

by:KMAN
ID: 2769476
I agree with oui_li.  The error usually arises when you have a field in your select statement that doesn't exist.
0
 

Author Comment

by:MelodyGarden
ID: 2771825
Public Function Tosendmail()
On Error GoTo Err_Tosendmail
'to generate mail according to user selected PO ID

    Dim gstrProgramPath As String
    gstrProgramPath = "C:\Program Files\InbetweenERP\InbetweenERP.mdb"
   
   
    DoCmd.SetWarnings False
    Dim myorder As Long, mymessage, mysign, myhdr, mycount As Long
    Dim mytoaddress As String, myccaddress As String, mybccaddress As String, mysubject As String
    Dim custmodelnbr As String
   
    myorder = Forms![frm_MailMenu]![cbxOrderListing]
    Set wrkJet = DBEngine.CreateWorkspace("", "admin", "", dbUseJet)
    Set Mydbs = wrkJet.OpenDatabase(gstrProgramPath)
    If myorder = Null Then
        MsgBox "Please select an order before sending mail!!", vbOKOnly, "Missing Order Number"
        Exit Function
    End If
    '-----------------------------------------------------------
    'this is the last session of the mail content, as mail footer
    mysign = vbCr & _
                "°°" &
                "°°°°" & vbCr & _
                vbCr
                       
    '----------------------------------------------------------------------------------
    'To Address Session
    Set rstSecondSelect = Mydbs.OpenRecordset("tbl_AddressTo", dbOpenDynaset)
    With rstSecondSelect
        .MoveLast
        mycount = rstSecondSelect.RecordCount
        .MoveFirst
        Do Until mycount = 0
            mytoaddress = !ToAddress & "; " & mytoaddress
        .MoveNext
        mycount = mycount - 1
        Loop
    End With
    mytoaddress = Left(mytoaddress, Len(Trim(mytoaddress)) - 1)
    mytoaddress = Chr(34) & mytoaddress & Chr(34)
    rstSecondSelect.Close
   
   '----------------------------------------------------------------
    'To avoid null database record in the ccaddress section
    Set rstThirdSelect = Mydbs.OpenRecordset("tbl_AddressCC", dbOpenDynaset)
    mycount = rstThirdSelect.RecordCount
    If mycount > 0 Then
         With rstThirdSelect
            .MoveLast
            mycount = rstThirdSelect.RecordCount
            .MoveFirst
            Do Until mycount = 0
                myccaddress = !CCAddress & ";" & myccaddress
                .MoveNext
                mycount = mycount - 1
            Loop
        End With
        myccaddress = Left(myccaddress, Len(Trim(myccaddress)) - 1)
        myccaddress = Chr(34) & myccaddress & Chr(34)
    End If
   rstThirdSelect.Close
   
   '------------------------------------------------------------
   'single line mail header
    Set rstFirstSelect = Mydbs.OpenRecordset("tbl_Order_InternalHdr", dbOpenDynaset)
    With rstFirstSelect
        .MoveLast
        .FindFirst "[ID]= " & myorder
        If .NoMatch Then
            MsgBox "No records found with " & myorder & "."
            Exit Function
        End If
       
        mysubject = "TTC Sales PO# " & ![CustPO] & "{" & ![Cust] & "}" & "  " & ![OrderDD]
        myhdr = vbCr & _
                        "«««««««««««««««««««««««««««««««««««««««««««««««««««««««««««««««««««««««««" & vbCr & _
                        "Ordering Company:" & vbTab & !Cust & vbCr & _
                        "«««««««««««««««««««««««««««««««««««««««««««««««««««««««««««««««««««««««««" & vbCr
        .MoveNext
   End With
   rstFirstSelect.Close
   '------------------------------------------------------------
 
   'multiple line mail content
    Set rstFourthSelect = Mydbs.OpenRecordset("qry_Sel_OrderDetail_Mailable", dbOpenDynaset)
    MsgBox 1
    With rstFourthSelect
       .MoveFirst
        Do Until .EOF
                custmodelnbr = ""
                If !CustModel <> "" Then
                     custmodelnbr = !CustModel
                Else
                     custmodelnbr = !ExtModel
                End If
                mymessage = "Item:" & vbTab & "(" & ![Line Number] & ")" * vbTab & custmodelnbr & _
                                vbTab & "ReqETD:" & vbTab & !ReqETD & vbCr & _
                             "Qty:" & vbTab & !OrderQty & vbTab & _
                                 vbTab & "Price:" & vbTab & !UnitCurr & Format(!UnitPrice, "0.00") & vbCr & _
                                 " --- " & vbCr & _
                             "Model:" & vbTab & !ExtModel & vbTab & "Color:" & !PColor & vbCr & _
                             "Draw#:" & vbTab & ![drawing] & vbTab & _
                             vbCr & vbCr & _
                mymessage = mymessage & mymessage
          .MoveNext
        Loop
    End With
   rstFourthSelect.Close
   '-----------------------------------------------------------------
   
   Mydbs.Close
   wrkJet.Close

    'send mail under close situation
    'DoCmd.SendObject , , acFormatRTF, mytoaddress, myccaddress, , mysubject, mymessage, False
    'send mail with open mail content
    DoCmd.SendObject , , acFormatRTF, mytoaddress, myccaddress, , mysubject, myhdr & mymessage & mysign, True
   
    'to update its mailsent indicator
  ''  DoCmd.RunSQL "UPDATE tbl_Order SET tbl_Order.MailSent = -1 WHERE (((tbl_Order.ID)=[Forms]![frm_MailMenu]![cbxOrderListing]));"
    DoCmd.SetWarnings True


Exit_Tosendmail:
    Exit Function

Err_Tosendmail:
    Msg = "Error # " & Str(Err.Number) & " was generated by " _
            & Err.Source & Chr(13) & Err.Description
    MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
    Resume Exit_Tosendmail
End Function




Error Message halted at:
Set rstFourthSelect = Mydbs.OpenRecordset("qry_Sel_OrderDetail_Mailable", dbOpenDynaset)
Might be you might wanted to have a full version for actual testing, all the fields are present and the SQL itself operates well....
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 4

Expert Comment

by:wesleystewart
ID: 2773191
Does this query:

qry_Sel_OrderDetail_Mailable

Have any criteria specified when you view it in the QBE grid (design view)?

Does it reference any forms that must be open to provide criteria?  Does it have any criteria statements with square brackets around them like [Enter Sales Region]?

Wes
0
 

Author Comment

by:MelodyGarden
ID: 2776147
Hi the problem hits when it touch the recordset stFourthSelect, the query qry_Sel_OrderDetail_Mailable itself runs perfect well. Do anyboby wanted to have a look at the problem, please don't hestiate to write to me at manc@tce.com, all experts are welcome..
0
 

Author Comment

by:MelodyGarden
ID: 2815767
Passky,

Please give me post something to the site, because I accept your answer.
Thanks.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2816009
Hi MelodyGarden:

Here's the mail I sent you:

Just checked your database and here are my suggestions:

1. Create a copy of qry_Sel_OrderDetail_Mailable and define a parameter for it:

Query: qry_Sel_OrderDetail_Mailable2

PARAMETERS Param_ID Long;
SELECT tbl_Order_InternalDet.ID, tbl_Order_InternalDet.[Line Number],
tbl_Order_InternalDet.PO, tbl_Order_InternalDet.CustModel,
tbl_Order_InternalDet.Market, tbl_Order_InternalDet.drawing,
tbl_Order_InternalDet.ExtModel, tbl_Order_InternalDet.PColor,
tbl_Order_InternalDet.PVersion, tbl_Order_InternalDet.PBrand,
tbl_Order_InternalDet.OrderQty, tbl_Order_InternalDet.UnitCurr,
tbl_Order_InternalDet.UnitPrice, tbl_Order_InternalDet.ReqETD,
tbl_Order_InternalDet.[ReqDD Rks], tbl_Order_InternalDet.ProdETD,
tbl_Order_InternalDet.ExeBy, tbl_Order_InternalDet.GenRemarks
FROM tbl_Order_InternalDet
WHERE (((tbl_Order_InternalDet.ID)=[Param_ID]));


Then in your code assign value to this parameter and open the query
recordset like this

Mydbs.QueryDefs("qry_Sel_OrderDetail_Mailable2").Parameters(0) = _
Forms![frm_MailMenu]![cbxOrderListing]

Set rstFourthSelect = _
Mydbs.QueryDefs("qry_Sel_OrderDetail_Mailable2").OpenRecordset

Also you should have maximum error trapping level on when you develop your
database so it's much easier to find all problems which are encountered. Add
On Error handler when you the code is working okay. To change Error trapping
settings go

Tools | Options -> General Tab -> ErrorTrapping=Break on all errors

I also noticed a typo (there was * instead of &) which caused an error right after above code lines:
should be like this:

mymessage = "Item:" & vbTab & "(" & ![Line Number] & ")" &
             vbTab & custmodelnbr & _
             vbTab & "ReqETD:" & vbTab & !ReqETD & vbCr & _
             "Qty:" & vbTab & !OrderQty & vbTab & _
             vbTab & "Price:" & vbTab & !UnitCurr & _
             Format(!UnitPrice, "0.00") & vbCr & _
             " --- " & vbCr & _
             "Model:" & vbTab & !ExtModel & vbTab & "Color:" & _
             !PColor & vbCr & _
             "Draw#:" & vbTab & ![drawing] & vbTab & _
             vbCr & vbCr

Regards
Paasky
0
 

Author Comment

by:MelodyGarden
ID: 2831590
I accept Paasky's answer, please.
0
 
LVL 10

Accepted Solution

by:
paasky earned 80 total points
ID: 2831984
Happy help you MelodyGarden. Above comment is my 'answer'.

With kindly regards,
Paasky
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now