Search and Display

[Guest]

I'm semi-new to Acces and I am using Access 2003, I've been working for some
time on trying to display the results from a search in a subform, in
datasheet view. I thought I had this accomplished until I hit a record with
multiple results, it seems to show the first record that is found and the
very next record, whether it matches the criteria or not, it also seems to
randomly choose the number of records it's willing to display, sometimes I
get two sometimes 3. I'm just wondering if there is a way to show only the
results that are relevant, and display multiple results if there are any.
Thanks a lot for your time.

 

[Guest]

The method you use depends on the complexity of the search operation. If
it’s a simple one or two field search, say Product and City to find all
suppliers in a particular city, then you just need two combo boxes on the
unbound parent form listing the Product and Cities, but whose values are the
key ProductID and CityID columns (otherwise you can end up confusing two
cities with the same name, so the Cities combo box would probably list City
and State). The combo box wizard can set this up for you so the key
ProductID and CityID columns are hidden, or we can talk you through
hand-crafting it (its pretty simple to do). With this simple scenario the
ListMasterFields property of the subform control would be:

cboProduct;cboCity

where these are the names of the combo boxes; and the LinkChildFields
property would be:

ProductID;CityID

these being the names of the fields in the subform's underlying recordset.
When selections are made in the combo boxes the subform will show just those
rows which match.

With a more complex search scenario, involving optional search parameters or
substring searches for instance, you can either set the RecordSource property
of the subform control's underlying form in the parent form's module on the
basis of the choices made, or use a query with parameters which reference the
controls on parent form, testing for Nulls to make them optional, and then
requery the subform to show the matching records.

If you could post back with a more detailed description of your requirements
we should be able to advise you more specifically.

Ken Sheridan
Stafford, England

 

[Guest]

Well I'm in a banking environment and got kind of roped into making a fraud
reporting database because I happen to be access certified, now this was back
in college and it's been a great while since I've had to do anything at all
with access. They gave me an example spreadsheet, and the sample word
documents they've used in the past to gather and record information. I've
gone and built the forms for the data entry that the tellers and managers in
the branches would be using to input data, now I need to build forms for
reporting for the people behind the scenes.

By the end of this I am going to need to be able to search by every field,
take the results and export them to tables, or excel spreadsheets for
printing and archiving, being able to sort records by amount lost, if we
managed to prevent the fraud, and probably a slew of other things, all done
from within a form so that it's as easy as possible on those who have to use
it. I'm trying to get the basics built for a meeting I have actually tomorrow
morning, what I have already is probably satisfactory, but I'd like to have
more. I'm going to be taking some of this home with me obviously, so I do
have time, and I plan to spend a lot of time researching and learning on my
own, but it helps to have people who know what they're doing for reference.

It's a very big project... Thanks again.

 

[Guest]

I managed to figure out why I was getting more than one result while the
others seemed to be irrelevant, I was using the same table for both the Main
Form and SubForm, I'm still messing around trying to figure out how to get
multiple results to show with a search. I've got an actual search button
built that compares the string in txtSearch to a certain field in the table
just as a basic trial and error approach. I'll be spending a lot of time here
over the next few weeks, I can just tell.