Copying data from one table to another

[Plumber]

I am running Access 2010. I need to fill in data on a form from one table to another on the lost focus event. Here is the code created using Expression Builder, which doesn't seem to work: =IIf([Device Address]=" ",[Device Address]=[Billing Address],[Device Address])

Any and all advice will be appreciated.

 

[Sketchin]

Where are putting this code, and in plain english, what is the IIF statement meant to do?

 

[boblarson]

Plumber -

1. "" is not the same as NULL. So, a NULL field will not be equal to "" (which is an "empty string").

2. Setting that in the control properties dialog isn't going to save anything to the table.

3. Along the same vein as Sketchin's question, what tables are involved, what does your form have as its record source, and how are you attempting to use your form?

 

[Plumber]

The two tables on which the form is based are CustomerTbl and TestRecordTbl. CustomerTbl has 'BillingAddress' field and TestRecordTbl has DeviceAddress field. Both fields are on the form that I am using

 

[Plumber]

The RecordSource for the form follows:
SELECT CustomerTbl.*, TestRecordTbl.*
FROM CustomerTbl INNER JOIN TestRecordTbl ON CustomerTbl.CustomerName=TestRecordTbl.CustomerName;

 

[boblarson]

IF your form's record source is updateable (and that is not necessarily the case but if you can update info in the form then it would be), you can set the value using VBA. But the lost focus event may not be the best. But for the moment let's say it is. You would put the code in the VBA Event Procedure not as a property in the events tab like you currently have.

See here for an example of how to get to the VBA window (if you don't know):


And the code would be

If Len(Me.DeviceAddress & vbNullstring) = 0 Then
   Me.DeviceAddress = Me.BillingAddress
End If

There's no need for an else because if there is something in there it will then keep it but if not then the Len code along with the appending of vbNullString will deal with both Nulls and Empty Strings ("").

 

[Plumber]

Thanks, I'll try this.

 

[Plumber]

Thanks Boblarson

That works fine. I am so grateful.

 

[Plumber]

Sketchin,
Thanks for all your help.