Table: Data Type: Lookup question

[kiers]

Hi All,

I have created 3 tables;

tblProjectDirec torates (Fields = ID, Directorate)
tblSubDirectora tes (Fields = ID, DirectorateID, Sub-Directorates)
tblProjects (Fields = ID, Project Number, Project Title, Directorate,
Sub-Directorate)

The objective is to use Lookup from 'Data Type' in the tblProjects to
limit the selection of Sub-Directorates based on the previously
selected 'Directorate'. At this stage I do not want to use a form -
just directly update the table.

I have set the Row Source (tblProjects, field=Sub-Directorates) to;

SELECT tblSubDirectora te.ID, tblSubDirectora te.[Sub-Directorate],
tblSubDirectora te.DirectorateI D FROM tblSubDirectora te WHERE
tblSubDirectora te.DirectorateI D=[Directorate];

However, no records are selected for the table field 'Sub-
Directorate'.

Any advice would be greatly appreciated.

Thanks

Kiers

[Rich P]

Re: Table: Data Type: Lookup question

Hello,

It is not possible to dynamically filter a Lookup datasource directly
from the Table design because a table does not have events to trigger
functions. You would have to do this in a form where you
programatically capture the value of a selected record and then set the
recordsource property of a combobox or listbox or subform that would be
based on tblProjects.

Private Sub Form1_Current()
'--get selected value here
combo1.RecordSo urce = "Select fld1 From tblWhatever Where something = '"
& selectedValue & "'"
combo1.Requery
me.Requery
End Sub


Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Roger]

Re: Table: Data Type: Lookup question

On Jun 26, 1:47 pm, kiers <k....@hotmail. co.ukwrote:

Hi All,
>
I have created 3 tables;
>
tblProjectDirec torates (Fields = ID, Directorate)
tblSubDirectora tes (Fields = ID, DirectorateID, Sub-Directorates)
tblProjects (Fields = ID, Project Number, Project Title, Directorate,
Sub-Directorate)
>
The objective is to use Lookup from 'Data Type'  in the tblProjects to
limit the selection of Sub-Directorates based on the previously
selected 'Directorate'. At this stage I do not want to use a form -
just directly update the table.
>
I have set the Row Source (tblProjects, field=Sub-Directorates) to;
>
SELECT tblSubDirectora te.ID, tblSubDirectora te.[Sub-Directorate],
tblSubDirectora te.DirectorateI D FROM tblSubDirectora te WHERE
tblSubDirectora te.DirectorateI D=[Directorate];
>
However, no records are selected for the table field 'Sub-
Directorate'.
>
Any advice would be greatly appreciated.
>
Thanks
>
Kiers

tblProjectDirec torates (Fields = ID, Directorate)
tblSubDirectora tes (Fields = ID, DirectorateID, Sub-Directorates)
tblProjects (Fields = ID, Project Number, Project Title, Directorate,
Sub-Directorate)


but in tblProjects, if I know the subDirectorate, then I know the
directorate for tblSubDirectora tes, so the directorate field is not
required

you should use a form with 2 comboboxes, one for directorate (based on
its table)
and one for subDirectorate based on its table filtered by the
directorate combobox

[kiers]

Re: Table: Data Type: Lookup question

On 27 Jun, 02:00, Roger <lesperan...@na tpro.comwrote:

On Jun 26, 1:47 pm, kiers <k....@hotmail. co.ukwrote:
>
>
>
>
>

Hi All,

>

I have created 3 tables;

>

tblProjectDirec torates (Fields = ID, Directorate)
tblSubDirectora tes (Fields = ID, DirectorateID, Sub-Directorates)
tblProjects (Fields = ID, Project Number, Project Title, Directorate,
Sub-Directorate)

>

The objective is to use Lookup from 'Data Type'  in the tblProjects to
limit the selection of Sub-Directorates based on the previously
selected 'Directorate'. At this stage I do not want to use a form -
just directly update the table.

>

I have set the Row Source (tblProjects, field=Sub-Directorates) to;

>

SELECT tblSubDirectora te.ID, tblSubDirectora te.[Sub-Directorate],
tblSubDirectora te.DirectorateI D FROM tblSubDirectora te WHERE
tblSubDirectora te.DirectorateI D=[Directorate];

>

However, no records are selected for the table field 'Sub-
Directorate'.

>

Any advice would be greatly appreciated.

>

Thanks

>

Kiers

>
tblProjectDirec torates (Fields = ID, Directorate)
tblSubDirectora tes (Fields = ID, DirectorateID, Sub-Directorates)
tblProjects (Fields = ID, Project Number, Project Title, Directorate,
Sub-Directorate)
>
but in tblProjects, if I know the subDirectorate, then I know the
directorate for tblSubDirectora tes, so the directorate field is not
required
>
you should use a form with 2 comboboxes, one for directorate (based on
its table)
and one for subDirectorate based on its table filtered by the
directorate combobox- Hide quoted text -
>
- Show quoted text -

Hi,

thanks for the advice. I have now written the form as suggested and it
works perfectly.

Thank you for your time,

Kiers