Help with searching

[Craig]

I am having my first go at building a database and need some help with using
search functions. My database has around 600 records, each of which describe
an inventory item. These items are split into groups such as pedestrian
mowers, mini tractors, chemical equipment etc. There is another field which
lists the unique inventory number for each item.
What i would like to do is to have two drop down menus. The first would list
all the groups. By selecting the group you wanted it would then bring up all
the relevant inventory numbers. Ideally by selecting the correct inventory
number it would bring up the record for that item.

Can someone just explain how to do this.

Thanks in advance.

 

[Conan Kelly]

Craig,

I'm no expert, but the only way that I know of doing this is by using a
form, and with what you want to do, you'll want a subform too. I don't have
a lot of experience in this area, but I have done this type of thing once
before. If I remember correctly, you can base your form off of a table or
query. If using a query, set up your query without any filtering. Create
your form and subform off of the table/query. On the sub form, you'll want
2 combo boxes. The source for the first combo box will be a SQL statement
that will be a Group By/Totals query on the Groups field, then you would
have code in the On Change or On Lost Focus events that will construct a SQL
statement for the source of the next combo box. The SQL statement will be a
basic select query (not a Group By/Totals) that will be filtered by the
group chosen in the first combo box. Then you will set your filter/source
in the sub query to display the record for the item selected in the 2nd
combo box.

If you have no prior training in Access or VBA (no classes or anything of
the sort), this is probably going to be a long difficult venture. How much
experience/knowledge do you have with Access?

Also, it has been a while since I've worked with forms or worked with VBA in
Access when it comes to forms (I work with VBA in XL all the time), but I
have successfully done what you are trying to do once before and I have an
example at home. If you want help with this, then I will have to look
through my stuff at home and refresh my memory on how to do things. This
will take several days to accomplish this kinda thing, especially through
posts on newsgroups. But I'm glad to try to help in any way I can.

HTH,

Conan

 

[Craig]

Hi Conan,

Similar situation to me. I used Access years ago and i've forgotten most of
it.

Basically all i want to do is for our workshop guys to be able to locate a
particular inventory item in the easiest way. If there's another way of doing
it such as entering the inventory number which brings up the relevant record
that would be fine as well.

Thanks
Craig