List values(based on Table/Query) in a Sub-Form does not refresh

[SK]

Greetings,

I want to be able to display the values in a field
(defined as combo-box) based on the values entered in a
previous field. Both the fields are on the Sub-Form.

The problem I am facing is that the combo-box value list
does not seem to refresh(somehow it seems to memorize the
value entered in the first record).

Bascically, I want to be able to provide a lookup value
(combo-box) for the employee code field(3rd field on the
Sub-Form) based on the Title(designation) chosen in the
current record(Title/Designation field is the 2nd field
on the Sub-Form and is after the Linked field).

Sub-Form Layout:

Region Title EmployeeCode ....

The following query is defined as the Row-source for the
EmployeeCode field in the Sub-Form.

SELECT tblEmployees.EmployeeCode, Trim([FirstName]) & " "
& Trim([LastName]) AS Expr1
FROM tblEmployees
WHERE (((tblEmployees.Title)=[Forms]![frmRegions]!
[frmRegionDefaults].[Form]![Title]));

In the first record, if I choose a particular value for
the Title(let's say "02"), the lookup value list(in the
EmployeeCode field) seems to be OK(i.e. it brings up all
the employee records that have a title code of "02").
However for all subsequent records, the lookup value is
the same irrespective of the title code in the that
record. Somehow, either the query does not seem to run
for every record or the contents are not being refreshed.
Using the Msgbox function, I have noticed that the value
of [Forms]![frmRegions]![frmRegionDefaults].[Form]!
[Title] DOES change based on the title code in the
current record.

Am I missing any step or there is a better way of
achieving this.

Thanks in advance,

SK

 

[Jonathan]

-----Original Message-----
Greetings,

I want to be able to display the values in a field
(defined as combo-box) based on the values entered in a
previous field. Both the fields are on the Sub-Form.

The problem I am facing is that the combo-box value list
does not seem to refresh(somehow it seems to memorize the
value entered in the first record).

Bascically, I want to be able to provide a lookup value
(combo-box) for the employee code field(3rd field on the
Sub-Form) based on the Title(designation) chosen in the
current record(Title/Designation field is the 2nd field
on the Sub-Form and is after the Linked field).

Sub-Form Layout:

Region Title EmployeeCode ....

The following query is defined as the Row-source for the
EmployeeCode field in the Sub-Form.

SELECT tblEmployees.EmployeeCode, Trim([FirstName]) & " "
& Trim([LastName]) AS Expr1
FROM tblEmployees
WHERE (((tblEmployees.Title)=[Forms]![frmRegions]!
[frmRegionDefaults].[Form]![Title]));

In the first record, if I choose a particular value for
the Title(let's say "02"), the lookup value list(in the
EmployeeCode field) seems to be OK(i.e. it brings up all
the employee records that have a title code of "02").
However for all subsequent records, the lookup value is
the same irrespective of the title code in the that
record. Somehow, either the query does not seem to run
for every record or the contents are not being refreshed.
Using the Msgbox function, I have noticed that the value
of [Forms]![frmRegions]![frmRegionDefaults].[Form]!
[Title] DOES change based on the title code in the
current record.

Am I missing any step or there is a better way of
achieving this.

Thanks in advance,

SK
.

Hi SK,
for the first combobox, [Title] use the AfterUpdate event
to run the line

EmployeeCode.requery


Luck
Jonathan

 

[SK]

Thanks, Jonathan. I tried this. The requery part seems to
requery
all the records and not just the current record. As a
result, it seems to display values only for the active
title and hide(or does not display) the values for all
other titles. e.g. If I have 3 records with title "01"
and 2 records with title "02" and if I change the title
of a record to "01", it displays BLANKS in the employee
column for records which had a title of "02". Please note
that the fields are in a SubForm. We need to requery just
the active record and not all the records....

Please advise.

Thanks in advance.

-----Original Message-----

-----Original Message-----
Greetings,

I want to be able to display the values in a field
(defined as combo-box) based on the values entered in a
previous field. Both the fields are on the Sub-Form.

The problem I am facing is that the combo-box value list
does not seem to refresh(somehow it seems to memorize the
value entered in the first record).

Bascically, I want to be able to provide a lookup value
(combo-box) for the employee code field(3rd field on the
Sub-Form) based on the Title(designation) chosen in the
current record(Title/Designation field is the 2nd field
on the Sub-Form and is after the Linked field).

Sub-Form Layout:

Region Title EmployeeCode ....

The following query is defined as the Row-source for the
EmployeeCode field in the Sub-Form.

SELECT tblEmployees.EmployeeCode, Trim([FirstName]) & " "
& Trim([LastName]) AS Expr1
FROM tblEmployees
WHERE (((tblEmployees.Title)=[Forms]![frmRegions]!
[frmRegionDefaults].[Form]![Title]));

In the first record, if I choose a particular value for
the Title(let's say "02"), the lookup value list(in the
EmployeeCode field) seems to be OK(i.e. it brings up all
the employee records that have a title code of "02").
However for all subsequent records, the lookup value is
the same irrespective of the title code in the that
record. Somehow, either the query does not seem to run
for every record or the contents are not being refreshed.
Using the Msgbox function, I have noticed that the value
of [Forms]![frmRegions]![frmRegionDefaults].[Form]!
[Title] DOES change based on the title code in the
current record.

Am I missing any step or there is a better way of
achieving this.

Thanks in advance,

SK
.

Hi SK,
for the first combobox, [Title] use the AfterUpdate event
to run the line

EmployeeCode.requery


Luck
Jonathan
.