Multiple Table find

[Jimee]

I have a form that has multiple tables that git filled in. Is there a way to use a find button and find a record by searching multiple tables at one time?

 

[BLeslie88]

Personally...

I would hard code it in VBA. You search on the find 'table' as a variable and you call this function passing the parameter of the table name. I use such a function regularly. I do not search multiple tables, but alas a loop calling this function and sending a user replies for each search would be suffice... If you need help for the coding let me know, I or someone else will gladly give it to you.

Good luck,
Brian

 

[Jimee]

Yes,

Please help w/ the code. I have some VB skills, but not a pro.

THANKS

 

[BLeslie88]

Function Find(vTable As String, vField As String, vSearch As Long)
Dim db As Database
Dim rs As DAO.Recordset
Dim strCriteria As String
Set db = CurrentDb()
Set rs = db.OpenRecordset(vTable, dbOpenDynaset)
' This function will allow to search diferent fields...
strCriteria = vSearch
If rs.EOF And rs.BOF Then
MsgBox "Table " & vTable & " is empty...", vbOKOnly, "Error"
End If
rs.MoveFirst
rs.FindFirst "[" & vField & "] = " & strCriteria & ""
If rs.NoMatch Then
MsgBox "Did not find " & vSearch & " in table " & vTable, vbOKOnly, "Error"
Else
Find = rs(1)
End If
End Function

' On other form
Private Sub cmdFind_Click()
Dim vSearch As Long
Dim vTable As String
Dim vField As String
Dim vSearchResult As String
' On other form
vField = "NoGroup"
vTable = "tblGroup"
vSearch = 1
vSearchResult = Find(vTable, vField, vSearch)
MsgBox "Found " & vSearchResult & " in table... " & vTable, vbOKOnly, "Success"
' now you can keep re-assigning the variables and recalling this function or use arrays, I don't have the time to build that for you.. ;p

End Sub

I will answer any questions about this code though...
Regards,
Brian

 

[Jimee]

I got a Run-Time error 3463 - Data type mismatch in criteria expression. on the following line:

rs.FindFirst "[" & vField & "] = " & strCriteria & ""

 

[BLeslie88]

Jimee

Well I was searching for a long in the index field.... If you are searching for a string you must pass a string....

Function Find(vTable As String, vField As String, vSearch As String)
Dim db As Database
Dim rs As DAO.Recordset
Dim strCriteria As String
Set db = CurrentDb()
Set rs = db.OpenRecordset(vTable, dbOpenDynaset)
' This function will allow to search diferent fields...
strCriteria = vSearch
If rs.EOF And rs.BOF Then
MsgBox "Table " & vTable & " is empty...", vbOKOnly, "Error"
End If
rs.MoveFirst
rs.FindFirst "[" & vField & "] = '" & strCriteria & "'"
' Note you need single quotes above for searching for a string...
' = ' " & strCriteria & " ' "
' Showing you the sytax with spaces so you can read... this should work...
If rs.NoMatch Then
MsgBox "Did not find " & vSearch & " in table " & vTable, vbOKOnly, "Error"
Else
Find = rs(1)
End If
End Function

' On other form
Private Sub cmdFind_Click()
Dim vSearch As String
Dim vTable As String
Dim vField As String
Dim vSearchResult As String
' On other form
vField = "NoGroup"
vTable = "tblGroup"
vSearch = "Name"
vSearchResult = Find(vTable, vField, vSearch)
MsgBox "Found " & vSearchResult & " in table... " & vTable, vbOKOnly, "Success"

End Sub

 

[Jimee]

One of my tables / fields is location / locations. Should the code looke like this for my scenario?
vField = "locations"
vTable = "Location"


If so, When I run it I get both success and error.

Here is my MDB. There is a test form called "Find".

What I am trying to do is search all tables for any particular string which the user will enter at prompt.

 

[BLeslie88]

Ok, well here's the good code...

Just re-name your find button...


Function Find(vTable As String, vField As String, vSearch As String)
Dim db As Database
Dim rs As DAO.Recordset
Dim strCriteria As String
Set db = CurrentDb()
Set rs = db.OpenRecordset(vTable, dbOpenDynaset)
' This function will allow to search diferent fields...
strCriteria = vSearch
If rs.EOF And rs.BOF Then
MsgBox "Table " & vTable & " is empty...", vbOKOnly, "Error"
End If
rs.MoveFirst
rs.FindFirst "[" & vField & "] = '" & strCriteria & "'"
' Note you need single quotes above for searching for a string...
' = ' " & strCriteria & " ' "
' Showing you the sytax with spaces so you can read... this should work...
If rs.NoMatch Then
MsgBox "Did not find " & vSearch & " in table " & vTable, vbOKOnly, "Error"
Find = "NO RESULTS"
Else
Find = rs(1)
End If
End Function

Private Sub cmdFind_Click()
Dim vSearch As String
Dim vTable As String
Dim vField As String
Dim vSearchResult As String
' On other form
vField = "locations"
vTable = "Location"
vSearch = InputBox("Location?", "Enter Location you are looking for.")
If vSearch = "" Then
MsgBox "You must enter a Location to search for....", vbOKOnly, "Input Error"
Exit Sub
End If
vSearchResult = Find(vTable, vField, vSearch)
If vSearchResult = "NO RESULTS" Then
Else
MsgBox "Found " & vSearchResult & " in table... " & vTable, vbOKOnly, "Success"
End If
End Sub

Good luck...
Brian