Inter-Dependent Fields

[FARAZ QURESHI]

Unfortunately I haven't yet found any reply 2 my previously posted question
at
http://www.microsoft.com/office/com...&p=1&tid=b79e5bbd-16b8-499d-971b-350e23a91ff4

Sure would appreciate a reply:

Table-I consists of:

1. Country;
2. City; and
3. Zip Code;

Table-II consists of:

1. Customer; &
2. Zip Code (Lookedup from Table-I);

How to create a form with:

1. Customer;
2. Country;
3. City; &
4. Zip Code

where if one:

1. selects a Country from the combo box only relevant Cities are reflected;

2. doesn't select a Country all Cities are reflected and upon selecting a
City from the combo box, the Country field automatically updates/changes to
the entry entered/changed in the City;

3. directly inserts a Zip Code both the fields "Country" & "City" update
automatically.

Thanx in advance!

 

[Allen Browne]

I don't think the approach you have here is going to work.

In some countries, there is no direct relation between cities and zip codes.
For example, here in Australia, one city can have multiple zip codes (e.g.
where there are multiple post offices in the heart of the city), and one zip
code can apply to multiple cities (where one post office services several
areas.) That means that there is no way to derive the result you ant from
the 2 tables you have.

To address your specific questions, this article explains how to limit the
Cities to those from the selected Country:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

And if it's any help, here's a routine we use where the user types the city
name into a combo and it looks up the postal code:
Combos with Tens of Thousands of Records - Postcodes example
at:
http://allenbrowne.com/ser-32.html

 

[FARAZ QURESHI]

Yahoo!!! Thanx Allen!
Sure was expecting you 2 b the one 2 reply. Can't even express my gratitude
in words. However, it was simply an example. You may also take the example as:
1. Manufacturer;
2. Product;
3. Product Code (Which may only be one for a model of a specific make)

A. If u directly enter the ProdCode manually, Manufacturer & Product fields
update automatically;
B. If u select a Product, Manufacturer & ProdCode updates automatically;
C. However, if u select only a Manufacturer, Product field Combo Box filters
as cascading;

Thanx again Allen!
--

Best Regards,
FARAZ A. QURESHI

I don't think the approach you have here is going to work.

In some countries, there is no direct relation between cities and zip codes.
For example, here in Australia, one city can have multiple zip codes (e.g.
where there are multiple post offices in the heart of the city), and one zip
code can apply to multiple cities (where one post office services several
areas.) That means that there is no way to derive the result you ant from
the 2 tables you have.

To address your specific questions, this article explains how to limit the
Cities to those from the selected Country:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

And if it's any help, here's a routine we use where the user types the city
name into a combo and it looks up the postal code:
Combos with Tens of Thousands of Records - Postcodes example
at:
http://allenbrowne.com/ser-32.html

 

[Allen Browne]

Use the link provided previously for instructions on cascading combo.

In your target table, is ProductCode a requried field? It is is, and it is
unique, then this table must not store Manufacturer and Product. Having all
3 fields opens the door to garbage (records where the ProductCode stored
does not match the Manufactuer and Product.)

Posting a deceptive question may not give desirable results.

 

[FARAZ QURESHI]

Thanx again Allen!
No doubt the doors to garbage might open and that the reason I seek solution
to this query.
Can I have the two fields of be depending on each other i.e. the first entry
leading to other?
For example, if I directly enter the ProdCode, the Product ComboBox
reflecting the relevant product only and if the entry in Product ComboBox is
entered or later updated the ProdCode too updates.

 

[Allen Browne]

Why have all three?

If ProdCode is unique, the table needs to store only this value. You can
determine the others from that.

 

[FARAZ QURESHI]

Sorry but I think I am still not clear! I am not asking for STORAGE but
DISPLAY of three. I may store only 1 but show the other relevant two on the
form.

Take for example:

I want to enter the sales made by an employee;

If I know his Employment Number I may enter it in the form for storage upon
which his Department and his Own Name may appear/be displayed on the two
related combo boxes on the form;

Such two additional combo boxes' entries may even not be stored but atleast
confirm the entry of the Employment Number to be correct;

However, if I am not sure about the Employment Number, I shall open the
Combo Box of Employees' Names and upon selecting his name from there the
Department field and the Employement Number updates and Employement Number
shall be stored;

But then uh oh I realize that the Department Name is incorrect! There are
two employees of the same name in different Departments OR I don't remember
the exact name. Now I resume the exercise 1. Select one Department 2. Names'
Combo Box filters and updates 3. Select the employee name 4. The Employment
Code updates and is the only field to be stored.

Employees Table consists of:
1. Employee Code (Primary ID Key)
2. Name
3. Department

Sales Table consists of:
1. Invoice (Primary ID Key)
2. Date
3. Quantity
4. Employee Code (Foreign ID Key)

Sorry for all the inconvenience, Allen!
Please see if you can help!

 

[Allen Browne]

Presumably you also have a Department table, and the Employees table uses
the DepartmentID rather than the name.

Create a query that uses both Employee and Sales tables.Select all fields
from Sales, the employee's name and DepartmentID from Employee. Use this
query as the RecordSource for your form.

In the form, use a combo for the employee code (so it displays the name but
stores the code.) Include an unbound combo for the department.

In the Current event of the form, assign the value of the DepartmentID field
to the unbound combo.

In the AfterUpdate event procedure of the unbound combo, set the RowSource
of the EmployeeCode combo. The article I linked in my first reply to you
shows how to code this.

You have several other issues to sort out as well, such as what to do if the
user chooses a department while there's a value in EmployeeCode that doesn't
match, e.g. you might want to assign Null to EmployeeCode.

You probably want to use the Undo events to reset the unbound combo also.

 

[FARAZ QURESHI]

Thanx Allen!

U R the best guide (After Chip & Max in Excel) that I have ever come across
in my experience with discussions! Wish I had a real ACCESS to experts like u
all around the clock!

Thanx again!

 

[marcelle b bouvet]

"FARAZ QURESHI" <[email protected]> a écrit dans le
message de groupe de discussion :
(e-mail address removed)...