Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    66

    Cool Unanswered: Selecting a specific company for a report?

    Hello again,

    I think it may be cause it's very late here in Colorado, but I can't seem to figure out how to handle this...

    I want my client to be able to print a report for a specific customer, but the customer names are all long enough that they won't be able to remember them easily. And the customers are growing so the list keeps changing.

    What I'd like is for access to give a list (maybe a drop down list?) of the customer names from the customer table, and then use that to filter the query (and hence filter the report!). It would be even better if they could use check boxes to allow them to choose more than one customer, but I'm not fussy :-)

    Isn't that possible somehow (Keeping in my that my VB skills are still pretty basic!)

    Thanks for your continuing help!!!

    Susan

  2. #2
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    Hi Susan,

    Fear not!! What you wish to achieve can be accomplished in a number of ways. Let's start with a simple one:-

    1. Create a new form. Add a list box which will display the list of customers.
    We'll call the form FrmGetCustomer and the list box LstCustomers . The list box must contain the Primary Key field of the customer and the customer name. The Primary Key field (we'll call it CustomerID) must be the Bound Column. If you are not familiar with any of these properties, search in Help.
    Put an "OK" button on the form, which the user can click after selecting a customer, and this will then run the report.

    2. In the query behind the report, you must include the CustomerID field. In the Criteria row for that field, type:

    =Forms!FrmGetCustomer!LstCustomers

    In other words, the query will only select the customer ID that the user has selected in the form. Notice the syntax : Forms!NameOfForm!NameOfControl.

    3. Now you can open the report from the OK button. You can add the button using the Button Wizard if you don't want to write the code manually, but the code is simply :

    DoCmd.OpenReport ReportName

    I hope this helps and let me know if you want to take things further to allow multiple selections (which is a little more complicated).
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  3. #3
    Join Date
    Apr 2004
    Posts
    66

    Talking

    Thanks a ton!!! You are a great explainer of this and now it works!! Hurray!!!

    If you have a bit of free time - would you be willing to explain the more complicated way of selecting multiple choices? It would also be very helpful!

    Again, thanks so much for your time and help!

    Susan

  4. #4
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    Hi Susan,

    I am so glad that my posting was useful to you, and thank you for your kind comments.

    Allowing the user to select multiple companies is more complicated, but don't worry. It's straightforward enough.

    Firstly, let us use the same form and control. Having allowed the user to select one company in LstCustomers, we now need them to be able to select more than one. When they have done that, they will click the "OK" button on FrmGetCustomer and the report will run, containing only those customers that they have selected.

    The first thing to do is to examine the properties of LstCustomers. You'll see that one of them is called "Multi Select", and that this property has three options: None, Simple and Extended. "None" is self-explanatory, but :

    "Simple" means the user can select more than one entry in the List Box by clicking on each one (and clicking again to de-select it)

    "Extended" means that the user can use the Shift and Control keys to select "ranges" of choices in the List Box (much as you'd do in Windows Explorer).

    If you leave this property set to “None”, the user will only ever be able to select one company at a time in the List Box.

    Set the property to either Simple or Extended, depending on how you want your users to be able to select the entries. It also depends on how many entries are in the List Box; with 500 customers you’d probably use Extended.

    So now our user can select as many customers as they wish from LstCustomers, and our attention naturally turns to the mechanics of including their selections in our report. Let’s call the report “RptSales” for want of anything better.

    One of the parameters of DoCmd.OpenReport is the “Where” condition. This is the string where we can tell Access directly which records to include in the report, and we’ll be using this to tell our report which customers to include. This parameter, of course, is an SQL string. So we have to build the SQL string from our list of customers that the user has selected. We’ll need code to do it.

    Firstly, however, delete the criteria from your query that you placed there yesterday, otherwise we’ll have a big failure on our hands!!

    The logic goes: for each customer selected in LstCustomers, add a clause to the SQL string so that the customer is selected. Let’s say the user has selected three customers : we want Customer6 OR Customer19 OR Customer27, for example.

    The SQL string (the “Where” condition” will therefore look something like “CustomerID = 6 OR CustomerID = 19 OR CustomerID = 27”.

    Don't forget, it's "OR" rather than "AND" as the operator : one customer ID cannot be one number AND another one at the same time.

    However, there is just one tiny problem. Consider the first section of the Where condition. It will say “CustomerID =“ and then a customer ID. This is before the first “OR”, when the second and subsequent IDs are included. Which ID do you use? Do we have to insert the user’s first customer selection here, and then use the “ORs” to select the rest in turn? If so, how do we do that?

    To get around this problem we will use a customer ID that does not exist. I usually use the ID of zero, as a lot of Primary Key fields are Autonumber fields that usually do not include a zero as the first ID in the table. Having now referred to a customer ID that we know doesn’t exist (“CustomerID = 0”) we can now loop through the IDs that the user has selected and append each in turn to our SQL statement with an OR. Don’t worry – all should become clear when you see the code. This is below, and I’ve commented it for ease of comprehension (the lines commencing with apostrophes). This code should run from the “Click” event of our button on FrmGetCustomer. Let’s call the button “BtnOK”. I haven’t included error trapping, which you always should of course.

    Sub BtnOK_Click()

    ‘First, create a string variable to store the SQL

    Dim MySQL As String

    ‘Store the “dummy” customer ID in it as the first section

    MySQL = "ID = 0"

    ‘Create a variable as a Variant. This is used to refer to a selected ‘row in the list box. It must be a Variant.

    Dim Customer

    ‘The next row means, “For each item in the rows in the list box that ‘have been selected…" because "ItemsSelected" is the Collection that stores these.

    For Each Customer In LstCustomers.ItemsSelected

    ‘…append the ID to the SQL string

    ‘ “ItemData” is the value returned from the bound column of the List Box in the current row, i.e. the Customer ID, as pointed to by our row index called Customer:

    MySQL = MySQL & " OR CustomerID = " & LstManagers.ItemData(Customer)


    ‘…and go on to the next

    Next

    ‘ The SQL might now look like “CustomerID = 0 OR CustomerID = 6 OR CustomerID = 27”

    ‘Now open the report, using our MySQL variable as the “Where” condition.


    DoCmd.OpenReport "RptSales", , MySQL


    End Sub

    The result is that only those customers selected in the List Box, and then appended to our Where statement, will be included in the report.

    I hope this is all fairly clear, Susan. Tell me if there’s anything I haven’t explained adequately. When you enter the code I’d recommend you do exactly that, rather than copying and pasting it, so that you gain a greater insight into how things work. Watch out that you don't type any Comment lines without them beginning with apostrophes.

    Once you have mentally assimilated this example, you’d be surprised how it will inspire you to solve similar problems.

    Good luck and I hope that I have helped you.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  5. #5
    Join Date
    Apr 2004
    Posts
    66
    Thank You
    Thank You
    Thank You
    Thank You
    Thank You
    Thank You
    Thank You
    Thank You
    Thank You

    Susan

  6. #6
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    Any time
    Any time
    Any time
    Any time
    Any time
    Any time
    Any time
    Any time
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

Posting Permissions

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