Different RowSource for a Combo Box in a Datasheet for each row?

[Tony_VBACoder]

Is there a way with Access 2000, with a datasheet view of a subform, to have
a combo box have a different RowSource (this would be a Query) for each row
of data? The RowSource would change based on a value selected in another
column in that row.

For instance, if my Order Detail table has the following 2 columns:
1) ProductID
2) ProductOfferingID

When the user picks a ProductID, the ProductOfferingID's RowSource (query)
would change based on the ProductID selected. That way, the
ProductOfferingID would be based on the ProductID selected. I was thinking I
could code VBA code in the AfterUpdate event of the ProductID combo box to
change the RowSource of the ProductOfferingID combo box and do a Requery.
Then, in the OnCurrent event of the row, I would use the same logic so that
the ProductOfferingID combo box would be synched back up.

The reason for this is, the data for the ProductOfferingID comes from 3 or 4
different tables of data, and it is the ProductID that determines what table
I need to query. I initially thought I could create separate Queries for
each table that I would pull the data from, using the ProductID that the user
selected on the SubForm as the WHERE clause. But when I did this, something
strange happens on my SubForm as I move from record to record, or when I add
a new record; for each row in my SubForm, the ComboBox for my
ProductOfferingID field goes blank.

Is this even possible?

 

[Guest]

Re: Different RowSource for a Combo Box in a Datasheet for each row?

No, because Datasheets only actually have one row
(the current row), and all the other rows are just painted
on the screen -- they are just pictures of rows, they don't
have any RowSource property or any other properties.

could code VBA code in the AfterUpdate event

It is possible, but it works very badly - it means all the
other rows in the datasheet display wrong, which is not
what you want to see.


However, normally you can use a union query instead.

The reason for this is, the data for the ProductOfferingID
comes from 3 or 4 different tables of data,

So you need to start with a union query of those 4 tables.
If you are using a unique key which is unique across all
4 tables, that may be all you need

and it is the ProductID that determines what table

On the other hand, if you are not using a key which is unique
across all 4 tables, you need to include a table identifier
in your union query.

Do this by adding a 'calculated' field to each of the select
statements in your union query:
"Select ....., 1 as TableID ... from ...;"
or
"Select ....., "tablename" as TableID ... from ...;"

Save the union query. It is a query with all of your data,
plus a TableID field on each row.

If each table only has 1 productID, you can use the productID
as your TableID. If not, you need to create a mapping between
productID and TableID.

If you have a mapping table, just join the mapping table to
the union query. Create a new query, including the union
query, to do that. If you don't have a mapping table, consider
creating one.

If you have mapped to ProductID, you can now add a criteria
to your new query, referring to
forms!myformname!ProductIDcboName

If you don't have that mapping, you can use a vba function
to do it. Add
MyMappingFunction(forms!myformname!ProductIDcboName)
as a field in your query.
In a VBA module, create MyMappingFunction( PID), a
vba function which takes a product ID, and returns TableID,
to match the TableID in your union query.

Of course, the way to avoid this is not to have 3 or 4 tables.
This is part of why it is better to just have on table, with an
ID field to identify the different sections.

(david)