LOOK-UP LIST BASED ON PREVIOUS CHOICE

[MARGIE]

I would like to make a field in a table that allowed only
choices from a list, and that list be based on the choice
from another list field. I have submitted this question
before and the answer I got (from Graham)didn't work
(probably me, I am lost!).

Here is what I tried:

Table Weed:

ID (text) WeedName (text) *PKey is ID
1 Musk Thistle
2 Spotted Knapweed

Table Agent:

ID(text) AgentName (text) *PKey is ID
1 Black Bug
2 Brown Bug
3 Yellow Bug
4 Red Bug

Table JointWeedAgent:

WeedID(text) AgentID(text)*PKey is both WeedID and AgentID
1 1
1 2
2 3
2 4

Table Release Data:

ID - AutoNumber
Date - Date
Site Name - Text
Target Weed - Look-up from Weed.WeedName
Number Released - Number
Agent - Text


Query qryWeedAgent:

Agent.ID, Agent.AgentName, Weed.WeedID

Then I made a form from the DATA table including all
fields. In design mode, I entered this code in the
AfterUpdate event procedure of Target Weed:

cboSelectAgent.RowSource= "Select * from qryWeedAgent where
WeedID=" _ & Nz(cboSelectWeed,0)

When I enter the form and choose a Weed name from the list,
I am sent to the debugger and it says "Syntax error -
Invalid Character" and highlights the "_". If I delete it,
I get "expected object" after the "&".

If anyone can help I would much appreciate any help. I am
only partially skilled in Access, and Visual Basic is
foreign to me ... so please explain as if I were a 10 year old!

Thanks!!!!

 

[John Vinson]

I would like to make a field in a table that allowed only
choices from a list, and that list be based on the choice
from another list field.

As far as I know, YOU CANNOT.

The use of drop-down lists ("combo boxes" properly) in a Table is, in
my opinion, a VERY bad idea. You should *not* be using table
datasheets for data entry; they are intended for design and debugging
ONLY, and do not have the flexibility that you need in order to do
this.

However, this is a perfectly routine procedure when you are using the
proper tool to enter data into tables - a Form. Create a Form based on
your table and put the combo box controls on that Form. See an example
with code at

http://support.microsoft.com/default.aspx?scid=kb;en-us;289670

or go to http://www.mvps.org/access and search for "dependent combo"
for another way to do this.