How to set different combo box values for new record?

[David Beaver]

A combo box is populated with values from a child table (and binds the ID of
the record from the child table to a foreign key). Some records in the child
table are marked Active, some are not. When viewing existing records in the
parent table, the combo box needs to be populated with all records from the
child table so users can view old records. But when adding a new record to
the parent, I only want to Active child values to be included in the combo.

Conceptually, it would be:

If parent record is new
set row source of combo box to "select ID, name from table where
active = yes"
else
set row source of combo box to "select ID, name from table"

I don't exactly know how to pull this off... how to a) test whether the
parent record is new, and b) where to put this code... on form load or form
open or ???

Thanks in advance.

 

[Marshall Barton]

A combo box is populated with values from a child table (and binds the ID of
the record from the child table to a foreign key). Some records in the child
table are marked Active, some are not. When viewing existing records in the
parent table, the combo box needs to be populated with all records from the
child table so users can view old records. But when adding a new record to
the parent, I only want to Active child values to be included in the combo.

Conceptually, it would be:

If parent record is new
set row source of combo box to "select ID, name from table where
active = yes"
else
set row source of combo box to "select ID, name from table"

Try using something like:
If Me.Newrecord Then 'Check for new record
Me.thecombo.RowSource = "select ID, name from table
where active = 'yes' "
Else 'existing record
Me.thecombo.RowSource = "select ID, name from table"
End If

If your user scenario is to commonly do a bunch of only new
record or only existing records, then you may want to get
more elborate to avoid resetting the combo's row source when
it's already set correctly.

 

[tina]

are you enforcing referential integrity on your parent/child table
relationships? i ask because 1) you should be, and 2) if you are, then it's
impossible to have any child records already existing when you are *adding a
new* parent record. also, normally the child record stores the primary key
value of the related parent record, as a foreign key. but the primary key
value of a *child* record isn't a foreign key to anything. your tables
structure sounds very confusing, or perhaps it's your form design that's
confusing - it's hard to tell.

hth

 

[Marshall Barton]

are you enforcing referential integrity on your parent/child table
relationships? i ask because 1) you should be, and 2) if you are, then it's
impossible to have any child records already existing when you are *adding a
new* parent record. also, normally the child record stores the primary key
value of the related parent record, as a foreign key. but the primary key
value of a *child* record isn't a foreign key to anything. your tables
structure sounds very confusing, or perhaps it's your form design that's
confusing - it's hard to tell.

I don't think so tina, at least not from what was posted.
The way I understand it, the combo box is used to select a
foreign key from a lookup table with active and inactive
data.

 

[tina]

maybe the op is just using incorrect terminology, Marsh. the first sentence
of the post says "A combo box is populated with values from a child table
(and binds the ID of
the record from the child table to a foreign key)." a lookup table is always
the parent table in a relationship, supplying definitions to one or more
fields in a child data table. as i said, very confusing. <shrugs and grins>

 

[Marshall Barton]

maybe the op is just using incorrect terminology, Marsh. the first sentence
of the post says "A combo box is populated with values from a child table
(and binds the ID of
the record from the child table to a foreign key)." a lookup table is always
the parent table in a relationship, supplying definitions to one or more
fields in a child data table. as i said, very confusing. <shrugs and grins>

No disagreement about confusing. I once tried to explain
parent/child in terms of outer joins to a "project manager"
and gave up by saying it was just a matter of which side you
were looking at that determined who was boss of the join??
She acted like a light bulb turned on and went away happy.

 

[tina]

LOL!

grins>


No disagreement about confusing. I once tried to explain
parent/child in terms of outer joins to a "project manager"
and gave up by saying it was just a matter of which side you
were looking at that determined who was boss of the join??
She acted like a light bulb turned on and went away happy.