Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Toggle and text search display results on lstbox (2003)

    Hi, I have a little problem. In my form I have three toggle bottoms used to search different occupations; pressing the search bottom it display the perspective information. I am trying to incorporate a txt box where i can toggle and enter i.e (last name or SSN) then press search and display just the individual (s) with the corresponding information. Also i want to keep the search bottom clickable and display all information for that occupation.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Toggle and text search display results on lstbox (2003)

    Try this:

    Private Sub Command12_Click()
    Dim strOccupation As String
    Dim strSQL As String

    Select Case Me.SelectMode
    Case 1
    strOccupation = "Instructor"
    Case 2
    strOccupation = "Student"
    Case 3
    strOccupation = "Assistance"
    End Select

    strSQL = "SELECT ID, LastName, FirstName, SSN FROM tblPersonnel " & _
    "WHERE Occupation = " & Chr(34) & strOccupation & Chr(34)

    If Not IsNull(Me!txtSearchInfo) Then
    strSQL = strSQL & " AND (LastName = " & Chr(34) & Me!txtSearchInfo & _
    Chr(34) & " OR SSN = " & Chr(34) & Me!txtSearchInfo & Chr(34) & ")"
    End If

    Me.lstDisplay.RowSource = strSQL
    End Sub

    PS It would be better to give the command button a more meaningful name than Command12.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Toggle and text search display results on lstbox (2003)

    Hans, Oh sorry for that I forgot to rename the button. The code work "excelente" You always have the answer. Thanks again.

  4. #4
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Toggle and text search display results on lstbox (2003)

    Hans, sorry for bothering again. I notice that I need to type the ssn as it appears in the table i.e (123-45-6789) to be able to display in the listbox. I tried to use the input mask of the textbox to insert the separators but it would not search for the info and display it. I have to manually type the numbers with the separators to be able to search the information. Any suggestions.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Toggle and text search display results on lstbox (2003)

    Make the Input Mask look like this:
    <code>
    000-00-0000;0;_
    </code>
    The 0 in the second section will include the separators in the value.

  6. #6
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Toggle and text search display results on lstbox (2003)

    I tried to use that format in the input mask and it does not work. The problem is When I input the information using te input mask. the search button does not execute and try to find the record. If I take out the input mask an type all the characters including separators and press the search; it will execute and display the record in the lstDisplay. Also I tried to use a lbl that displays "*No Record Found" to know if the record does not exit. I used the following after the code that you provided:

    Private Sub cmdSearch_Click()
    Dim strOccupation As String
    Dim strSQL As String

    Select Case Me.SelectMode
    Case 1
    strOccupation = "Instructor"
    Case 2
    strOccupation = "Student"
    Case 3
    strOccupation = "Assistance"
    End Select

    strSQL = "SELECT PID, LastName, FirstName, SSN FROM tblPersonnel " & _
    "WHERE Occupation = " & Chr(34) & strOccupation & Chr(34)

    If Not IsNull(Me!txtSearchInfo) Then
    strSQL = strSQL & " AND (LastName = " & Chr(34) & Me!txtSearchInfo & _
    Chr(34) & " OR SSN = " & Chr(34) & Me!txtSearchInfo & Chr(34) & ")"
    End If

    Me.lstDisplay.RowSource = strSQL

    If IsNull(lstDisplay) Then
    Me.LblNoData.Visible = True
    Me.txtSearchInfo.SetFocus

    End If

    End Sub

    When there is a record that is not in the table, the lbl comes up working fine. I wanted not to show the lbl after the txtSearchInfo got the focus; In the txtbox afterUpdate I enter Me.lblNoData.Visible= False. Everything is good until I type new name or ssn that is in the table. I press search the lbl comes back up to show that there is not record but the lstbox is displaying a record now. Sorry for all this mess.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Toggle and text search display results on lstbox (2003)

    You shouldn't test whether the list box is null - until the user makes a selection it will always be null. Instead, check whether the ListCount property is 0.

    In the attached version, I don't show the label but hide the list box, which will automatically make the label behind it visible.
    And the input mask for the search text box works as intended for me.
    Attached Files Attached Files

  8. #8
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Toggle and text search display results on lstbox (2003)

    That is a good idea to hide the lstbox. and display the lbl No Data. With the input mask that you had in the sample i input the ssn 123456789 and it showed no data. I took the input mask and typed the ssn 123-45-6789 and the result came up in the listbox; That is the same problem that I am having. Could you check on that.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Toggle and text search display results on lstbox (2003)

    When I type 123456789 in the search text box, the input mask automatically inserts the dashes, so it comes out as 123-45-6789. If I then click the Search button, one record is found - see screenshot.
    Attached Images Attached Images
    • File Type: png x.png (7.3 KB, 0 views)

  10. #10
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Toggle and text search display results on lstbox (2003)

    Hans, You were right. when I look at my code in detail. I was missing where to display the info at the end of the statement. Sorry for the trouble. Thank you for your help and Professional Knowledge.

  11. #11
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Toggle and text search display results on lstbox (2003)

    Sorry for bothering again with this same thread. I just came across trying to insert different options, when i started using the previous form. Everythings is working great. I am trying to insert two more toggle options to use them kind of like a sort. Where a could click the student, instructor or assistance; then click on a new toggle set to "full time" or "part time", finally have the last toggle for "active" or "inactive". Do I have to post this in a new thread?. If not this is what i am currently doing. I use the event afterupdate on Me.Selectmode to active the new toggles. Then I try to use the same strSQL changing the strOccupation to strComponent for (full time or part time) and strStatus for (active or inactive).

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Toggle and text search display results on lstbox (2003)

    How are these stored in the table? In the form of text fields with values such as "full time" and "active", or in the form of number fields, or of Yes/No fields?

  13. #13
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Toggle and text search display results on lstbox (2003)

    They're all store as text value.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Toggle and text search display results on lstbox (2003)

    Try this, but substitute the appropriate names:

    Private Sub cmdSearch_Click()
    Dim strOccupation As String
    Dim strComponent As String
    Dim strStatus As String
    Dim strSQL As String

    Select Case Me.SelectMode
    Case 1
    strOccupation = "Instructor"
    Case 2
    strOccupation = "Student"
    Case 3
    strOccupation = "Assistance"
    End Select

    ' ComponentMode is option group for full time/part time
    Select Case Me.ComponentMode
    Case 1
    strComponent = "Full Time"
    Case 2
    strComponent = "Part Time"
    End Select

    ' StatusMode is option group for active/inactive
    Select Case Me.StatusMode
    Case 1
    strStatus = "Active"
    Case 2
    strStatus = "Inactive"
    End Select

    strSQL = "SELECT ID, LastName, FirstName, SSN FROM tblPersonnel " & _
    "WHERE Occupation = " & Chr(34) & strOccupation & Chr(34) & _
    " AND Component = " & Chr(34) & strComponent & Chr(34) & _
    " AND Status = " & Chr(34) & strStatus & Chr(34)

    If Not IsNull(Me!txtSearchInfo) Then
    strSQL = strSQL & " AND (LastName = " & Chr(34) & Me!txtSearchInfo & _
    Chr(34) & " OR SSN = " & Chr(34) & Me!txtSearchInfo & Chr(34) & ")"
    End If

    Me.lstDisplay.RowSource = strSQL
    End Sub

  15. #15
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Toggle and text search display results on lstbox (2003)

    You are the brain!!!. I did not know you could used more than once the "AND" clause. Thank you again. Are you putting all of the users problems in a book that users can buy as reference.

Page 1 of 2 12 LastLast

Posting Permissions

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