Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    2Q: Report-Source accesible via Form howto? (2003)

    (Please make your links clickable. You can also insert images into a post. See <!help=19>Help 19<!/help>.)

    Hi everyone it's me again,

    well I tried asking this question in another forum before but I got no answer at all not even a single "I don't know" so I'm back to you guys...


    I have a report which creates badges from a member database, now there's two things I want to change:

    Problem 1

    When I want to print just certain people's badge (Let's say John Johnson and Mike Meyers) and not all of the records. I can do that by clicking on the report-source button an define criterias for the last and the first name, works like a charm and only the badges from those guys get printed off. (take a peek at the screenshot located at http://www.rvl-medizintechnik.de/screen.jpg and you see what I mean)

    Now I do want the database to be form-based (standalone) so I need something where the user can enter the last/first name (anything) of multiple people and open the report based on the users choice.

    That was the hard problem.
    ------------------
    Problem 2

    I know I saw some tutorial somewhere which was really complicated and didn't quite do what I want: I want to clean up the database so only have 1 report for badges. Right now I have like 6 reports or so, each one being the same report besides the recordsource (different tables). How to I let the user choose (let's say from a combo) the recordsource and then preview the report based on the users choice?

    Thanks alot guys I hope my explanations help you to find a solution.

    Dennis

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

    Re: 2Q: Report-Source accesible via Form howto? (2003)

    1. See if the reply to <post#=364117>post 364117</post#> helps.

    2. If you have 6 tables with the same structure, perhaps you should merge them into one table with an extra identifier. Then, you'd only have to filter the report, instead of changing the record source.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2Q: Report-Source accesible via Form howto? (2

    1. Hmm looks exactly like my problem, but using the ID as an identifier doesn't work for me, hence I need to user Last/First Name. To do that a drop down list, just as you implied as a second solution, would be just perfect. Maybe you could tell me a bit more how I'd use that with an onClick event to build a where statement.

    2. You are right I could do so, but I think I like it better that way, merging all tables together into one would be too much work in terms of redoing forms. And more important, when I have to repair the database in some way it's better to have those suckers seperate to see where an error happened. And actually it's just 4 tables with almost the same structure (Teachers, Delegates, Personnel, Administrative Staff).
    Can't imagine when there's something wrong with the 300 record Delegates database and I have to fish for 1 of the 30 Ad-Staff...

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

    Re: 2Q: Report-Source accesible via Form howto? (2

    About problem 2: you can pass the name of the record source to be used in the OpenArgs argument of DoCmd.OpenReport, and use it in the On Open event of the report to set the Record Source:

    On the form:

    Private Sub cmdReport_Click()
    Dim strSource As String
    Select Case ...
    Case 1
    strSource = "tblThis"
    Case 2
    strSource = "tblThat"
    Case Else
    strSource = "qryOther"
    End Select
    DoCmd.OpenReport "rptSomething", acViewPreview, , , , strSource
    End Sub

    On the report

    Private Sub Report_Open(Cancel As Integer)
    If Not IsNull(Me.OpenArgs) Then
    Me.RecordSource = Me.OpenArgs
    End If
    End Sub

    Note for others reading this thread: OpenArgs for reports was introduced in Access 2002. It is not available in Access 2000 and earlier.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2Q: Report-Source accesible via Form howto? (2

    Awesome, this did the trick perfectly. I'll add a school combo box in the form header so only those people from that school get chosen.

    Lil' Typo in the last line DoCmd instead of DoDmd (just for people wanting to copy the code)

    Btw there's another solution here: http://www.rogersaccesslibrary.com/downloa...ificReports.mdb
    It's using a criteria table - maybe I'll apply that one in some other case too.

    Hand you're helping me out alot thanks.

  6. #6
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2Q: Report-Source accesible via Form howto? (2

    huh as easy as that. Thank you hans it's implented already into the database I think I'm getting somewhere.

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

    Re: 2Q: Report-Source accesible via Form howto? (2

    Edit: corrected typo pointed out by Moontear

    Create a query based on tblDelegates.
    Add the DID, LastName and FirstName fields to the query grid.
    Set Sort to Ascending for LastName and FirstName.
    Save the query as qryDelegates.

    Put a list box (not a combo box) on a form, and set the following properties:
    Column Count: 3
    Column Widths: 0;2.5cm;2.5cm (you can vary the widths, but the first must be 0)
    Control Source: (leave blank)
    Row Source Type: Table/Query
    Row Source: qryDelegates
    Bound Column: 1
    Name: lboDelegates
    Multi Select: Simple or Extended.

    Simple means: user can select / deselect an item in the list by clicking it.
    Extended means: user can select items as in Windows Explorer, using click+Shift click to select a contiguous range, and Ctrl+click to (de)select individual items.

    Put a command button next to the list box.
    Caption: Badges.
    Name: cmdBadges
    On Click event procedure:

    Private Sub cmdBadges_Click()
    Dim strWhere As String
    Dim varItem As Variant

    If Me.lboDelegates.ItemsSelected.Count = 0 Then
    MsgBox "Please select one or more delegates.", vbExclamation
    Me.lboDelegates.SetFocus
    Exit Sub
    End If

    For Each varItem In Me.lboDelegates.ItemsSelected
    strWhere = strWhere & ", " & varItem
    Next varItem
    strWhere = "DID In (" & Mid(strWhere, 3) & ")"

    DoCmd.OpenReport "rptBadges", acViewPreview, , strWhere
    End Sub

    Here, rptBadges is the name of the badges report.

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

    Re: 2Q: Report-Source accesible via Form howto? (2

    Thanks for pointing out the typo (that's what you get for writing air code). I have corrected it.

  9. #9
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2Q: Report-Source accesible via Form howto? (2

    Let's see if you still follow this thread...

    Ok there is a problem with the code I just saw now:

    No matter on what name I click on the list, hitting the Preview Report button (your code) only gives out the record corresponding to the LINE of the List not the ID Number.

    Example: I click on Joe Black, hit the preview report button and John Johnson pops up in the report.
    Joe Black is the first record in the List sorted by last name.
    John Johnson is the first record in the table

    I come to the conclusion, that instead of using the DID (ID) for looking up people, your code somehow only uses the line of the list I'm in, which does not work - only if I would sort the list by DID, which I do not want.

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

    Re: 2Q: Report-Source accesible via Form howto? (2

    This isn't my day, I think. Try replacing

    strWhere = strWhere & ", " & varItem

    with

    strWhere = strWhere & ", " &Me.lboDelegates.ItemData(varItem)

  11. #11
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2Q: Report-Source accesible via Form howto? (2

    Perfect! I also understand what was wrong - always good to know what code means [img]/forums/images/smilies/wink.gif[/img]

    Thanks Hans

Posting Permissions

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