Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Exclamation Unanswered: Reports Filter by combo box

    I have created reports which needs to be filter out based on user selected value from the combo box.

    Detail : I have form with option group and combo box... when user selects one of the option combo is visible which allows user to pick value and then click on "cmdPreview" to preview reports. I have wrote following code but nothing happens when user clicks on the "cmdPreivew"


    Please tell me what's wrong here.


    Private Sub cmdPreviewReports_Click()
    On Error GoTo printerror

    Dim strReport As String
    Dim strField As String
    Dim strWhere As String

    Case 5

    strReport = "rptMotherInfantScheduledFollowUpVisitByHomeVisito r"
    strField = "HomeVisitor"
    strWhere = "[HomeVisitor]=" & Me![cboHomeVisitor]
    DoCmd.OpenReport strReport, acViewPreview, , strWhere

    End Select

    printerror:
    If Err.Number = 2501 Then
    MsgBox "There Is No Expected Deliveries for Selected Date Range"
    Resume Next
    End If

    End Sub
    Skharva

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Your case statement is malformed. What are you evaluating?

    try using an if statement instead.

  3. #3
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183
    I'm using case statement because i wil be using option group
    Skharva

  4. #4
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183
    I'm attaching the db
    Attached Files Attached Files
    Skharva

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's coo, you need to form the case statement properly then

    Select Case yourControl
    Case etc
    Case something else
    End Select

  6. #6
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183
    I do have my case statement correct because for all the other option it wrks fine its only when user have to filter it out by combo box it does't work
    Skharva

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ok, could you post the whole sub then? There's a good chance something is slightly out of place.

  8. #8
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183
    Here is the whole sub and i have also attached the db

    Private Sub cmdPreviewReports_Click()
    'On Error GoTo printerror

    Dim strReport As String
    Dim strField As String
    Dim strWhere As String

    Select Case OptReports

    Case 5

    strReport = "rptMotherInfantScheduledFollowUpVisitByHomeVisito r"
    strField = "HomeVisitor"
    strWhere = "[HomeVisitor]=" & Me![cboHomeVisitor]
    DoCmd.OpenReport strReport, acViewPreview, , strWhere

    Case 6

    strReport = "rptMotherInfantScheduledFollowUpVisitByGeographic Region"
    strField = "GeographicRegion"
    'strWhere = "[GeographicRegion]=" & Me![cboRegion]
    'DoCmd.OpenReport strReport, acViewPreview, , strWhere


    If Not IsNull(Me.cboRegion) Then
    'strWhere = "[GeographicRegion]=" & Me![cboRegion]
    strWhere = strField & "=" & Me.cboRegion
    DoCmd.OpenReport strReport, acViewPreview, , strWhere
    End If

    End Select

    End sub
    Attached Files Attached Files
    Skharva

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You're missing quotes.

    strWhere = "[HomeVisitor]='" & Me![cboHomeVisitor] & "'"

  10. #10
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Thumbs up

    Thanks....that wrks now
    Skharva

  11. #11
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183
    One more thing if you can help me out with.

    I also need to create reports that will tell me number of mother screened, total # of Mother enrolled and total # of infant enrolled...by week....month or yearly.....Any suggestion or example how to do this ?

    Thanks alot
    Skharva

Posting Permissions

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