I am tryingto create a database in which the form is very specific

[Guest]

I need the form to react in this manner.

First: I have 3 tables: Region, City, Event.
There are 11 regions, and multiple cities for each region. In the form, I
need the user to pick a region then have a set of corresponding sities for
that region and only that region.

Next: the user picks a city. i.e. (Bay Area Region) then choose from the
following cities. (Alameda, Contra Costa, Marin, San Fran, San Mateo, and
Santa Cruz).

Next: the user picks a city. Then they need to fill out the event
information. I have made sure that no information is duplicated and the
tables are as lean as posibble. I can make the table collect the data the way
I wnat them to, but when I start building my form I just can't make the
information appear the way I need it to.

Tables are as follows:

Campaign Region Table
ID
Campaign Region (listed regions within a record)

City
ID
City (listed cities within a record)
Campaign Region (used lookup wizard to link)

Event
ID
City (used lookup wizard to link)
Number of events (used lookup wizard and created own list. 1st, 2nd...)
Month (used lookup wizard and created own list. Jan., Feb...)
Name of event
Event sponsor
Attendance
Individuals reached

Form:

User choose from drop down box (Region)
User choose from drop down box (cooresponding city to region)
User choose from drop down box (number of event)
User choose from drop down box (month)
User enter (name of event)
User enter (event sponsor)
User enter (attendance i.e. 100)
User enter (individuals rached i.e. 50)
callculator (developped in a query, indv. reached x 3) nothing entered.

how do I make this work?????????????????????????????

 

[Guest]

Elizabeth:
You need three combo boxes Region, City and Event. When you select the
event you open a form that allows data entry as you note is required.

Here is how to do it.
Create an unbound form with three Combo boxes on it. Combo Region,
ComboCity, ComboEvent
Combo box ComboRegion lists your regions from the <Capaign Region Table>
Combo box ComboCity Lists Cities from the <City> table but, here is the
secret. In the Query that determins the data to show on this combo box enter
code as follows in the 'Criteria' row of the Query in the Region Column

=forms![Form0].ComboRegion.Value

Similarly, ComboEvent will have two entries like this that define both the
Region Column and the City Column.

Secret# 2 is that on each afterupdate event you will have to requery to get
the combos to show the correct value list.


Good luck.
Jim
jim at andersonsoftware dot ca