Results 1 to 2 of 2
  1. #1
    j2curtis64 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2011
    Posts
    56

    Display Fields in Search Form

    I am creating a search form where an end user can enter a field in a text box, then hit a command button and values are returned. The problem is I have two fields that are text (cardholder and approving official) values are being returned as numeric in the form. I am expecting the form to display the names not numeric values.

    Fields from Issues table
    cardholder - text
    approving official - text
    RequisitionNumber - text
    Transactionnumber - text
    Category - text
    Status - text


    Below is the copy of the code
    Private Sub cmdSearch_Click()
    'Set the Dimensions of the Module
    Dim strSQL As String, strOrder As String, strWhere As String
    Dim dbNm As Database
    Dim qryDef As QueryDef
    Set dbNm = CurrentDb()
    'Constant Select statement for the RowSource
    strSQL = "SELECT issues.id, issues.ApprovingOfficial, issues.CardHolder, issues.RequisitionNumber, issues.transactionnumber,issues.category, issues.status " & _
    "FROM issues"
    strWhere = "WHERE"
    'strOrder = "ORDER BY issues.id;"

    'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
    If Not IsNull(Me.CardHolder) Then '<--If the textbox txtcardholder contains no data THEN do nothing
    strWhere = strWhere & " (issues.cardholder) Like '*" & Me.CardHolder & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef


    End If
    If Not IsNull(Me.ApprovingOfficial) Then
    strWhere = strWhere & " (issues.approvingofficial) Like '*" & Me.ApprovingOfficial & "*' AND"
    End If
    If Not IsNull(Me.RequisitionNumber) Then
    strWhere = strWhere & " (issues.RequisitionNumber) Like '*" & Me.RequisitionNumber & "*' AND"
    End If
    If Not IsNull(Me.Category) Then
    strWhere = strWhere & " (issues.category) Like '*" & Me.Category & "*' AND"
    End If
    If Not IsNull(Me.Status) Then
    strWhere = strWhere & " (issues.status) Like '*" & Me.Status & "*' AND"
    End If
    'Remove the last AND from the SQL statment
    strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
    'Pass the SQL to the RowSource of the listbox
    Me.lstCustInfo.RowSource = strSQL & " " & strWhere & "" & strOrder
    End Sub

  2. #2
    j2curtis64 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2011
    Posts
    56
    My problem is the code i am using for cardholder and approving official is for text boxes. These two sources (the form where this is pulling from is combo boxes). How do i update my code to use combo boxes?

    See below....

    'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
    If Not IsNull(Me.CardHolder) Then '<--If the textbox txtcardholder contains no data THEN do nothing
    strWhere = strWhere & " (issues.cardholder) Like '*" & Me.CardHolder & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
    End If
    If Not IsNull(Me.ApprovingOfficial) Then
    strWhere = strWhere & " (issues.approvingofficial) Like '*" & Me.ApprovingOfficial & "*' AND"
    End If

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Search across multiple fields
    By Nexus13 in forum Programming
    Replies: 2
    Last Post: 07-08-2011, 02:38 PM
  2. Replies: 1
    Last Post: 04-01-2011, 01:28 PM
  3. Help with Search Fields
    By jhawk in forum Forms
    Replies: 2
    Last Post: 03-16-2011, 08:42 AM
  4. Help with Search Fields
    By jhawk in forum Programming
    Replies: 0
    Last Post: 03-15-2011, 08:05 AM
  5. Replies: 5
    Last Post: 08-10-2010, 02:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums