Разработка информационной системы, хранящей данные об автомобилях, продавцах, информацию о продажах, страница 5

MsgBox "нет машин такого года выпуска"

End If

Case 2

pr = Двигатель.Value

db.QueryDefs.Delete ("Запрос")

Set req = db.CreateQueryDef("Запрос")

req.SQL = "SELECT * FROM Автомобили WHERE Автомобили.ДвигательКубсм =" & "" & pr & ""

DoCmd.OpenQuery ("Запрос")

Case 3

model = Модель.Value

db.QueryDefs.Delete ("Запрос")

Set req = db.CreateQueryDef("Запрос")

req.SQL = "SELECT * FROM Автомобили WHERE Автомобили.Модель =" & "" & model & ""

DoCmd.OpenQuery ("Запрос")

End Select

Exit Sub

er:

MsgBox "Ошибка ввода данных. выберите критерии отбора, заполните поля"

End Sub

Private Sub НайтиПродавцы_Click()

Dim db As Database

Dim fio As String

Set db = CurrentDb

fio = ФИО.Value

On Error GoTo er

db.QueryDefs.Delete ("Запрос2")

Set req = db.CreateQueryDef("Запрос2")

If Флажок.Value = True Then

req.SQL = "SELECT * FROM Продавцы WHERE Продавцы.Фамилия =" & "'" & fio & "'"

DoCmd.OpenQuery ("Запрос2")

Else

req.SQL = "SELECT Фамилия, ДатаРождения,Оплата,пол FROM Продавцы WHERE Продавцы.Фамилия =" & "'" & fio & "'"

DoCmd.OpenQuery ("Запрос2")

End If

Exit Sub

er:

MsgBox "Ошибка ввода данных.выбирете продавца"

End Sub

DDL-код

CREATE TABLE T_Prodazha (

id_zakaz INTEGER NOT NULL,

id_car INTEGER NOT NULL,

data_zakaza DATE NOT NULL,

fio VARCHAR ( 255 ) NOT NULL,

id_pokupatel INTEGER NOT NULL,

marka VARCHAR ( 255 ) NOT NULL,

T_FormZ_ID INTEGER,

T_Pokupatel_id_pokupatel INTEGER NOT NULL,

T_avto_id_car INTEGER NOT NULL,

CONSTRAINT PK_T_Prodazha33 PRIMARY KEY (id_zakaz)

);

CREATE INDEX TC_T_Prodazha59 ON T_Prodazha (T_avto_id_car );

CREATE INDEX TC_T_Prodazha58 ON T_Prodazha (T_FormZ_ID );

CREATE INDEX TC_T_Prodazha60 ON T_Prodazha (T_Pokupatel_id_pokupatel );

CREATE TABLE T_avto (

id_car INTEGER NOT NULL,

marka VARCHAR ( 255 ) NOT NULL,

proizvoditel VARCHAR ( 255 ) NOT NULL,

god_vipyska INTEGER NOT NULL,

obem_dvigatelia INTEGER NOT NULL,

zvet VARCHAR ( 255 ) NOT NULL,

cena INTEGER NOT NULL,

T_FormA_ID INTEGER,

CONSTRAINT PK_T_avto34 PRIMARY KEY (id_car)

);

CREATE INDEX TC_T_avto61 ON T_avto (T_FormA_ID );

CREATE TABLE T_Pokupatel (

id_pokupatel INTEGER NOT NULL,

fio VARCHAR ( 255 ) NOT NULL,

telefon INTEGER NOT NULL,

adress VARCHAR ( 255 ) NOT NULL,

T_FormP_ID INTEGER,

CONSTRAINT PK_T_Pokupatel32 PRIMARY KEY (id_pokupatel)

);

CREATE INDEX TC_T_Pokupatel57 ON T_Pokupatel (T_FormP_ID );

CREATE TABLE T_FormP (

T_FormP_ID INTEGER NOT NULL,

CONSTRAINT PK_T_FormP35 PRIMARY KEY (T_FormP_ID)

);

CREATE TABLE T_FormA (

T_FormA_ID INTEGER NOT NULL,

CONSTRAINT PK_T_FormA36 PRIMARY KEY (T_FormA_ID)

);

CREATE TABLE T_FormZ (

T_FormZ_ID INTEGER NOT NULL,

CONSTRAINT PK_T_FormZ37 PRIMARY KEY (T_FormZ_ID)

);

ALTER TABLE T_Prodazha ADD CONSTRAINT FK_T_Prodazha27 FOREIGN KEY (T_FormZ_ID) REFERENCES T_FormZ (T_FormZ_ID)  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE T_Prodazha ADD CONSTRAINT FK_T_Prodazha29 FOREIGN KEY (T_avto_id_car) REFERENCES T_avto (id_car)  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE T_Prodazha ADD CONSTRAINT FK_T_Prodazha28 FOREIGN KEY (T_Pokupatel_id_pokupatel) REFERENCES T_Pokupatel (id_pokupatel)  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE T_avto ADD CONSTRAINT FK_T_avto26 FOREIGN KEY (T_FormA_ID) REFERENCES T_FormA (T_FormA_ID)  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE T_Pokupatel ADD CONSTRAINT FK_T_Pokupatel25 FOREIGN KEY (T_FormP_ID) REFERENCES T_FormP (T_FormP_ID)  ON DELETE NO ACTION ON UPDATE NO ACTION;