Reports and lists (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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:

strWhere = "LastName = '" & Me.lstNames & "'"

DoCmd.OpenReport "MyReport", acPreview, , strWhere

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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 & “#”

Reply | Quote

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

Reply | Quote

I don’t understand why it wouldn’t work with ID, but you could try putting the client name in the WHERE condition:

strWhere = "[Client Name] = '" & Me.List2.Column(1) & "'"

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

In your previous reply, there is a typo: you have

strWhere = "[Client Name] = '" & Me.List2.Column(1) & ""

There should have been a single quote between the double quotes at the end. It should be

strWhere = "[Client Name] = '" & Me.List2.Column(1) & "'"

Reply | Quote

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

Reply | Quote

Try

strWhere = "[Client Name] = '" & Me.List2.Column(1) & "'" & _
  " And ([Date] Between #" & Me.StartDate & "# And #" & Me.EndDate & "#)"

Please be careful about the placement of the quotes. StartDate and EndDate stand for the names of the text boxes on your form.

Reply | Quote

Hi Hans,

I tried something similar but noticed the following. First, the original form was unbound as were the two input boxes. The code ran as follows:

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

When I tried to restrict the report by name and bound the form to the table with the client names, and bound the list to Client Name, I found that entering the code the way and you and Charlotte suggested no longer gives me the ability to find the names startdate or enddate when I type me. There are no start and end dates in the actual tables that are in the query the is bound to the report. The only date is the date entered as the billing date. When running the report I am trying to run the report for a date range that varies from report to report, etc.

Alicia

Reply | Quote

GOT IT!!!! I finally cut and pasted your code in and it ran. I can’t thank all of you enough.

Have a grand day!

Leesha

Reply | Quote