crashnburn99
Registered User.
- Local time
- Yesterday, 20:46
- Joined
- Jan 16, 2013
- Messages
- 48
I found a sample database called "universal search". It is perfect for what I am in need of, with one small problem. I would like to have it to where a user clicks on a result and it brings them to a specific record on a separate form. I have tried changing the code up to accomplish this but I cannot seem to figure it out. Here is the code to the form. I have highlighted in red the portion that I tried to alter to no prevail. Thank you for your time and knowledge.
Option Compare Database
Option Explicit
Private Sub cmdClear_Click()
On Error Resume Next
Me.lstList.RowSource = """TableName"";""Identifier"";""FieldName"";""FieldValue"""
Me.lblNumRecords.Caption = ""
End Sub
Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim rs As DAO.Recordset
Dim strSearch As String
Dim strTableName As String
Dim strIdentifierValue As String
Dim strIdentifierName As String
Dim strFieldName As String
Dim strFieldValue As String
Dim strListRowSource As String
Dim i As Long
Dim lngCount As Long
If Me.txtSearch <> "" Then
strSearch = Me.txtSearch
strListRowSource = """TableName"";""IDValue"";""IDName"";""FieldName"";""FieldValue"""
Me.lstList.RowSource = strListRowSource
Me.lblNumRecords.Caption = ""
lngCount = 0
' iterate through each user table in the db
For Each tdf In CurrentDb.TableDefs
strTableName = tdf.Name
' exclude system tables
If Not tdf.Name Like "MSys*" Then
Set rs = CurrentDb.OpenRecordset(strTableName)
If Not rs.BOF Then
' iterate through each record in the table
Do Until rs.EOF
' iterate through each field in the record
For i = 0 To rs.Fields.Count - 1
' test for a match
strFieldName = rs.Fields(i).Name
strFieldValue = Nz(rs(strFieldName).Value, "")
If strFieldValue Like "*" & strSearch & "*" Then
' assuming pk is not compound and is first field in table
strIdentifierValue = rs(0).Value
strIdentifierName = rs(0).Name
strListRowSource = strListRowSource & ";" & strTableName & ";" & strIdentifierValue & ";" & strIdentifierName & ";" & strFieldName & ";" & strFieldValue
lngCount = lngCount + 1
End If
Next i
rs.MoveNext
Loop
End If
End If
Next tdf
Me.lstList.RowSource = strListRowSource
Beep
Me.lblNumRecords.Caption = lngCount & " matching value(s) founds."
Else
MsgBox "Please enter text to search.", vbCritical
End If
Exit_cmdSearch_Click:
Exit Sub
Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click
End Sub
Private Sub Form_Load()
Me.lblNumRecords.Caption = ""
Me.txtSearch = "Search"
End Sub
Private Sub lstList_DblClick(Cancel As Integer)
Dim varItm As Variant
Dim intI As Integer
Dim strTableName As String
Dim intIdentifierValue As Integer
Dim strIdentifierName As String
Dim strFieldName As String
Dim strFieldValue As String
For Each varItm In lstList.ItemsSelected
For intI = 0 To lstList.ColumnCount - 1
Select Case intI
Case 0:
strTableName = lstList.Column(intI, varItm)
Case 1:
intIdentifierValue = lstList.Column(intI, varItm)
Case 2:
strIdentifierName = lstList.Column(intI, varItm)
Case 3:
strFieldName = lstList.Column(intI, varItm)
Case Else:
strFieldValue = lstList.Column(intI, varItm)
End Select
Next intI
Next varItm
Dim strOpenArgs As String
Dim strSQL As String
strSQL = "SELECT * FROM " & strTableName & " WHERE " & strIdentifierName & " = " & intIdentifierValue
strOpenArgs = strTableName & "|" & strSQL
DoCmd.OpenForm "customerSchedules", , , "CustomerID= '" & Me.intIdentifierValue & "'"
End Sub
Option Compare Database
Option Explicit
Private Sub cmdClear_Click()
On Error Resume Next
Me.lstList.RowSource = """TableName"";""Identifier"";""FieldName"";""FieldValue"""
Me.lblNumRecords.Caption = ""
End Sub
Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim rs As DAO.Recordset
Dim strSearch As String
Dim strTableName As String
Dim strIdentifierValue As String
Dim strIdentifierName As String
Dim strFieldName As String
Dim strFieldValue As String
Dim strListRowSource As String
Dim i As Long
Dim lngCount As Long
If Me.txtSearch <> "" Then
strSearch = Me.txtSearch
strListRowSource = """TableName"";""IDValue"";""IDName"";""FieldName"";""FieldValue"""
Me.lstList.RowSource = strListRowSource
Me.lblNumRecords.Caption = ""
lngCount = 0
' iterate through each user table in the db
For Each tdf In CurrentDb.TableDefs
strTableName = tdf.Name
' exclude system tables
If Not tdf.Name Like "MSys*" Then
Set rs = CurrentDb.OpenRecordset(strTableName)
If Not rs.BOF Then
' iterate through each record in the table
Do Until rs.EOF
' iterate through each field in the record
For i = 0 To rs.Fields.Count - 1
' test for a match
strFieldName = rs.Fields(i).Name
strFieldValue = Nz(rs(strFieldName).Value, "")
If strFieldValue Like "*" & strSearch & "*" Then
' assuming pk is not compound and is first field in table
strIdentifierValue = rs(0).Value
strIdentifierName = rs(0).Name
strListRowSource = strListRowSource & ";" & strTableName & ";" & strIdentifierValue & ";" & strIdentifierName & ";" & strFieldName & ";" & strFieldValue
lngCount = lngCount + 1
End If
Next i
rs.MoveNext
Loop
End If
End If
Next tdf
Me.lstList.RowSource = strListRowSource
Beep
Me.lblNumRecords.Caption = lngCount & " matching value(s) founds."
Else
MsgBox "Please enter text to search.", vbCritical
End If
Exit_cmdSearch_Click:
Exit Sub
Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click
End Sub
Private Sub Form_Load()
Me.lblNumRecords.Caption = ""
Me.txtSearch = "Search"
End Sub
Private Sub lstList_DblClick(Cancel As Integer)
Dim varItm As Variant
Dim intI As Integer
Dim strTableName As String
Dim intIdentifierValue As Integer
Dim strIdentifierName As String
Dim strFieldName As String
Dim strFieldValue As String
For Each varItm In lstList.ItemsSelected
For intI = 0 To lstList.ColumnCount - 1
Select Case intI
Case 0:
strTableName = lstList.Column(intI, varItm)
Case 1:
intIdentifierValue = lstList.Column(intI, varItm)
Case 2:
strIdentifierName = lstList.Column(intI, varItm)
Case 3:
strFieldName = lstList.Column(intI, varItm)
Case Else:
strFieldValue = lstList.Column(intI, varItm)
End Select
Next intI
Next varItm
Dim strOpenArgs As String
Dim strSQL As String
strSQL = "SELECT * FROM " & strTableName & " WHERE " & strIdentifierName & " = " & intIdentifierValue
strOpenArgs = strTableName & "|" & strSQL
DoCmd.OpenForm "customerSchedules", , , "CustomerID= '" & Me.intIdentifierValue & "'"
End Sub