For next loop

[sdonovan]

I don't know if this is what I need, but here goes.

I have a database I constructed and one feature I am trying to create is this. When creating a record I need it to access another table and add itself and reference a primary key. (I think)

The application is a contact db which tracks all my servers worldwide with different information. The information I am working with is called a TCNO which is a Time Compliance Network Order. Basically a security patch put out by Microsoft. TCNO = 03-11, Microsoft Security Bulletin (MSB) MS02-61 etc, etc.

All information about a TCNO is created and stored in the TCNO table. I then have a Bases table which is all the Air Force bases worldwide that has one of the servers installed. Clearly a many to many relationship. A third table is created to assign a TCNO to a base, LW_INSTALLTCNO:Table.

So the layout is this (PK = Primary Key)

LW_Bases: Table
PK Base_Name
Unit
Address
MAJCOM

LW_TCNO: Table
PK TCNO
MSB
MKBA
Title

LW_InstalledTCNO: Table
PK Base_Name
PK TCNO
Installed yes/no
Date Installed

The forms to enter stuff is based off each table.

All TCNO's I create apply to all bases so what I want is to be able to create the TCNO and hit add and have it applied to all bases in the database shown in one of two list boxes. (This is where I think I need my loop) List box 1 is TCNO's installed. List box 2 is not installed.

Currently I create the TCNO and have to individually add it to each base thru another form with a check box to indicate it is installed and the date.

I want to bring up the form with two list boxes and already see it in the not installed list box, double click on it and bring up the install form and enter the date.

I dont want the extra step to have to individualy add it to each base.

Would I use a For next loop, and how would I construct it?

Thanks.

 

[WayneRyan]

sddonovan,

I think you're close right now. I would remove the yes/no
field from the Installed table.

You have a table of bases.
You have a table of TCNOs.
You have a table of Installs.

with the appropriate queries you can do things like:

Select TCNOs
From TCNOs
Where BaseID = SomeID And
      TCNO NOT IN (Select TCNO
                   From   Installs
                   Where  BaseID = BaseID)

That would give you a list of the Installs that
a particular base needs.

There is no need to replicate information that
can be calculated.

hth,
Wayne