Home > Blockchain >  .FindFirst Not Working Properly in Access
.FindFirst Not Working Properly in Access

Time:04-30

I am creating a database for contact information for research participants. In it, I have a form with a command button that is supposed to save 1 of 2 possible reports as a pdf for each participant based on the value of one of the text fields on the form (which is linked to a table where the field is automatically calculated as 0 or 2). Basically if the value of this field is 0 then I want the "UnsignedLetter" report saved for that participant or if the value of this field is 2, I want the "SignedLetter" report saved.

I've got most of it working except when it comes to selecting the correct report to save. FindFirst seems to be the closest to being successful but it's not quite right. When I click the button for the code below, the participants coded as 0 get both versions of the report and not just the "UnsignedLetter" version. The same does not happen with the participants coded as 2. For example, on a recordset of 5 people with 3 coded as 2 and 2 coded as 0, I get 3 correct "SignedLetter" pdf's, 2 correct "UnsignedLetter" pdf's, and an additional 2 incorrect "SignedLetter" pdf's. This is the code I'm working with:

Private Sub SaveLetters_Click()
    Dim rs As DAO.Recordset
    Dim sFolder               As String
    Dim sFile                 As String
        
    On Error GoTo Error_Handler

    sFolder = Application.CurrentProject.Path & "\"
    
    Set rs = Me.RecordsetClone
    
    With rs
          
        .FindFirst "OncID = 2"
                
            Do While Not .EOF
                DoCmd.OpenReport "SignedLetter", acViewPreview, , "[ID]=" & ![ID], 
acHidden
                sFile = Nz(![UNumber], "") & "_signed" & ".pdf"
                sFile = sFolder & sFile
                DoCmd.OutputTo acOutputReport, "SignedLetter", acFormatPDF, sFile, , , , 
acExportQualityPrint
            
                DoCmd.Close acReport, "SignedLetter"
                .MoveNext
            Loop
        
    End With
    
    With rs
    
        .FindFirst "OncID = 0"
        
        Do While Not .EOF
            DoCmd.OpenReport "UnsignedLetter", acViewPreview, , "[ID]=" & ![ID], acHidden
            sFile = Nz(![UNumber], "") & "_unsigned" & ".pdf"
            sFile = sFolder & sFile
            DoCmd.OutputTo acOutputReport, "UnsignedLetter", acFormatPDF, sFile, , , , 
            acExportQualityPrint
            
            DoCmd.Close acReport, "UnsignedLetter"
            .MoveNext
           
        Loop
      End With
      
MsgBox "Letters Sent to File", vbOKOnly   vbInformation, "Task Completed"

    Application.FollowHyperlink sFolder

Error_Handler_Exit:
    On Error Resume Next
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Exit Sub

Error_Handler:
    If Err.Number <> 2501 Then
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: cmd_GenPDFs_Click" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly   vbCritical, "An Error has Occured!"
    End If
    Resume Error_Handler_Exit
End Sub

I've been searching for 2 days at this point and I can't find any solutions. I've tried doing the 2 With rs's separately, using 1 With rs as after the Then portion of an If .NoMatch statement for the other, changing the calculation so it results in text and not a number, and anything else I can think of or find online. I feel like I'm within throwing distance of tears at this point and would really appreciate any help you guys have.

CodePudding user response:

There is no need for FindFirst. Most of the code for both reports is the same. Use an If Then Else and a variable to select appropriate report and dynamically execute commands.

Dim sRpt As String
With Me.RecordsetClone
Do While Not .EOF
    If !OncID = 0 Then
        sRpt = "Unsigned"
    Else
        sRpt = "Signed"
    End If
    DoCmd.OpenReport sRpt & "Letter", acViewPreview, , "[ID]=" & ![ID], acHidden
    DoCmd.OutputTo acOutputReport, , acFormatPDF, _
        CurrentProject.Path & "\" & Nz(![UNumber], "") & "_" & sRpt & ".pdf"
    DoCmd.Close
    .MoveNext
Loop
End With

  • Related