Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2014
    Posts
    6

    Unanswered: Filter a split form using a combobox and option buttons

    Hi everyone,

    I am trying to open a filtered split form based on user inputed specifications in one form to open up the filtered split form in a different form. First I need to specify which form (which is one of three forms), then I need to specify the selected week (which is a column in the table feeding the second form). Once filtered, I should see about 20 different rows with the same week after the filter.

    Right now my selection form looks like the first attachment titled SpecifyFormScreenshot.
    Click image for larger version. 

Name:	SpecifyFormScreenshot.png 
Views:	4 
Size:	8.4 KB 
ID:	15655

    This first form asks for the week (in a dropdown combobox), and the shift (in a group of three option buttons). After the user specifies how they would like to filter the form, I would like for them to be able to click the button that says "Open Table". Right now, that button has a Build Event for "On Click" that looks like the second attachment titled OpenTableScreenshot.
    Click image for larger version. 

Name:	OpenTableScreenShot.png 
Views:	7 
Size:	21.3 KB 
ID:	15654

    The Option Group is titled Frame 17. Shift 1 = 1, Shift 2 = 2, and Shift 3 = 3. The input for the week dropdown combobox is titled "WeekInput". I am just worrying about Shift 1 right now, once I figure that out I can use that format to apply it to Shift 2 and Shift 3.

    I am having trouble specifying the Where Condition so that it applies the combobox's current value. On the form titled Shift 1, the week field is titled "WeekInput" and that should just be set to the value of the combobox.

    With the current value, when I click the Open Table button, I get a pop up box that says "Enter Parameter Value" for "WeekInput".

    Hopefully all that makes sense, does anyone know how to fix this and am I going about it the right way?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Use VBA procedures, not macros.
    Have a nice day!

  3. #3
    Join Date
    Jul 2014
    Posts
    6
    I'm not overly familiar with visual basic, how should I structure this?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's what can be done. I've prefixed the controls names so that the code is easier to understand.
    Code:
    Private Sub Command_OpenTable_Click()
    
        Dim strFormName As String
        Dim strCriteria As String
        
        Select Case Me.Frame_Shift.Value
            Case 1: strFormName = "Form 1"
            Case 2: strFormName = "Form 2"
            Case 3: strFormName = "Form 3"
        End Select
        strCriteria = "WeekInput='" & Me.Combo_WeekInput & "'"
        DoCmd.OpenForm strFormName, , , strCriteria
        
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Jul 2014
    Posts
    6
    Awesome thanks! I'm definitely getting pretty close - I might not have named everything correctly so let me know if you see something wrong with that but it doesn't seem to like the last line. I just got everything else to work though I think!

    Here's the code I entered in:

    Option Compare Database


    Private Sub OpenTable_Click()

    Dim strFormName As String
    Dim strCriteria As String

    Select Case Me.Frame17
    Case 1: strFormName = "Aub Extrusion Mill Shift 1"
    Case 2: strFormName = "Aub Extrusion Mill Shift 2"
    Case 3: strFormName = "Aub Extrusion Mill Shift 3"
    End Select

    strCriteria = "WeekInput='" & Me.WeekInput & "'"
    DoCmd.OpenForm strFormName, , , strCriteria


    End Sub
    Last edited by accessuser237; 07-03-14 at 13:27.

  6. #6
    Join Date
    Jul 2014
    Posts
    6
    Oh wait just kidding I got it!! Thanks again!

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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