Newbie If...Then question...please help

[rbemiller]

I have a form in which the user selects the Sales Order number, then all other fields are populated based on that number. Among those fields are info for customers' address. The Issue: I have a shipping table and a billing table, if there is address info in the shipping table, the form needs to be populated w/ the shipping table info, otherwise, it needs to be populated with the address info from the billing table...which would always have address info.
Can anyone help me with the If... Then code I would need for this?

 

[cpod]

In the query that your form is based on enter something like this in a new field:

CustAddress:iif([shippingaddressfield] is null,[billingaddressfield],[shippingaddressfield])

You then reference CustAddress in your form.

 

[Pat Hartman]

What you really need to do is change your table structure so that ALL addresses are in a single table and probably your business logic also. If I understand your current structure, you allow for 1 billing address per customer (which is reasonable) and you allow for 1 optional shipping address (which is not reasonable). You must be working with clients who only want to ship to one location. That is too narrow minded for any order entry system I have ever used.

A workable table structure is:

tblAddress:
AddressId (autonumber primary key)
CustomerId (foreign key to tblCustomer)

tblCustomer:
CustomerId (autonumber primary key)
BillingAddressId (foreign key to tblAddress)
rest of the customer fields

tblOrder:
OrderId (autonumber primary key)
ShipToAddressId (foreign key to tblAddress)
rest of the order fields

This structure allows for a single billing address and as many shipping address as are necessary. Some systems actually store the ShipTo address with the order. But if you have a lot of repeat business and customers that usually use the same shipping address, my structure will require much less storage. It also means that if the customer uses the same address for both billing and shipping, you only need to keep one instance which will eliminate many update problems if the customer submits an address change.

The Order form uses a combo that allows the user to choose one of the customer's addresses as the ShipTo address. It should also have a not in list event to allow you to add new shipping address on the fly.

 

[rbemiller]

I appreciate your reply Pat, but the problem is that I'm pulling all of the info for my application from a 1/2 Million dollar software package, and thus it is not feasible or even possible for me to change something within that system. The more likely scenario is that I've probably been pointed in the wrong direction for this shipping info. This is an extremely sofisticated system, and I'm sure they've got good table structure. It's just that I don't know where to look for it. But, enough about me...