Is your billing address the same as your shipping...

[bassman197]

Not exactly what I want to do, but very similar! In our contract writing program, ONE client may have MANY vendors. Usually, all the vendors are reporting to the same address - the one on the client's contract, so I usually want all of the vendor contracts to display the same location address as the one on the client's. But what if the florist needs to go to the church and all the other vendors need to go to the banquet hall? I need a way to have the client address be the default on the vendor contract, while allowing an alternate address to be entered if needed. I'm thinking of adding a yes/no field to the vendor contract called UseAlternateAddress, which would default to no. Then putting something like this on the ON CURRENT function for the vendor contract form:

on current:

If me.UseAlternateAddress.value = no then
me.VendorLocationName.controlsource = forms![ClientLocationName]me.VendorLocationAddress.controlsource = forms![ClientLocationAddress]
(etc for City, State, zip) ELSE

me.VendorLocationName.controlsource = me.[AltLocationName]
me.VendorLocationAddress.controlsource = me.[AltLocationAddress]
(etc for City, State, zip - the Alt fields reside in the same table as the other vendor fields, along with the UseAlternateAddress yes/no field).

End If
End Sub

This sounds like it should work, but seems like a sweeping change, giving Access a lot to calculate as someone is using the mouse wheel to scroll through the vendor contracts. Maybe I'm just being overly cautious - is using VBA to change a control source common practice? I just don't want to introduce anything that would make the progarm error-prone. Any advice appreciated!

(The only other way I can think of: I could also just have vendor address fields filled in with the same info as the client address by default, then the user could simply type over them if needed (no control source manipulation needed). But this means that the same address would be stored in two different places (or more, if 7 or 8 vendors!) - not a very elegant solution.)

 

[nathansav]

What i would do is have a separate table for each address. You can then link this to your main contract with a selection of addresses in the address table and an ID for the address in the contract table.

ie.

tbl_Main_Contract
Main_Contract_ID AutoNum
Main_Contraact_Name Text
Florist_Address Number
Cheauffer_Address Number

tbl_Addresses
Address_ID
Address_Line1
Address_Line2
Address_Line3

Then next to each address you can have a drop down, "select * from tbl_Addresses" which would allow you to select an address, and have the ID bound to the record.

When the contract is 1st set up, the address of the contract is put straight into the address table for selection.

Hope this helps.