combo box problem

[E.Q.]

I'm working on a database to track assigned goals. It almost works, but I'm
having trouble figuring out why one combo box doesn't work.
Pertinent tables (and fields): tblGoalComps (lngGCID -primary key-,
chrGoalCompName), tblObjectives (lngObjID-primary key-,lngGCID -related to
tblGoalComps -,chrObjName), and tblGoalLog (lngLogID - primary key -,lngGCID,
lngObjID-related to tblObjectives,dtmEventDate,chrDescr)
I used the form wizard to create frmGoalLog based on tblGoalLog. Combo box
cboGCID is populated by a query to select distinct records for lngGCID. The
combo box has two columns looking at lngObjID and chrGoalCompName bound to
column 1. This combo box seems to work OK. Combo box cboObjID is populated
by the following SQL:
Select Disting tblObjectives.ObjID,tblObjectivw.chrObjName FROM
tblObjectives WHERE tblObjectives.lngGCID = forms!frmGoalLog!cboGCID.
The idea is to enter a goal in the first combo box and get the associated
objectives to pop into the second combo box. What I usually get is the second
combo box is populated by the objectives associated with the goal in the
previous record.
I've tried to hit F9 after changing the first combo box, that generates an
error message "You cannot add or change a record because a related record is
required in tblObjectives." I've placed Me.Refresh in VBA associated with a
few Events and get the same error message along with the error cod 3201.
I've also tried DoCmd.Requery with GotFocus and got a different error "Run
Time Error 2109. There is no field named '17' in the current record."
I can get the form to work if after entering the desired goal in cboGCID, I
accept one of the objectives listed in cboObjID - even though that objective
isn't related to the goal in the first combo box. I then hit F9 and the
proper objectives appear in cboObjID.
The fact that it works eventually makes me think that i'm close to getting
it correct, but I can't figure out what else to try. (Also, I'd like to share
this with coworkers and I'd like the work flow to not include the step of
accepting an incorrect objective.)
Any help will be appreciated.
Peace.
E.Q.

 

[Damian S]

Hi E.Q.

Generally for cascading combo boxes you have an After Update event in one
combo box to call a requery to the next combo box.

eg: me.NEXTCOMBO.requery in the after update event of FIRSTCOMBO

Hope this helps.

Damian.

 

[Ken Sheridan]

Damien is right about the need to requery the objectives combo box in the
AfterUpdate even procedure of the goals combo box. However, your tblGoalLog
table is not fully normalized as it contains the redundant lngGCID column.
The value in this column is implied by the value in the value in the lngObjID
column. By having both the table is open to the risk of update anomalies as
an invalid objective can be entered for a goal.

The solution is to delete the lngGCID column form the tblGoalLog table and
use an unbound combo box on the form. The objectives combo box remains bound
as now and operates in the same way. However, you do have to assign a value
to the unbound goals combo box in the form's Current event procedure so that
it shows the correct goal for the objective in any existing record.

I posted a demo file of how to handle this sort of data with correlated
combo boxes in both single and continuous form view at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


Its slightly more complex than your scenario as there are three levels to
the hierarchy (the English local administrative units of County, District and
Parish) rather than your two, but the principle is the same.

Ken Sheridan
Stafford, England

 

[E.Q.]

Thank you both for answering this question. The link Ken provided showed
beautifully how to approach this type of structure. (It also gave me a reason
to lookup DLookup on the help menu; I'd never used it before, so I've
actually gained a couple new tools here.)
Thank you and Have a Peaceful Day.
E.Q.