Help With Linking

[dan.cawthorne]

Can Some One Help Me Please,

Im Going Around In Circles Trying Different Things To Over Come My
Issue With My Link, But I Just Cant Seem Get My Head Round The Issue.

My Problems Is I Wont To Be Able to Select Multiply Drawings, for a
Particular Enquiry for a Particular Project.

I Have a Projects Table. Primary Key = [ProjectQNo]

Then I have a Drawings Table Primary Key = [ProjectQNo] The
Relationship is a One to Many Enforce Referential Integrity

and this Link Works Fine, and have the form as a subform in the
projects Form.

Moving a away I have a Enquiry Table Primary Key - [EnquiryNo] This
Table Stores The Actual Enquiry Like [Return Date] [Date Sent],
[ProjectsQNo] [Project Title] [Sent Type] [Sentby]

This Table Is Link Table With Two Primary Keys [EnquiryNo] and
[Company ID] This Table Also Holds Specific Information eg [Status]
[Used] [Recieved Date]

This Table Is Also Link to the Suppliers Table.

This Allows me to create Enquirys to different companys while it been
for same project.

What I Would Like to Do is in the Creat Enquiry Form, Table is to have
a Subform, Set to Continue, and When I Select the project in the drop
down list im then able to select Drawings For that Enquiry. but the
way ive tryed give me a Jet Database Error.

Ive Tries Creating a Link Table By Have 2 Fields = Primary Keys
[EnquiryNo] and [Drawing ID] LInking Back to the Other Tables and also
tried putting the [ProjectQNo] is this table too.

But Just aimt working.

 

[dan.cawthorne]

Can Some One Help Me Please,

Im Going Around In Circles Trying Different Things To Over Come My
Issue With My Link, But I Just Cant Seem Get My Head Round The Issue.

My Problems Is I Wont To Be Able to Select Multiply Drawings, for a
Particular Enquiry for a Particular Project.

I Have a Projects Table. Primary Key = [ProjectQNo]

Then I have a Drawings Table Primary Key = [ProjectQNo] The
Relationship is a One to Many Enforce Referential Integrity

and this Link Works Fine, and have the form as a subform in the
projects Form.

Moving a away I have a Enquiry Table Primary Key - [EnquiryNo] This
Table Stores The Actual Enquiry Like [Return Date] [Date Sent],
[ProjectsQNo] [Project Title] [Sent Type] [Sentby]

This Table Is Link Table With Two Primary Keys [EnquiryNo] and
[Company ID] This Table Also Holds Specific Information eg [Status]
[Used] [Recieved Date]

This Table Is Also Link to the Suppliers Table.

This Allows me to create Enquirys to different companys while it been
for same project.

What I Would Like to Do is in the Creat Enquiry Form, Table is to have
a Subform, Set to Continue, and When I Select the project in the drop
down list im then able to select Drawings For that Enquiry. but the
way ive tryed give me a Jet Database Error.

Ive Tries Creating a Link Table By Have 2 Fields = Primary Keys
[EnquiryNo] and [Drawing ID] LInking Back to the Other Tables and also
tried putting the [ProjectQNo] is this table too.

But Just aimt working.

i think i've achieved what i was looking for, but what im still aint
achieved, is that when the user clicks on the drawing dropdown list,
it lists all the drawings for every project, if there a way getting
the list to just show the drawings related to that Project ive just
selected in the previous field?

 

[John W. Vinson]

i think i've achieved what i was looking for, but what im still aint
achieved, is that when the user clicks on the drawing dropdown list,
it lists all the drawings for every project, if there a way getting
the list to just show the drawings related to that Project ive just
selected in the previous field?

We can't see your database so I'll have to be rather general here - but this
is called a "dependent combo box".

Base the Drawings combo, not on the drawings table directly, but on a Query of
the drawings table. Use a criterion on the project field like

=Forms!NameOfForm!NameOfCombo

to select just those drawings pertaining to the project selected.

You'll need one line of VBA code in the Project combo's AfterUpdate event:

Private Sub cboProject_AfterUpdate()
Me!cboDrawing.Requery
End Sub

using the actual name of your controls of course.

John W. Vinson [MVP]

 

[dan.cawthorne]

We can't see your database so I'll have to be rather general here - but this
is called a "dependent combo box".

Base the Drawings combo, not on the drawings table directly, but on a Query of
the drawings table. Use a criterion on the project field like

=Forms!NameOfForm!NameOfCombo

to select just those drawings pertaining to the project selected.

You'll need one line of VBA code in the Project combo's AfterUpdate event:

Private Sub cboProject_AfterUpdate()
Me!cboDrawing.Requery
End Sub

using the actual name of your controls of course.

John W. Vinson [MVP]

Thank You For You Reply John, I Actualy Did Some Think Like that,

With in the Drawing Combo I Opened the Select Query and Added The
Project Field to it, and the Put The criterion as you mentioned to the
main forms Project Combo

So When I Selected The Project it Listed thier drawings,

Did Notice though is when i go back and changed the project, the
drawing combo on subform Wouldn't ReUpdate on the related drawings, Id
have to close the form and reopen it.

Does That After Update code do that you suggested? and what is the Me!
Expression?

 

[John W. Vinson]

Did Notice though is when i go back and changed the project, the
drawing combo on subform Wouldn't ReUpdate on the related drawings, Id
have to close the form and reopen it.

Does That After Update code do that you suggested? and what is the Me!
Expression?

That's exactly what it's for. Me!<controlname> means "the instance of
controlname on this form" - Me! is just a shortcut for the full reference
Forms!NameOfTheForm!.

John W. Vinson [MVP]