coding a form using drop down menus to limit choices

  • Thread starter Thread starter Mike H
  • Start date Start date
M

Mike H

Hello -

I had posted this in "getting started" but after discovering this
group I feel this would probably be better suited to ask here. I have
what I think is fairly simple task. I'll break it into two
parts:

The first task involves using a dropdown menu to chose a value. After
that, the user can choose one of 6 queries that pertain to that value.
(for example, say you had "Supplier" as the main value, and "Sales
Manager", "Customer Service", "President" for sub values on buttons
that take you to a query for each sub value).

I need to know how to tell Access to only look up a "Customer
Service", for example, that only pertains to the first value chosen.

Secondly, in a related task, I need to be able to add new records for
the sub values of any given value (say, a new "President" record for
"Supplier X") by filtering with a drop down for the main value, then a
second drop down for which sub value this new record is going into.

Finally, is there an easy way to prompt access to export everything
into an excel file (or at least csv / text delimited)?


You can view a screen shot of my general layout here. I sincerely
apprecate any and all help you can give me!
http://www.azheads.com/images/access_ss_june0704.jpg
 
I don't usually answer cross-posted questions. Like a lot of the people who answer questions here, I'm a freelance developer and while I don't mind donating some of my time to help other Access users, I don't want to spend my time answering questions that have already been answered elsewhere. But, just having been on the Getting Started list, it hasn't been

What you want to do is make sure you have your categories - major and minor (sub) - in a table so you can use the table as the source for your Combo instead of typing them in manually

Let's say you have tblCategories with fields for the MajorCat and SubCat

The RowSource of your Combo for major categories (cboCats) will be: "SELECT [tblCategories].[MajorCat] FROM tblCategories
The RowSource of your Combo for sub-categories (cboSubCats) will be: "SELECT [tblCategories].[SubCat] FROM tblCategories

What you want to do is, in the AfterUpdate event of cboCats, rebuild the RowSource string/query for cboSubCats to filter it for only the main category and the requery it. The code should look something like this

' declare variable
Dim strCat as Strin
Dim strSource as Strin
' define main categor
strCat = [cboCats
' build new query for sub cat comb
strSource = "SELECT [tblCategories].[SubCat] FROM tblCategories WHERE [tblCategories].[MajorCat]='" & strCat & "'
' filter sub cat combo for selected main ca
cboSubCats.RowSource = strSourc
cboSubCats.Requer

Build a form in the database for adding new records to tblCategories

To Export, look at the TransferSpreadsheet action (for macros) or method (for code)

Last but far from least - don't be afraid (or too lazy) to look up Help files. You can get a lot of questions answered and code examples from them

Hope this helps

Howard Brod


----- Mike H wrote: ----

Hello -

I had posted this in "getting started" but after discovering thi
group I feel this would probably be better suited to ask here. I hav
what I think is fairly simple task. I'll break it into tw
parts

The first task involves using a dropdown menu to chose a value. Afte
that, the user can choose one of 6 queries that pertain to that value
(for example, say you had "Supplier" as the main value, and "Sale
Manager", "Customer Service", "President" for sub values on button
that take you to a query for each sub value)

I need to know how to tell Access to only look up a "Custome
Service", for example, that only pertains to the first value chosen

Secondly, in a related task, I need to be able to add new records fo
the sub values of any given value (say, a new "President" record fo
"Supplier X") by filtering with a drop down for the main value, then
second drop down for which sub value this new record is going into

Finally, is there an easy way to prompt access to export everythin
into an excel file (or at least csv / text delimited)


You can view a screen shot of my general layout here. I sincerel
apprecate any and all help you can give me
http://www.azheads.com/images/access_ss_june0704.jp
 
Back
Top