Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old January 28th, 2006, 12:41 AM
Registered User
 
Join Date: Jan 2006
Location: Adelaide, SA, Australia.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default updating text fields with option buttons

Hi

I new to VBA and having a few problems with a database I'm working on.

I have a form that contains customer details. The are two sections one for the Registered Office Address and one for the Trading Office Address. I want to be able to place 2 option buttons (yes/no) on the form that will allow the user to auto fill the Trading Office Address fields if the Trading Office is the same as the Registered Office.

Any suggestions on the best code to use would be greatly appreciated.


Kind Regards

David
  #2 (permalink)  
Old January 30th, 2006, 09:49 AM
Authorized User
 
Join Date: Jan 2006
Location: , , South Africa.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to gws_byte
Default

Hi redtechcoms,

There are a number of ways to do this...

One of the ways is to set the 'after update' event on the option/check box control. The sub can check the control state and set the values accordingly.

Here is a quick example assuming the following fields- street1,street2,street3,street4,postal1,postal2,po stal3,postal4 and check20(the check box) it would read something like this...

Private Sub Check20_AfterUpdate()

If Check20 = True Then
postal1 = street1
postal2 = street2
postal3 = street3
postal4 = street4

End If

End Sub

Hope that helps

  #3 (permalink)  
Old January 30th, 2006, 03:24 PM
Registered User
 
Join Date: Jan 2006
Location: Adelaide, SA, Australia.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi GWS

Thanks, works great!!!!!! Really appreciate your assistance.

Kind regards

David

Kind Regards

David
  #4 (permalink)  
Old January 31st, 2006, 03:25 AM
Authorized User
 
Join Date: Jan 2006
Location: , , South Africa.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to gws_byte
Default

Hey David,

Absolute pleasure, I was thinking that there might be something you want to consider though...

What if some one "accidentally' uses the check box it might over write the existing information.

To prevent this you could add a check to see if there is any data in the fields, if there is a warning box with the option to cancel or continue would be the ideal. Let me know if you need some help with it.

Glad I could help

Dave Morrison aka gws_byte


  #5 (permalink)  
Old January 31st, 2006, 01:04 PM
Registered User
 
Join Date: Jan 2006
Location: Adelaide, SA, Australia.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Dave

Funny you mentioned that, I posted the same problem at a google group and got a reply a few days after yours. The suggested code included a message box confirming the insert of the text.

Again thanks for your help, I hoping I might be able to trouble you with one other problem I'm having. The database I'm working on collects company financial data. I have a form that contains fields for 3 years of financial info. During the intial data input I want the user to be able to set the commencement of the year using a combo and the other 2 fields auto fill. ie if they select 1970 from the combo then the other fields will update with 1971 & 1972. I know it's probably a simple addtion formula but its been driving me NUTS!!!.

Kind regards

David

Kind Regards

David
  #6 (permalink)  
Old February 1st, 2006, 04:10 AM
Authorized User
 
Join Date: Jan 2006
Location: , , South Africa.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to gws_byte
Default

Hi David,

Its simple-ish... Again assuming the 3 fields are called SourceDate,SecondDate, ThirdDate and have a NUMBER(integer) Data Type...

The code might look something like this:

Private Sub SourceDate_AfterUpdate()

Dim Answer1 As VbMsgBoxResult
Answer1 = MsgBox("This will auto fill the other two Fields, are you sure you want to do this?" _
    , vbYesNo)

If Answer1 = vbNo Then
Exit Sub
End If

SecondDate = SourceDate + 1
ThirdDate = SourceDate + 2

End Sub

I have also included a message box giving the user the option to cancel the autofill.

I hope this sorts it out for you :)
p.s. you can get me direct on messenger or hotmail 9am - 3pm CAT(central african time)

Dave - MAD
nil illegitimi carborundum
Semper in excreta, sumus solum profundum variat
  #7 (permalink)  
Old February 2nd, 2006, 02:25 PM
Registered User
 
Join Date: Jan 2006
Location: Adelaide, SA, Australia.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Dave

Works great!!!

Thanks again for all you help.

Kind Regards

David
  #8 (permalink)  
Old February 2nd, 2006, 03:14 PM
Registered User
 
Join Date: Jan 2006
Location: Adelaide, SA, Australia.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Dave

Sorry but I've done something thats changed the outputs. I setup the code in my test database and it worked fine. When i put it into the operational database the outputs won't work. I select the Year which comes from a combo (cmbPeriod_1) that takes the years from a value list. The output goes to 2 text boxes, (Period_2) & (Period_3). For some reason the output to the text boxes just gives a single number output, ie if I select 1966 from the cmb I get values in Period_2 & Period_3 of "7" and "8". I'm guessing the either the table or form field formats are incorrect. I've tried changing a few but no luck. Any suggestions?????

Kind Regards

David


Similar Threads
Thread Thread Starter Forum Replies Last Post
Create Check Boxes/Option buttons/Option Group hewstone999 Access VBA 1 March 14th, 2008 07:25 AM
Error is using option buttons return value Subuana Beginning VB 6 2 May 1st, 2006 03:39 PM
Using Option buttons NovieProgrammer Access VBA 1 April 2nd, 2005 09:16 PM
Is one (1) a 'length' for option buttons? John K. King Javascript How-To 2 November 7th, 2004 06:04 PM
Option buttons, queries and combo boxes... Anubis Access VBA 26 November 20th, 2003 12:45 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.