Is there a way

[Nicolette]

Is there a way to have a checkbox determine if fields in a form auto populate?

I usually keep billing and shipping addresses seperate but I was trying to think of a way to condense it down so I was thinking would it be possible to have a checkbox that is if shipping is same as billing it would auto populate the billing address for use as the shipping address for an order but that leads me to another question I was thinking that my order table would then have to contain this shipping address but only if different from the customers billing address... Since I do have customers that use multiple shipping addresses...

or is there a better way to do this?

 

[PNGBill]

Try adding a tick box to your Customers Table to be ticked if the two addresses are the same.

In your forms & queries that you use to enter a new order, include an If Then that will use the Billing Address instead of Shipping Address.

Just depends how your form is setup.
If you use DLookup in the your form text box control, then this is where you will put your if then statement.
If a query is used, then put the if then in that.

You may need to ask advice specific to what you are trying to do as you proceed.

 

[Nicolette]

Ok so in working on this I got to wondering.... would it be better to create the data for tblOrderHeader and tblOrderDetails at the same time instead of two seperate forms.

 

[vbaInet]

What I would do is to have a button that will open a pop up form that contains a list of all the customer's billing and shipping addresses. To make it even more intuitive, you will split the addresses into two containers/frames, billing addresses at the top and shipping addresses below that. Use a listbox to display the addresses.

The listbox will have two columns - first will be the address line and the second the postcode.

A button at the bottom of this form will read "Copy to Billing" or "Copy to Shipping" depending on where it was called. You can use OpenArgs for this. When clicked it will copy the address to the appropriate part of the form (and probably close).

Since the user has these addresses readily available, it's up to him/her to select the appropriate one.

With regards the checkbox, disable it until a billing address has been entered. Once checked, it will copy what's in the billing address to the shipping address. So yes, you will still need to keep both. One reason why I mentioned copying from the billing to shipping is because sometimes your customer may want his/her neighbour to receive the goods on his/her behalf and instead of the user having to enter the address they can simply alter the house number.

 

[Nicolette]

ok so just to make sure i understand because I think you are on the same lines as I am I have just a couple clarifying questions if that is ok...

1) When I enter a new customer would I enter Just the billing address and the current shipping address for that order?

2) How would you handle customers that regularly ship to multiple addresses. Like I have one lady that once a month buys a charm for each of her closest friends there is 4 they are each sent to a different address. she has done this for about 6 months now every once in a while she will also have one sent to her daughter and grand-daughter also so I keep a total of 7 address for her. this isn't usual but I do have a number of other customers that sometimes have orders sent to their home or their work or a friend, neighbor or relative. Since they are regular customers how would I keep all these different addresses that each uses?

Should I have a table set up of addresses assigned to each customerID?

I think this is getting very technical and difficult....

 

[Nicolette]

I combined my form that fills in tblOrderHeader and the form that fills in tblOrderDetails into one form. So now i'm thinking that I should have a contnious form that lists all orders and from there I have buttons for adding new orders, and editing existing orders (I'm using orders meaning an order that has NOT been fulfilled yet) Am I still on an appropriate path with this or am I vearing in the wrong direction again?

 

[Nicolette]

Oh I just thought of something is there a way to change the title that is visible on the form depending on what action the form will be performing ie. adding, editing?

 

[vbaInet]

This can get very techincal.

1) Do you mean when you enter a new customer or a new order?

2) That's now taking things a step further. You would need a Recipients table but to keep things simple you may have just one more field setup for Recipient's Name which would relate to an order.

A customer can have multiple addresses (like you've mentioned) so I'm imagining that you have a Addresses table setup? For each address you will indicate whether it's a billing or shipping address. A field will be sufficient for this. But then again, would this require another Address Type table?

With your regards using a continuous form for your orders I think you should have a separate Orders form. You can list all the orders in the continuous form but new orders should be entered via a "Single" Orders form.

Yes you can change the caption of a form using Me.Caption = "adding"

 

[Nicolette]

Me.Caption = "adding"

where do I put this?

 

[vbaInet]

On the event of the button that creates the new record for "adding" or on the button that edits the record for "editing".

Or look into using the On Dirty event and Me.NewRecord

 

[Nicolette]

ok and how about the title of the form? I have been playing with it and I can't figure it out

 

[Nicolette]

Yeah buddy I figured something out on my own... I got the right titles to show in the form not the window caption still working on that one....

On the form I put two titles one "Edit Customer" and the other "Add Customer" I set the alignment exactly the same on both they are identical just the first word inside is different then in the action of the edit button of the frmCustomerList I set the action to SetProperty in the Arguments I set Auto_Title0, to Visible and -1 (True)and i did another SetProperty and in the Arguments I set Title2 to Visible and 0 (False)

and I did the opposit for the add button.

And it works Perfect. Now i know there probably is another way to do it and i'm all ears but i'm still stoked just to have figured something out on my own from start to finish!

 

[vbaInet]

Just as you would make the label visible it's the same way you would write Me.Caption = "something". But I think the label is better and one label is sufficient.

 

[Nicolette]

ok so now i'm playing with this for the form caption can you see what is wrong with it I can't figure it out.

Private Sub Form_Current()
Me.Caption = "Customer:& " " & IIF([FirstName] AND [LastName] Is Null, "New Customer", Me!FirstName.Value & " " & Me!LastName.Value)
End Sub

I'm sure it is something simple but I have not been able to figure it out i'm playing with a sample I found.

 

[Nicolette]

But I think the label is better and one label is sufficient.

what I kept running into was I couldn't get the text to change and if i deleted all the text the text box obviously was removed also.

 

[SteveSchapel]

Nicolette,

Nearly. Try it like this:

Private Sub Form_Current()
   Me.Caption = "Customer: " & IIf(IsNull(Me.FirstName) And IsNull(Me.LastName), "New Customer", Me!FirstName & " " & Me!LastName)
End Sub

 

[Nicolette]

OH I was so close! Thanks for the help! Notes taken for future reference!

 

[Nicolette]

1) Do you mean when you enter a new customer or a new order?

New customer.

2) That's now taking things a step further. You would need a Recipients table but to keep things simple you may have just one more field setup for Recipient's Name which would relate to an order.

So could I have the recipient field work in combination with the addresses table?

A customer can have multiple addresses (like you've mentioned) so I'm imagining that you have a Addresses table setup? For each address you will indicate whether it's a billing or shipping address. A field will be sufficient for this. But then again, would this require another Address Type table?

I'm working on it.

With your regards using a continuous form for your orders I think you should have a separate Orders form. You can list all the orders in the continuous form but new orders should be entered via a "Single" Orders form.

Yes ok that is what I was thinking.

 

[Nicolette]

What I would do is to have a button that will open a pop up form that contains a list of all the customer's billing and shipping addresses.

This is in the form for the orders correct?

To make it even more intuitive, you will split the addresses into two containers/frames, billing addresses at the top and shipping addresses below that.

So would this be like a split form in datasheet view? Would it be like cutomer info on top with all their addresses below?

Use a listbox to display the addresses.

The listbox will have two columns - first will be the address line and the second the postcode.

And this would also be in the form for the order?

A button at the bottom of this form will read "Copy to Billing" or "Copy to Shipping" depending on where it was called. You can use OpenArgs for this. When clicked it will copy the address to the appropriate part of the form (and probably close).

I think I understand that part

 

[vbaInet]

New or old customer doesn't really matter as long as you have a Addresses table. The customerID would be a foreign key in the Addresses table. The PKs in the Addresses table could be CustomerID and Postcode. There will be a field which will indicate whether that address is Billing or Shipping.

As for the recipients field I mentioned, since you're not really worried about tracking recipients, it will be a field in the Orders table. The default name should be the customer's name. So if a customer wanted to send to other people, you add a shipping address and write the Recipients name in the recipient's field. However, if you want to link a recipient to an address permanently then you need a Recipients table. It's up to you how much normalisation you want to do.

The pop-up form will be related to the Orders form yes. One button each for Billing and Shipping address controls. This is where OpenArgs comes in, because you can use that to determine whether the button to open the form was called on the Shipping or Billing side.

The way the Addresses will be displayed on the pop-up form will be in a listbox with two columns. Two listboxes, one at the top with a Billing label, and the other with a Shipping label.