Overwhelmed, Just Started, Quick Question

  • Thread starter Thread starter AccessNoob27
  • Start date Start date
A

AccessNoob27

Hey, I just opened Access (2003) for the first time ever today. I'm trying to
get through the tutorials, but it's a lot to take in. Could someone explain
how I could do the following in the simplest (read: newb-friendly) way
possible? Appreciate it.

I want to create a form that has a few combo boxes which pull options from a
single table. I want a subsequent combo box to change according to the option
the user pics on the first combo box. Then I want a text box to populate from
a table based on the preceeding choices.

I.e.:
Combo Box 1 pulls from Table_Meals and lists the options: "Breakfast, Lunch,
Dinner"
*User selects Breakfast*
Combo Box 2 is then populated from options from Table_Breakfast and lists:
"Cereal, Eggs, Pancakes"
*User selects Eggs*
Text Box is then populated with "To make eggs, crack over frying pan..."

Any help anyone can provide would be greatly appreciated!
 
You're prematurely thinking about forms! You need to think about tables
first and get them right. Just from your post it is obvious you haven't
gotten them right yet. You can be helped by this newsgroup to get your
tables correct. Post the details of what you want the database to do and get
help on setting up the correct tables.

Steve
(e-mail address removed)
 
AccessNoob27,

Well, you have moved right on to forms without telling us anything about the
tables... It would help to know your table(s) set up. As for your
question, sounds like you are looking for cascading combos, here's a
sample...

http://www.fontstuff.com/access/acctut10.htm

However, yu said you have a single table which doesn't quite sound correct
based on what you want our combo boxes to do. It also sounds like you might
be using look-up fileds in tables. Here's why that is not a good idea...

http://www.mvps.org/access/lookupfields.htm

It also indicates you might be committing spreadsheet with Access, which is
never a good thing. Here's some stuff to help you understand Relational
Database Design...

Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page...
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials

Sample data models...
http://www.databasedev.co.uk/table-of-contents.html

That should be enough to get you started...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hey, I just opened Access (2003) for the first time ever today. I'm trying
to
get through the tutorials, but it's a lot to take in. Could someone explain
how I could do the following in the simplest (read: newb-friendly) way
possible? Appreciate it.

I want to create a form that has a few combo boxes which pull options from a
single table. I want a subsequent combo box to change according to the
option
the user pics on the first combo box. Then I want a text box to populate
from
a table based on the preceeding choices.

I.e.:
Combo Box 1 pulls from Table_Meals and lists the options: "Breakfast, Lunch,
Dinner"
*User selects Breakfast*
Combo Box 2 is then populated from options from Table_Breakfast and lists:
"Cereal, Eggs, Pancakes"
*User selects Eggs*
Text Box is then populated with "To make eggs, crack over frying pan..."

Any help anyone can provide would be greatly appreciated!
 
Here are Detailed ramblings from a fellow Newbie..

Like steve said the most important aspect about access is properly
"Normalized tables" and your example with food seemed like you have partially
correct mindset with the exception of the fact that i like eggs for dinner So
you would not want a table for each meal type instead use a junction table so
that many recipes can be offered at many different times..

your example might start with the following Tables..

Table_MealTimes (meal times table)
field 1 MealTimeID (PK) Autonumber PK is primary Key lots of people seem
to agree autonumber is a good way for beginners to start with
field 2 MealTime text (breakfast, lunch, dinner ect.)

Table_FoodItems
Field 1 FoodItemID (PK) AutoNumber
FoodItem Text
HowtocookItem Memo

and your junction table

Table_MealJunction
Field 1 MealTimeID (FK) Number FK is a fvoreign key meaning it represents a
Primary key in another table
Field 2 RecipeID (FK) Number


ok Now your form

A combobox has a lot of stuff going on depending on how it is setup It may
display a value like Breakfast but the Combo's Bound Column may actually
equal 3
which would be the Autonumber (PK) for the MealTime's table record for
Breakfast.. but the (PK) column is hidden from view by setting its width to 0"
Combos have a "Control source" (where the data is stored) and a "row source"
(the data that is in the combos list) these will be unbound combos so the
Control sources will be left blank..

Combo 0 would have a row source of your mealtimes table like this
SELECT [MealTimes].[MealTimeID], [MealTimes].[MealTime] FROM MealTimes;
set bound column to 1, column count to 2 and column widths to 0";1"
so if breakfast is selected the combos value would actually be [mealTimeID]

Combo 1 would have a row source like this
SELECT FoodItems.FoodItemID, FoodItems.FoodItem, FoodItems.howtocook FROM
FoodItems INNER JOIN JunctionTBL ON
FoodItems.FoodItemID=JunctionTBL.FoodItemID WHERE
(((JunctionTBL.MealTimeID)=[Forms]![Form1]![Combo0]));
set bound column to 1, column count to 3 and column widths to 0";1";0"
so we can throw in the How to cook Info

notice the junction and food items have an inner join on foodItemID and a
WHERE statement so the displayed records must have a match between the
junctiontables MealTimeID and the Combo 0's selection..

Now lets add a unbound textbox to display your cooking instructions..
call it HowToText

Ok now that the combos and text box are set up we need to add Code to do
what we want..
this will be done in each combos AfterUpdate event so when we change the
value of the combo then move on or hit enter the AfterUpdate event is fired
and our code runs...

Click Combo 0's event tab and in the Build ... box select Code builder and
the VBA Editor will open and the code will be like this.
Note: when you use a 'Apostrophe in code it remains as a comment and does
not affect the procedure

'******Copy Code From Here *********
Private Sub Combo0_AfterUpdate() 'after we pick a mealtime requery combo1
Me!Combo1.Requery
End Sub
'When a meal time is selected the records in the fooditems Combo0 will be
adjusted

Private Sub Combo1_AfterUpdate() 'after we pick a food item
Me!HowToText = Me!Combo1.Column(2)
End Sub
'When we pick a food item the HowToText Box will be populated with the
'Text in Combo1's 3rd (not Visible) column (actually Column(2))
'*********to Here **********

Im sorry if this seems goofy but i am also new at this and i Hope this helps
get you going..

If it does please check it as helpfull

Barry
 
Back
Top