Simple (?) combo-box problem

  • Thread starter Thread starter Ian Chappel
  • Start date Start date
I

Ian Chappel

I have a sub-form based on tblDetails (many), which has a combo-box to
Lookup values from tblCost (one). No problem.

But I want only Costs to be listed in the combo-box which are for a
specific job. The Jobs are listed in another table which is on the "one" end
of a relationship to Costs on the "many" end. So I will need to be able to
enter a Job in a (unbound?) combo-box to enable only the correctly-filtered
data to be listed in the Costs combo-box.

I don't really want to store the Job value, as the Cost will define the
specific Job. When I use the wizard to create a form including Jobs and
Costs, I am unable to enter any data, which doesn't surprise me, as I could
enter an incompatible Cost/Job.

Any ideas?
 
So, you have 3 tables:
- tblCost (one record for each cost category)
- tblJob (one record for each job)
- tblDetails (a record for each Cost in a Job.)

The subform is bound to tblDetail.
The main form is bound to tblJob?
And you want to use an unbound combo to filter the main form so it contains
only those jobs that have a particular Cost in the subform?

If that's the goal, see:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html
The article describes how to set dynamically the RecordSource of the main
form so that it contains only the jobs that have a matching cost in
tblDetails.
 
Sorry, I've not explained too well. Your understanding is correct re. the 3
tables and the subform, but no further.

The Main form is bound to tblHeaders, which has a one to many relationship
with tblDetails - I think the main form is in fact irrelevant to what I'm
asking here.

What I'm asking is all related to inputting one line in the subform. I want
to select a Job, and then be able to choose from Costs which belong only to
that Job. However, I do not need to record the Job in tblDetails, only the
Cost which will in effect specify the Job.
 
I don't understand the relationship between the headers and the details, and
therefore what details are actually loaded into the subform, and therefore
how you need to find those details.

Neither do I understand the relationship between details and costs.

It sounds like you want to use an unbound text box in the form header
section of a continuous subform, and use it to filter the subform so it only
shows the costs associated with one job.
So one job has many costs?
And each line of tblDetails represents one cost of the job?
But the main form is not an issue, so the subform has all the costs for all
jobs loaded anyway?
In which case you could just filter the subform for the Job number?
Or there is no Job number field (as a foreign key) in tblDetails so you
can't apply that kind of filter?
 
Okay, a simple solution would be to create a query that combines the Details
and Costs tables, and use this as the source for your subform. This gives
you the JobID as a field in the subform, and you can then filter on JobID.

(If you run into problems with trying to enter a new record in this subform,
remove the DefaultValue property from all fields in the Costs table.)

If one invoice can cover multiple jobs, this approach would still not show
ALL the Costs for the different jobs, because only the details for one
header are loaded into the subform. The article suggested earlier would then
address this issue.
 
Back
Top