Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2014

    Unanswered: Setting a Parameter Query in VB code


    I currently have some code that runs a query and sends an email via outlook from that query. However I want to be able to set a parameter in the query, i have read online that you need to set the parameter in the Visual Basic code as well but i literally have no idea how and what i read online seem quite confusing!

    This is the code i have at the moment:

    Private Sub Command80_Click()
    Dim MyDb As DAO.Database
    Dim rsEmail As DAO.Recordset
    Dim sToName As String
    Dim sSubject As String
    Dim sMessageBody As String
    Set MyDb = CurrentDb()
    Set rsEmail = MyDb.OpenRecordset("qry_InductionLetter", dbOpenSnapshot)
    With rsEmail
            Do Until rsEmail.EOF
                If IsNull(.Fields(6)) = False Then
                    sToName = .Fields(6)
                    sSubject = "Invite to World of Work Induction Course:  " & .Fields(10)
                    sMessageBody = "Dear " & .Fields(0) & " " & .Fields(1) & "," & vbCrLf & vbCrLf & _
                    "Thanks," & .Fields(11) & vbCrLf
     DoCmd.SendObject acSendNoObject, , , _
                        sToName, , , sSubject, sMessageBody, False, False
                End If
    End With
    Set MyDb = Nothing
    Set rsEmail = Nothing
    End Sub
    This sends an induction letter to the candidtates in the qry_InductionLetter. At the moment the query brings back everyone was is eligible for the Induction even if they have already attended, so for my parameter i need the user to choose which course they want to attend as i dont want letters to be sent out to the same candidates each time.

    Thanks for any help in advanced!

  2. #2
    Join Date
    Apr 2014
    Provided Answers: 34
    You CAN set params in code. But it's easier to set the params in the query via forms.
    The query reads a form to get the params.
    'where [myFld] = '" & forms!frmMain!txtBox & "'"

    BUT if you want to go the code route...
    set qdf = currentdb.querydefs("qsQry")
    qdf.parameters("Enter Start Date") = #1/1/2014#

  3. #3
    Join Date
    Mar 2014
    I know how to set the parameters in the query but when i have done this, i get the following error message in the code;

    Run time error '3061'
    Too few parameters. Expected 1.

    Basically i want to somehow adapt my existing code to allow the query to have a parameter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts