Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Reports and lists (Access 2000)

    I need to be able to open a report from a list. The goal being that the user locates the name of the individual on a list located on a form. On click or double click, the report opens for that individual. Presently, the report is controlled by a query and the query asks for the individual's name. This however is not user friendly esp. if the name is typed in incorrectly. I have tried using the similar code as that used to open a form (bookmarked) from a name on a list, but alas have found this doesn't work with a report. Any help would be appreciated!!

    Thanks,
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reports and lists (Access 2000)

    What exactly do you mean by a "list"? Are you talking about a form with a listbox on it, a combobox, or something else? In any case, you would need to change the query your report is based on so it is no longer a parameter query. Then in the code you execute when the user makes a selection, you would create a string that is like the WHERE part of a SQL statement but without the word "where", something like "LastName = 'Jones'". In the line you use to open the report, you would include that string like this:

    <pre>strWhere = "LastName = '" & Me.lstNames & "'"

    DoCmd.OpenReport "MyReport", acPreview, , strWhere</pre>


    That would open a report called "MyReport" in preview mode and would only display the records where the LastName matched the selection in the list box. This assumes that there is a LastName column in the listbox and that it is the bound column for the list. The single quotes are needed in the where string if the value is text. If the value is a number, then you don't need the single quotes.
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reports and lists (Access 2000)

    Hi Charlotte! Thanks for the post. I tried the code you suggested but am still running into an issue. First, yes it was a list box on a form that I am referring to. The form is bound to tblClientDemographics and the list is bound to control source "ClientName". I entered the code as follows:
    strWhere = "ClientName = '" & Me.List2 & "'"
    DoCmd.OpenReport "rptClientInitial-CopyInvoice", acPreview, , strWhere
    My problem is that the report still opens showing all of the clients in the table.

    In addition to needing the report to open by client name, I need the report to open by a From and To date range. I have two input boxes on the form, one labeled "startdate" and one labeled "enddate". I tried taking the code out for the dates to be sure that the report first opened by cient name but that didn't help. The code I'm using for the dates is as follows:

    Dim ReportName
    ReportName = "rptClientInitial-CopyInvoice"
    DoCmd.OpenReport _
    ReportName:=ReportName, _
    view:=acViewPreview, _
    WhereCondition:="[Date] BETWEEN #" & _
    Me.STARTDATE & "# AND #" & _
    Me.ENDDATE & "#"

    This code works fine.

    Any suggestions would be greatly appreciated!!

    Thanks again,
    Leesah

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

    Re: Reports and lists (Access 2000)

    Try the following: position the insertion point (the blinking cursor) after the line strWhere = " ... "
    Press Enter to create a new line, and insert the instruction Debug.Print strWhere
    Switch to Access, open the form, and preview the report.
    Switch back to the Visual Basic Editor, and activate the Immediate window (Ctrl+G).
    You should see the Where condition used in opening the report. Do you see anything unusual?
    If you wish, you can copy the string from the immediate window and paste it into a reply

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reports and lists (Access 2000)

    Hi Hans,

    Thanks for the help. Did what you suggested and this is the error message that comes up before I get to the "immediate window"

    "Syntax error (missing operator) in query expression '(client name ='15')'"

    This is the code its referringto

    strWhere = "Client Name= '" & Me.List2 & "'"

    This is what is in the immediate window in the visual basic editor:

    Client Name= '7'

    Each time I select a different name from the list box the ID number changes so it is recognizing the change in name. Also, I tried replacing "client name" with "ID" but still get a similar error.

    Thanks,
    Alicia

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

    Re: Reports and lists (Access 2000)

    In the first place, your field name contains a space; you must enclose it in square brackets, otherwise SQL gets confused: [Client Name]

    It would appear that the bound column of your list box is the client ID, which is numeric, not a string.

    You can try this:

    strWhere = "[ID] = " & Me.List2

    (replace ID by the name of the ID field). Note the lack of quotes around Me.List2

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reports and lists (Access 2000)

    Are you trying to restrict the report by client name or clientID? It's highly unlikely that you have a client named "7", so I assume that your report is actually based on clientID, not name, even though you want to select a name. It looks like the bound column for your listbox is clientID rather than clientname, so just change the where string to "ClientID=" & Me.List2 (not single quotes needed unless the ID is a string) and try that. Once you've got that working, you can expand the where string using the AND operator to something like this:

    strWhere = "ClientID=" & Me.List2 & " AND [Date] BETWEEN #" & Me.STARTDATE & "# AND #" & Me.ENDDATE & "#"
    Charlotte

  8. #8
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reports and lists (Access 2000)

    I am trying to restrict the report by client name not ID. The SQL behind the row source reads as follows:

    SELECT tblClientDemographics.ID, tblClientDemographics.[Client Name]
    FROM tblClientDemographics
    ORDER BY tblClientDemographics.[Client Name];

    If I take out "ID" nothing shows in the list box (even after adjusting column settings etc.) If I replace "ID" for "client name" I get a similar error message. RE placing backets around Client Name when I did this prior to posting, the report opens however if it blank without ANY names let alone the name I'm trying to restrict it to.

    I've removed the date restiriction piece at this point till I get the name piece straightened out but will try Charlottes suggestion after I ge this name restriction down.

    Thanks,
    Alicia

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

    Re: Reports and lists (Access 2000)

    I don't understand why it wouldn't work with ID, but you could try putting the client name in the WHERE condition:<pre>strWhere = "[Client Name] = '" & Me.List2.Column(1) & "'"</pre>

    Column(1) refers to the second column in the list box (the index is zero-based); Column(0) is the hidden ID column.

    If you have several clients with the selected name (but different ID's), the report will (should) display all of them.

  10. #10
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reports and lists (Access 2000)

    I tried adding the column piece and now at least in the debug screen it shows the name vs the ID number so we're making progress! (Thank you for your patience). I am however still getting a syntax error and when I go to debug the portion hightlighted is as follows:

    DoCmd.OpenReport "rptClientInitial-CopyInvoice", acViewPreview, , strWhere

    The entire code is as follows:

    strWhere = "[Client Name] = '" & Me.List2.Column(1) & ""
    Debug.Print strWhere
    DoCmd.OpenReport "rptClientInitial-CopyInvoice", acViewPreview, , strWhere

    Thanks,
    Alicia

  11. #11
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Reports and lists (Access 2000)

    Alicia,

    A couple of things to look for.

    1. Check your data for trailing spaces. If trailing spaces are present, you can trim the data to remove them or change the = to a like in your strWhere statement with an * at the end to get all data starting with the Client Name.

    2. You are using Column1 of the listbox. Are you sure you are getting the correct data. You could temporarily put a msgbox in front of the strWhere such as MSGBOX Me.List2.Column(1) (cant remember if you need .value at the end) to see what data is actually being passed to your strWhere clause.



    HTH


    Post immediately edited to correct name.
    Regards,

    Gary
    (It's been a while!)

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

    Re: Reports and lists (Access 2000)

    There is nothing wrong with your statement in itself, but you did declare strWhere, I hope?

    Dim strWhere As String

    Otherwise, if you have Option Explicit (recommended!), VBA will complain about an undefined variable. If you still have problems, please specify what the error message says.

  13. #13
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reports and lists (Access 2000)

    Ugh, I'll admit with all the changes I didn't declare strwhere, but added it and still receive the syntax error as written above. I don't understand it. When I yank the code that refers to the docmd to open the report, I don't get a blow up and the immediate box shows that the correct name is found. If I take out the ,,strwhere at the end of the code used to open the report, the report opens, but all clients are in the report.

    Alicia

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

    Re: Reports and lists (Access 2000)

    In your previous reply, there is a typo: you have <pre>strWhere = "[Client Name] = '" & Me.List2.Column(1) & ""</pre>

    There should have been a single quote between the double quotes at the end. It should be<pre>strWhere = "[Client Name] = '" & Me.List2.Column(1) & "'"</pre>


  15. #15
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reports and lists (Access 2000)

    YAHOO!!!!!!!!!! That did it. I'm so thrilled.

    Now, to get the restrictions on dates to work. As I mentioned earlier, I have two unbound list boxes, one labeled startdate and and one labeled enddate to restrict the time frame for the report. I tried the AND statement that Charlotte mentioned earlier but got errors. Any suggestions?

    Alicia

Page 1 of 2 12 LastLast

Posting Permissions

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