Using the same form for entry and query

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

Hello,
I have a form based upon a table for data entry. This
form is used to enter quotes to our customers. Alot of
times, we quote the same project to multiple customers
and so what I would like to do is use the form also as a
query form. IE when the user press a button marked
search, it clears all the fields on the form and allows
them to look up a value entered into one of the empty
fields and display the related records in the form. I
used the wizard to create a "findrecord" button but that
only brings up the built-in Find/Replace form. This would
be too confusing for my coworkers. I have tried many
approaches to this problem in an attempt to find a
solution including making a seperate form that is
accessed when the "Search" button is pressed.
Unfortunately, this displays the results in Datasheet
view, which is not feasible for data entry. Any help with
this would be greatly appreciated. Thanks so Much!!
 
I recommend that you take a look at using combo boxes for searching. If
you have a limited number of items you search for (like a 100 or so) a
combo will be quite fast and its automatic lookup will be very intuitive
to use.
Otherwise, you can use an unbound text box and search the form in code,
placing something like this in the text box AfterUpdate event:

'Look for the search string using a "hidden" recordset of the form.
Me.Form.RecordsetClone.FindFirst "SearchField = '" &
Me.txtSearchCriteria & "'"
if Me.Form.RecordsetClone.NoMatch then
msgbox "No match found"
else
'If string was found, move the "real" recordset to that position.
'This will bring up that record in the actual form.
Me.Form.Recordset.Bookmark = Me.Form.RecordsetClone.Bookmark
end if

where you will replace SearchField with the name of the field to search
and Me.txtSearchCriteria with the name of the text box you enter search
string into.
Good luck,
Pavel
 
Back
Top