SQL Question

[encore0000]

have a multisearch form performing a search on the tables based on what the users enters in the textboxes. The results are displayed in a texbox at the bottom of the page. tblTicketInfo is the main database. user_ID is a foreign key inside tblTicketInfo which has a lookup to user_ID inside the tblUserInfo which is a is the primary key in there.

tblUserInfo contains lastname first name and phone number. When I execute my multisearch, the search results display the user_ID as a number instead of the last name. I want to be able to dispaly the last name from the user_ID number in my search results. Please let me know if u have questions

here is 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 tblTicketInfo.ticket_ID, tblTicketInfo.Machine_Number, tblTicketInfo.user_ID, tblTicketInfo.ticketStatus_ID, tblTicketInfo.location_ID " & _
"FROM tblTicketInfo"

strWhere = "WHERE"

strOrder = "ORDER BY tblTicketInfo.ticket_ID;"


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

If Not IsNull(Me.txtUser) Then
strWhere = strWhere & " (tblTicketInfo.user_ID) Like '*" & Me.txtUser & "*' AND"
End If

If Not IsNull(Me.txtLocation) Then
strWhere = strWhere & " (tblTicketInfo.location_ID) Like '*" & Me.txtLocation & "*' AND"
End If

If Not IsNull(Me.txtStatus) Then
strWhere = strWhere & " (tblTicketInfo.ticketStatus_ID) Like '*" & Me.txtStatus & "*' 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

Private Sub lstCustInfo_DblClick(Cancel As Integer)
'Open frmCustomer based on the ID from lstCustInfo listbox


DoCmd.OpenForm "frmAllTickets", , , "[ticket_ID] = " & Me.lstCustInfo, , acDialog 'open another form with ticket details

End Sub

 

[scriverb]

Try making this modification:

'Constant Select statement for the RowSource
strSQL = "SELECT tblUserInfo.LastName & ", " & tblUserInfo.FirstName as Name, tblTicketInfo.ticket_ID, tblTicketInfo.Machine_Number, tblTicketInfo.user_ID, tblTicketInfo.ticketStatus_ID, tblTicketInfo.location_ID " & _
"FROM tblTicketInfo INNER JOIN tblUserInfo ON tblTicketInfo.User_ID = tblUserInfo.User_ID "

I just added the LastName and FirstName fields to the select and added an INNER join between the table tblTicketInfo and tblUserInfo on the commond User_ID fields.

Post back and let me know if this works for you.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???