Automatically update the contents of a table field

[iliast]

How do I automatically update the contents of a table field, depending on what I'm importing into another field in the same table, using a compo-box?
I have two tables. One is called "Materials" (IDMaterial, MaterialName, MaterialCode) and the other is table "Use" (IDUse, Date, IDMaterial, MateriaslCode).


I want in Table Use, when I select Material from the compo-box (IDMaterial), to automatically appear in the next field, the corresponding MaterialCode, from the Materials table.
I emphasize that this is what I want to do ONLY ON THE TABLE and that I will only work on. THERE WILL BE NO FORMS.
Thank you.

[June7]

Why? This defeats a principle of relational database not to duplicate data. Save IDMaterial, pull MaterialsCode in a query that joins tables.

Might be able to accomplish this with a Data Macro that executes an UPDATE action SQL. I don't use Data Macros.

[iliast]

Why? This defeats a principle of relational database not to duplicate data. Save IDMaterial, pull MaterialsCode in a query that joins tables.

Might be able to accomplish this with a Data Macro that executes an UPDATE action SQL. I don't use Data Macros.

My dear friend, what about to reveal specific only columns in the compo-box itself...? I don't mind to not have an extra field for "MaterialCode". It's enough for me selecting a Material from compo-box to see in the same field 2 columns. Name of the material, and code of the material. The result is the same for me. But... only working on the table. No forms.

[June7]

Why no form?

Build combobox (lookup field) in table so MaterialCode is displayed for selection but IDMaterial is saved.

[CJ_London]

in a form, you would reference the relevant combo column with something like

=fldName.column(2)

but this is not functionality available in a table.

So unless you are prepared to use a form, you are out of luck. You could look at using a data macro which might or might not work (I don't use them either) but that is only triggered when you leave the row, not when you update your combo column.

only other suggestion is to concatenate your material name and code together as the displayed column.

to clarify terminology

depending on what I'm importing into another field in the same table, using a compo-box?

you are not importing data, you are creating a link. Once that link is created, you can show the related data in a query.

[iliast]

in a form, you would reference the relevant combo column with something like

=fldName.column(2)

but this is not functionality available in a table.

So unless you are prepared to use a form, you are out of luck. You could look at using a data macro which might or might not work (I don't use them either) but that is only triggered when you leave the row, not when you update your combo column.

only other suggestion is to concatenate your material name and code together as the displayed column.

to clarify terminology

you are not importing data, you are creating a link. Once that link is created, you can show the related data in a query.

Well, english isn't my native language, I'm sorry Ajax :-)
The conclusion is that what I want is not done on a Table.
Okay. Let's look at the Form option.

[June7]

I normally do not recommend lookup field in table but that would meet your requirement to display MaterialsCode without a form.

[CJ_London]

@June - OP want's to display material code and name in two separate columns

@iliast - did you consider my suggestion of concatenating code and name into one column?

your rowsource would be something like

SELECT IDMaterial, MaterialCode & " - " & MaterialName FROM Materials ORDER BY MaterialCode

[June7]

Or:

SELECT IDMaterial, MaterialName & " - " & MaterialCode FROM Materials ORDER BY MaterialName;

So user types material name instead of material code but both values are displayed. Use whichever makes users happier.