How to autopopulate a text field based on a check boxes being ticked msacc 2007?

[wedd]

I have 3 organisations: MVP, CTT and B.I.G, now I know the code to autopopulate...however, based on each checkbox being ticked one after the other, I would like the following prices to be auto-populated into a blank deposit text box field. for example:


MVP will display a deposit of £100.00
CTT will display a deposit of £200.00
b.i.g will display a deposit of £110.00


In addiiton, I would like enforce user to only select one checkbox at a time..i'e when they select one check box...the other checkboxes will be nulled...so it's impossible for them to select 2 or all three checkboxes at the same time. Can this be done using access 2007? If so, how can this be done? Do you have any sample code as i have limited experience performing this interesting problem...



Thanks for your great ideas:friends:

[Imdabaum]

I would again suggest using the groupbox control. It gives you the option to use a radio button, checkbox, or toggle button. It will also let you choose which is the default value. If you don't want to use the groupbox, then you have to explicitly set the value to all other checkbox's in the related panel to false when the other is true.


Sub chk1_AfterUpdate()
If chk1=1 Then
chk2=false
chk3=false
'set other values as appropriate If chk1 corresponds to MVP then textbox = 100.00
End Sub

Sub chk2_AfterUpdate()
chk1=false
chk3 = false
'set other values as appropriate If chk1 corresponds to CTT then appropriate textbox = 200.00
End Sub

Sub chk3_AfterUpdate()
chk1=false
chk2 = false
'set other values as appropriate If chk1 corresponds to b.i.g then appropriate textbox = 110.00
End Sub

[wedd]

Thanks, I've used a combox in the past to auto-populate...I've learnt something new today

[Imdabaum]

Glad to help.

[wedd]

Chk1 works fine but chk2, chk3 and chk4 I get an error message. Do I need to change the IF statement to match where and which checkbox that needs to be ticked?

[wedd]

Oh, and for the prices that correspond with the checkboxes. Is it possible for each price to appear in a blank text box I named as Discount? Is it possible to do this? If so, how can this be done with code?


Thanks for your help. Its much appreciated.

[Imdabaum]

Yes. Sorry I left that out. Good on you for spotting it.

Sure it's like calling your kid or significant other. Just reference their name within the appropriate subroutine.

Me.Discount = 100.00 'or 110.00 or 200.00 depending on which checkbox was ticked.

[hansup]

I have 3 organisations: MVP, CTT and B.I.G, now I know the code to autopopulate...however, based on each checkbox being ticked one after the other, I would like the following prices to be auto-populated into a blank deposit text box field. for example:


MVP will display a deposit of £100.00
CTT will display a deposit of £200.00
b.i.g will display a deposit of £110.00


In addiiton, I would like enforce user to only select one checkbox at a time..i'e when they select one check box...the other checkboxes will be nulled...so it's impossible for them to select 2 or all three checkboxes at the same time. Can this be done using access 2007? If so, how can this be done? This situation seems to be a natural fit for an Option Group, as Imdabaum suggested.

The option group will ensure that only one of the 3 check boxes can be checked at one time. Then use the option group's AfterUpdate event to determine which of the choices was selected and place the corresponding value into your txtDeposit text box. I attached a Zip file of a sample database to show you how this can be done simply, but here is the code from the AfterUpdate event:

Private Sub fraOrganisations_AfterUpdate()
Dim curDeposit As Currency
Dim strError As String
Select Case Me.fraOrganisations
Case 1 ' MVP
curDeposit = 100#
Case 2 ' CTT
curDeposit = 200#
Case 3 ' B.I.G.
curDeposit = 110#
Case Else ' oops
strError = "unexpected value for Organisation: " & _
Me.fraOrganisations
End Select
If Len(strError) > 0 Then
MsgBox strError
Else
Me.txtDeposit = curDeposit
End If
End Sub

[wedd]

Thank you!

[wedd]

I entered the code, however when I click on the checkboxes the deposit prices don't appear in the deposit text box. Any reasons why? I do have access 20007 and I know there are issues with this version running code sometimes

[Imdabaum]

Check the code and make sure the code matches your control names. Go to your AfterUpdate() sub and press F9 and then run it. You should be able to step through line by line and get evaluations of the controls when you highlight your mouse over it.

One other thought, Hansup's form is based on an option group. If you aren't using an optoin group control and are still using the individual check boxes, you may find that your code is skipping the code entirely.

[wedd]

The sample you sent me is good...however when i click on the checkboxes on your sample the deposit text box is blank and doesn't display the individual prices that match with the organisation. Is there something else that needs to be added to the code for this action to run?

Thanks for your help

[Imdabaum]

What are your security settings like? Do you trust outside applications? It worked fine for me.

[wedd]

I've just changed the trusted security settings and enabled all macros

[wedd]

I'm a bit new to 2007 version of access. Where do I search to trust outside applications? Oh, I just realised I have an additional checkbox called equipment. Can I also include this into the code? I actually implemented your feature into my database and it works. But will I have to recreate it in order that it saves and able to function within my database? I've also noticed that access 2007 doesn't function well when other code is imported from earlier versions. Have you experienced the same thing?


Thanks for your solutions I'm definiately learning about access 2007 functionality.

[Imdabaum]

I'm a bit new to 2007 version of access. Where do I search to trust outside applications? Oh, I just realised I have an additional checkbox called equipment. Can I also include this into the code?[/code]

Yes, but you may need to go into the design mode and edit the collection so that Equipment is an option.

[quote=wedd]I actually implemented your feature into my database and it works. But will I have to recreate it in order that it saves and able to function within my database? I've also noticed that access 2007 doesn't function well when other code is imported from earlier versions. Have you experienced the same thing?

I haven't experienced this. But I've mostly used the code from older databases and modified it to the needs in my database, so I haven't generally done a straight copy and paste so much as Writing my function and writing what I see while adding modifications. I do however import Modules that I commonly use. But this has worked fine for me so far.

[hansup]

The sample you sent me is good...however when i click on the checkboxes on your sample the deposit text box is blank and doesn't display the individual prices that match with the organisation. Is there something else that needs to be added to the code for this action to run?I tested wedd.mdb with Access 2007. It works fine on my system. I have the database stored in a "Trusted Location".

See Create, remove, or change a trusted location for your files (http://office.microsoft.com/en-us/word-help/create-remove-or-change-a-trusted-location-for-your-files-HA010031999.aspx#BM31)

[wedd]

Yes, it works! Thanks it was the security settings

[wedd]

I've adapted the checkboxes to my database. Although the checboxes are working fine...at the moment the prices aren't automatically appearing in the text box. Any reasons why this may be happening? Could it also be something to do with the security settings on my database?


Thanks

[Imdabaum]

Are you getting any errors? Have you tried stepping through in Debug mode with Break points?

[hansup]

I've adapted the checkboxes to my database. Although the checboxes are working fine...at the moment the prices aren't automatically appearing in the text box. Any reasons why this may be happening? Could it also be something to do with the security settings on my database?Please show us your code.

[wedd]

No errors are appearing. Its just when I click on the various checkboxes thedifferent prices of the deposits son't appear. A similar thing occured when I first opened the database you sent me...however I changed the trust settings and it worked well.

[wedd]

Here is my code:

Private Sub fraOrganisations_AfterUpdate()
Dim curDeposit As Currency
Dim strError As String
Select Case Me.fraOrganisations
Case 1 ' Maitland Park Sports Centre
curDeposit = 100#
Case 2 ' Queens Crescent Community Centre
curDeposit = 200#
Case 3 ' Fleet Community Centre
curDeposit = 100#
Case 4 ' Equipment
curDeposit = 100#
Case Else ' oops
strError = "unexpected value for fraOrganisations: " & _
Me.fraOrganisations
End Select
If Len(strError) > 0 Then
MsgBox strError
Else
Me.txtDeposit = curDeposit
End If
End Sub

[wedd]

I've included equipment as a new option. Thanks for your help

[wedd]

I am using access 2007. I'm not sure if that could be causing the issue...

[hansup]

No errors are appearing. Its just when I click on the various checkboxes thedifferent prices of the deposits son't appear. A similar thing occured when I first opened the database you sent me...however I changed the trust settings and it worked well. OK, I will assume your database is stored in a trusted location. Let me know if we need to discuss that point further.

I don't see anything wrong with the AfterUpdate code. And it looks to me like you added equipment properly.

Add a MsgBox line to your code as I indicated below. The reason for this is to determine whether fraOrganisations_AfterUpdate is evaluated at all after you select a different check box on the form.

Since you're running the database from a trusted location, the only other reason I can suggest for why fraOrganisations_AfterUpdate wouldn't run is if the option group frame is not named fraOrganisations.

Anyway, the first step is for you to tell us whether or not you see the MsgBox.

Private Sub fraOrganisations_AfterUpdate()
Dim curDeposit As Currency
Dim strError As String

MsgBox "fraOrganisations_AfterUpdate active"

Select Case Me.fraOrganisations
Case 1 ' Maitland Park Sports Centre
curDeposit = 100#
Case 2 ' Queens Crescent Community Centre
curDeposit = 200#
Case 3 ' Fleet Community Centre
curDeposit = 100#
Case 4 ' Equipment
curDeposit = 100#
Case Else ' oops
strError = "unexpected value for fraOrganisations: " & _
Me.fraOrganisations
End Select
If Len(strError) > 0 Then
MsgBox strError
Else
Me.txtDeposit = curDeposit
End If
End Sub