Find list of records from search of multiple fields in Access DB

[Sherman6789]

We have four tables linked together in a MS Access 2000 database. We need to have a form where several fields are listed as either drop downs or text boxes. When a search button is pressed the database is searched for records which match the info placed in the entry boxes. Boxes which have nothing entered are ignored. The results should appear in a sub-form table. When a record on the resulting subform is selected by highlighting the record and pressing enter or a button, a form will appear with the selected record's data filled in. That record can then be printed or updated. Thanks for any assistance and suggestions.

 

[LaurieHamlin]

Are you asking about specific coding or designing the database?

 

[Sherman6789]

Hello Ms. Hamlin,

I have already designed the database and it is in use. The managers have decided that they need a new search feature. The current design allows the user to input a title or item name and the resulting items appears in a completed form.

What is b eing asked for now is the ability to insert one or more choices for various fields, at the same time, and a listing of all records that match will appear in a subform spreadsheet appearance. After the user selects his or her choice by highlighting the item, it will appear on the screen in a completed form.

I was thinking that we shouold have a special form with either pull down boxes or text boxes representing the main fields. The user fills in or selects items and the computer "filters out" the records that match.

Hope this makes sense. I do not know how to code the filter information. Any assistance will be appreciated.

-WRS

 

[embryo]

I have a similar application. I put an onclick event onto my "Search" command button which executes this code and populates a big combobox with the results..
This might get ytou started:

Private Sub btn_OK_Click()
On Error GoTo btn_OK_Err

Dim strWhere As String
Dim strAnd As String
Dim strSelect As String
Dim strOrderBy As String
Dim stDocName As String
Dim strFilter As Date

stDocName = "FrmSearchResults"
If Me!TxtClientID = 483 Then
strSelect = "Select [DebtorID],[AccountName],[Status],[Balance],[Type] FROM [qrySearchResults]"
Else
strSelect = "Select [DebtorID],[AccountName],[Status],[Client],[Branch] FROM [qrySearchResults]"
End If
strAnd = ""
strWhere = ""

DoCmd.OpenForm stDocName, acNormal

If Me!Combo50 = "1" Then
strOrderBy = " Order By Accountname"
ElseIf Me!Combo50 = "2" Then
strOrderBy = " Order By Accountname DESC"
ElseIf Me!Combo50 = "3" Then
strOrderBy = " Order By DebtorID"
ElseIf Me!Combo50 = "4" Then
strOrderBy = " Order By DebtorID DESC"
ElseIf Me!Combo50 = "5" Then
strOrderBy = " Order By ClientID, Status, AccountName"
ElseIf Me!Combo50 = "6" Then
strOrderBy = " Order By Status, ClientID, AccountName"
ElseIf Me!Combo50 = "7" Then
strOrderBy = " Order By Type DESC, AccountName ASC"
ElseIf Me!Combo50 = "8" Then
strOrderBy = " Order By Balance DESC"
Else
strOrderBy = " Order By Accountname"
End If

        
 If Not IsNull(Me!Combo53) And Me!Combo53 <> "" Then
     strWhere = strWhere & strAnd & " Status like '" & Me!Combo53 & "%'"
    strAnd = " And"
End If
        
If Not IsNull(Me!TxtAcctName) And Me!TxtAcctName <> "" Then
     strWhere = strWhere & strAnd & " AccountName like '" & Me!TxtAcctName & "%'"
    strAnd = " And"
End If

If Not IsNull(Me!TxtLast) And Me!TxtLast <> "" Then
     strWhere = strWhere & strAnd & " DebtorLastName like '" & Me!TxtLast & "%'"
    strAnd = " And"
End If

If Not IsNull(Me!TxtFirst) And Me!TxtFirst <> "" Then
     strWhere = strWhere & strAnd & " DebtorFirstName like '" & Me!TxtFirst & "%'"
    strAnd = " And"
End If


If Not IsNull(Me!TxtSpouse) And Me!TxtSpouse <> "" Then
     strWhere = strWhere & strAnd & " SpouseName like '" & Me!TxtSpouse & "%'"
     strAnd = " And"
End If

If Not IsNull(Me!TxtClientID) And Me!TxtClientID <> "" Then
     strWhere = strWhere & strAnd & " ClientID = " & Me!TxtClientID
    strAnd = " And"
End If

If Not IsNull(Me!TxtPhone) And Me!TxtPhone <> "" Then
     strWhere = strWhere & strAnd & " HomePhone like '" & Me!TxtPhone & "%'"
    strAnd = " And"
End If

If Not IsNull(Me!TxtOtherPhone) And Me!TxtOtherPhone <> "" Then
     strWhere = strWhere & strAnd & " OtherPhone like '" & Me!TxtOtherPhone & "%'"
    strAnd = " And"
End If

If Not IsNull(Me!TxtAddress1) And Me!TxtAddress1 <> "" Then
     strWhere = strWhere & strAnd & " Address1 like '" & Me!TxtAddress1 & "%'"
    strAnd = " And"
End If

If Not IsNull(Me!TxtCity) And Me!TxtCity <> "" Then
     strWhere = strWhere & strAnd & " City like '" & Me!TxtCity & "%'"
    strAnd = " And"
End If

If Not IsNull(Me!TxtState) And Me!TxtState <> "" Then
     strWhere = strWhere & strAnd & " State like '" & Me!TxtState & "%'"
    strAnd = " And"
End If

If Not IsNull(Me!TxtPOE) And Me!TxtPOE <> "" Then
     strWhere = strWhere & strAnd & " POE like '" & Me!TxtPOE & "%'"
    strAnd = " And"
End If

If Not IsNull(Me!TxtDebtorID) And Me!TxtDebtorID <> "" Then
     strWhere = strWhere & strAnd & " DebtorID like '" & Me!TxtDebtorID & "%'"
    strAnd = " And"
End If

If Not IsNull(Me!TxtCreditorAcctNumber) And Me!TxtCreditorAcctNumber <> "" Then
     strWhere = strWhere & strAnd & " CreditorAcctNumber like '" & Me!TxtCreditorAcctNumber & "%'"
    strAnd = " And"
End If

If Not IsNull(Me!TxtDLNumber) And Me!TxtDLNumber <> "" Then
     strWhere = strWhere & strAnd & " DL_Number like '" & Me!TxtDLNumber & "%'"
    strAnd = " And"
End If

If Not IsNull(Me!TxtGName) And Me!TxtGName <> "" Then
     strWhere = strWhere & strAnd & " GuarantorName like '" & Me!TxtGName & "%'"
    strAnd = " And"
End If

If Not IsNull(Me!TxtNOKName) And Me!TxtNOKName <> "" Then
     strWhere = strWhere & strAnd & " NOKName like '" & Me!TxtNOKName & "%'"
    strAnd = " And"
End If

If Not IsNull(Me!theCalendar) And Me!theCalendar <> "" Then
     strFilter = Me!theCalendar
     strWhere = strWhere & strAnd & " PlaceDate >= CONVERT(DATETIME, '" & strFilter & "', 102)"
End If

If Not IsNull(strWhere) And strWhere <> "" Then
'MsgBox strSelect & " Where" & strWhere & strOrderBy
    Forms!FrmSearchResults!Combo0.RowSource = strSelect & " Where" & strWhere & strOrderBy
Else
    Forms!FrmSearchResults!Combo0.RowSource = strSelect
End If

Exit Sub


btn_OK_Err:
 MsgBox Error$


End Sub

Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If

 

[PWise]

Sherman6789:

I see that you posted in ADP fourm is your database A SQL Database?

 

[Sherman6789]

To: pwise.

It is a plain MS Access DB. I expect portions to use SQL type steps.