D
David Newmarch
I'm trying to build a database for a journal that I manage, and one task is
to handle transactions with subscription agencies who have their own
subclients. I accommodate this by having one table for PrimaryClients
related to another for SecondaryClients. This works fine for viewing and
editing client details, like entering a new agency or subclient, or when a
subscriber (subclient) switches from one agency to another. (I accommodate
direct subscribers by making each of them a subclient of themselves as
primary client). But I'm having difficulty trying to configure the way orders
are entered. A primary client (an agency) sometimes places an order that
includes all their subclients, and sometimes it’s just for selected
subclients. And an order can also be for more than one catalogue item.
I've structured my orders and order details tables and forms to try to
handle this.
My tblOrders has the following fields:
OrderID
OrderDate
PrimaryClientID
My tblOrderDetails has these fields:
OrderDetailsID
OrderID
SecondaryClientID
CatalogID
Quantity
Currency
UnitPrice, etc...
I have an Orders form with an OrderDetails subform, and the problem is with
the subform (which must be a continuous form - to easily view multiple
details for each order - and cannot therefore have a subsidiary subform). I
have one combo box on the subform for selecting catalogue items, and I need
another which is filtered to allow selection only of the subclients of the
primary client selected in the Orders form.
Where I’m stuck is that I can't figure out how to tie the subform
successfully to both the OrdersID field *and* the PrimaryClientID field. My
cboSecondaryClientID combo box on the subform shows *all* subclients and I’ve
so far been unsuccessful trying to filter it to the PrimaryClientID on the
Orders form.
There must be an obvious and simple solution. Please can somebody point me
to it!
I don't know enough about either SQL or VBA to tackle it on my own but I’m
very willing to learn!
to handle transactions with subscription agencies who have their own
subclients. I accommodate this by having one table for PrimaryClients
related to another for SecondaryClients. This works fine for viewing and
editing client details, like entering a new agency or subclient, or when a
subscriber (subclient) switches from one agency to another. (I accommodate
direct subscribers by making each of them a subclient of themselves as
primary client). But I'm having difficulty trying to configure the way orders
are entered. A primary client (an agency) sometimes places an order that
includes all their subclients, and sometimes it’s just for selected
subclients. And an order can also be for more than one catalogue item.
I've structured my orders and order details tables and forms to try to
handle this.
My tblOrders has the following fields:
OrderID
OrderDate
PrimaryClientID
My tblOrderDetails has these fields:
OrderDetailsID
OrderID
SecondaryClientID
CatalogID
Quantity
Currency
UnitPrice, etc...
I have an Orders form with an OrderDetails subform, and the problem is with
the subform (which must be a continuous form - to easily view multiple
details for each order - and cannot therefore have a subsidiary subform). I
have one combo box on the subform for selecting catalogue items, and I need
another which is filtered to allow selection only of the subclients of the
primary client selected in the Orders form.
Where I’m stuck is that I can't figure out how to tie the subform
successfully to both the OrdersID field *and* the PrimaryClientID field. My
cboSecondaryClientID combo box on the subform shows *all* subclients and I’ve
so far been unsuccessful trying to filter it to the PrimaryClientID on the
Orders form.
There must be an obvious and simple solution. Please can somebody point me
to it!
I don't know enough about either SQL or VBA to tackle it on my own but I’m
very willing to learn!