general design question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form called Vendor that displays general info on a vendor. I need
to create a search window with various search criteria for the user to use
such as Vendor_Nbr, Region, Zip Code in order to locate the record they want.
I am looking for some basic info/advice on the best way to go about creating
a search window - is there a preferred way to do this in access?

I know how to make forms, I have never done a search window before and
wasn't sure how to start. I know how to filter but thought a search window
might be more involved than filtering records. Any help in guiding me to a
basic start point would be great.

Thanks!
 
there are numerous ways to set up a search form. here's a solution i used
recently:

1. create a form bound to the Vendors table, to display the vendor records;
i'll call it frmVendors. set it up however you want, to display the data to
best advantage. this form will be used as a subform.
2. create an unbound form;, i'll call it frmSearch. on the form, put one
unbound control for each "criteria" you want to make available to the user.
for example, if you want to allow searches by Vendor_Nbr, Region, ZipCode
only - then add three controls. the controls may be any combination of
textbox, combobox, listbox. you make that decision for each criteria based
on whether you want the user to choose from a list of values, or enter
values "freehand", for the criteria. for the purpose of example, we'll say
that one of your controls is a textbox called txtVendorNbr.
3. add a subform control to frmSearch; i'll call it ChildVendors. set its'
SourceObject to the name of the form you created in Step 1 (frmVendors).
4. open frmVendors in design view, open the Properties box, click on the
RecordSource property, and click the Build button (...) at the right. add
the Vendors table to the query design window that opened up, and add the
fields you want the user to see in the form. add the following criteria to
the Vendor_Nbr field, as

Forms!frmSearch!txtVendorNbr Or Forms!frmSearch!txtVendorNbr Is Null

for each additional field that you want to add criteria to, use the same
syntax as above, changing the name of the control that you added to
frmSearch in Step 2.
close the design window and save the form.
5. back in frmSearch, add a command button; i'll call it cmdSearch. add the
following code to the button's Click event, as

Me!ChildVendors.Form.Requery

now the user can enter criteria in any (or all) of the controls on
frmSearch, then click the command button. Access will requery the subform
(frmVendors), and present only those records that match the given criteria.

hth
 
Perhaps, you might use the existing edit form.

Have you tired using query by forms?

While editing some data, you can go

records->filter->Filter by form

You then get a record in which you can type values into any of the fields
....and then search...

You can also put your cursor in any field...and hit ctrl-f to search

And, you can also put your cursor in ay field, such as sales rep, or
city..and hit the filter button (or records->filter-by selection).

There is probably a few more ways to search with the built in features that
I am forgetting right now.

So, try the above ways...as they are built in...and don't requite any code
writing skills...

However, if you want to jump in, and are comfortable writing code, you can
certainly consider building a search form. I have some ideas here

http://www.members.shaw.ca/AlbertKallal/Search/index.html

And, a few more screen shots here

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm


If you are going to code a form to search, then you can try the following:

Build a unbound form. Place a text box near the top. Lets call the control
txtLastName. Turn just about everything off for this form (record selector,
navigation buttons etc). This form is NOT attached to a table, nor any data
(we call this a un-bound form).

In addition to the above, you an build nice continues form that displays the
columns of data in a nice list. you can then put this form as a sub-form
into the above form.

Then, in the txtLastName after update event you simply stuff the results
into that sub-form.


dim strSql as string


strSql = "select * from tblCustomer where LastName like '" & me.txtLastName
& "*'"


me.MySubFormname.Form.RecordSource = strSql.

The above is how the seach form example screen shots work....
 
I got it to work! Thanks for your help! I have another question - hopefully
you get this - how can I allow the user to navigate to the record they would
like to select from the search results?

I have the results displayed in the subform - is there an easy navigation
that could let them double-click or click on a record and take them to that
record to be edited in the form I have already created?
 
if i understand correctly: your search form works, filtering the records in
the subform to match the criteria the user enters in one or more unbound
controls in the main form. now you want the user to be able to double click
on a record in the subform, and have a separate form open to that same
record.

it's easy enough. open your subform in design view, and add a private
function to the form's module, as

Private Function isOpenForm()

DoCmd.OpenForm "DataEntryFormName", , , _
"PrimaryKeyField = " & Me!PrimaryKeyField

End Function

in the above code, substitute the correct name of the data entry form, in
place of DataEntryFormName. presumably the Search subform AND the data entry
form are both based on the same data table; so for both instances of
PrimaryKeyField, substitute the correct name of that table's primary key
field. (read up on the DoCmd.OpenForm action, to understand how the WHERE
clause argument works.)

while still in the subform's design view, select all the bound controls -
textbox and combobox - at the same time. in the Properties box, go to the
DoubleClick event line, and type the following expression directly on the
line, as

=isOpenForm()

this is a quick way to assign this function to the event of all the controls
at one time. now the user can double click in any control of a record in the
subform, and the data entry form will open, filtered to that same record.

hth
 
Back
Top