Replace

[Rajtomar]

have a simple data base of maintaining expenses. When user enter
expense details in the expense form, two fields use lookup table i.e.
on whom the expense was incurred and expense category like food,
clothes, study etc. I 've given a command button near these lookup
boxes for the user to add/edit the list of lookup table(In case the
user doesn't fid a suitable match in the category). This command
button opens the form connected to lookuptable in datasheet view. The
user can now view and add a new category or edit a existing category.
This same lookup table is used to send criteria to a select query. So
the select query will always get the updated lookup table. Now what
about the records created before with the older name of the category.
They will never be able to be retrieved by that query(as the criteria
lookup is also changed)

Now my problem is that if a user edits a category in a lookup table,
than the previous name of the category will never be selected for the
query and hence will always remain unlooked in the table.
So there should be a way that as soon as a user edits the name of a
catgory, a procedure should run and should replace all the existing
records in the table with the new name as edited by the user.


I hope i am clear to every one and also i am a sure that if there is
a
procedure for this its gonna be tough one.

 

[Allen Browne]

So your expense table has an ExpenseCategoryID field that gets its value
from an ExpenseCategory lookup table? If so, it shoul be very easy to do
what you are asking for.

Open the Relationship window (Database Tools tab of the ribbon in Access
2007, or Tools menu in previous versions.) Make sure you have a relationship
between Expense and ExpenseCategory, and that you have checked the box for
Relational Integrity. If the ExpenseCategoryID is a Text type field in table
design (not an AutoNumber field), also check the box for Cascading Update in
the Edit Relationship dialog.

The enforced relationship means that no one can enter an expense category
that does not have a match in the lookup table. But you can change the
expense category name in lookup table, and it will show correctly in all the
records that use this category.

If the ExpenseCategoryID is a text field, the cascading update does what you
asked for, i.e. all related records in the Expense table are automatically
updated to the new name.

The button beside your combo for managing the lookup list is fine. If you
are interested in some alternative approaches, see:
Adding values to lookup tables
at:
http://allenbrowne.com/ser-27.html

 

[sippanun teerarat]

duggggggggggggggggggggggggggggggg you