Continuous subform - possible to use different recordsource for lo

[markmarko]

Our company has installation billing codes that are either primary or
secondary. Every job must have 1 primary code, and may have 0, 1, or many
secondary codes.

I had originally set up the billing codes as a 'details' table, which
displays in a subform on my frmWorkOrders.

The control BillingCode uses a lookup which references a list of all billing
codes, and the list has a yes/no to indicate which codes are primary.

My question is this... Is it possible to have the subform only show primary
codes originally, then once a primary code is chosen, subsequent records in
the subform will only show lookups for secondary codes?

I think that if I change the lookup to only showing secondary codes, that
will mess with my original record which already would have a primary code in
it? Is this correct? Is there a way around it?

 

[Bob Quintal]

Our company has installation billing codes that are either primary
or secondary. Every job must have 1 primary code, and may have 0,
1, or many secondary codes.

I had originally set up the billing codes as a 'details' table,
which displays in a subform on my frmWorkOrders.

The control BillingCode uses a lookup which references a list of
all billing codes, and the list has a yes/no to indicate which
codes are primary.

My question is this... Is it possible to have the subform only
show primary codes originally, then once a primary code is chosen,
subsequent records in the subform will only show lookups for
secondary codes?

I think that if I change the lookup to only showing secondary
codes, that will mess with my original record which already would
have a primary code in it? Is this correct? Is there a way around
it?

Interesting problem.

Perhaps a workable solution is to move the primary code to a
different subform, or even to the main form, and set a filter on the
second subform to only show the secondary codes (if any). Both
subforms could be based on the one table. That way, the combo boxes
could be set for the relevant type, and your problem goes away.

You might also think of moving the primary code to the main table,
since it is a 1 to 1 relation with your main table, and the
secondaries have a 1 to many relationship.

 

[markmarko]

Thanks,

I was thinking about separate subforms, but I was thinking I would need
separate tables... Looks like I won't.

 

[Bob Quintal]

Thanks,

I was thinking about separate subforms, but I was thinking I would
need separate tables... Looks like I won't.

The only issue you might encounter is that having records in both the
primary snd secondary subforms that are under change at the same time
may generate a message that somebody else has changed the record.

Having code that forces a save when moving from one subform to the
other should fix this.