Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    snsmith is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    23

    Question Error with OpenRecordset


    I've been programming in Access for a few years but I am very new to programming in VBA. I've found some videos and such online to get most of what I need completed. I'm currently working on a mass send email code to send out specific details to each person that shows up on a predefined query. I've already got the form filter set based on what I'm trying to send, but the openrecordset option is killing me and I don't know how to fix it.

    First off I do have spaces in my column names which were great in the beginning of Access but seem to be causing me grief in VBA. I have multiple columns of data to pull but no matter how I set this line I still get errors as "Too few parameters. Expected 12". If someone can help guide me I would be very appreciative.

    Set rs = db.OpenRecordset("SELECT [myUFL Master Project], [Check Number], [Date of Check], [Total Amt of Check], [Department ID], [Deposit ID], [Date of Deposit], [Payment is Split], [Is Backup Available], [Payee], [PI Name], [Check Notification contact Email] " & _
    " FROM OCRChecksReceivedDeptNotification")


    Thanks!
    Sandra

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You get this error a lot if you use crosstab queries but you can also get it with select queries. What it generally means is you are referencing an input value in your criteria from an external source (i.e. [forms]![formname]![fieldname]) and MS access doesn't know how to interpret what the value should be. You can *usually* resolve this by going to your query design and opening up the PARAMETERS window and typing in the variable name and defining the data type. This does not always solve the problem though and the way I've gotten around this is to dynamically build my SQL statement at run time rather than setting up the base query with a bunch of criteria/formulas.

  3. #3
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Its often easier to figure out when you do your select clause as a variable.
    You can add a debug.print to see what it resolves to and if it is syntactically correct.

    Code:
    Dim strSql as string
    
    strSql = "SELECT [myUFL Master Project], [Check Number], [Date of Check], [Total Amt of Check], [Department ID], [Deposit ID], [Date of Deposit],  & _
    "[Payment is Split], [Is Backup Available], [Payee], [PI Name], [Check Notification contact Email]  FROM OCRChecksReceivedDeptNotification"
    
    debug.print strSql
    
    Set rs = db.OpenRecordset(strSql)

  4. #4
    snsmith is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    23
    Thanks for responding so quickly.

    The base query OCRChecksReceivedDeptNotification does have an input value that is selected from the home page of reports and forms. I took it off and tried to add SQL to set the filter but now it's telling me access can't find the field and that is the correct field. If it helps, here is my full code and the bolded red is giving me the error.
    Private Sub Dept_Deposit_Notification_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String

    Dim emailTo As String
    Dim emailCC As String
    Dim emailSubject As String
    Dim emailText As String

    Dim outApp As Outlook.Application
    Dim outMail As Outlook.MailItem
    Dim outStarted As Boolean

    On Error Resume Next
    Set outApp = GetObject(, "Outlook.application")
    On Error GoTo 0
    If outApp Is Nothing Then
    Set outApp = CreateObject("Outlook.application")
    outStarted = True
    End If


    Set db = CurrentDb
    strSQL = [OCRChecksReceivedDeptNotification]![Deposit ID] = [Forms]![Choose Forms, Reports or Queries]![Deposit ID]
    Set rs = db.OpenRecordset("SELECT myUFL_Master_Project, Check_Number, Date_of_Check, Total_Amt_of_Check, Department_ID, Deposit_ID, Date_of_Deposit, Payment_is_Split, Is_Backup_Available, Payee, PI_Name, Check_Notification_contact_Email " & _
    " FROM OCRChecksReceivedDeptNotification")

    Do Until rs.EOF

    emailTo = rs.Fields("Check Notification Contact Email").Value

    emailSubject = emailSubject = "Funds received for " & rs.Fields("PI Name").Value & "'s project " & rs.Fields("myUFL Master Project").Value

    emailText = "Hello, " & vbCrLf
    emailText = "OCR has recently completed a deposit for one of your projects. Additional information can be found below or on the deposit log which will be updated in the next few business days. " & vbCrLf
    emailText = "myUFL Project Number: " & rs.Fields("myUFL Project Number").Value & vbCrLf
    emailText = "Check Number: " & rs.Fields("Check Number").Value & vbCrLf
    emailText = "Date of Check: " & rs.Fields("Date of Check").Value & vbCrLf
    emailText = "Total Amount of check: " & rs.Fields("Total Amt of Check").Value & vbCrLf
    emailText = "IDC Charged to Projet: " & rs.Fields("IDC Charged to Project").Value & vbCrLf
    emailText = "Department ID: " & rs.Fields("Department ID").Value & vbCrLf
    emailText = "Date of Deposit: " & rs.Fields("Date of Deposit").Value & vbCrLf
    emailText = "Payment is split: " & rs.Fields("Payment is split").Value & vbCrLf
    emailText = "Is Backup Available: " & rs.Fields("Is Backup Available").Value & vbCrLf
    emailText = "Payee: " & rs.Fields("Payee").Value & vbCrLf
    emailText = "Deposit ID: " & rs.Fields("Deposit ID").Value & vbCrLf & vbCrLf

    emailText = " Thank you," & vbCrLf
    emailText = " OCR"


    Set outMail = outApp.CreateItem(olMailItem)
    outMail.To = emailTo
    outMail.CC = emailCC
    outMail.Subject = emailSubject
    outMail.Body = emailText
    outMail.Send
    outMail.Display

    rs.MoveNext
    Loop


    rs.Close
    Set rs = Nothing
    Set db = Nothing

    If outStarted Then
    outApp.Quit
    End If

    Set outMail = Nothing
    Set outApp = Nothing


    End Sub

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    that formula should be strSQL = [value1] & " = " & [value 2]

    if you are attempting to build a criteria clause.

    But you're not really doing anything with strSQL so it's hard to tell

  6. #6
    snsmith is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    23
    Quote Originally Posted by moke123 View Post
    Its often easier to figure out when you do your select clause as a variable.
    You can add a debug.print to see what it resolves to and if it is syntactically correct.

    Code:
    Dim strSql as string
    
    strSql = "SELECT [myUFL Master Project], [Check Number], [Date of Check], [Total Amt of Check], [Department ID], [Deposit ID], [Date of Deposit],  & _
    "[Payment is Split], [Is Backup Available], [Payee], [PI Name], [Check Notification contact Email]  FROM OCRChecksReceivedDeptNotification"
    
    debug.print strSql
    
    Set rs = db.OpenRecordset(strSql)

    *****************
    This was VERY HELPFUL. I've gotten a lot further than I had been.

    the email was generated, the to and from were generated but the subject line and text were didn't populate. Surprisingly, to me, only the last line posted in the email.

    Private Sub Dept_Deposit_Notification_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String

    Dim emailTo As String
    Dim emailCC As String
    Dim emailSubject As String
    Dim emailText As String

    Dim outApp As Outlook.Application
    Dim outMail As Outlook.MailItem
    Dim outStarted As Boolean

    On Error Resume Next
    Set outApp = GetObject(, "Outlook.application")
    On Error GoTo 0
    If outApp Is Nothing Then
    Set outApp = CreateObject("Outlook.application")
    outStarted = True
    End If


    Set db = CurrentDb
    strSQL = "SELECT [myUFL Master Project], [Check Number], [Date of Check], [Total Amt of Check], [Department ID], [Deposit ID], [Date of Deposit], [IDC Charged to Project], [Payment is Split], [Is Backup Available], [Payee], [PI Name], [Check Notification contact Email] FROM OCRChecksReceivedDeptNotification"

    Debug.Print strSQL
    Set rs = db.OpenRecordset(strSQL)

    Do Until rs.EOF

    emailTo = rs.Fields("[Check Notification Contact Email]").Value
    emailCC = "OCR-Financials@ahc.ufl.edu"

    emailSubject = emailSubject = "Funds received for " & rs.Fields("[PI Name]").Value & "'s project " & rs.Fields("[myUFL Master Project]").Value

    emailText = "Hello, " & vbCrLf
    emailText = "OCR has recently completed a deposit for one of your projects. Additional information can be found below or on the deposit log which will be updated in the next few business days. " & vbCrLf
    emailText = "myUFL Project Number: " & rs.Fields("myUFL Master Project").Value & vbCrLf
    emailText = "Check Number: " & rs.Fields("Check Number").Value & vbCrLf
    emailText = "Date of Check: " & rs.Fields("Date of Check").Value & vbCrLf
    emailText = "Total Amount of check: " & rs.Fields("Total Amt of Check").Value & vbCrLf
    emailText = "IDC Charged to Project: " & rs.Fields("IDC Charged to Project").Value & vbCrLf
    emailText = "Department ID: " & rs.Fields("Department ID").Value & vbCrLf
    emailText = "Date of Deposit: " & rs.Fields("Date of Deposit").Value & vbCrLf
    emailText = "Payment is split: " & rs.Fields("Payment is split").Value & vbCrLf
    emailText = "Is Backup Available: " & rs.Fields("Is Backup Available").Value & vbCrLf
    emailText = "Payee: " & rs.Fields("Payee").Value & vbCrLf
    emailText = "Deposit ID: " & rs.Fields("Deposit ID").Value & vbCrLf & vbCrLf

    emailText = " Thank you," & vbCrLf
    emailText = " OCR"


    Set outMail = outApp.CreateItem(olMailItem)
    outMail.To = emailTo
    outMail.CC = emailCC
    outMail.Subject = emailSubject
    outMail.Body = emailText
    outMail.Display

    rs.MoveNext
    Loop


    rs.Close
    Set rs = Nothing
    Set db = Nothing

    If outStarted Then
    outApp.Quit
    End If

    Set outMail = Nothing
    Set outApp = Nothing


    End Sub

  7. #7
    snsmith is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    23
    also, how can I limit the email to just those where the [Forms]![Choose Forms, Reports or Queries]![Deposit ID] equals the Deposit ID of the records? if I put the filter on the query i get the too few parameters expected.

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    strSQL = "SELECT [myUFL Master Project], [Check Number], [Date of Check], [Total Amt of Check], [Department ID], [Deposit ID], [Date of Deposit], [IDC Charged to Project], [Payment is Split], [Is Backup Available], [Payee], [PI Name], [Check Notification contact Email] FROM OCRChecksReceivedDeptNotification WHERE [Depost ID] = " & forms!formname![deposit id]

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Here's a point that may help in the future. One reason this error arises is spelling of object names. Another is that the side of Access that will process your vba sql string cannot resolve a form control reference when it is coming from the vba side. How to fix? One of
    - put form control reference in query object (stored query)
    - modify stored query qdef property at run time
    - define parameters property in stored query (as was mentioned, I think)
    - define qdef parameters of sql statement at run time (not of a stored query)
    - in vba assign form reference to a variable and pass the variable, not the form control reference

    The latter being the most flexible since it can be dynamic vba, plus it's simpler than creating and assigning qdef properties. At least it has worked for me in the past.
    EDIT
    @snsmith - please use code tags and indentation for code. Without them your code is too hard to read.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I would suggest to following approach that always worked for me. Put all your criteria in your query (OCRChecks....) and save it. Then simply modify your code like this:
    Code:
    Private Sub Dept_Deposit_Notification_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    
    Dim emailTo As String
    Dim emailCC As String
    Dim emailSubject As String
    Dim emailText As String
    
    Dim outApp As Outlook.Application
    Dim outMail As Outlook.MailItem
    Dim outStarted As Boolean
    
    'vlad
    Dim qdf as Dao.QueryDef
    Dim prm as Parameter
    'vlad
    On Error Resume Next
    Set outApp = GetObject(, "Outlook.application")
    On Error GoTo 0
    If outApp Is Nothing Then
    Set outApp = CreateObject("Outlook.application")
    outStarted = True
    End If
    
    
    Set db = CurrentDb
    'strSQL = "SELECT [myUFL Master Project], [Check Number], [Date of Check], [Total Amt of Check], [Department ID], [Deposit ID], [Date of Deposit], [IDC Charged to Project], [Payment is Split], [Is Backup Available], [Payee], [PI Name], [Check Notification contact Email] FROM OCRChecksReceivedDeptNotification"
    
    Debug.Print strSQL
    
    set qdf=db.QueryDefs("OCRChecksReceivedDeptNotification")
    For each prm in qdf.parameters
         prm.value=Eval(prm.name)
    Next prm
    
    'Set rs = db.OpenRecordset(strSQL)
    Set rs=qdf.openrecordset
    
    Do Until rs.EOF
    Cheers,
    Vlad

  11. #11
    snsmith is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    23
    Hi Vlad,
    that definitely helped with setting the filter, thank you. I still cannot figure out why it only fills in the emailTo, Email CC and Final line of emailText, but skips all the others?

    Private Sub Dept_Deposit_Notification_Click()

    Dim db As Dao.Database
    Dim rs As Dao.Recordset
    Dim strSQL As String



    Dim emailTo As String
    Dim emailCC As String
    Dim emailSubject As String
    Dim emailText As String


    Dim outApp As Outlook.Application
    Dim outMail As Outlook.MailItem
    Dim outStarted As Boolean


    Dim qdf As Dao.QueryDef
    Dim prm As Parameter


    On Error Resume Next
    Set outApp = GetObject(, "Outlook.application")
    On Error GoTo 0

    If outApp Is Nothing Then
    Set outApp = CreateObject("Outlook.application")
    outStarted = True
    End If


    'get data
    Set db = CurrentDb

    Debug.Print strSQL

    Set qdf = db.QueryDefs("OCRChecksReceivedDeptNotification")
    For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
    Next prm

    'send email
    Do Until rs.EOF
    emailTo = rs.Fields("[Check Notification Contact Email]").Value
    emailCC = "OCR-Financials@ahc.ufl.edu"

    emailSubject = emailSubject = "Funds received for " & rs.Fields("[PI Name]").Value & "'s project " & rs.Fields("[myUFL Master Project]").Value

    emailText = "Hello, " & vbCrLf
    emailText = "OCR has recently completed a deposit for one of your projects. Additional information can be found below or on the deposit log which will be updated in the next few business days. " & vbCrLf
    emailText = "myUFL Project Number: " & rs.Fields("myUFL Master Project").Value & vbCrLf
    emailText = "Check Number: " & rs.Fields("Check Number").Value & vbCrLf
    emailText = "Date of Check: " & rs.Fields("Date of Check").Value & vbCrLf
    emailText = "Total Amount of check: " & rs.Fields("Total Amt of Check").Value & vbCrLf
    emailText = "IDC Charged to Project: " & rs.Fields("IDC Charged to Project").Value & vbCrLf
    emailText = "Department ID: " & rs.Fields("Department ID").Value & vbCrLf
    emailText = "Date of Deposit: " & rs.Fields("Date of Deposit").Value & vbCrLf
    emailText = "Payment is split: " & rs.Fields("Payment is split").Value & vbCrLf
    emailText = "Is Backup Available: " & rs.Fields("Is Backup Available").Value & vbCrLf
    emailText = "Payee: " & rs.Fields("Payee").Value & vbCrLf
    emailText = "Deposit ID: " & rs.Fields("Deposit ID").Value & vbCrLf

    emailText = " Thank you," & vbCrLf
    emailText = " OCR"


    Set outMail = outApp.CreateItem(olMailItem)
    outMail.To = emailTo
    outMail.CC = emailCC
    outMail.Subject = emailSubject
    outMail.Body = emailText
    outMail.Display

    rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    Set db = Nothing
    If outStarted Then
    outApp.Quit
    End If
    Set outMail = Nothing
    Set outApp = Nothing

    End Sub


    Thanks,
    Sandra

  12. #12
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    please use bookends on your code, makes it easier on the viewers.

  13. #13
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    this is skipping the body of your message because you're overwriting it each time you use EMAILTEXT = <blah>

    you might be better off doing something like

    Code:
    dim smsg as string
    
    smsg = smsg &  "Hello, " & vbCrLf
    smsg = smsg &  "OCR has recently completed a deposit for one of your projects. Additional information can be found below or on the deposit log which will be updated in the next few business days. " & vbCrLf
    smsg = smsg &  "myUFL Project Number: " & rs.Fields("myUFL Master Project").Value & vbCrLf
    smsg = smsg &  "Check Number: " & rs.Fields("Check Number").Value & vbCrLf
    smsg = smsg &   "Date of Check: " & rs.Fields("Date of Check").Value & vbCrLf
    smsg = smsg &   "Total Amount of check: " & rs.Fields("Total Amt of Check").Value & vbCrLf
    smsg = smsg &   "IDC Charged to Project: " & rs.Fields("IDC Charged to Project").Value & vbCrLf
    smsg = smsg &  "Department ID: " & rs.Fields("Department ID").Value & vbCrLf
    smsg = smsg &   "Date of Deposit: " & rs.Fields("Date of Deposit").Value & vbCrLf
    smsg = smsg &   "Payment is split: " & rs.Fields("Payment is split").Value & vbCrLf
    smsg = smsg &   "Is Backup Available: " & rs.Fields("Is Backup Available").Value & vbCrLf
    smsg = smsg &   "Payee: " & rs.Fields("Payee").Value & vbCrLf
    smsg = smsg &  "Deposit ID: " & rs.Fields("Deposit ID").Value & vbCrLf
    
    emailtext = smsg
    this way you can also debug.print your entire message at one shot to error check it.

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by rpeare View Post
    please use bookends on your code, makes it easier on the viewers.
    Maybe it will happen now that you've asked too.

  15. #15
    snsmith is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    23
    This worked! Thank you all so much!

    Quote Originally Posted by rpeare View Post
    this is skipping the body of your message because you're overwriting it each time you use EMAILTEXT = <blah>

    you might be better off doing something like

    Code:
    dim smsg as string
    
    smsg = smsg &  "Hello, " & vbCrLf
    smsg = smsg &  "OCR has recently completed a deposit for one of your projects. Additional information can be found below or on the deposit log which will be updated in the next few business days. " & vbCrLf
    smsg = smsg &  "myUFL Project Number: " & rs.Fields("myUFL Master Project").Value & vbCrLf
    smsg = smsg &  "Check Number: " & rs.Fields("Check Number").Value & vbCrLf
    smsg = smsg &   "Date of Check: " & rs.Fields("Date of Check").Value & vbCrLf
    smsg = smsg &   "Total Amount of check: " & rs.Fields("Total Amt of Check").Value & vbCrLf
    smsg = smsg &   "IDC Charged to Project: " & rs.Fields("IDC Charged to Project").Value & vbCrLf
    smsg = smsg &  "Department ID: " & rs.Fields("Department ID").Value & vbCrLf
    smsg = smsg &   "Date of Deposit: " & rs.Fields("Date of Deposit").Value & vbCrLf
    smsg = smsg &   "Payment is split: " & rs.Fields("Payment is split").Value & vbCrLf
    smsg = smsg &   "Is Backup Available: " & rs.Fields("Is Backup Available").Value & vbCrLf
    smsg = smsg &   "Payee: " & rs.Fields("Payee").Value & vbCrLf
    smsg = smsg &  "Deposit ID: " & rs.Fields("Deposit ID").Value & vbCrLf
    
    emailtext = smsg
    this way you can also debug.print your entire message at one shot to error check it.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Too few parameters error on CurrentDb.OpenRecordset
    By hfreedman1957 in forum Programming
    Replies: 4
    Last Post: 05-12-2017, 08:45 PM
  2. Replies: 3
    Last Post: 02-26-2016, 12:34 PM
  3. dbSeeChanges does not fix openrecordset error
    By lringstad in forum Access
    Replies: 1
    Last Post: 12-03-2013, 03:33 PM
  4. Replies: 7
    Last Post: 07-24-2013, 02:01 PM
  5. db.OpenRecordset error
    By jscriptor09 in forum Programming
    Replies: 4
    Last Post: 01-27-2012, 12:17 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums