Display Fields in Search Form

[j2curtis64]

Hello,

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) and the values are being returned as numeric in the form. I am expecting the form to display the names, not numeric values.

The fields are tied to a table (called Issues) and a form called Issue Details. The cardholder and approvingofficial fields are combo boxes in the issues detailss form. How do i write the code to make this work if for combo box? The other fields (req#, trans$, category, status) are displaying properly. In the Issue Details form they are text boxes.

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

Thx

 

[GinaWhipp]

Are you using Look-up fields at the table level? See...

Stop!
Lookup fields in tables are a feature provided by Microsoft that causes a lot of confusion, see…
http://www.mvps.org/access/lookupfields.htm

You can use what we like to refer to as *Look-Up tables*. Look-Up Tables are simply regular tables that hold lists of values for use in Combo Boxes on forms.