Thread: Search from help

I need help please.

I need to create a form to search my DB by: ANumber, ID2Number, LastName, and DateOfBirth. Can someone please guide me on how to do it.
I have created the form with the search boxes mentioned and what I want is to be able to search by multiple or single entries. For example if someone inputs ANumber it will find it. Or if LastName and DOB it will find records with that criteria, but not DOB alone (If possible)

I also need a message if all the textbobxes to search are blank.

This is what I have so far:

Private Sub SearchCmd_Click()
On Error GoTo Err_SearchCmd_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAdpEdit"

If Me.ANumberTxt1 & "" = "" Then
MsgBox "PLease enter A-Number"
Me.ANumberTxt1.SetFocus
Exit Sub

Else
If Not IsNull([ANumberTxt1]) Then
stLinkCriteria = "[ANumb]=" & "'" & Me![ANumberTxt1] & "'"
End If
End If


DoCmd.OpenForm stDocName, , , stLinkCriteria

If IsNull(Forms!frmAdpEdit!ANumb) Then
DoCmd.Close acForm, "frmAdpEdit"
MsgBox "Recod Does Not Exist"
End If


Exit_SearchCmd_Click:
Exit Sub

Err_SearchCmd_Click:
MsgBox Err.Description
Resume Exit_SearchCmd_Click

End Sub

It works fine but I need the other textboxes to be seaechable.

Thank you.

[Basic Tools] An intro to making complex databases using simple methods.

Hope this helps with your search needs.

Hi, haven't been around here for a while, popped on the other day and this post was of particular interest. After a fair bit of messing around i have come to the conclusion which first hit me. This kind of criteria filter is best suited to a form which is query based. The query will hold the form text box parameters. Actually very straight forward. Have you tried this?

Your frmAdpEdit record source will be a query. The Query will contain references to the txt boxes on you form i.e.

Code:

Like [Forms]![UnboundForm]![ANumberTxt1] & "*" 

Full example based on the info you provided,

Code:

SELECT DataTable.ANumb, DataTable.ID2Number, DataTable.LastName, DataTable.DateOfBirth
FROM DataTable
WHERE (((DataTable.ANumb) Like [Forms]![UnboundForm]![ANumberTxt1] & "*") AND ((DataTable.ID2Number) Like [Forms]![UnboundForm]![txtID2Number] & "*") AND ((DataTable.LastName) Like [Forms]![UnboundForm]![txtLastName] & "*") AND ((DataTable.DateOfBirth) Like [Forms]![UnboundForm]![txtDateOfBirth] & "*"));

Your form command button will then read the results of this query when opening the frmAdpEdit

From the VBA you provided a effective sollution is as follows,

Notice i am not inlcuding DOB in the first Null statement, also note i am using "AND"

Code:

Dim stDocName As String
Dim stLinkCriteria As String

    If IsNull(ANumberTxt1) And IsNull(txtID2Number) And IsNull(txtLastName) Then
      MsgBox "Please Enter More Search Criteria"
        Me.ANumberTxt1.SetFocus
    Exit Sub
Else
    stDocName = "frmAdpEdit"
      DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If
  
    If IsNull(Forms!frmAdpEdit!ANumb) Then
      DoCmd.Close acForm, "frmAdpEdit"
        MsgBox "Recod Does Not Exist"
End If

This simply make a DOB seach on its own move to the MsgBox and Exit the sub.

I have attached an example to help.

Regards AOG

OMG, I can't believe you did this for me. What a xmas present. AOG123, God bless you I wish you the best.

Merry Christmas!!!!!