Iif function trouble

[Guest]

I am putting a simple customer data base together that includes the field
[BillAddress] and [ShipAddress]. On some records they are the same. So, I
included a yes/no box [SameShipAddress?]. To simplify data entry I would
like the [ShipAddress] to fill automatically if it is the same as
[BillAddress]. For the "control source" on the [ShipAddress] I used:

=Iif(([SameShipAddress?]=yes),[BillAddress],null)

This results when I check the [SameShipAddress?] box the [ShipAddress] fills
with the same as [BillAddress] but when the box is not checked I am unable to
type any text because it is bound to the expression. Is there a value for
the <false part> that will allow me to enter whatever into [ShipAddress]?

 

[John W. Vinson]

I am putting a simple customer data base together that includes the field
[BillAddress] and [ShipAddress]. On some records they are the same. So, I
included a yes/no box [SameShipAddress?]. To simplify data entry I would
like the [ShipAddress] to fill automatically if it is the same as
[BillAddress]. For the "control source" on the [ShipAddress] I used:

=Iif(([SameShipAddress?]=yes),[BillAddress],null)

This results when I check the [SameShipAddress?] box the [ShipAddress] fills
with the same as [BillAddress] but when the box is not checked I am unable to
type any text because it is bound to the expression. Is there a value for
the <false part> that will allow me to enter whatever into [ShipAddress]?

I'd use a different technique. You can "push" the BillAddress into the
ShipAddress in the afterupdate event of the checkbox.

Select the checkbox in form design view; view its properties. On the Events
tab click the ... icon by the AfterUpdate event, and select Code Builder. Edit
the two lines Access gives you to

Private Sub ShipSameAddress_AfterUpdate()
If Me![SameShipAddress?] Then
If IsNull(Me!BillAddress) Then
MsgBox "Please fill in billing address", vbOKOnly
Else
Me!ShipAddress = Me!BillAddress
End If
End If
End Sub

Note that this will stomp on any bill address that's already been entered. Not
sure if you want to do that!

John W. Vinson [MVP]