Allow data input in a subform connected to two comboboxes

[Webmaster]

Hi everyone,

Input data to a subform which shows records filtered
by a combobox and input data in it is no problem.

If I have the following situation:
table1 table2 table3
ID1 -----> ID1
ID3 <----ID3
FIELD11 FIELD21 FIELD31
FIELD12 FIELD22 FIELD32

I face the problem that I cant choose an
ID1 by combobox1, then ID3 by combobox2
and then input data in table2, field21....
(thru a query or subform) so that the new
record contains the IDs from
table 1 and 3.

Any help appreciated in advance

Michael

 

[John Vinson]

Hi everyone,

Input data to a subform which shows records filtered
by a combobox and input data in it is no problem.

If I have the following situation:
table1 table2 table3
ID1 -----> ID1
ID3 <----ID3
FIELD11 FIELD21 FIELD31
FIELD12 FIELD22 FIELD32

I face the problem that I cant choose an
ID1 by combobox1, then ID3 by combobox2
and then input data in table2, field21....
(thru a query or subform) so that the new
record contains the IDs from
table 1 and 3.

Normally what you would do here is have a Form based on Table1, with a
Subform based on Table2; use ID1 as the master/child link field so
that new records on the subform automatically inherit the parent
table's ID. A Combo Box on the subform, based on Table3 and bound to
ID3, lets you insert the link to Table3. The other fields in Table2
should be then available for editing.

If this is what you've tried and it's not working post back with more
details!

 

[Webmaster]

Thank You very much John,

perhaps I described my problem not exactly:

The two comboboxes should work as a
filter for already existing records in table2 which
meet the criteria given in the comboboxes.

In addition it should be possible to add new records
which then also have the chooses IDs from table 1 and 3.

I think, Your proposal solves the second part, but doesn't
the first part. The subform will show only records
with one ID1 but with different ID3.

As I want to make calculations visible to the user in the
subform (f.e. sum up a filed PERCENT so that the sum is 100%)
I need also the filter-function for ID3.

Anyhow thank You for Your help.

 

[John Vinson]

Thank You very much John,

perhaps I described my problem not exactly:

The two comboboxes should work as a
filter for already existing records in table2 which
meet the criteria given in the comboboxes.

Sorry! I did misinterpret. In that case I'd just use a Form based on
table2 without reference to table3 at all; you can have two unbound
combo boxes (say in the form's Header) with code in the combos'
AfterUpdate events to filter the form to that single record.

As I want to make calculations visible to the user in the
subform (f.e. sum up a filed PERCENT so that the sum is 100%)
I need also the filter-function for ID3.

Now this I simply don't understand. What are you calculating? What's
ID3?

 

[Webmaster]

Thank You very much John,

for Your help as well as Your interest.

Now this I simply don't understand. What are you calculating? What's
ID3?

I didn't want to bother anyone with too much detail, but here
some explanations what I'm trying to do:
Table1 contains records with counters for different medias
(f.e. electricity counters, water counters and so on).
Table3 contains records of splitting criteria, that means
different consumer units of the medias. This could be
buildings, firms, centers and so on.
In table2 I want to give the user the possibility to split
the consumption of each counter to the different consumer units.
This should be possible for every kind of criteria so
that the sum for buildings is 100% for centers 100% .......
So the table will look like:
CounterID splittingcriteria unit Percentage
counter123 building building1 30
counter123 building building2 45
counter123 building building3 25
counter123 center center1 15
counter123 center center2 50
counter123 center center3 35
counter456 center centerx 25
counter456 center centery 40
counter456 center centerz 35

Combobox1 should filter the counter.
Combobox2 should filter the kind of splitting criteria (f.e. buildings)
Now the user can spread the 100% into shares for the choosen counter
and he should see after each record-input the running sum
and what he has left to split.
counter123 building building1 30
counter123 building building2 45
counter123 building building3
75
(left 25)

At the end of the day (perhaps already in this live)
every unit will get his "Report of consumption" over all
counters and all medias.

Michael

 

[John Vinson]

Combobox1 should filter the counter.
Combobox2 should filter the kind of splitting criteria (f.e. buildings)
Now the user can spread the 100% into shares for the choosen counter
and he should see after each record-input the running sum
and what he has left to split.
counter123 building building1 30
counter123 building building2 45
counter123 building building3
75
(left 25)

You can use a Subform based on this table; the mainform could be
unbound or bound to either the counter table or the building table.

If it's unbound, use

cboCounter;cboSplitting

as the Master Link Field, and CounterID;SplitID as the Child Link
Field (using your own control and fieldnames of course). Set it up as
a Continuous Form, and use a Long Integer (NOT a Single or Double,
roundoff error will cause problems!) for the percentage.

On the subform's Footer put a textbox with Control Source

=Sum([percent])

to get the running sum.