How link a form's field to more than one data sources?

[farah]

Hi,

I have a field in my form that was originally linked to a table and worked fine. Recently, I added some new fields to my form. Now, I want that particular field to copy a number from another field, if a condition is met; and if that condition is not met, I want it to be linked to the original table as it was before.

I appreciate your help,
Farah

[June7]

Do you want that 'number from another field' saved into the original field? Why are you duplicating data? Why do two fields need the same value?

What you want requires code to be executed by some event. The trick is figuring out what event(s).

[farah]

June7,
Thanks very much for your reply. Actually, I had a field on my form to record waste reductions. We have 250 facilities reporting those numbers to us and each of them has a form to be updated. Each year, we had to overwite that field to record the new data. Recently, I added new fields for each year of 2012, 2013, 2014 to record the related numbers and avoid overwriting data. The original field that I had contains waste reduction data from 2002 to 2012, based upon what year we received the data from facilities. For example in one form, it maybe 2007 data and in another form it may be 2012 data.

Now, I want the new field that I created for 2012 to read the old field and if the data recorded in that field is for 2012, copy it in the new field. Otherwise, I want the new field to be blank and linked to my table that contains the data for 2012, so the operator can enter the 2012 data in that field. I tried to use the expression builder and define conditional terms to do that, but was not successful. If I can not do that, I will have to go through all 250 forms and check if there is any data for 2012 in the old field and transfer it to the new field.

I hope it is clear what I need to do.
Thanks,
Farah

[June7]

Field for each year and a table for each year is not normalized data structure and really very poor design.

Should be one table for all years with a field for the year.

Should be one form and filter records to view desired year.

[farah]

Thanks for your comment. I have only one table with all years. Only on forms, I have fields for different years. If it was just the forms, it would work fine but I run Crystal reports based on the data on my forms. If I could have all 2012 data in one field, it would be easier to link the Crystal report to the database.

Farah

[June7]

Sorry, I am confused. Why are there different textboxes on form for each year? Why do you use Crystal reports and not Access reports? I don't know how Crystal report is based on data on form. Doesn't Crystal link to tables/queries? Can't Crystal report have dynamically filtered recordsource?

[Demerit]

On the same table, you should use one field for year and use queries to filter data to what you want. That way flexibility is increased

[farah]

Thank you guys! I appreciate your help.