Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557

    Unanswered: Find Different things from Form

    Okay I have a basic search system setup using a bunch of one form that opens a form based on a query that gets its value from the first form to find parts.

    I need a better way of seaching my parts... there are 3 different main ways of sorting them...

    Series - (9 different series)
    Class - (6 different classes)
    Both

    I was thinking a few list boxes on one form would be best.

    After I get it where it will show the narrowed version of parts in a list box I will need the user to then be able to select what parts they need to be able to run a report on it. (like a custom catalog)

    Tell me if I am making sense. I will show you a picture of the current form I am using.

    Thanks,
    JS
    Attached Thumbnails Attached Thumbnails findform.gif  
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    I do kinda the same thing ... Ex:
    Attached Thumbnails Attached Thumbnails find.bmp  

  3. #3
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    But do you use a different form and query for each option that can be chosen?
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  4. #4
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557

    Code

    I am going to post my code... for that find form.

    Code:
    Option Compare Database
    
    Private Sub cmdCancel_Click()
        DoCmd.Close acForm, "Find"
    End Sub
    
    Private Sub cmdOkay_Click()
    If Frame12.Value = 2 Then
        DoCmd.OpenForm "Find Parts", acNormal, , , acFormPropertySettings
        DoCmd.Close acForm, "Find"
    ElseIf Frame12.Value = 1 Then
        DoCmd.OpenForm "Find Tools", acNormal, , , acFormEdit, acWindowNormal
        DoCmd.Close acForm, "Find"
    ElseIf Frame12.Value = 3 Then
        DoCmd.OpenForm "Find Series", acNormal, , , acFormPropertySettings
        DoCmd.Close acForm, "Find"
    ElseIf Frame12.Value = 5 Then
        DoCmd.OpenForm "Find Series-Class", acNormal, , , acFormPropertySettings
        DoCmd.Close acForm, "Find"
    ElseIf Frame12.Value = 4 Then
        DoCmd.OpenForm "Find Class", acNormal, , , acFormPropertySettings
        DoCmd.Close acForm, "Find"
    End If
    End Sub
    
    Private Sub Form_Load()
        txtpart.Enabled = False
        Combo84.Enabled = False
    End Sub
    
    Private Sub Frame12_Click()
        txtpart.Enabled = True
        Combo84.Enabled = True
    End Sub
    That code is relatively simple. Except the output is in a form format. I need users to be able to select one or multiple parts and run a report on that at any given time. :-/

    Each form has its own query which equates to about 5 different queries and 5 different forms.
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by JSThePatriot
    But do you use a different form and query for each option that can be chosen?
    Ah ... Nope. When a "Find" is performed, the same type of info is returned ... I build a filter for my WHERE clause that selects the appropriate records ... Wanna see it?

  6. #6
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    I would love to see it... I have never messed much with filters though I proly should.
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by JSThePatriot
    I would love to see it... I have never messed much with filters though I proly should.
    Ok ... I warn you tho, my code can cause brain damage to those not prepared ...

    Code:
    Function ConstructFilter() As String
        Dim FilterString As String
        Dim HaveFrom As Boolean, HaveThru As Boolean
        
        FilterString = "( "
        If SearchOnGroup.Value <> 0 Then
            Select Case SearchOnGroup.Value
                Case 1  ' Project Title
                    FilterString = FilterString & "[Work Orders II].[Project Title]"
                    Select Case MatchGroupBox.Value
                        Case 1  ' Exact
                            FilterString = FilterString & "='" & SearchTxt.Value & "'"
                        Case 2  ' Partial
                            FilterString = FilterString & " LIKE '%" & SearchTxt.Value & "%'"
                    End Select
                Case 2  ' Work Order
                    FilterString = FilterString & "[Work Orders II].WorkOrderNumber"
                    Select Case MatchGroupBox.Value
                        Case 1  ' Exact
                            FilterString = FilterString & "='" & SearchTxt.Value & "'"
                        Case 2  ' Partial
                            FilterString = FilterString & " LIKE '%" & SearchTxt.Value & "%'"
                    End Select
                Case 3  ' Brand
                    FilterString = FilterString & "[Work Orders II].WorkOrderNumber LIKE '%" & BrandComboBox.Value & "%'"
                Case 4  ' Requestor
                    FilterString = FilterString & "[Work Orders II].OwnerID='" & OwnerComboBox.Value & "'"
                Case 5  ' Customer
                    FilterString = FilterString & "CustomerContacts.ContactID=" & CustomerComboBox.Value
                Case Else
                    MsgBox "Garbage"
            End Select
            FilterString = FilterString & " )"
        End If
        
        If OpenGroup.Value <> 0 Then
            If SearchOnGroup.Value <> 0 Then FilterString = FilterString & " AND ( "
            Select Case OpenGroup.Value
                Case 1  ' Open
                    FilterString = FilterString & "[Work Orders II].Closed=False"
                Case 2  ' Closed
                    FilterString = FilterString & "[Work Orders II].Closed=True"
                Case Else
                    MsgBox "Garbage"
            End Select
            FilterString = FilterString & " )"
        End If
        
        HaveFrom = False
        HaveThru = False
        If FromTxt.Value & "" <> "" Then HaveFrom = True
        If ThruTxt.Value & "" <> "" Then HaveThru = True
        If HaveFrom Or HaveThru Then
            FilterString = FilterString & " AND ( [Work Orders II]."
            If OpenGroup.Value = 2 Then
                FilterString = FilterString & "ClosingDate"
            Else
                FilterString = FilterString & "IssueDate"
            End If
        End If
        If HaveFrom And HaveThru Then
        FilterString = FilterString & " BETWEEN #" & FromTxt.Value & "# AND #" & ThruTxt.Value & "#"
        Else
            If HaveFrom Then FilterString = FilterString & "=#" & FromTxt.Value & "#"
            If HaveThru Then FilterString = FilterString & "=#" & ThruTxt.Value & "#"
        End If
        If HaveFrom Or HaveThru Then FilterString = FilterString & " )"
        
        ConstructFilter = FilterString
    End Function

  8. #8
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    haha Yea I would say that is to put it mildly
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  9. #9
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    So you think that I should filter my form?

    hmm... I still will have to figure all of this out as well as I need to see if this will work as far as my users being able to select multple parts to print a report of :/


    Hmm...
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by JSThePatriot
    So you think that I should filter my form?

    hmm... I still will have to figure all of this out as well as I need to see if this will work as far as my users being able to select multple parts to print a report of :/


    Hmm...
    JS
    Something to consider ... I don't filter any form. I put the filter on the query I use to populate the form ... Since I have a split DB and it's not linked at all, I have to use temp tables as holding areas for the forms to work on ...

  11. #11
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Hmm the only thing is my form isnt set to a record set...


    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  12. #12
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by JSThePatriot
    Hmm the only thing is my form isnt set to a record set...


    JS
    Doesn't have to be ...What things are you looing to do on this form (add,update,delete, etc ...)?

  13. #13
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    No all I want to happen is for like sales guys to be able to select the parts they would like on a report print out.

    (Of course the add edit and delete stuff would be nice for me but I can handle not having that functionability)



    Thanks,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  14. #14
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Code:
    Private Sub lst1_AfterUpdate()
        If lst1.Value = "Parts" Then
            With lst2
                .Visible = True
                .Enabled = True
                .RowSourceType = "Value List"
                .RowSource = "Series;Class;Both"
            End With
        ElseIf lst1.Value = "Tools" Then
            With lst2
                .Visible = True
                .Enabled = True
                .RowSourceType = "Table/Query"
                .RowSource = "SELECT * FROM Tools"
            End With
        End If
    End Sub
    The 'SELECT' statement only pulls the primary key of the tools table. How do I get it to present all fields and/or the fields I select?
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  15. #15
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by JSThePatriot
    Code:
    Private Sub lst1_AfterUpdate()
        If lst1.Value = "Parts" Then
            With lst2
                .Visible = True
                .Enabled = True
                .RowSourceType = "Value List"
                .RowSource = "Series;Class;Both"
            End With
        ElseIf lst1.Value = "Tools" Then
            With lst2
                .Visible = True
                .Enabled = True
                .RowSourceType = "Table/Query"
                .RowSource = "SELECT * FROM Tools"
            End With
        End If
    End Sub
    The 'SELECT' statement only pulls the primary key of the tools table. How do I get it to present all fields and/or the fields I select?
    You're using a listbox? One thing I see is a value list and a table/query ... Make sure you do your requery for the table/query within that block.

Posting Permissions

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