Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2004
    Posts
    54

    Talking Unanswered: Filter on command button click question

    Hello,

    I'm trying to use a command button to open a form and apply a filter, not to the form, but just for a combo box on the form.

    There are 3 forms for each of my sales guys, so the filter would just reflect a hard coded ID (where salesID = 1)

    My vb for the command button is:



    Private Sub Command5_Click()
    On Error GoTo Err_Command5_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "MachineDetail2"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_Command5_Click:
    Exit Sub

    Err_Command5_Click:
    MsgBox Err.Description
    Resume Exit_Command5_Click

    End Sub


    On MachineDetail2, there's a combo box with customer names to choose from. Each customer name has a salesID associated with it. I'm not sure how to just show the particular guy's customers on the form. Any ideas would be greatly appreciated.

    Thanks!

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Here are two methods:

    1)
    Open the form, and then set the filter of the combo box directly:
    DoCmd.OpenForm "frmName"
    [Forms]![frmName]![controlName].rowsource = "SELECT ..."

    2)
    Pass some value to the form's OpenArgs parameter, which can then be used to set the form state accordingly.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Aug 2004
    Posts
    54
    I tried the first method, but it said the Object Doesn't Support This Method.

    Yar....

  4. #4
    Join Date
    Aug 2004
    Posts
    54
    I'm not so sure how to use the OpenArgs..

  5. #5
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    Sorry, combo boxes and list boxes don't have filters! Forms do and so do queries. Probably want a sub form here. (But I'm learning about em too)

  6. #6
    Join Date
    Feb 2005
    Posts
    333
    It seems like you haven't established a way to select the salesman yet. I would use another combo box to select the salesID. Once a selection is made from this combo requery the customer combo box. If your not familiar with VB, do this to filter your combo box:

    1. right click the box and select properties
    2. find the row source field and click the three dots on the right hand side.
    this will launch the query designer
    3. create your query from the approprate table and use the following criteria:
    this should be in the salesID column
    [forms]![MachineDetail2]![name of the above combo that has salesID]

  7. #7
    Join Date
    Aug 2004
    Posts
    54
    Unfortunately that's not quite my problem.

    The sales ID is kept track of in a customer form.

    The sales form contains a combo box with customers to choose from, and a button to open a list of customers (which contains the salesID).

    When a salesman goes into his personal form, it gives him the option of opening a new sales form. I would like the sales form here to filter the customer list depending on who is opening it.

    Example:

    Salesman 1 opens the program, clicks on the Sales switchboard, and is asked to log-in.

    He enters his username and password, and his custom form opens up with all his current sales forms.

    He wants to create a new sales form, so he clicks the appropriate button. A new empty sales form opens, but it's the same for all salesmen, and pre-loaded with all past customer names.

    Thanks!

  8. #8
    Join Date
    Feb 2005
    Posts
    333
    When a salesman goes into his personal form, it gives him the option of opening a new sales form. I would like the sales form here to filter the customer list depending on who is opening it.
    Are you using salesID to filter the data for the personal form? I would think that you have a one-to-many relationship between the salesman table and the customer table that is joined by the salesID field.

    In any case, you should be able to determine the salesID of the specific salesman once the personal form is open. Once you have the salesID, define the row source of the combo box using the query design view. make the first row [salesID] with a criteria = "wherever you access the salesID" and the second row the customer name. Change the column count to 2. Change the column width property to 0";3".

  9. #9
    Join Date
    Aug 2004
    Posts
    54
    I can't really do that, since all salesmen use the same new worksheet form.

    I hardcoded user names and passwords into vba to let them into their personal pages.

    The pages just filter their past sales forms by their salesID.

    There's a button on each one of the personal pages that allows them to make a new worksheet, but this is not tied to salesID.

Posting Permissions

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