Combo box controls second but alows entries on condition of first

[Andy G]

Hi everyone,

I have a combo box that selects from a table of DRUGS

ID (autonumber)
DRUG (Text)

second table called DRUGS_Sub (COMBO BOX ON FORM)
this table is to store drugs from selecting option (other) in parent
table DRUGS
ID (autonumber)
ID_DRUGS_FK
SUB (text)

Third Table to link (NEED TO UPDATE THIS TABLE FROM FORM)
CUST_X_DRUGS
ID_DRUGS_FK
ID_CUST_FK

CUST table
ID
ID_DRUGS_FK (M-M)
.... amongst other things

from looking at code avalable from your threads and links I've managed
to get the first combo to select items in second combo based on first
choice.

What I want to know is how in my form do I get the third table to fill
in cust ID when data entered in drugs etc I'm only storing the autoid.

So basically I want to store under CUST table a link to what drugs
they are using from my list and if they choose other then be able to
add another drug.
It would be nice to make this a multiple selection. to store and
provide statistics on.

(Limited programming experience C++)trying to understand VBA

Help of anykind muchly appreciated

Andy

 

[Pieter Wijnen]

This is how I normally solve that kind of problem.. (I always have a hidden
form called Global to store all kind of variables - as Public variables tend
to get lost with erronous code + the added benefit that I can use the same
queries for multiple forms when needed

assume
Table: CUSTOMER
Fld: CUSTOMERID (PK )
....

Table: DRUG
Fld : DRUGID (PK)

Table: CUSTOMERDRUG
Fld : CUSTOMERDRUGID (PK) (not really neccessary in this case - but
old customs ... + if you add some date tracking /logging... )
Fld : F_CUSTOMER (FK) (F_ for clarification : select your
own code standard - but stick to it to avoid extra work - what did I call
that field in that table again type of thing)
Fld : F_DRUG (FK)

make a form with 5 (significant) controls

1) combo Box for Customer
2) list box of all drugs (not linked to Customer)
3) List box of drugs linked to customer
4) Cmdbutton to add drugs
5) CmdButton to remove drugs (move it to an historical table?)

1) rowsource is trivial, add the CustomerID to Forms!global!CustomerID on
change or change query refs to form's name
2) RowSource: SELECT DRUGID,.... FROM DRUG A WHERE NOT EXISTS (SELECT 'X'
FROM CUSTOMERDRUG B WHERE B.F_DRUG = A.DRUGID AND
B.F_CUSTOMER=Forms!Global!CustomerID), set the multiselect property on &
play around with the .itemselected property for extra "sexyness"
3) rowsource. SELECT .... FROM DRUG A, CUSTOMERDRUG B WHERE A.DRUGID =
B.F_DRUG AND B.F_CUSTOMER=Forms!global!CustomerID (I tend to use "generic"
SQL whenever I can for portability)

4) basically an insert into CUSTOMERDRUG followed by a requery of the
listboxes
5) same as 4) only a DELETE FROM said table instead

HTH

--
Pieter Wijnen

When all else fail try:
http://www.mvps.org/access
http://www.granite.ab.ca

 

[Andy G]

Thanks for your prompt reply Pieter Wijnen,
will give this a try and let you know how I went.

Andy

 

[Pieter Wijnen]

all in a days work

Pieter

Thanks for your prompt reply Pieter Wijnen,
will give this a try and let you know how I went.

Andy


"Pieter Wijnen" <[email protected]> wrote in message