Search Form Help Please! (1 Viewer)

[crashnburn99]

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

 

[pbaldy]

If CustomerID is numeric, the syntax changes:

http://www.baldyweb.com/wherecondition.htm

 

[crashnburn99]

Wow... that is where I got the code I was using.... Didn't even think about it being a numeral. Pbaldy yet again you are my hero!! Thank you very much!

 

[pbaldy]

Happy to help!

 

[crashnburn99]

Pbaldy if I may pick your brain again. I added an attachment field to my table for storing PDF's, but now when I run the search it states that there is an "Invalid Argument" I have tested it out and it seems to be the attachment field. Is there anyway to exclude the attachment field from the search?

 

[pbaldy]

I've never used the attachment field (I store the path). What exact code fails?

 

[crashnburn99]

To be honest I am not sure. It does not bring me to the debugger it just states that there is an invalid argument. I have removed the attachment field and tried it again and it works. When I put it back I get the same error message. Is there a user friendly way to add links to the file location? The friend that I am making this for is not very computer savy so I am trying to make it as fool proof as possible.

 

[pbaldy]

If you temporarily comment out this line:

On Error GoTo Err_cmdSearch_Click

and run the code, you should be able to debug and see the line causing the problem.

 

[crashnburn99]

Perfect! So it gives me "Run-Time Error '3001' : Invalid Argument" and it highlights this portion of the code:

strFieldValue = Nz(rs(strFieldName).Value, "")

 

[pbaldy]

Offhand, it looks like you're trying to put an attachment into a String variable. See if this helps:

http://blogs.office.com/b/microsoft...-the-new-attachment-field-in-access-2007.aspx

 

[crashnburn99]

I'm away from my computer now but I will check that as soon as I can. Thank you very much for the help

 

[crashnburn99]

I dabbled with some of the code from the link that you sent me but I am still getting that error. It seems that just by having the attachment control in the table it errors out. I was thinking of making another table just for attachments that is linked by customer ID's but again unless there is some way of excluding that table from the search I'm going to end up in the same boat. Not sure if it makes a difference but I am using access 2010 and the universalsearch was made with access 2000.

 

[crashnburn99]

I decided just to trash the old search form and followed Johns advice from this post. It worked out very well. Thank you again PBaldy for all your help.

 

[pbaldy]

Glad you found something that worked.