Shipping address different from customer address

[CC]

Hi! I'm setting up an Orders database. My users will enter the order
information using a form. Here is how I'd like it to go - please let me know
if this is possible and how I'd set it up:

1. The first thing they will enter is the customer ID. The shipping address
fields should autopopulate based on the customer ID that is entered.

2. If the shipping address is different from the default address, the user
can type in a shipping address ID in another field, and that address will
appear.

3. If the shipping address is a one-time use only and doesn't exist in a
table, then the user can manually type it into the fields.

Any help is appreciated! Thanks so much!
C

 

[PC Datasheet]

You need these tables:
TblCustomer
CustomerID
CustName
CustMailingAddress
CustShippingAddress
CustCity
CustState
CustZipcode

TblOrder
OrderID
CustomerID
OrderDate
<Your other order fields>

Your Order form should be based on TblOrder. CustomerID should be entered
with a combobox named CustomerID based on TblCustomer. Put the following
code in the AfterUpdate event of the combobox:
Dim Db As DAO.Database
Dim Rst As DAO.Recordset
Set Db = CurrentDb
Set Rst = Db.OpenRecordset("TblCustomer",dbOpenDynaSet)
Rst.FindFirst "[CustomerID] = " & Me!CustomerID
With Rst
Me!ShippingAddress = !CustShippingAddress
Me!ShippingCity = !CustCity
Me!ShippingState = !CustState
Me!ShippingZipcode = !CustZipcode
End With
Rst.Close
Set Rst = Nothing
Set Db = Nothing

The above is your basic code for your Q1. You can modify this for 2 and 3.

 

[CC]

Thanks for your help! But actually, in my situation, I have one customer who
has 68 different offices, so there are 68 possible shipping addresses. I
still need to use the same customer ID for this one customer because all
billing and tracking go to a central place.

So I'm considering creating another table that's just for Shipping
Addresses. Would I still be able to do what I want to do?

You need these tables:
TblCustomer
CustomerID
CustName
CustMailingAddress
CustShippingAddress
CustCity
CustState
CustZipcode

TblOrder
OrderID
CustomerID
OrderDate
<Your other order fields>

Your Order form should be based on TblOrder. CustomerID should be entered
with a combobox named CustomerID based on TblCustomer. Put the following
code in the AfterUpdate event of the combobox:
Dim Db As DAO.Database
Dim Rst As DAO.Recordset
Set Db = CurrentDb
Set Rst = Db.OpenRecordset("TblCustomer",dbOpenDynaSet)
Rst.FindFirst "[CustomerID] = " & Me!CustomerID
With Rst
Me!ShippingAddress = !CustShippingAddress
Me!ShippingCity = !CustCity
Me!ShippingState = !CustState
Me!ShippingZipcode = !CustZipcode
End With
Rst.Close
Set Rst = Nothing
Set Db = Nothing

The above is your basic code for your Q1. You can modify this for 2 and 3.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Hi! I'm setting up an Orders database. My users will enter the order
information using a form. Here is how I'd like it to go - please let me know
if this is possible and how I'd set it up:

1. The first thing they will enter is the customer ID. The shipping address
fields should autopopulate based on the customer ID that is entered.

2. If the shipping address is different from the default address, the user
can type in a shipping address ID in another field, and that address will
appear.

3. If the shipping address is a one-time use only and doesn't exist in a
table, then the user can manually type it into the fields.

Any help is appreciated! Thanks so much!
C

 

[PC Datasheet]

How about something like:

TblCustomer
CustomerID
CustomerName

TblAddressType
AddressTypeID
AddressType (Mailing, Shipping)

TblCustAddress
CustAddressID
CustomerID
AddressTypeID
StreetAddress
City
State
Zip

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Thanks for your help! But actually, in my situation, I have one customer who
has 68 different offices, so there are 68 possible shipping addresses. I
still need to use the same customer ID for this one customer because all
billing and tracking go to a central place.

So I'm considering creating another table that's just for Shipping
Addresses. Would I still be able to do what I want to do?

You need these tables:
TblCustomer
CustomerID
CustName
CustMailingAddress
CustShippingAddress
CustCity
CustState
CustZipcode

TblOrder
OrderID
CustomerID
OrderDate
<Your other order fields>

Your Order form should be based on TblOrder. CustomerID should be entered
with a combobox named CustomerID based on TblCustomer. Put the following
code in the AfterUpdate event of the combobox:
Dim Db As DAO.Database
Dim Rst As DAO.Recordset
Set Db = CurrentDb
Set Rst = Db.OpenRecordset("TblCustomer",dbOpenDynaSet)
Rst.FindFirst "[CustomerID] = " & Me!CustomerID
With Rst
Me!ShippingAddress = !CustShippingAddress
Me!ShippingCity = !CustCity
Me!ShippingState = !CustState
Me!ShippingZipcode = !CustZipcode
End With
Rst.Close
Set Rst = Nothing
Set Db = Nothing

The above is your basic code for your Q1. You can modify this for 2 and 3.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Hi! I'm setting up an Orders database. My users will enter the order
information using a form. Here is how I'd like it to go - please let

me
know

if this is possible and how I'd set it up:

1. The first thing they will enter is the customer ID. The shipping address
fields should autopopulate based on the customer ID that is entered.

2. If the shipping address is different from the default address, the user
can type in a shipping address ID in another field, and that address will
appear.

3. If the shipping address is a one-time use only and doesn't exist in a
table, then the user can manually type it into the fields.

Any help is appreciated! Thanks so much!
C