Multiple conditional value list referencing same table

January 13, 201015 yr

Newbies

Hi All,

I apologise in advance if this is posted in the wrong forum, but I'm a total FM newb!

Anywho, What I'm trying to do is make a database for my pharmacy that records all the formulas we prepare. Without going into details (don't wanna bore you) these formulas are made up of different APIs and each API has multiple batches associated with it (APIs are delivered in batches and we need to record which one was used. Each Batch may only belong to one API.)

I have an AddBatch table with two fields; API ID and Batch ID. Each time a batch comes in we record the Batch ID and the API that it is. Now in the Formula table I want to be able to choose an API from a dropdown list and have the second dropdown list show only those batches belonging to the API selected in the first.

Now here's the clincher: I've already managed to do the above. The challenge is that each Formula consists of more than one API and therefore I need multiple conditional value lists all referencing the same table (AddBatch).

My set up is as follows:

Table: Addbatch: API ID (APIs Value list);Batch ID

Table: Formulas:: API ID (APIs Value list);Batch ID (Fields repeat)

-AddBatch::API ID and Formulas::API ID are related

Value List: APIs (values added manually)

Value List: AddBatch::Batch ID (related values only)

-Formulas::Batch ID references the value list above.

I've set the fields in the Formulas table to repeat 3 times. The problem is that, say I put API-1 into the first field, then I'll see all the batches for API-1 in its corresponding batches field. But say I then put API-2 into the second API field, then the corresponding batch field will show all batches for both API-1 and API-2, which isn't very helpful. I need to be able to have three (or more) separate boxes for APIs and each box's corresponding batch field should show the batches for the API referenced above.

Sorry if this all a bit confusing or doesn't make sense but I'm completely stumped. Hopefully some of you smart people'll be able to figure it out.

Thanks!