473,326 Members | 2,023 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Table: Data Type: Lookup question

Hi All,

I have created 3 tables;

tblProjectDirectorates (Fields = ID, Directorate)
tblSubDirectorates (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 tblSubDirectorate.ID, tblSubDirectorate.[Sub-Directorate],
tblSubDirectorate.DirectorateID FROM tblSubDirectorate WHERE
tblSubDirectorate.DirectorateID=[Directorate];

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

Any advice would be greatly appreciated.

Thanks

Kiers
Jun 27 '08 #1
3 1380
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.RecordSource = "Select fld1 From tblWhatever Where something = '"
& selectedValue & "'"
combo1.Requery
me.Requery
End Sub
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jun 27 '08 #2
On Jun 26, 1:47*pm, kiers <k....@hotmail.co.ukwrote:
Hi All,

I have created 3 tables;

tblProjectDirectorates (Fields = ID, Directorate)
tblSubDirectorates (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 tblSubDirectorate.ID, tblSubDirectorate.[Sub-Directorate],
tblSubDirectorate.DirectorateID FROM tblSubDirectorate WHERE
tblSubDirectorate.DirectorateID=[Directorate];

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

Any advice would be greatly appreciated.

Thanks

Kiers
tblProjectDirectorates (Fields = ID, Directorate)
tblSubDirectorates (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 tblSubDirectorates, 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
Jun 27 '08 #3
On 27 Jun, 02:00, Roger <lesperan...@natpro.comwrote:
On Jun 26, 1:47*pm, kiers <k....@hotmail.co.ukwrote:


Hi All,
I have created 3 tables;
tblProjectDirectorates (Fields = ID, Directorate)
tblSubDirectorates (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 tblSubDirectorate.ID, tblSubDirectorate.[Sub-Directorate],
tblSubDirectorate.DirectorateID FROM tblSubDirectorate WHERE
tblSubDirectorate.DirectorateID=[Directorate];
However, no records are selected for the table field 'Sub-
Directorate'.
Any advice would be greatly appreciated.
Thanks
Kiers

tblProjectDirectorates (Fields = ID, Directorate)
tblSubDirectorates (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 tblSubDirectorates, 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
Jun 28 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Vladimir | last post by:
Hello, I have a table in MS Access database. It has one field (with BYTE datatype) that has several properties set in Lookup tab of table Design View. Display Control = Combo Box. Row Source...
7
by: Marco Simone | last post by:
Hi, What is your opinion about using Lookup field in table. I would like to use lookup field in table 1, so that I can choose data in combo box from table 2. Is this good design of database? ...
1
by: Old Timer | last post by:
I wish to type in a number in my "Code" field, for instance 1060, I then wish the number 1060 to trigger an event that will fill in the next field (township field) For instance, 1060 brings up and...
2
by: RLC | last post by:
We're developing an application request/packaging/rollout worflow application for our 50 site, 40,000 user company. There is a request table, an engineering table, a distribution table, etc. etc....
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
1
by: LurfysMa | last post by:
I am working on an electronic flashcard program. Most of the subjects are simple lists of questions and answers. Those seem to be working. Some of the "subjects" have "categories" of questions. ...
11
by: Paul H | last post by:
Suppose I have a table called tblPeople and I want a field to illustrate whether each person prefers cats or dogs. I could do it one of three ways. 1. A plain text field Create a text field in...
5
by: Andrus | last post by:
I'm creating a database Winforms application using VCS Express 2005 I have some large lookup tables (may be up to 500000 records) which contains name and id and are stored in sql server. I...
14
by: Mark | last post by:
I have a table with a field that uses a combobox to populate values. The Lookup tab within table design mode is the following: Display Control Combo Box Row Source Type ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.