Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    parameter query and automation (access 2003)

    I am using a command button to open a word e-mail merge document. The merge is based on a parameter query. When I try to open the word document, I receive an error message that word is 'unable to open the datasource' If the parameter query is changed to a simple select query everything works fine.
    Finally, if I open the word document directly I am prompted for the parameter criteria and it works as well.

    Is there a way around the parameter issue

    nebbia

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: parameter query and automation (access 2003)

    I usually assemble the SQL string for the mail merge in code, incorporating the parameter value(s). So the final SQL is not a parameter query.

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: parameter query and automation (access 2003)

    I've tried the following SQL string without success

    SELECT Patients.First, Patients.Name, Visit.Visit, Demographics.[PatientE-mail], Format([tblAppointments].[ApptTime],"h:nn AM/PM") AS ApptTime, Format([tblAppointments].[ApptDate],"mm/dd/yyyy") AS ApptDate
    FROM (Demographics INNER JOIN Patients ON Demographics.PatientID = Patients.PatientID) INNER JOIN (Visit INNER JOIN tblAppointments ON Visit.VisitID = tblAppointments.VisitID) ON Patients.PatientID = Visit.PatientID
    WHERE (((Demographics.[PatientE-mail]) Is Not Null) AND ((tblAppointments.apptdate)=[date]))
    WITH OWNERACCESS OPTION;
    Where is my error?

    nebbia

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: parameter query and automation (access 2003)

    Is [date] a parameter here?

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: parameter query and automation (access 2003)

    yes that is the criteria that I would like to enter

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: parameter query and automation (access 2003)

    I'd use a form with a text box in which the user can enter the date. You'd then use code like this:
    <code>
    Dim strDate As String
    Dim strSQL As String
    strDate = Format(Me.txtDate, "mm/dd/yyyy")
    strSQL = "SELECT Patients.First, Patients.Name, Visit.Visit, Demographics.[PatientE-mail], " & _
    "Format([tblAppointments].[ApptTime],'h:nn AM/PM') AS ApptTime, " & _
    "Format([tblAppointments].[ApptDate],'mm/dd/yyyy') AS ApptDate " & _
    "FROM (Demographics INNER JOIN Patients ON Demographics.PatientID = Patients.PatientID) " & _
    "INNER JOIN (Visit INNER JOIN tblAppointments ON Visit.VisitID = tblAppointments.VisitID) " & _
    "ON Patients.PatientID = Visit.PatientID " & _
    "WHERE Demographics.[PatientE-mail] Is Not Null AND tblAppointments.apptdate=#" & strDate & "# " & _
    "WITH OWNERACCESS OPTION"
    </code>
    Use this strSQL in the OpenDataSource line in your Automation code.

  7. #7
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: parameter query and automation (access 2003)

    Apologies for the trouble I tried inserting your code as follows;

    Private Sub Text0_AfterUpdate()
    Dim wrdApp As Object
    Dim wrdDoc As Object
    Dim blnStartWord As Boolean
    Dim strSQL As String

    On Error Resume Next

    Set wrdApp = GetObject(, "Word.Application")
    If wrdApp Is Nothing Then
    Set wrdApp = CreateObject("Word.Application")
    If wrdApp Is Nothing Then
    MsgBox "Can't start Word.", vbExclamation
    Exit Sub
    Else
    blnStartWord = True
    End If
    End If

    On Error GoTo ErrHandler

    Dim strSQL As String
    strDate = Format(Me.txtDate, "mm/dd/yyyy")
    strSQL = "SELECT Patients.First, Patients.Name, Visit.Visit, Demographics.[PatientE-mail], " & _
    "Format([tblAppointments].[ApptTime],'h:nn AM/PM') AS ApptTime, " & _
    "Format([tblAppointments].[ApptDate],'mm/dd/yyyy') AS ApptDate " & _
    "FROM (Demographics INNER JOIN Patients ON Demographics.PatientID = Patients.PatientID) " & _
    "INNER JOIN (Visit INNER JOIN tblAppointments ON Visit.VisitID = tblAppointments.VisitID) " & _
    "ON Patients.PatientID = Visit.PatientID " & _
    "WHERE Demographics.[PatientE-mail] Is Not Null AND tblAppointments.apptdate=#" & strDate & "# " & _
    "WITH OWNERACCESS OPTION"
    'documents.open(file address")
    Set wrdDoc = wrdApp.Documents.open("C:My DocumentsAccesstemplatesappointmentReminder.doc")
    With wrdDoc.MailMerge
    .OpenDataSource _
    Name:="", _
    LinkToSource:=True, _
    Connection:="DSN=MS Access Database;DBQ=" & CurrentDb.Name, _
    SQLStatement:=strSQL, _
    SubType:=8 ' = wdMergeSubTypeWord2000
    .SuppressBlankLines = True
    End With

    wrdApp.Visible = True
    wrdApp.Activate

    ExitHandler:
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    If Not wrdDoc Is Nothing Then
    wrdDoc.close SaveChanges:=False
    End If
    If Not wrdApp Is Nothing And blnStartWord Then
    wrdApp.Quit SaveChanges:=Nothing
    End If
    Resume ExitHandler
    End Sub

    The error message "duplicate declaration" of strSQL As String

    Can the form be avoided or can I open the word document directly from access. As I said when the documented itself is opened, there is a prompt for the parameter criteria

    nebbia

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: parameter query and automation (access 2003)

    You now have two lines

    Dim strSQL As String

    in your code. Remove the second one (I usually put all declarations at the beginning of the procedure).

    Why would you want to avoid the form? You can do error checking, and it looks much better than the rather spartan parameter input dialog.

  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: parameter query and automation (access 2003)

    Not having much luck

    Now its hanging on strDate = Format(Me.txtDate, "mm/dd/yyyy") specifically .txtdate

    nebbia

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: parameter query and automation (access 2003)

    txtDate was just an example, meant to represent the name of the text box on the form.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: parameter query and automation (access 2003)

    If you prefer to be prompted for the date, change

    With wrdDoc.MailMerge
    .OpenDataSource _
    Name:="", _
    LinkToSource:=True, _
    Connection:="DSN=MS Access Database;DBQ=" & CurrentDb.Name, _
    SQLStatement:=strSQL, _
    SubType:=8 ' = wdMergeSubTypeWord2000
    .SuppressBlankLines = True
    End With

    to

    With wrdDoc.MailMerge
    .OpenDataSource _
    Name:=CurrentDb.Name, _
    LinkToSource:=True, _
    Connection:="QUERY qrySomething", _
    SubType:=wdMergeSubTypeWord2000
    .SuppressBlankLines = True
    End With

    where qrySomething is your parameter query

  12. #12
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: parameter query and automation (access 2003)

    i will try that. I don't know if you got my last post but the code will not compile at .txtDate (strDate = Format(Me.txtDate, "mm/dd/yyyy")). Do you see the problem?

    Thanks

    nebbia

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: parameter query and automation (access 2003)

    I also posted a reply about that - just above the other one.

  14. #14
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: parameter query and automation (access 2003)

    Sorry for the crossover.

    I've taken your suggestion and using a form to enter the date. The code compiles.but I now get message 'string is longer than 255 characters' which freezes the program.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: parameter query and automation (access 2003)

    Try the following:
    - Create a query with the following SQL:

    SELECT Patients.First, Patients.Name, Visit.Visit, Demographics.[PatientE-mail], Format([tblAppointments].[ApptTime],"h:nn AM/PM") AS ApptTime, Format([tblAppointments].[ApptDate],"mm/dd/yyyy") AS ApptDate
    FROM (Demographics INNER JOIN Patients ON Demographics.PatientID = Patients.PatientID) INNER JOIN (Visit INNER JOIN tblAppointments ON Visit.VisitID = tblAppointments.VisitID) ON Patients.PatientID = Visit.PatientID
    WHERE Demographics.[PatientE-mail] Is Not Null
    WITH OWNERACCESS OPTION;

    - Save it as qryMerge (or whatever you prefer)
    - Change the code that assigns strSQL to

    strSQL = "SELECT * FROM qryMerge WHERE ApptDate=" & Chr(34) & strDate & Chr(34)

    This moves the long SQL string to inside Access, and lets Word use a short SQL string.

Page 1 of 2 12 LastLast

Posting Permissions

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