Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2006
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open Report based on selection (2003)

    For some reason, I have had no success in being able to open a report and only view a user-defined portion of the data. I know there are several posts already about this, but here's my situation:

    I have a form "frmPersonnelListings" with an option group "fmListings" of option buttons on which a user may choose a listing (e.g. Fire Companies, Special Teams, Dept. Personnel) to view. There is a combo box "cboSpecify"; its Row Source is dependent on the choice in the Option Group. For instance, if you choose Fire Companies, the combo box shows the different fire companies to choose from. The form also has a command button "cmdPreview," using the following code (created by the Command Button Wizard) to open the report which corresponds to the choice in the option group:
    Dim stDocName As String
    Dim stLinkCriteria As String

    If fmListings = 1 Then
    stDocName = "rptFireCompanies"
    ElseIf fmListings = 2 Then
    stDocName = "rptSpecialTeams"
    ElseIf fmListings = 3 Then
    stDocName = "rptDeptPersonnel"
    End If

    DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

    Exit_cmdPreview_Click:
    Exit Sub

    Err_cmdPreview_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreview_Click
    End Sub

    Each report is based on a query that includes the field "OrgID."

    What do I need to change so that it will open the report to display only those records where the OrgID matches the OrgID of the choice from the Combo Box, or to display all records if there is no choice made?

    One note: the OrgID is a 9-digit number stored as Text. I have received some data mismatch errors in playing around with this- could this be the problem?

    Thanks!

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

    Re: Open Report based on selection (2003)

    Insert the following code above the line DoCmd.OpenReport ...:

    If Not IsNull(Me.cboSpecify) Then
    stLinkCriteria = "OrgID = " & Chr(34) & Me.cboSpecify & Chr(34)
    End If

  3. #3
    New Lounger
    Join Date
    Jun 2006
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Report based on selection (2003)

    Hans,

    Thanks, as always. It works great now. I think my problem was in there being too many " " in the line of code- I need to start using Chr(34).

    Tony

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

    Re: Open Report based on selection (2003)

    Glad it worked.

    Using Chr(34) looks a bit cumbersome, but it makes the code easier to maintain than double double quotes or single quotes.

    Also see Chr() function and quotes within strings (all).

Posting Permissions

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