Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2009
    Posts
    4

    Unanswered: Access DoCmd.OpenReport where issue

    I am trying to generate an output file for each record in a table referenced through a query.

    I cannot find the correct format for the where clause to print out only one record per file. Here is my code and the DoCmd.OpenReport "rpt", acViewNormal, , "strID = " & strID will not work using any examples I have found..

    any help would be appreciated.


    Private Sub Command27_Click()
    Dim FileName As String
    Dim strID As String
    Dim rpt As String
    Dim stWhereStr As String

    Set dabs = CurrentDb
    Set rs = dabs.OpenRecordset("repqry_PHO_Build")

    rpt = "Report_Export"

    If rs.RecordCount <> 0 Then
    Do While Not rs.EOF
    strID = rs("Cnt")
    MsgBox strID, vbInformation, rs.RecordCount()
    DoCmd.OpenReport "rpt", acViewNormal, , "strID = " & strID
    MsgBox rs.ID, vbInformation, rs.RecordCount()
    DoCmd.OutputTo acOutputReport, "Report_Export", "RichTextFormat (*.rtf)", "c:\Documents and Settings\sxwn\My Documents\Project_PHO\" & "PHO_Record" & Format$(Date, "mmddyyyy") & Format$(Time, "hhmmss") & rs.[ID] & ".doc", False, "", 0

    rs.MoveNext
    Loop
    End If
    DoCmd.Close acReport, "Report_Export"
    DoCmd.Close acQuery, "repqry_PHO_Build"
    End Sub

    Scott

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    "strID = " & strID may not work because it's name indicates it's a string, so try "strID = """ & strID & """" and see if that helps
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I may be missing something here (not for the first time) as I don't use DAO, but what is/does 'rs.ID' and 'rs.[ID]' mean ?

    I have not come across this recordset property/method before!

    If someone could enlighten me, then I will not make a fool of myself (on this issue) next time !!


    MTB

    ps. What is the error (and where if applicable) that you are experiencing ?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    does strID have a value.. have you checked in debug
    Im suspicious of...
    Code:
    strID = rs("Cnt")
    I'd of thought it should be
    Code:
    strID = rs!Cnt
    I'd agree if strID is a string column make sure its encapsualted with a " or ', your choice.

    For me I find it easier on the eye as
    "strID = " & chr$(34) & strID & chr$(34)
    I'd want to check that waht was being sent to the SQL engine is what I think it is. so Id actaully assign it to a variable before EXECuting it. or prove its correct by duplicating ans viewing the variable.

    incidentally I'd suggest you don't name a working variable the same as a column from a table or query, its easy to confuse yourself over which value is which. of course if you didn't prefix your column names with the datatype then you'd be part way there . To me an ID is a numeric, a Code or Type is alphabetic or alpha numeric. so there should be no need to prefix the datatype on the column name.

    make sure you follow a good debugging practise, prove the logic, line by line, make sure your foundations are right. its easy to get into a debugging loop messign around with abstract concepts when its thge basics that are screwed. its easy to look at some code that is so straightforward and think thats what it says (the eye and the brain will deceive you if you let 'em)
    Last edited by healdem; 01-19-09 at 09:02.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2009
    Posts
    4

    Still can't find correct syntax

    I have tried the "strID = """ & strID & """" and "strID = " & chr$(34) & strID & chr$(34)
    MsgBox strID, vbInformation, rs.RecordCount() pops up a message box with correct info so I know the strID is not blank and is being displayed as the correct value. The field in the table is numberic.
    This runs and creates the output files with the correct strID number in it as formatted but each file contains all records in the table.
    The original rs("ID") I have tried as rs!ID, rs.[ID] and all the above. have not gotten different results.
    If I comment out the DoCmd.OpenReport line the program runs and creates one file for each record labled correctly with the strID as the last three characters.
    PHO_Record01192009105824233.doc
    PHO_Record01192009105828234.doc
    PHO_Record01192009105833235.doc
    PHO_Record01192009105836236.doc
    PHO_Record01192009105842237.doc

    What else am I missing?

    This also pops up the save as window from printing which I really do not want. Should I be using set filter instead?

  6. #6
    Join Date
    Jan 2009
    Posts
    4
    I have put display messages all through the code and the results are always what is expected. The where clause just is not correctly tying to the strID.

    Private Sub Command27_Click()
    Dim FileName As String
    'Dim strID As String
    Dim rpt As String
    Dim stWhereStr As String

    Set dabs = CurrentDb
    Set rs = dabs.OpenRecordset("repqry_PHO_Build")

    rpt = "Report_Export"
    If rs.RecordCount <> 0 Then
    Do While Not rs.EOF
    MsgBox strID, vbInformation, "BeforeAssign"
    strID = rs!Ctr
    MsgBox strID, vbInformation, "AfterAssign"
    DoCmd.OpenReport rpt, acViewPreview, , "strID = " & Chr$(34) & strID & Chr$(34), acWindowNormal
    MsgBox strID, vbInformation, "AfterPreview"
    DoCmd.Close acReport, rpt
    'DoCmd.OutputTo acOutputReport, rpt, "RichTextFormat(*.rtf)", "c:\Documents and Settings\sxwn\My Documents\Project_PHO\" & "PHO_Record" & Format$(Date, "mmddyyyy") & Format$(Time, "hhmmss") & rs.[ID] & ".doc", False, "", 0

    rs.MoveNext
    MsgBox strID, vbInformation, "AfterNext"
    Loop
    MsgBox strID, vbInformation, "AfterLoop"
    End If
    DoCmd.Close acReport, "Report_Export"
    End Sub

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If this...
    DoCmd.OpenReport rpt, acViewPreview, , "strID = " & Chr$(34) & strID & Chr$(34), acWindowNormal
    and this...
    DoCmd.OpenReport rpt, acViewPreview, , "strID = """ & strID & """", acWindowNormal (for integer type matches)

    didn't work. Check what you dim strID as. Note: you commented out the Dim statement.

    I personally like to use:
    Dim strID as variant
    and then...
    DoCmd.OpenReport rpt, acViewPreview, , "strID = """ & strID & """", acWindowNormal

    Also make sure you close rs. I didn't see any type of rs.close, set rs = nothing type code when you're done (although you're using DAO and I use ADO and I can't recall closing recordsets in DAO). I would do this before you open the document and after you get all the variables you need from rs (or at the end of your looping.)
    Last edited by pkstormy; 01-19-09 at 15:45.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Jan 2009
    Posts
    4

    giving up

    Yes, that was an attempt to force error if not sting. Not commented out in my code. In further research I am finding several references that DoCmd.Outputto does not support any type of where clause being passed anyways so. I think I will drop back to messy cycling through temp table to get results.
    Thanks unless you see other option?
    Scott

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Could you open the report (based on the criteria) ...ie. docmd.openreport "......

    and then do a docmd.output......

    Note: rs.[ID] should be rs!ID

    Note: There is something in the code bank which does something similar to this (in one of the Report/exporting titles).
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Be interesting to see what the solution is. I use the where clause of openreport in conjunction with DAO a lot; I can assure you they work perfectly.

    That should mean that either you aren't expressing the criteria correctly or it's a data issue.

    All that message boxing going on there only looks at one small piece of the puzzle, the variable strID. I would assume there is nothing wrong with that.

    Things I would try:

    1. Try using a different variable name than the fieldname. Verify that strID is the full field name of the column you are trying to apply criteria to and not IDstr or TABLENAME.strID etc.

    2. Be sure the where clause is accurate... make a query manually using criteria in the column you're restricting by and look at the SQL WHERE clause... look for differences.

    3. Check that statements like SELECT * FROM repqry_PHO_Build WHERE strID = "pick a string id of yours" work as expected.

    4. Try MsgBoxing "strID = " & Chr$(34) & strID & Chr$(34). Look at the result carefully, even copy it into a where clause of a query and then run the query.

    5. Finally, can you be sure it's not working? Are you getting an error message? If not, then perhaps it is doing exactly what you have asked it to; you only think it's wrong!

    Just suggestions for you to check out...

    Good luck!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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