Update field only if field is not null

[Brett]

My database has 2 tables: Table1 & Table2. If a field is not null on a
record in table2, then the not null fields in table1 that correspond to
the records in table1 needs to be updated to match the field in table2.

What I have is a form that is linked to Table2. If the users want to
change a field in the main database (table1), they fill the change in
to the form (which is linked to table2) If there is no change to the
field, they simply leave it blank and that particular field won't be
updated on the main database(table1). The users can change up to 30
fields listed on the form.

For example, I have a record in a Table2. Of the 30 available fields,
only 3 of them are filled in and the rest are null. So I want to be
able to update just those 3 fields in the main table (table2). This is
easy to do if it's one record. But I'm trying to automate this process
and want to be able to do this update many times for different records.
I have tried to do this using a update query that has EVERY field from
Table2 in it updating to EVERY corresponding field in Table1 based on a
criteria of some kind.

I've tried putting criteria of <>Null in the criteria of each field in
the update query. But, since there is usually one field in Table2 that
is null, none of the other fields are not null won't update.

Also, I've tried to use an IIf statment in the Field line: something
like IIf([Table2]![Field1]<>Null, [Table2]![Field1],
[Table1]![Field1]). Also, I've tried IIf(IsNull([Table2]![Field1]),
[Table2]![Field1], [Table1]![Field1]). So basically I guess my
statement above says something like, if field1 in table2 is not null,
update field1 in table1 to the value of field1 in table2, if field1 in
table2 is null, update field1 in table1 to itself. The problem here I
get is an error that says my IIf(*****) statement is not a valid name
or the text is too long.

Does all this make sense and if so do you know a way to accomplish
this? Also let me know if you need more detail.

Thanks

[ManningFan]

I'm a bit confused. You can't update more than one record at a time,
so why not update the record on the AfterUpdate event on the form?
Store each field in a variable, and compare them to the original record
using a DLookup. Then you can use some If/Thens to update the values
if len(me.txtField1) 1.

I'm not sure if that made sense. I know what I want to say but I'm not
sure I said it right.
Brett wrote:

My database has 2 tables: Table1 & Table2. If a field is not null on a
record in table2, then the not null fields in table1 that correspond to
the records in table1 needs to be updated to match the field in table2.

What I have is a form that is linked to Table2. If the users want to
change a field in the main database (table1), they fill the change in
to the form (which is linked to table2) If there is no change to the
field, they simply leave it blank and that particular field won't be
updated on the main database(table1). The users can change up to 30
fields listed on the form.

For example, I have a record in a Table2. Of the 30 available fields,
only 3 of them are filled in and the rest are null. So I want to be
able to update just those 3 fields in the main table (table2). This is
easy to do if it's one record. But I'm trying to automate this process
and want to be able to do this update many times for different records.
I have tried to do this using a update query that has EVERY field from
Table2 in it updating to EVERY corresponding field in Table1 based on a
criteria of some kind.

I've tried putting criteria of <>Null in the criteria of each field in
the update query. But, since there is usually one field in Table2 that
is null, none of the other fields are not null won't update.

Also, I've tried to use an IIf statment in the Field line: something
like IIf([Table2]![Field1]<>Null, [Table2]![Field1],
[Table1]![Field1]). Also, I've tried IIf(IsNull([Table2]![Field1]),
[Table2]![Field1], [Table1]![Field1]). So basically I guess my
statement above says something like, if field1 in table2 is not null,
update field1 in table1 to the value of field1 in table2, if field1 in
table2 is null, update field1 in table1 to itself. The problem here I
get is an error that says my IIf(*****) statement is not a valid name
or the text is too long.

Does all this make sense and if so do you know a way to accomplish
this? Also let me know if you need more detail.

Thanks

[Brett]

ManningFan wrote:

I'm a bit confused. You can't update more than one record at a time,

I'm not trying to update more than one record

so why not update the record on the AfterUpdate event on the form?

Maybe I can do that.....here's some more detail:

The form I created and Table2 which holds the data from the form is
more of a "holding" area. The users inputs the data onto a form. Then
at a later date, they go back to the form, select the record they
created and they "flip a swith," by moving a drop down box from No to
Yes. When the drop down box says Yes, then I can initiate the change to
the fields. I don't really want that changing to yes to trigger
anything. I'd rather maintain the control of when the data is updated
in the main table.

Here's something more specific that I need to do or could do in a macro
or vba procedure:
Check each record (in Table2) to see if the "switch" is flipped to yes,
if yes, then I check each field in the record (in Table2 still). If the
field is null, then I go to the next field. If the field is not null,
then I change the corresponding field in Table1 to the value in table2.
Then go to the next field

Does that make sense?

Store each field in a variable, and compare them to the original record
using a DLookup. Then you can use some If/Thens to update the values
if len(me.txtField1) 1.

Based on the above detail I added would you still do what you
recommended by storing each field in a variable & such??

I was hoping to be able to do this without using vba/sql or
both.....but alas it looks like this is the thing to do..O how my
laziness is continually challenged!!!! I use vba in excel but have not
yet dove into learning vba & sql in access. I have a "beginning access
vba" by wrox publishing that I can reference and of course lots of
online documentation. But, I think I need pointed a specific direction.

I'm not sure if that made sense. I know what I want to say but I'm not
sure I said it right.

Yes that makes perfect sense

>

Brett wrote:

My database has 2 tables: Table1 & Table2. If a field is not null on a
record in table2, then the not null fields in table1 that correspond to
the records in table1 needs to be updated to match the field in table2.

What I have is a form that is linked to Table2. If the users want to
change a field in the main database (table1), they fill the change in
to the form (which is linked to table2) If there is no change to the
field, they simply leave it blank and that particular field won't be
updated on the main database(table1). The users can change up to 30
fields listed on the form.

For example, I have a record in a Table2. Of the 30 available fields,
only 3 of them are filled in and the rest are null. So I want to be
able to update just those 3 fields in the main table (table2). This is
easy to do if it's one record. But I'm trying to automate this process
and want to be able to do this update many times for different records.
I have tried to do this using a update query that has EVERY field from
Table2 in it updating to EVERY corresponding field in Table1 based on a
criteria of some kind.

I've tried putting criteria of <>Null in the criteria of each field in
the update query. But, since there is usually one field in Table2 that
is null, none of the other fields are not null won't update.

Also, I've tried to use an IIf statment in the Field line: something
like IIf([Table2]![Field1]<>Null, [Table2]![Field1],
[Table1]![Field1]). Also, I've tried IIf(IsNull([Table2]![Field1]),
[Table2]![Field1], [Table1]![Field1]). So basically I guess my
statement above says something like, if field1 in table2 is not null,
update field1 in table1 to the value of field1 in table2, if field1 in
table2 is null, update field1 in table1 to itself. The problem here I
get is an error that says my IIf(*****) statement is not a valid name
or the text is too long.

Does all this make sense and if so do you know a way to accomplish
this? Also let me know if you need more detail.

Thanks