Access 2003 form-copy address tick box

[Simone Lee]

Hi Everyone!

I'm relatively new to Access and completely new to VBA! I've been asked to create a tick box on a form that fills in an address from another field. This is what I've managed to put together thus far:

Private Sub Same_as_clients_address_AfterClick()
 
Dim r As Long
r = MsgBox("Did the incident occur at the client's address?", vbExclamation + vbYesNo + vbDefaultButton2, "Warning")
 
If r = vbYes Then
Me![Clients_Street_Address] = Me![Where_incident_occurred_street_address]
Me![txtClients_Suburb] = Me![Where_incident_occurred_suburb]
Me![txtClients_State] = Me![Where_incident_occurred_state]
Me![txtClients_Postcode] = Me![Where_incident_occurred_postcode]
Else
If r = vbNo Then
Me![Where_incident_occurred_street_address] = Null
End
End If
End If
End If
 
If Me![Same_as_clients_address] Then
Me![Clients_Street_Address] = Me![Where_incident_occurred_street_address]
Me![txtClients_Suburb] = Me![Where_incident_occurred_suburb]
Me![txtClients_State] = Me![Where_incident_occurred_state]
Me![txtClients_Postcode] = Me![Where_incident_occurred_postcode]
Else
Me![Clients_Street_Address] = Null
Me![txtClients_Suburb] = Null
Me![txtClients_State] = Null
Me![txtClients_Postcode] = Null
End If
 
End Sub

When I fill out the form in Access and tick the box, nothing happens and no window pops up.

This has been driving me nuts for days and I appreciate any help!!!

Thanks so much :)

[MMcCarthy]

OK first of all there were a few errors in your code layout. You had some extra end if statements. I've tidied up the code for you so see if that makes a difference first.

Private Sub Same_as_clients_address_AfterClick()
 
Dim r As integer
    r = MsgBox("Did the incident occur at the client's address?", vbExclamation +         vbYesNo + vbDefaultButton2, "Warning")
 
    If r = vbYes Then
        Me![Clients_Street_Address] = Me![Where_incident_occurred_street_address]
        Me![txtClients_Suburb] = Me![Where_incident_occurred_suburb]
        Me![txtClients_State] = Me![Where_incident_occurred_state]
        Me![txtClients_Postcode] = Me![Where_incident_occurred_postcode]
    Else
        Me![Where_incident_occurred_street_address] = Null
    End If
 
    If Me![Same_as_clients_address]=-1 Then
        Me![Clients_Street_Address] = Me![Where_incident_occurred_street_address]
        Me![txtClients_Suburb] = Me![Where_incident_occurred_suburb]
        Me![txtClients_State] = Me![Where_incident_occurred_state]
        Me![txtClients_Postcode] = Me![Where_incident_occurred_postcode]
    Else
        Me![Clients_Street_Address] = Null
        Me![txtClients_Suburb] = Null
        Me![txtClients_State] = Null
        Me![txtClients_Postcode] = Null
    End If
 
End Sub
 

[NeoPa]

Also Simone, what's the name of the actual CheckBox control?

I ask that as the name of the event procedure is not right. Assuming the actual name is "Same_as_clients_address" then the procedure definition should be :

Private Sub Same_as_clients_address_AfterUpdate()

Other pointers that may help with code like this are :

1. When using multiple references to properties of the same object (in this case Me) it is good practice to use the With ... command.
2. When using booleans values (which is what the value of a CheckBox is) it makes no sense to compare them to another boolean value. In fact, in many cases people get into difficulties because VBA doesn't treat only TRUE values as true. The proper way to treat booleans within an If or IIf() is simply to reference them.
   
   The reason for this is that If takes a boolean argument. If {boolean} Then. Thus, it makes sense to use :
   If X = Y Then
   as (X = Y) returns a boolean value. The following lines make equally little sense though :
   

   If Me.CheckBox = True Then
   If (X = Y) = True Then

   The extra complication within VBA is that any numeric value not equal to zero is treated as TRUE so if you save the result of a calculation in a numeric variable (X) then test it, the following two lines give opposite results (when naturally they would be expected to ehave the same) :
   

   X = 52 - 36
   If X Then {True result}
   If X = True Then {False result}

EG.

Private Sub Same_as_clients_address_AfterUpdate()
    With Me
        If .Same_as_clients_address Then
            .Clients_Street_Address = .Where_incident_occurred_street_address
            .txtClients_Suburb = .Where_incident_occurred_suburb
            .txtClients_State = .Where_incident_occurred_state
            .txtClients_Postcode = .Where_incident_occurred_postcode
        Else
            .Clients_Street_Address = Null
            .txtClients_Suburb = Null
            .txtClients_State = Null
            .txtClients_Postcode = Null
        End If
    End With
End Sub

PS. This code indicates what I would guess is the logic you're after assuming [Same_as_clients_address] is the CheckBox control on your form and once it's been set there is no need to re-ask the same question.

[Mihail]

Hi all !

Simone,
1) As NeoPa teach you, is a good practice to use With - End With when you refer the same object multiple times.
But, in your case, is not strongly necessary to refer Me.
This keyword (Me) refer your current form and Access work in "Me" as default. So, NeoPa's code will work even if you remove With Me and End With lines from the code.

2) You can use MsgBox function in IF syntax:

If MsgBox(.....) = vbYes then
     'Code if TRUE
Else
     'Code if FALSE
End If

Yes, the code is more elegant as you design it but... what is variable "r" ? Of course, now you know: "r" store the MsgBox result, but after 3 month (or 3 years) you need to (re)understand your own code to see what mean "r As Long". For this purpose (maintenance) my practice is to create a module (I name it all the time "modPublic". In this module I define global variables, subroutines, functions and so on. One of this variable is ( invariable :) )

Public Msg As VbMsgBoxResult

I use this variable instead your "r":

    Msg = MsgBox("Did the incident occur at the client's address?", vbExclamation + vbYesNo + vbDefaultButton2, "Warning")
    If Msg = vbYes then
         'Code if TRUE
    Else
         'Code if FALSE
    End If

or, using Select Case,

    Select Case Msg
        Case vbYes
            'Code if Msg = vbYes
        Case vbNo
            'Code if Msg = vbNo
        Case vbCancel
            'Code if Msg = vbCancel
        Case vbRetry
            'Code if Msg = vbRetry
    End Select

That is about MsgBox function. As NeoPa say, I think is not necessary for now in your code.

3) Is a question for NeoPa as expert

Me![Clients_Street_Address] = Me![Where_incident_occurred_street_address]

This is a line from Simone code.
I don't understand what type of object are Clients_Street_Address and Where_incident_occurred_street_address. Reading next lines from Simone's code and your posts, NeoPa, I understand that must be objects in the form (text boxes). In this case I can't see any reason to populate a text box in a form with the value of other text box from the same form.
More: Are this text boxes bound to a table ? If YES, the first line from ELSE clause

.Clients_Street_Address = Null

damage the data base, isn't it ? The field [Clients_Street_Address] will loose the data.

I think that if the check box Same_as_clients_address will be bound to a new field [Same_as_clients_address] in the table can solve the entire problem.

I ask you please, NeoPa, to check my reasoning because I am in a dark fog.
I think that an answer for me is also an answer for Simone (so I have not, again :), some problems with the forum rules).

Thank you !

[NeoPa]

Mihail:
This keyword (Me) refer your current form and Access work in "Me" as default. So, NeoPa's code will work even if you remove With Me and End With lines from the code.

Technically that's true, yet such advice is not helpful as there is value in using Me in the code to allow easier maintenance and clearer, more readable, code. It's not an accident that so many experienced Access developers use it. Nor is it because they just never bothered to find another way. Experience teaches us to use the better ways when we find and understand them.

Mihail:
3) Is a question for NeoPa as expert

The reason is that Simone does exactly want to clear the values from these controls when the CheckBox is cleared. Remember, the values have been set automatically in the code when the CheckBox was set, so the existing values are known logically. With the Checkbox now cleared it makes sense that the client values no longer match the address data for the incident.

Technically, it would be a good idea that when these controls are set to match the controls for the incident, they are also set so they cannot be changed by the operator. When they are cleared, they should then (and only then) be re-enabled for operator entry.

The reasoning behind the question though, makes good sense, even if a little more consideration needs to be applied to the code.

Does that make things somewhat clearer?

[Mihail]

Yes, NeoPa and thank you.
I think you understand English as I understand... Romanian :) . In this context (the data must be cleared) what you say has sens.
But a bit of fog remain:

In your code:

If .Same_as_clients_address Then
     .Clients_Street_Address = .Where_incident_occurred_street_address

why to fill .Clients_Street_Address (it is a text box, isn't it ?) with data from .Where_incident_occurred_street_address (it is also a text box from the same form; or I miss understood?) ?

[NeoPa]

The question revolves around a form which has two sets of controls for addresses. One is for the client's address and the other is for the address of where the incident occurred. On this form it is not necessary to enter the address in for the client and the incident if the CheckBox to say they are the same is checked. When a record (on the form) indicates the addresses are the same the form enters the address in automatically by copying the address held for the incident into the client's address controls.

Imagine a form that reports crimes of personal attacks. If someone were to be attacked in a shopping centre then separate addresses would be required for the client's home and where the attack took place. For those incidents where the attack takes place at the client's address, it is unnecessary (and prone to potential error) to allow the address to be entered twice by the operator, so instead the form does it for them automatically when the CheckBox is set.

Does that make it all clearer?

[Mihail]

Yes. Now it is clear. Thank you very much, NeoPa.
Also I wish to thank Simone for the post because I learn a lot.

Good luck, Simone !

[Simone Lee]

Hey everyone!

Thank you so much for your help, I never expected such helpful responses! I will try those suggestions now and let you know how I go :)
Thanks again