Re: populating list on criteria

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

On Thu, 22 May 2008 19:34:57 -0700 (PDT), hinterbichler@xxxxxxxxx wrote:

Just to add to my post above.
In the listbox (on the subform Inputs) I would like to list the
following

From table Inputs

The all "numIn" on the same "txtConDev" with the same number
"numDev" for the same customer "txtProjID".


Christian

This is basically the structure you already have.
Create a new database with a new module and
copy and paste this code into the module and save it.

Type call CreateTablesDDL in the Immediate window
and press enter. This creates the tables. This is only meant
to be an example.

It can be implemented as a main form on CustomerProjects and
two subforms on ProjectControlDevices and ProjectControlInputDevices.

Sub CreateTablesDDL()

With CurrentProject.Connection

.Execute _
"CREATE TABLE Customers " & _
"(" & _
"customer_id VARCHAR (10) NOT NULL, " & _
"customer_name VARCHAR (50) NOT NULL, " & _
"PRIMARY KEY (customer_id) " & _
");"

.Execute _
"CREATE TABLE Projects " & _
"(" & _
"project_id CHAR (5) NOT NULL, " & _
"project_name VARCHAR (50) NOT NULL, " & _
"PRIMARY KEY (project_id) " & _
");"

.Execute _
"CREATE TABLE CustomerProjects " & _
"(" & _
"customer_id VARCHAR (10) NOT NULL " & _
"REFERENCES Customers (customer_id), " & _
"project_id CHAR (5) NOT NULL " & _
"REFERENCES Projects (project_id), " & _
"PRIMARY KEY (customer_id, project_id) " & _
");"

.Execute _
"CREATE TABLE ControlDevices " & _
"(" & _
"condev_name VARCHAR (30) NOT NULL, " & _
"condev_num INTEGER NOT NULL, " & _
"PRIMARY KEY (condev_name, condev_num) " & _
");"

.Execute _
"CREATE TABLE ProjectControlDevices " & _
"(" & _
"project_id CHAR (5) NOT NULL " & _
"REFERENCES PROJECTS (Project_id), " & _
"condev_name VARCHAR (30) NOT NULL, " & _
"condev_num INTEGER NOT NULL, " & _
"CONSTRAINT fk_controldevices " & _
"FOREIGN KEY (condev_name, condev_num) " & _
"REFERENCES ControlDevices " & _
"(condev_name, condev_num), " & _
"PRIMARY KEY (project_id, condev_name, condev_num) " & _
");"

.Execute _
"CREATE TABLE InputDevices " & _
"(" & _
"indev_name VARCHAR (30) NOT NULL, " & _
"indev_num INTEGER NOT NULL, " & _
"PRIMARY KEY (indev_name, indev_num) " & _
");"

.Execute _
"CREATE TABLE ProjectControlInputDevices " & _
"(" & _
"project_id CHAR (5) NOT NULL, " & _
"condev_name VARCHAR (30) NOT NULL, " & _
"condev_num INTEGER NOT NULL, " & _
"CONSTRAINT fk_projectcontroldevices " & _
"FOREIGN KEY (project_id,condev_name, condev_num) " & _
"REFERENCES ProjectControlDevices " & _
"(project_id, condev_name, condev_num), " & _
"indev_name VARCHAR (30) NOT NULL, " & _
"indev_num INTEGER NOT NULL, " & _
"CONSTRAINT fk_inputdevices " & _
"FOREIGN KEY (indev_name, indev_num) " & _
"REFERENCES InputDevices (indev_name, indev_num), " & _
"PRIMARY KEY (project_id, condev_name, condev_num, " & _
"indev_name, indev_num) " & _
");"

End With

End Sub
.