Search feature and updating text boxes

  • Thread starter Thread starter ian
  • Start date Start date
I

ian

Hi guys, i've got an aqward problem
I have made a form with 7 fields
Type
Code check
Description
Retail
Trade
In warehouse
Sold and undelivered
and below that i have a search box. What i want to do is to be able to type
any information into the search box, either a code, a bit of a description,
maybe even a price, and it will show the information i need in the boxes.

I have made some code for the search button, but i don't want it to make a
preview, which is what its doing, and anyway, it doesnt work, because the
preview is empty :P

Private Sub Searchclick_Click()
On Error GoTo Err_Searchclick_Click
If IsNull(Me!Search) Then
MsgBox "A valid code must " & _
"be entered.", _
vbExclamation, "Valid code needed"
Else
Me!Search.SetFocus
stTableName = "Stock3"
DoCmd.OpenTable stTableName, acPreview, strSearchtable

End If
Exit_Searchclick_Click:
Exit Sub

Err_Searchclick_Click:
MsgBox Err.Description
Resume Exit_Searchclick_Click

End Sub

Hope you guys understand what i need!

Many many thanks,

Ian.
 
Assuming that the user will key a Code into the search box
and is looking for an exact match, the following might
help get you started:

dim db as database, rs as recordset
set db=currentdb()
Set rs = db.OpenRecordset("Stock3", dbOpenDynaset)
rs.FindFirst ("Code='" & me!Search.Value & "'")
if NOT rs.eof them
... move data from current record to
... the fields on your form
else
msgbox "No Match"
endif
rs.Close
set db=nothing

But you said that you wanted to be able to search on any
fragment. You could build a complex search criteria with a
long string of "cond1 OR cond2 OR cond3" or you could
simply, apply the seach field to each possible column
using something like " LIKE '" & me!Search.Value & "%'"

and keep opening and closing the recordset until FindFirst
does not immediately return eof.
 
Back
Top