Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2003
    Posts
    43

    Post Unanswered: Report source problem Thx for your help !!

    Hi all,


    I’m trying to create a report based on the results of an sql query. The sql query returns a series of result in a listbox called lstresults. What I would like to do is use this listbox as the source for a report . But I don’t know how to proceed ?

    My knowledge of access is still very limited so please bear with me. Here is the code I used for the sql query.

    Private Sub RefreshQuery()
    Dim SQL As String
    Dim SQLWhere As String

    SQL = "SELECT EmailName, OrganisationID, FirstName, LastName, EmailName, Title, Workphone, FaxNumber FROM Contacts Where Contacts!ContactID <> 0 "

    If Not Me.chkAuteur Then
    SQL = SQL & "And Contacts!EmailName like '*" & Me.txtRechAuteur & "*' "
    End If
    If Not Me.chkFamille Then
    SQL = SQL & "And Contacts!FirstName like '*" & Me.cmbRechFamille & "*' "
    End If
    If Not Me.chkResume Then
    SQL = SQL & "And Contacts!OrganisationID like '*" & Me.txtRechResume & "*' "
    End If
    If Not Me.chkTitre Then
    SQL = SQL & "And Contacts!Title like '*" & Me.txtRechTitre & "*' "
    End If
    If Not Me.chkType Then
    SQL = SQL & "And Contacts!LastName like '*" & Me.cmbRechType & "*' "
    End If

    SQLWhere = Trim(Right(SQL, Len(SQL) - InStr(SQL, "Where ") - Len("Where ") + 1))

    SQL = SQL & ";"

    Me.lblStats.Caption = DCount("*", "Contacts", SQLWhere) & " / " & DCount("*", "Contacts")
    Me.lstResults.RowSource = SQL
    Me.lstResults.Requery

    End Sub

    I then created a button with the onclick;

    DoCmd.OpenReport "Contacts", acViewNormal, , "[OrganisationID] = " & Me.lstResults

    But then I don’t know what to do next

    Thx in advance for your help

    Chiron

  2. #2
    Join Date
    Oct 2003
    Location
    São Paulo - Brazil
    Posts
    91
    Wich is the report data source ?
    My environment: Windows XP/ Access 2000 - Using Microsoft DAO 3.6 Library

  3. #3
    Join Date
    Nov 2003
    Posts
    43
    The listbox containing the query results ie lstResults

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    a report has a .RecordSource just like a form

    save your SQL somewhere Public (and in scope: the form will do if it will be open while the report runs) and set the .recordsource in the _open of the report.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Nov 2003
    Posts
    43
    Thx for your answer izyrider but I'm not sure how this will do it. But there again I'm just a novice.

    BTW the form will be open when the report is launched since the report is based on the results of the sql query listed in the listbox 'lstresults'.

    How do I set the .recordsource to the 'lstresults' ?

    Cheers !!

    Chiron

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Once again izy is right on the mark a full day ahead of me.



    izy is suggesting you declare and build your SQL string ina public module as opposed to your form. Instead of declaring:

    Private Sub RefreshQuery()
    Dim SQL As String

    PUt that ina public module instead by creating a new module, and adding:

    Global SQL As String

    Now when you call or set "SQL", it will retain it's value. So you can use the exact same query as the recordsource for as many objects as you want. Essentially "Global" means that the value will be available to ANY sub or object in the application.

    Now when you want to open the report, simply add:

    Me.RecordSource = SQL

    To the OnOpen event of the report.

  7. #7
    Join Date
    Nov 2003
    Posts
    43

    Wink

    Thx for your detailed explanation Teddy !! I really appreciate !!
    I will try that !!

    Cheers !

    Chiron

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    thanks Teddy for clarifying my jumbled post.

    the form will be open (says Chiron).
    i wonder if _Open of the report could use:
    me.recordsource = forms!nameOfTheForm.lstResults.RowSource

    i've not tried it, but they are both SQL strings so it ought to work.

    ...thus saving a module and a global.

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by izyrider
    thanks Teddy for clarifying my jumbled post.

    the form will be open (says Chiron).
    i wonder if _Open of the report could use:
    me.recordsource = forms!nameOfTheForm.lstResults.RowSource

    i've not tried it, but they are both SQL strings so it ought to work.

    ...thus saving a module and a global.

    izy
    Good point.

    I haven't ever done that either... but if it works I think that may be quite a bit cleaner.

  10. #10
    Join Date
    Nov 2003
    Posts
    43

    Talking

    Thank you !! Thank you !! Thank you !!
    Thx izy & Teddy !!

    It does work brilliant !!

    Chiron

  11. #11
    Join Date
    Nov 2003
    Posts
    43
    Well… I’m almost there !! I still have a slight problem with my report.

    I’ve set the _Open of the report to
    me.recordsource = forms!nameOfTheForm.lstResults.RowSource as you suggested

    so that my report is based on the result of my sql query where;

    Me.lstResults.RowSource = "SELECT EmailName, OrganisationID, TitleOfCourtesy, FirstName, LastName, EmailName, Title, Workphone, FaxNumber FROM Contacts;"

    However I would also like to have on my report all the records of another table (tbl_organisation) where the OrganisationID fields are the same. (as the lstresults).

    Ok I feel I’m not being very clear here.

    I have two tables ‘Organisation’ (pk:OrganisationID) and ‘Contacts’ linked through a one to many relationships on the ‘OrganisationID’ field.

    I have created a search form which returns information on my contacts in a listbox called lstresults.
    I use this information as a source for my report using the code you suggested.

    Up to here everything works fine. Now I would also like my report to show information about the organisation my contacts belong to. (ie information from the table ‘organisation’ where the ‘OrganisationID’ fields of the lstresults are = ). Any idea how I can achieve that. I’ve tried all sorts of ‘Join’ to no avail.

    Thanks again for your help !!

    Chiron

Posting Permissions

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