Auto Populate Using Previous Field

[teetertotter57]

Hi,

I am very new to Access, so I hope I can explain this satisfactorily. I have a company that has a field for MailingAddress and PhysicalAddresses for shipping. When I enter the mailing address information I'd like the shipping to automatically populate with the information; however, I want to be able to change the physical address, if it differs. Is there a way I can do this without using a combo or list box? I tried changing the control source, which works, but I can't go in and make a change on the physical address without it re-populating the mailing address. Help, please.

Thank You,
Regina

 

[bob fitz]

I'm a bit confused by the terms you've used for the addresses (MailingAddress, PhysicalAddresses, shipping)

However, you could have some code in the After Update event of the first address which will copy the value in the first address to the control showing the second address. Something like:
Me.SecondAddress = Me.ActiveControl

 

[teetertotter57]

Bob,

The MailingAddress and PhysicalAddress are the names of two of my fields.

By using this code, will it allow me to change the PhysicalAddress field without changing the MailingAddress field?

Thank You,
Regina

 

[bob fitz]

Yes, if PhysicalAddress is the address filled in by the code.
In the line of code I gave you, you will need to change SecondAddress with the name of the control (sounds like this may be PhysicalAddress) so the line of code would be:
Me.PhysicalAddress = Me.ActiveControl

 

[teetertotter57]

Bob,

I'm a bit confused. Actually, I'm thoroughly confused. I may not have explained myself well. Where does this code go? After Update in the PhysicalAddress (address 1)? In which field property do I make this change?

tblAgency
MailingAddress (I type this data in)
PhysicalAddress Control Source set to MailingAddress (Automatically
populates, but allows me to change it without changing
the MailingAddress field)

Thank You,
Regina

 

[bob fitz]

Code in After UpDate of MailingAddress

 

[teetertotter57]

Bob,

I tried typing in MePhysicalAddress=Me.ActiveControl and tried Me.PhysicalAddress=Me.MailingAddress.

I received a message that said "Microsoft Access cannot find the object "Me".

You may need to explain this to me like you are talking to a 6 year old, although most 6 year olds would probably get this.

Thank You,
Regina

 

[bob fitz]

What is the name of the form that you are working with

 

[teetertotter57]

Bob,

I'm working in Access 2010, JPX America Database, Agency Form. This form tells me everything I need to know about the law enforcement agency: Mailing Address, Physical Address for shipping purposes, Contact information, etc.

Thank You,
Regina

 

[bob fitz]

Please confirm that the code in your form's code module is:

Private Sub MailingAddress_AfterUpdate()
Me.PhysicalAddress = Me.ActiveControl
End Sub

 

[teetertotter57]

Oh, Bob, I think I'm in over my head if you are talking modules. I entered that in the properties under the Event tab in After Update.

Thank You,
Regina

 

[bob fitz]

Oh, Bob, I think I'm in over my head if you are talking modules.

Well let's hope I can save you
Try this:
Select [Event Procedure] from the drop down in the property. Then click the button on the far right that has three dots(...) on it. This will open the code module for the form and should insert the first and last line of code for this event:

Private Sub MailingAddress_AfterUpdate()

End Sub

Just copy and paste the line of code between these lines so it looks like:

Private Sub MailingAddress_AfterUpdate()
Me.PhysicalAddress = Me.ActiveControl
End Sub

Give it a try.

 

[teetertotter57]

Yes! Worked Perfectly, but when I went to change the shipping address, it also changed the mailing address. Is there a way that allows me to override that if the two are different? By the way, I have a notebook of procedures, so I never have to ask the same question twice. They are in alpha order. I usually check there before I post a question.

Thanks,
Regina

 

[bob fitz]

What do you have as the Control Source property for the shipping address and the mailing address controls.

 

[teetertotter57]

Control Source for Shipping is Mailing
Control Source for Mailing is Mailing

 

[bob fitz]

Control Source for Shipping is Mailing
Control Source for Mailing is Mailing

Surely Control Source for Shipping should be something like Shipping. I don't know the names of the fields in the forms data source so that is just a guess, but there needs to be a field for each address in the table.

 

[teetertotter57]

Bob,

The control source for the ShippingAddress is set to MailingAddress. When I change the control source back to ShippingAddress, the field doesn't populate. I also noticed that the code is now in two places in the properties. I selected Events and at the top is On Click. This is where I selected the event and the ... It populated the After Update, as well. Is this correct? I am still unable to change the Shipping information without it changing the Mailing info.

Thank You,
Regina

Regina

 

[bob fitz]

The control source for the ShippingAddress should be ShippingAddress
If the control that shows this data is named ShippingAddress, then the code should be:

Private Sub MailingAddress_AfterUpdate()
Me.ShippingAddress = Me.ActiveControl
End Sub

and should only be used in the After UpDate event of MailingAddress.

 

[teetertotter57]

Bob,

I did exactly what you said. I made sure the control source for the ShippingAddress field was set to Shipping Address. I deleted the event then went back in, copied and pasted exactly what you have there. The shipping address field is not populating.

Just to clarify, when I went in to past the event this was already there:
Private Sub MailingAddress_OnClick ()

I pasted over that one time and I left it there the other. Neither time was I able to automatically populate the ShippingAddress field.

Thank You,
Regina

 

[bob fitz]

Regina, I'm off to bed now, but I will come back to this asap (probably tomorrow evening). Can you copy and paste here all the code in your module.