Combo Box Lookup

[dan.cawthorne]

How Do I Create a Combo Box Lookup, which only shows me related
drawings to which project ive selected in my enquiry, Query? the
drawings are in a related table to a project table?

 

[Anand]

Hello,
Assuming you have a Form with a field to select the Project and the ComboBox
lookup field. In the AfterUpdate event of the Project field set the Combo
Box's row source to a query which filters records in your drawings tables
with the related record in your Projects table. The SQL statement (to query
the records) would look something like this:

"SELECT tblDrawings.somefield1, tblDrawings.somefield2 FROM tblDrawings
WHERE tblDrawings.foreignkey = " & me.Project & ""

tblDrawings.foreignkey is the related field from tblProjects. You may have
set a One-to-Many relationship here
I am assuming here that the related field is a Number. Me.Project refers to
your Project field in the Form.

Every time you select a Project in your Project field the records in the
ComboBox will show related drawings for that project.

HTH
Anand

 

[dan.cawthorne]

Hello,
Assuming you have a Form with a field to select the Project and the ComboBox
lookup field. In the AfterUpdate event of the Project field set the Combo
Box's row source to a query which filters records in your drawings tables
with the related record in your Projects table. The SQL statement (to query
the records) would look something like this:

"SELECT tblDrawings.somefield1, tblDrawings.somefield2 FROM tblDrawings
WHERE tblDrawings.foreignkey = " & me.Project & ""

tblDrawings.foreignkey is the related field from tblProjects. You may have
set a One-to-Many relationship here
I am assuming here that the related field is a Number. Me.Project refers to
your Project field in the Form.

Every time you select a Project in your Project field the records in the
ComboBox will show related drawings for that project.

HTH
Anand

Yeh The Drawings Table is Set to One to Many From The Projects Table,

The Project Field Lookup has the following Select qry of "SELECT
Projects.[Project QNo], Projects.[Project Title] FROM Projects; "

Dont I Need to Do Anything on The Drawing Combo Lookup Field,

Cause at moment the drawings lookup box has a select qry has the
following. "SELECT Drawings.[Drawing Number], Drawings.[Drawings
Title], Drawings.Revision FROM Drawings; "

The Drawings Number Field is set to text as some times drawings
numbers have letters in it.

 

[Bob Quintal]

Hello,
Assuming you have a Form with a field to select the Project
and the ComboBox lookup field. In the AfterUpdate event of
the Project field set the Combo Box's row source to a query
which filters records in your drawings tables with the
related record in your Projects table. The SQL statement (to
query the records) would look something like this:

"SELECT tblDrawings.somefield1, tblDrawings.somefield2 FROM
tblDrawings WHERE tblDrawings.foreignkey = " & me.Project &
""

tblDrawings.foreignkey is the related field from tblProjects.
You may have set a One-to-Many relationship here
I am assuming here that the related field is a Number.
Me.Project refers to your Project field in the Form.

Every time you select a Project in your Project field the
records in the ComboBox will show related drawings for that
project.

HTH
Anand

Yeh The Drawings Table is Set to One to Many From The Projects
Table,

The Project Field Lookup has the following Select qry of
"SELECT Projects.[Project QNo], Projects.[Project Title] FROM
Projects; "

Dont I Need to Do Anything on The Drawing Combo Lookup Field,

Cause at moment the drawings lookup box has a select qry has
the following. "SELECT Drawings.[Drawing Number],
Drawings.[Drawings Title], Drawings.Revision FROM Drawings; "

The Drawings Number Field is set to text as some times
drawings numbers have letters in it.

as shown above, but modified to your table/fields

WHERE Drawings.[Project QNo] = """ & me![cboProject QNo] & """"

 

[dan.cawthorne]

Yeh The Drawings Table is Set to One to Many From The Projects
Table,

The Project Field Lookup has the following Select qry of
"SELECT Projects.[Project QNo], Projects.[Project Title] FROM
Projects; "

Dont I Need to Do Anything on The Drawing Combo Lookup Field,

Cause at moment the drawings lookup box has a select qry has
the following. "SELECT Drawings.[Drawing Number],
Drawings.[Drawings Title], Drawings.Revision FROM Drawings; "

The Drawings Number Field is set to text as some times
drawings numbers have letters in it.

as shown above, but modified to your table/fields

WHERE Drawings.[Project QNo] = """ & me![cboProject QNo] & """"

Thank You For Getting Back Intouch with me, I really appricaite the
Help, I think I'm Missing Someing, I dont Know If Its Because the
Drawing Field Combo is on a Subform,

But Ive Tried To Add The Code To My Afterup Date On The ProjectQNo
Combo Field Code I Tried Was ="SELECT Drawings.[ProjectQNo], Drawings.
[Drawing Number] FROM Drawings WHERE Drawings.[ProjectQNo] = """ & me!
[cboProject QNo] & """"""

When I Select A Project i Get The Following Dailog Message.

The Expression After Update You entered as the event Property Setting
Produced the following error: The Object Doesnt Contain The Automation
Object "Me."

 

[Bob Quintal]

innews:[email protected]:

The Project Field Lookup has the following Select qry of
"SELECT Projects.[Project QNo], Projects.[Project Title]
FROM Projects; "

Dont I Need to Do Anything on The Drawing Combo Lookup
Field,

Cause at moment the drawings lookup box has a select qry
has the following. "SELECT Drawings.[Drawing Number],
Drawings.[Drawings Title], Drawings.Revision FROM Drawings;
"

The Drawings Number Field is set to text as some times
drawings numbers have letters in it.

as shown above, but modified to your table/fields

WHERE Drawings.[Project QNo] = """ & me![cboProject QNo] &
""""

Thank You For Getting Back Intouch with me, I really
appricaite the Help, I think I'm Missing Someing, I dont Know
If Its Because the Drawing Field Combo is on a Subform,

But Ive Tried To Add The Code To My Afterup Date On The
ProjectQNo Combo Field Code I Tried Was ="SELECT
Drawings.[ProjectQNo], Drawings. [Drawing Number] FROM
Drawings WHERE Drawings.[ProjectQNo] = """ & me! [cboProject
QNo] & """"""

When I Select A Project i Get The Following Dailog Message.

The Expression After Update You entered as the event Property
Setting Produced the following error: The Object Doesnt
Contain The Automation Object "Me."

The subform might have something to do with it.

Since you are on the subform and the project combobox is on hte
parent, the reference to me should become a reference to parent.

"SELECT Drawings.[ProjectQNo],
Drawings. [Drawing Number]
FROM Drawings
WHERE Drawings.[ProjectQNo] = """ &
parent![cboProject QNo] & """"""