Need to Create Multi Search Form

[kristi.owen091010]

I have a form called Employees that is linked to five subforms. I need to create a form that gives me search options that pulls the data from the Employee's form. I am new to Access and do not really know how to do code. Below are the fields I need to search off of:

PIN # (On main Employee Form) - Exact Match
Former PIN (On subform in Employee Form) - Exact Match
Company Name - A search that looks like data would be good (subform in Employee form)
Account # - (On subform in Employee Form) - Exact Match

Any help would be greatly appreciated!

Thank you,

Kristi

 

[John Big Booty]

Welcome to the forum.

Perhaps the sample here is doing the sort of thing you are looking for.

 

[kristi.owen091010]

Hi! Thank you for welcoming me . Do you know if this would work for a form that is based off of tables instead of a query. Also will it pull up the subform information? I apologize I am new to the forum and obviously do not know what I am doing in Access yet. It would be nice if their was a macro I could create. I am not very good with code. Thank you for your help!

 

[kristi.owen091010]

I was able to create a search form. However when I try to run my code at work it does run the search function. I assume it has something to do with the filepath, but I am not sure what is wrong with it. Any ideas?

Private Sub cmdSearch_Click()
Dim LSQL As String, SrchStr As String, NumOfItems As Double: NumOfItems = 0
Dim dbs As Database
Set dbs = OpenDatabase("network link")
If cmdSearch.Caption = "Clear Search" Then
LSQL = "select * from Employee" 'Set to table

cmbColName.Enabled = True: txtSrchStr.Enabled = True 'enable buttons
cmdSearch.Caption = "Search": cmbColName.Value = "": txtSrchStr.Value = "" 'clear values
Else
If cmbColName.Value = "" Or txtSrchStr.Value = "" Then Exit Sub 'No values exits
SrchStr = txtSrchStr.Value 'get search value
LSQL = "select * from Employee" 'get from table

'Modify search value if number or birth date
Select Case cmbColName.Value
Case "Number"
'Done this way since numbers do not need the '' for the values
LSQL = LSQL & " WHERE " & GetTabVal(cmbColName.Value) & " = '" & SrchStr & "'"
Case Else
LSQL = LSQL & " WHERE " & GetTabVal(cmbColName.Value) & " LIKE '*" & SrchStr & "*'"
dbs.Execute " INSERT INTO Log ([Date/Time],[User Record], Event) VALUES " & "(NOW(),1,'Search Based on Name');"
End Select

cmbColName.Enabled = False: txtSrchStr.Enabled = False 'disable buttons
cmdSearch.Caption = "Clear Search"
End If
Me.RecordSource = LSQL 'apply record filter
NumOfItems = Me.RecordsetClone.RecordCount
'if record filter was applied and there are no items found
If NumOfItems = 0 And cmbColName.Enabled = False Then
MsgBox "No records found for '" & SrchStr & "'.", vbOKOnly, "Removing Filter" 'lets user know
LSQL = "select * from Employee" 'just get raw data

'enable buttons, clear values and reset record source
cmbColName.Enabled = True: txtSrchStr.Enabled = True
cmdSearch.Caption = "Search": cmbColName.Value = "": txtSrchStr.Value = ""
Me.RecordSource = LSQL
End If
End Sub
Function GetTabVal(PassSelVal As String)
Select Case PassSelVal
Case "First Name" 'Value that the user selected
GetTabVal = "[First Name]" 'Actual name for the column on the table used
Case "Last Name"
GetTabVal = "[Last Name]"
Case "Number"
GetTabVal = "[PIN #]"
End Select
End Function