How to auto-fill corrosponding field?

[Sam Kuo]

Hi,

How do I link 2 fields in a data-entry subform such that when the "new code"
is entered, the "old code" is automatically filled in? The data-entry table
currently has no records in it.

The master table also contains both new and old code fields.
I've changed new code text box to combo box with row sorce as "SELECT [new
code] FROM mastert table;". But then what do I do with the corrosponding old
id text box so it auto-fills for visual confirmation only?

I've tried this line provided by Gerald Stanley MCSD for a similar problem,
but it didn't work for me -- maybe I wasn't interpreting the line correctly?

setting its ControlSource to something along the lines of
=DLookUp("nameCol", "masterTable", "id = " & txtid)

I tried =DLookUp("old code", "master table", "id = "& new code)

 

[Sam Kuo]

Just to further clarify my problem, the data-entry subform here is no
different from a data-entry form (i.e. there is no linked main form in the
same window). Thanks

 

[John Vinson]

Hi,

How do I link 2 fields in a data-entry subform such that when the "new code"
is entered, the "old code" is automatically filled in? The data-entry table
currently has no records in it.

The old code should certainly NOT be stored in this table, if it can
always be looked up. It would be redundant, waste space, and risk data
integrity - what if the old/new code pair was different in the master
table and this table??

The master table also contains both new and old code fields.
I've changed new code text box to combo box with row sorce as "SELECT [new
code] FROM mastert table;". But then what do I do with the corrosponding old
id text box so it auto-fills for visual confirmation only?

For visual confirmation only, which would be a good idea, just include
both codes in the Combo Box's RowSource. Set the Control Source of a
textbox on the form to

=comboboxname.Column(n)

where (n) is the zero based index of the field you want to see - i.e.
(1) means the second column in the combo's row source query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Sam Kuo]

Thanks John. This is exactly what I needed

Hi,

How do I link 2 fields in a data-entry subform such that when the "new code"
is entered, the "old code" is automatically filled in? The data-entry table
currently has no records in it.

The old code should certainly NOT be stored in this table, if it can
always be looked up. It would be redundant, waste space, and risk data
integrity - what if the old/new code pair was different in the master
table and this table??

The master table also contains both new and old code fields.
I've changed new code text box to combo box with row sorce as "SELECT [new
code] FROM mastert table;". But then what do I do with the corrosponding old
id text box so it auto-fills for visual confirmation only?

For visual confirmation only, which would be a good idea, just include
both codes in the Combo Box's RowSource. Set the Control Source of a
textbox on the form to

=comboboxname.Column(n)

where (n) is the zero based index of the field you want to see - i.e.
(1) means the second column in the combo's row source query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Sam Kuo]

For visual confirmation only, which would be a good idea,

just include both codes in the Combo Box's RowSource.
Set the Control Source of a textbox on the form to

=comboboxname.Column(n)

Just one more question, John.

I later created another subform -- "below" this data-entry subform -- to
show a list of already entered records. By setting the ControlSource of
textbox to what you suggested works great as visual confirmation and so can
not be edited in data-entry form, but it doesn't show up in the list subform.

Can I possibly have the best of both worlds for the list subform(locking
from overwrite and auto-fills)?

 

[John Vinson]

I later created another subform -- "below" this data-entry subform -- to
show a list of already entered records. By setting the ControlSource of
textbox to what you suggested works great as visual confirmation and so can
not be edited in data-entry form, but it doesn't show up in the list subform.

Can I possibly have the best of both worlds for the list subform(locking
from overwrite and auto-fills)?

Base the Subform on a query joining the tables, and set its AllowEdits
property to False, would be my suggestion.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[punking]

This work great for me, helped me out of quite a bind!!
--
\|||/
(o o)
----ooO-(_)-Ooo--------

Thanks John. This is exactly what I needed

Hi,

How do I link 2 fields in a data-entry subform such that when the "new code"
is entered, the "old code" is automatically filled in? The data-entry table
currently has no records in it.

The old code should certainly NOT be stored in this table, if it can
always be looked up. It would be redundant, waste space, and risk data
integrity - what if the old/new code pair was different in the master
table and this table??

The master table also contains both new and old code fields.
I've changed new code text box to combo box with row sorce as "SELECT [new
code] FROM mastert table;". But then what do I do with the corrosponding old
id text box so it auto-fills for visual confirmation only?

For visual confirmation only, which would be a good idea, just include
both codes in the Combo Box's RowSource. Set the Control Source of a
textbox on the form to

=comboboxname.Column(n)

where (n) is the zero based index of the field you want to see - i.e.
(1) means the second column in the combo's row source query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps