Auto Populate

[David]

Hi All,

I have a question and hope that what I want to do will work...

I have a form in my Access 2007 database, I have a drop down selector with
Yes and No as the options, I then have some address fields below (registered
and trading). The drop down asks if the registered and trading address are
the same. I would like it if the fields in the trading address section were
filled in with what is entered in the registered address section if the drop
down selector says yes.

Could someone tell me if it is possible to do this?

Thanks

 

[Larry Daugherty]

Access is capable of doing what you want to do. As to exactly what it
is that you want to do, no one here can tell. Access has lots of
*objects* which have *methods* and *properties*. When you ignore the
standard nomenclature of Access and ask questions in your own terms
your posts will likely be ignored. The volunteers here hope to help
you resolve your technical issues. It's up to you to express your
issues in terms we can all understand. Lurk the Access newsgroups,
observe the posts and you'll learn.

HTH

 

[David]

Well I am sorry if I cannot put my question in to terms you understand,
perhaps I misunderstood that groups were in place to help people that are
learning to use a product and do not know the technical jargon.

I am sure from the info I have provided someone could work out what I am
trying to do, I am not asking for schematics to build a nuclear reactor that
can make toast at the same time!

Next time perhaps I should not bother.

The few spoil it for the many

 

[John Spencer]

David, what you want is possible.

You will need to use some VBA to do it.

In design view, click on the drop down selector
Select Events and in the AfterUpdate event enter
[Event Procedure]
Click on the 3 dots at the end and enter code like the following.
You will need to replace the names with the names of your controls.

Private Sub ComboYEsNo_AfterUpdate()
If Me.ComboYesNo = "yes" then
If Len(Me.TradeAddress) _
+ Len(Me.TradeCity) _
+ Len(Me.TradeZIP) = 0 then
Me.TradeAddress = Me.RegisteredAddress
Me.TradeCity = Me.RegisteredCity
Me.TradeState = Me.RegisteredState
Me.TradeZip = Me.RegisteredZIP

End If
End if
End Sub

 

[David]

Hi John

Thanks for the info, I have just given it a try and so far no good....

I have built a test table and form with the following fields

ID
TradingNumber
TradingStreet
TradingPostcode
RegisteredNumber
RegisteredStreet
RegisteredPostcode
TradingSameRegistered (drop down with Yes No)

this is the code I am using on the AfterUpdate of the drop down

Private Sub TradingSameRegistered_AfterUpdate()
If Me.TradingSameRegistered = "Yes" Then
If Len(Me.TradingNumber) _
+ Len(Me.TradingStreet) _
+ Len(Me.TradingPostcode) = 0 Then
Me.TradingNumber = Me.RegisteredNumber
Me.TradingStreet = Me.RegisteredStreet
Me.TradingPostcode = Me.RegisteredPostcode

End If
End If
End Sub

I fill in the registered fields first then use the drop down and select yes
but the trading fields do not populate, could you shed some light on what I
am doing wrong?

Thanks

 

[John Spencer]

One small error on my part. I forgot that testing the length of a null
returns NULL and not Zero. I've modified the test to make it correct (I hope).

Private Sub TradingSameRegistered_AfterUpdate()
If Me.TradingSameRegistered = "Yes" Then
If Len(Me.TradingNumber & "") _
+ Len(Me.TradingStreet & "") _
+ Len(Me.TradingPostcode & "") = 0 Then
Me.TradingNumber = Me.RegisteredNumber
Me.TradingStreet = Me.RegisteredStreet
Me.TradingPostcode = Me.RegisteredPostcode

End If
End If
End Sub

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County