SEARCH FORM 1

[waymond]

I have a table with item# Description, I need to create a form that allows the user with unbound text boxes to search for either the item# description or have the option for all

Thank You
Waymond

 

[ChopinFan]

You can do it a number of ways. One of the easiest is to use DoCmd.ApplyFilter "qryQueryName", where the query is a separate database object (not just sql in the form module). The query then refers to the unbound combobox on the form as a criteria to determine which record the users wants to find. Just remember to have a feature to remove the filter when the user is done (DoCmd.ShowAllRecords and then DoCmd.Requery). Otherwise they won't be able to go to any other records!

Hope that helps.

 

[kjv1611]

So, you just want to be able to search with one text box or with all. You can do it a couple of different ways.

1.) You can have 2 buttons: one that says "search all", and one that says "search by Item#" And then return accordingly...

Private Sub cmdSearchAll_Click()
  DoCmd.OpenTable "tblItemDetail"
End Sub

Private Sub cmdSearchItem_Click()
  Dim strSQL As String
  strSQL = "SELECT * " & _
           "INTO tblResults " & _
           "FROM tblItemDetail " & _
           "WHERE Item# = '" & txtItemNumber & "'"
  DoCmd.RunSQL
  DoCmd.OpenTable "tblResults"
  strSQL = vbNullString
End Sub

2.) You can have one "Search" button that either returns "All" or thsoe that match the item detail, according to what is in the text box.

Something like:

Private Sub cmdSearch_Click()
  If IsNull(txtItem) Then
    DoCmd.OpenTable "tblItemDetail"
  Else
    Dim strSQL As String
    strSQL = "SELECT * " & _
             "INTO tblResults " & _
             "FROM tblItemDetail " & _
             "WHERE Item# = '" & txtItemNumber & "'"
    DoCmd.RunSQL
    DoCmd.OpenTable "tblResults"
    strSQL = vbNullString
  End If
End Sub

Now, of course, you'll need to "tweak" all of that to suit your needs. For instance, with this particular method, you'll need to "DROP" the results table with it's no longer needed, or else at the next time the button is pressed, before the new table is created. Also, you probably would be best setting the recordsource of a subform or just some additional text boxes to the values in the table you return, whether all or the ones matching the item #, so that you do not allow the end user to change the original data.