Clearing data in a control

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form where users will enter order information. There is one section
for the "Bill To" information and another for the "Ship To" information. I
have a check box on there that is used when the "Ship To" is the same as the
"Bill To". The users will put a check in it to copy the "Bill To" information
over to the "Ship To" controls. When they clear the check from the check box
I want the information to clear out so they can enter a custom "Ship To"
address. I tried using the "Me.ShipTo = Null" code but that clears it and
when I close and open the form it clears out any data that was typed in it.
How can I set it up so it only erases the data when the check box is
unchecked so I can enter data and have it stay there?
 
Sorry about that. Here is the code. As you can see when the data is carried
over I also lock the controls so they can't make any changes but when the
check box is unchecked the controls are unlocked so changes can be made.

If [ShippingName] = True Then
Me.ShipToName = Me.Customer
Me.ShipToAddress1 = Me.BillToAddress1
Me.ShipToAddress2 = Me.BillToAddress2
Me.ShipToCity = Me.BillToCity
Me.ShipToState = Me.BillToState
Me.ShipToZipCode = Me.BillToZipCode
Me.ShipToName.Locked = True
Me.ShipToAddress1.Locked = True
Me.ShipToAddress2.Locked = True
Me.ShipToCity.Locked = True
Me.ShipToState.Locked = True
Me.ShipToZipCode.Locked = True
Else
Me.ShipToName.Locked = False
Me.ShipToAddress1.Locked = False
Me.ShipToAddress2.Locked = False
Me.ShipToCity.Locked = False
Me.ShipToState.Locked = False
Me.ShipToZipCode.Locked = False
End If
 
Sorry about that again Tom. This code is also duplicated in the CurrentEvent
of the form as well.

Private Sub ShippingName_AfterUpdate()
If [ShippingName] = True Then
Me.ShipToName = Me.Customer
Me.ShipToAddress1 = Me.BillToAddress1
Me.ShipToAddress2 = Me.BillToAddress2
Me.ShipToCity = Me.BillToCity
Me.ShipToState = Me.BillToState
Me.ShipToZipCode = Me.BillToZipCode
Me.ShipToName.Locked = True
Me.ShipToAddress1.Locked = True
Me.ShipToAddress2.Locked = True
Me.ShipToCity.Locked = True
Me.ShipToState.Locked = True
Me.ShipToZipCode.Locked = True
Else
Me.ShipToName.Locked = False
Me.ShipToAddress1.Locked = False
Me.ShipToAddress2.Locked = False
Me.ShipToCity.Locked = False
Me.ShipToState.Locked = False
Me.ShipToZipCode.Locked = False
End If
End Sub

The name "Secret Squirrel" has no significance except that it's been my
nickname since I was a teenager. There used to be an old cartoon called "The
adventures of Secret Squirrel". Since I reminded everyone of that character I
was dubbed the name.
Tom Wickerath said:
Post the entire code. I see an IF....END IF, but I have no idea where you are
using this (Form_Current, checkbox_Click, etc.).

By the way, is there any significance to your alias "Secret Squirrel"? I've
kind of been wondering about that ever since I saw it a while back. <smile>


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Secret Squirrel said:
Sorry about that. Here is the code. As you can see when the data is carried
over I also lock the controls so they can't make any changes but when the
check box is unchecked the controls are unlocked so changes can be made.

If [ShippingName] = True Then
Me.ShipToName = Me.Customer
Me.ShipToAddress1 = Me.BillToAddress1
Me.ShipToAddress2 = Me.BillToAddress2
Me.ShipToCity = Me.BillToCity
Me.ShipToState = Me.BillToState
Me.ShipToZipCode = Me.BillToZipCode
Me.ShipToName.Locked = True
Me.ShipToAddress1.Locked = True
Me.ShipToAddress2.Locked = True
Me.ShipToCity.Locked = True
Me.ShipToState.Locked = True
Me.ShipToZipCode.Locked = True
Else
Me.ShipToName.Locked = False
Me.ShipToAddress1.Locked = False
Me.ShipToAddress2.Locked = False
Me.ShipToCity.Locked = False
Me.ShipToState.Locked = False
Me.ShipToZipCode.Locked = False
End If
Click to expand...
Click to expand...
 
Hi Secret Squirrel,

Per your first message:
"I tried using the "Me.ShipTo = Null" code but that clears it ...",

this would be an expected result if that code was included in the
Form_Current event procedure. Form_Current runs when you first open a form,
and each time the focus leaves one record and moves to another.

This code is also duplicated in the CurrentEvent of the form as well.
Click to expand...

Whenever the same code must be run from more than one event procedure, it is
better to put the code into a new procedure (subroutine or function), and
call this procedure from various places. That way, you only have one
procedure to maintain, not two or more identical procedures.

I would try something like this (Caution -- untested. I have omitted
error-handling for clarity, but you should add error handling):


Private Sub Form_Current()
If Me.NewRecord Then
LockControls (False)
Else
LockControls (True)
End If
End Sub

Private Sub ShippingName_Change
LockControls (False)
UpdateShippingInfo
LockControls (True)
End Sub

Private Sub NameOfComboBox_AfterUpdate()
LockControls (False)
UpdateShippingInfo
LockControls (True)
End Sub

Private Sub LockControls(blnLock As Boolean)
Me.ShipToName.Locked = blnLock
Me.ShipToAddress1.Locked = blnLock
Me.ShipToAddress2.Locked = blnLock
Me.ShipToCity.Locked = blnLock
Me.ShipToState.Locked = blnLock
Me.ShipToZipCode.Locked = blnLock
End Sub

Private Sub UpdateShippingInfo()
If [ShippingName] = True Then
Me.ShipToName = Me.Customer
Me.ShipToAddress1 = Me.BillToAddress1
Me.ShipToAddress2 = Me.BillToAddress2
Me.ShipToCity = Me.BillToCity
Me.ShipToState = Me.BillToState
Me.ShipToZipCode = Me.BillToZipCode
Else
Me.ShipToName = Null
Me.ShipToAddress1 = Null
Me.ShipToAddress2 = Null
Me.ShipToCity = Null
Me.ShipToState = Null
Me.ShipToZipCode = Null
End If
End Sub


Thanks for the history lesson on the cartoon!


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Secret Squirrel said:
Sorry about that again Tom. This code is also duplicated in the CurrentEvent
of the form as well.

Private Sub ShippingName_AfterUpdate()
If [ShippingName] = True Then
Me.ShipToName = Me.Customer
Me.ShipToAddress1 = Me.BillToAddress1
Me.ShipToAddress2 = Me.BillToAddress2
Me.ShipToCity = Me.BillToCity
Me.ShipToState = Me.BillToState
Me.ShipToZipCode = Me.BillToZipCode
Me.ShipToName.Locked = True
Me.ShipToAddress1.Locked = True
Me.ShipToAddress2.Locked = True
Me.ShipToCity.Locked = True
Me.ShipToState.Locked = True
Me.ShipToZipCode.Locked = True
Else
Me.ShipToName.Locked = False
Me.ShipToAddress1.Locked = False
Me.ShipToAddress2.Locked = False
Me.ShipToCity.Locked = False
Me.ShipToState.Locked = False
Me.ShipToZipCode.Locked = False
End If
End Sub

The name "Secret Squirrel" has no significance except that it's been my
nickname since I was a teenager. There used to be an old cartoon called "The
adventures of Secret Squirrel". Since I reminded everyone of that character I
was dubbed the name.
Click to expand...
 
Wow, that's a lot of code for such a simple process. :)
I will test it out but before I do I'm a little unclear what this code is for:

Private Sub NameOfComboBox_AfterUpdate()
LockControls (False)
UpdateShippingInfo
LockControls (True)
End Sub

Which "ComboBox" are you referring to?

Do you remember that cartoon? It wasn't really that popular.

Secret Squirrel said:
Sorry about that again Tom. This code is also duplicated in the CurrentEvent
of the form as well.

Private Sub ShippingName_AfterUpdate()
If [ShippingName] = True Then
Me.ShipToName = Me.Customer
Me.ShipToAddress1 = Me.BillToAddress1
Me.ShipToAddress2 = Me.BillToAddress2
Me.ShipToCity = Me.BillToCity
Me.ShipToState = Me.BillToState
Me.ShipToZipCode = Me.BillToZipCode
Me.ShipToName.Locked = True
Me.ShipToAddress1.Locked = True
Me.ShipToAddress2.Locked = True
Me.ShipToCity.Locked = True
Me.ShipToState.Locked = True
Me.ShipToZipCode.Locked = True
Else
Me.ShipToName.Locked = False
Me.ShipToAddress1.Locked = False
Me.ShipToAddress2.Locked = False
Me.ShipToCity.Locked = False
Me.ShipToState.Locked = False
Me.ShipToZipCode.Locked = False
End If
End Sub

The name "Secret Squirrel" has no significance except that it's been my
nickname since I was a teenager. There used to be an old cartoon called "The
adventures of Secret Squirrel". Since I reminded everyone of that character I
was dubbed the name.
Tom Wickerath said:
Post the entire code. I see an IF....END IF, but I have no idea where you are
using this (Form_Current, checkbox_Click, etc.).

By the way, is there any significance to your alias "Secret Squirrel"? I've
kind of been wondering about that ever since I saw it a while back. <smile>


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Secret Squirrel said:
Sorry about that. Here is the code. As you can see when the data is carried
over I also lock the controls so they can't make any changes but when the
check box is unchecked the controls are unlocked so changes can be made.

If [ShippingName] = True Then
Me.ShipToName = Me.Customer
Me.ShipToAddress1 = Me.BillToAddress1
Me.ShipToAddress2 = Me.BillToAddress2
Me.ShipToCity = Me.BillToCity
Me.ShipToState = Me.BillToState
Me.ShipToZipCode = Me.BillToZipCode
Me.ShipToName.Locked = True
Me.ShipToAddress1.Locked = True
Me.ShipToAddress2.Locked = True
Me.ShipToCity.Locked = True
Me.ShipToState.Locked = True
Me.ShipToZipCode.Locked = True
Else
Me.ShipToName.Locked = False
Me.ShipToAddress1.Locked = False
Me.ShipToAddress2.Locked = False
Me.ShipToCity.Locked = False
Me.ShipToState.Locked = False
Me.ShipToZipCode.Locked = False
End If
Click to expand...
Click to expand...
Click to expand...
 
Which "ComboBox" are you referring to?

I guess I assumed that you had a combo box for selecting a customer, similar
to that found in the Northwind sample database, on the Orders form. If not,
then just disregard that part.
Do you remember that cartoon? It wasn't really that popular.
Click to expand...

Nope. Can't say I ever saw it. That's why I wasn't making the connection
with your alias.

I have to sign off now....my lunch break is over. I won't be checking back
in for several hours.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Yes I do have a ComboBox that I use to select the customer. I will input that
code as well. I will test it all out and let you know how it works. Thanks
again for all your help. Hopefully it all works out well.

Thanks again!
Secret Squirrel
 
Ok I just tried out the code and I have to questions and one problem.

1. The code you wrote for the "ShippingName" Change event can't be done
because the checkbox doesn't have a change event. Where should this code go?

2. Does this only pertain to new records? I noticed in the current event
code of the form that you're using an "IF' statement for a new record. I may
just be tied and not understanding that code.

The problem is with the current code the way you wrote....When I put a check
in the checkbox it does carry over the information but when I remove the
check I can't input data into the "Ship" fields because they are still
locked. I checked my code twice to make sure I did it like you said. I can't
see why it would be locked with the check removed. I substituted the "Change"
event with the "After Update" event since the change event doesn't exist in
the checkbox properties.

Thanks
SS
 
Hi Secret Squirrel,
Where should this code go?
Click to expand...

Okay, try the Click event procedure. There's a reason I issued the caution:
untested. ;-)

Regarding the Form_Current event procedure, we test for NewRecord to allow
the person the ability to freely edit a new record. Once the record has been
saved, it is no longer considered a new record.
The problem is with the current code the way you wrote....When I put a check
in the checkbox it does carry over the information but when I remove the
check I can't input data into the "Ship" fields because they are still
locked.
Click to expand...

If you are willing to send me a compacted and zipped copy of your database,
I will take a look at this. It's always much easier (for me at least) to have
a database to work on versus trying to troubleshoot without having one
available.

If you are interested, send me a private e-mail message with a valid
reply-to address. My e-mail address is available at the bottom of the
contributor's page indicated below. Please do not post your e-mail address
(or mine) to a newsgroup reply. Doing so will only attract the unwanted
attention of spammers.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Back
Top