Auto Populate Two Columns

[access2010]

We are trying to Auto Populate 2 fields in our Access 2003 table by choosing one field.

When the Field “Code” is Populated we would like to Auto Populate the Description Field.

Can we please receive your suggestion on how this should be done?

Thank you.
Nicole

 

[June7]

Why would you have Description field in Vendors table? That would be duplicating data between tables. Just retrieve Description from Vendors_Type in query joining tables.

 

[plog]

It shouldn't be done because that's not how databases work. You don't move data to and from tables--you relate tables and then via queries all the data in those tables is accessible together.

So, Vendors should not have a Description field that is simply copied from Vendors_Type. You already have a Code field, so the two tables are related and thus all the data in both tables is accessible. When you need all the data together in one source you build a query, like the one you have.

 

[Eugene-LS]

Please change your query "Vendors_With_TypeQ" to the following:

SELECT [Vendor#], Code,
DLookUp("Description","Vendors_Type","Code = '" & [Code] & "'") AS Description
FROM Vendors;

 

[plog]

Access2010: Please disregard Eugene's post.

Eugene:

1. You gave absolutely no reason for your proposed change. You need to always give a "why" to people for solutions. Don't just throw code at people. Try and educate them.

2. You are absolutely wrong. Dlookups have no place in queries. If you need data from another table, you JOIN that table in the query and reference the field that way.

 

[June7]

Agree, DLookup() is not appropriate for this case. And it wouldn't save data into table anyway.

These tables have a relationship - use it.

Only reason to save Description would be if there is possibility description could change in future and don't want it changed in existing records. This is an issue usually associated with pricing.

 

[Eugene-LS]

2. You are absolutely wrong. Dlookups have no place in queries. If you need data from another table, you JOIN that table in the query and reference the field that way.

I don't see your solution.

By the way, query:

SELECT [Vendor#], Code, Vendors_Type.Description
FROM Vendors
LEFT JOIN Vendors_Type ON Vendors.Code = Vendors_Type.Code;

Is not editable ...

 

[Eugene-LS]

Don't just throw code at people. Try and educate them.

I delegate this honorable duty to you!
But I don't see any education on your part.

 

[June7]

plog and I have both made comments in an attempt to educate OP. We have not offered any code because we don't think it is needed.

And that query should not be the RecordSource for a form. It could be appropriate for a report.

There are two records for SHP code in Vendors_Type - one needs to be removed. Then set this field as a primary key.

Now your query will allow edit of some fields.

 

[access2010]

Why would you have Description field in Vendors table? That would be duplicating data between tables. Just retrieve Description from Vendors_Type in query joining tables.

Thank you Jun7 for your question.

We had used the field name Description to signify the products or services this vendor supplies for our accounting records.
I will change the field name to "Services_Supplies"

Nicole

 

[June7]

Doesn't matter what you name the field in Vendors table, still say the data should not be there.

 

[access2010]

Please change your query "Vendors_With_TypeQ" to the following:

SELECT [Vendor#], Code,
DLookUp("Description","Vendors_Type","Code = '" & [Code] & "'") AS Description
FROM Vendors;

Please change your query "Vendors_With_TypeQ" to the following:

SELECT [Vendor#], Code,
DLookUp("Description","Vendors_Type","Code = '" & [Code] & "'") AS Description
FROM Vendors;

Thank you, Eugene-LS.
Your suggestion has given us the answer we had been looking for.
Nicole

 

[mike60smart]

We are trying to Auto Populate 2 fields in our Access 2003 table by choosing one field.

When the Field “Code” is Populated we would like to Auto Populate the Description Field.

Can we please receive your suggestion on how this should be done?

Thank you.
Nicole

Hi Nicole

I have attached your database reworked to show how the tables are related

As indicated by others the Description only needs to be in the Lookup Table and not in the Vendors table

 

[access2010]

Hi Nicole

I have attached your database reworked to show how the tables are related

As indicated by others the Description only needs to be in the Lookup Table and not in the Vendors table

Thank you, mike60smart for your database.
We use Access 2003 in our NGO's office; would you be so kind to convert your database to an Access 2003 version.

Thank you.
Nicole

 

[mike60smart]

Thank you, mike60smart for your database.
We use Access 2003 in our NGO's office; would you be so kind to convert your database to an Access 2003 version.

Thank you.
Nicole

Here with 2003 format

 

[access2010]

Here with 2003 format

mike60smart = P.E.R.F.E.C.T.
Thank you.​

Nicole

 

[mike60smart]

Glad to help