Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2010
    Posts
    11

    Unanswered: Same as Billing Address Check Box...copy info...

    I run a company that does online orders. We collect both billing addresses and shipping addresses.

    I want to be able to have a check box in my Customer Information form that I can click to automatically copy the Billing Information over to the Shipping information fields if they are the same.

    I have NO idea how to do this. Please be detailed in your responses on how I would go about doing this.

  2. #2
    Join Date
    Oct 2004
    Location
    Oxfordshire, UK
    Posts
    89
    You're going to have to write some code so lets get you to behind the scenes.
    1. Open the form in design view and select the check box.
    2. Right click to open the popup menu and select "Properties".
    3. In the "Properties" dialogue box select the "Event" tab.
    4. Go to "On Click" and select [Event Procedure].
    5. Click on the elipsis button (...) to take you to the VB editor window where you'll see something like:

    Private Sub Check1_Click()

    End Sub

    What happens next depends on where you record this address information i.e. which fields in what tables. So could you post the table and field names and we'll take it from there.

  3. #3
    Join Date
    Feb 2010
    Posts
    11
    Quote Originally Posted by MyNewFlavour View Post
    You're going to have to write some code so lets get you to behind the scenes.
    1. Open the form in design view and select the check box.
    2. Right click to open the popup menu and select "Properties".
    3. In the "Properties" dialogue box select the "Event" tab.
    4. Go to "On Click" and select [Event Procedure].
    5. Click on the elipsis button (...) to take you to the VB editor window where you'll see something like:

    Private Sub Check1_Click()

    End Sub

    What happens next depends on where you record this address information i.e. which fields in what tables. So could you post the table and field names and we'll take it from there.
    The Billing address info is being recorded in the same form as the shipping address. These two are side by side. I just don't want to have to re-enter the information if I don't have to.

    The name of the Form and cooresponding Table is "Customer Info"
    The field names in the form are as follows....
    Billing Address, Billng City, Billing State, Billing Zip, Billing Country
    Shipping Address, Shipping City, Shipping State, Shipping Zip, Shipping Country

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You could store addresses as separate entities and attach them to customers. Solves a few problems in exchange for some overhead. Also allows an arbitrary number of address to be stored.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Oct 2004
    Location
    Oxfordshire, UK
    Posts
    89
    OK so here's how you could do it:

    Private Sub Check1_Click()
    'This code assumes we got text boxes called txtBillingAddress, txtBillngCity, txtBillingState, txtBillingZip, txtBillingCountry which are linked to billing fields.
    'txtShippingAddress etc. are the text boxes linked to the shipping fields.
    Dim strBillingAddress As String
    Dim strShippingAddress As String

    'Concatonate address control values to see if we've got anything in the either group.
    strBillingAddress = Nz(txtBillingAddress, "") & Nz(txtBillngCity, "") & Nz(txtBillingState, "") & Nz(txtBillingZip, "") & Nz(txtBillingCountry, "")
    strShippingAddress = Nz(txtShippingAddress, "") & Nz(txtShippingCity, "") & Nz(txtShippingState, "") & Nz(txtShippingZip, "") & Nz(txtShippingCountry, "")

    'No billing address so nothing to be done.
    If strBillingAddress = "" Then Exit Function

    'We already have a shipping address so lets ask the user if they really want to update it to the billing address.
    'No if the user clicked on the checkbox by mistake.
    'Yes if the billing address was update after the Shipping address had been updated or
    'if user entered different address by mistake.
    If Not strShippingAddress = "" Then
    If MsgBox("Do you want to overwrite the shipppping address with the billing address?", vbYesNo + vbDefaultButton2) = vbNo Then
    Exit Function
    End If
    End If

    'And finally update the shipping address to the billing address.
    txtShippingAddress = txtBillingAddress
    txtShippingCity = txtBillngCity
    txtShippingState = txtBillingState
    txtShippingZip = txtBillingZip
    txtShippingCountry = txtBillingCountry
    End Sub

  6. #6
    Join Date
    Feb 2010
    Posts
    11

    Smile

    Quote Originally Posted by MyNewFlavour View Post
    OK so here's how you could do it:

    Private Sub Check1_Click()
    'This code assumes we got text boxes called txtBillingAddress, txtBillngCity, txtBillingState, txtBillingZip, txtBillingCountry which are linked to billing fields.
    'txtShippingAddress etc. are the text boxes linked to the shipping fields.
    Dim strBillingAddress As String
    Dim strShippingAddress As String

    'Concatonate address control values to see if we've got anything in the either group.
    strBillingAddress = Nz(txtBillingAddress, "") & Nz(txtBillngCity, "") & Nz(txtBillingState, "") & Nz(txtBillingZip, "") & Nz(txtBillingCountry, "")
    strShippingAddress = Nz(txtShippingAddress, "") & Nz(txtShippingCity, "") & Nz(txtShippingState, "") & Nz(txtShippingZip, "") & Nz(txtShippingCountry, "")

    'No billing address so nothing to be done.
    If strBillingAddress = "" Then Exit Function

    'We already have a shipping address so lets ask the user if they really want to update it to the billing address.
    'No if the user clicked on the checkbox by mistake.
    'Yes if the billing address was update after the Shipping address had been updated or
    'if user entered different address by mistake.
    If Not strShippingAddress = "" Then
    If MsgBox("Do you want to overwrite the shipppping address with the billing address?", vbYesNo + vbDefaultButton2) = vbNo Then
    Exit Function
    End If
    End If

    'And finally update the shipping address to the billing address.
    txtShippingAddress = txtBillingAddress
    txtShippingCity = txtBillngCity
    txtShippingState = txtBillingState
    txtShippingZip = txtBillingZip
    txtShippingCountry = txtBillingCountry
    End Sub
    I did it! I did it! I had to change a couple little things here and there...but IT WORKS!!! WOOHOOOO!!! Thanks sooo much! I've been working on this all day and I finally feel like I've actually accomplished something today! Thanks a bunch! You're a life and MIND saver lol!here's to you!

  7. #7
    Join Date
    Aug 2010
    Posts
    2
    Your code works wonderfully; however it appears to have made the change for ALL RECORDS and not just the one record I'm focused on.

    Is there a way to change this so it only focuses on the current record?

  8. #8
    Join Date
    Oct 2004
    Location
    Oxfordshire, UK
    Posts
    89
    Quote Originally Posted by nnfone1000 View Post
    Your code works wonderfully; however it appears to have made the change for ALL RECORDS and not just the one record I'm focused on.

    Is there a way to change this so it only focuses on the current record?
    Hi nnfone, this should work if the form shows 1 record (only) and the control fields have different names from the field names. How are you using this?

  9. #9
    Join Date
    Aug 2010
    Posts
    2
    Im using this on a form that shows one record at a time so that the user can edit the contractor information shown; however I've added an unbound check box that can be selected in the mailing address is the same as the physical address to be checked and update those fields...below is how I used the code you supplied and altered for my use. Maybe I'm doing something wrong that I'm not seeing:

    Private Sub chkSameInfo_Click()

    'This code assumes we got text boxes called txtMailingAddress, txtBillngCity, txtMailingState, txtMailingZip, which are linked to Mailing fields.
    'txtShippingAddress etc. are the text boxes linked to the shipping fields.
    Dim strMailingAddress As String
    Dim strContactAddress As String

    'Concatonate address control values to see if we've got anything in the either group.
    strMailingAddress = Nz(MailingAdd, "") & Nz(MailingCity, "") & Nz(MailingState, "") & Nz(MailingZip, "")
    strContactAddress = Nz(PhysAdd, "") & Nz(PhysCity, "") & Nz(PhysState, "") & Nz(PhysZip, "")

    'No mailing address so nothing to be done.
    If strMailingAddress = "" Then Exit Sub

    'We already have a Mailing address so lets ask the user if they really want to update it to the Contact address.
    'No if the user clicked on the checkbox by mistake.
    'Yes if the Contact address was updated after the Mailing address had been updated or
    'if user entered different address by mistake.
    If Not strContactAddress = "" Then
    If MsgBox("Do you want to overwrite the Contact address with the Mailing address?", vbYesNo + vbDefaultButton2) = vbNo Then
    Exit Sub
    End If
    End If

    'And finally update the Mailing address to the Contact address.
    MailingAdd = PhysAdd
    MailingCity = PhysCity
    MailingState = PhysState
    MailingZip = PhysZip
    End Sub

  10. #10
    Join Date
    Mar 2014
    Posts
    2

    I need help too.

    It looks straight forward above, but it's not working for me. In my table, among other things, I have the following text boxes, BillingStreetAddress, ShippingAddress, BillingCity, ShippingCity, BillingState, ShippingState, BillingZipCode and ShippingZipCode.

    Ive put a checkbox in the form and set the event procedure as follows:

    Private Sub Check23_Click()
    ShippingAddress = BillingStreetAddress
    ShippingCity = BillngCity
    ShippingState = BillingState
    ShippingZip = BillingZip

    End Sub

    When I click on the checkbox, nothing happens.

    Any ideas what I might be doing wrong?

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    nope
    is the code running?
    what happens if you put a breakpoint on the code and see if it runs

    have you get set warning off set to true
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Mar 2014
    Posts
    2
    Thanks for the quick reply.

    I was just testing another part of my database and came back to my add customer form and it seems to be working fine now.

    I just started using Access for a school project and am new to databases (and most programming). I guess I just needed to use it in the proper way or something.

    Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •