By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,246 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

Automatically activate checkbox when Combobox-field is not empty

P: 41
Hello

It is me again. I have another issue that I can not solve by myself or with google and would be happy if you could help me.

My database consists a form with details of persons. There is a subform with a combobox that consists "languages" and there is a checkbox on the mainform (yes/no) with the name "Translator".

I would like that the checkbox "Translator" is automatically activated if a language from the combobox in the subform is chosen and vice versa. What I would like in addition, is that the checkbox can not be activated or deactivated manually by the enduser of the form.

Can anybody tell me how I can do that?

Greetings and thanks!!

Mike
Aug 7 '17 #1

✓ answered by MikeMikeCH

Phil helped me to solve the problem, thanks a lot! The VBA-Codes are like that:

Main form

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. [Checkbox Übersetzer] = [T_Sprache-Unterformular].Form.RecordsetClone.RecordCount > 0
  3. End Sub

Subform

First VBA After Update:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2. Me.Parent![Checkbox Übersetzer] = Me.RecordsetClone.RecordCount > 0
  3. End Sub
Second VBA AfterDelConfirm
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterDelConfirm(Status As Integer)
  2. Me.Parent![Checkbox Übersetzer] = Me.RecordsetClone.RecordCount > 0
  3. End Sub
Thank you very much, Phil!!!

Share this Question
Share on Google+
16 Replies


PhilOfWalton
Expert 100+
P: 1,430
With the number of answers I have provided, I might just as well have written your DB

Firstly if you have a Combo box for languages, you restrict the person to only 1 language,

Secondly, from your description of what you want, if a language is selected, by definition the person is a translator, so there is no point in the translator checkbox.

Thirdly, with only one language allowed, this should be a Combo box in your main form, so what is in your subform?

Do you need a rethink?

Phil
Aug 7 '17 #2

P: 41
Dear Phil

Thanks a lot, and yes, you have provided really a lot of answers for which I am very thankful!! I hope I won't have many more questions...

I have a subform in which is a combobox with languages. For each person, more than one language can be chosen. The languages are in an individual "page" in a subform. On the main page of the form, I have the checkbox with the languages so that you see just in a second whether a person can be asked for translation or not. Therefore, the checkbox.

Greetings

Mike
Aug 7 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
OK Mike, that's clear, but are you saying that if a person has any language then they can translate ALL the languages listed on the subform, or are you saying that they are good at some languages which they can translate, and have limited ability in other languages? In which case the checkbox should be on the subform.

If it is the former, I still can't see what the check box achieves as an entry on a subform is far easier to see than a tick in a chec box.

Phil
Aug 7 '17 #4

P: 41
Dear Phil

Thanks for your answer. My question is about the former situation, meaning that when a language is in the combo, then the person can do the translation for that language and is a translator.

The checkbox is to make it much faster visible for the user of the database. To understand that, I send you a short attachment.

Mike
Attached Files
File Type: pdf model.pdf (31.5 KB, 87 views)
Aug 7 '17 #5

PhilOfWalton
Expert 100+
P: 1,430
OK. First set the Translator Check Box to Locked = Yes. That stops manual changing.

Then on current of the main form you need something like
Expand|Select|Wrap|Line Numbers
  1. ChkTranslater = [LanguageSubForm].[Form].RecordsetClone.RecordCount > 0
  2.  
So see if there are any language records and set the Translator box accordingly.

You will need a similar routine on the After Update of the language subform

Phil
Aug 7 '17 #6

P: 41
Dear Phil

I could not really succeed, sorry. I have a main form in which the checkbox is situated, a subform and a combobox in the subform.

If you feel tired of helping me, I am really sorry. I will try not to poste more questions after that problem...

Thanks!

Mike
Aug 7 '17 #7

PhilOfWalton
Expert 100+
P: 1,430
More than happy to help. Mike.

So what's the name of your subform?
Is there any information on it other than the language?
What code did you try?

Phil
Aug 7 '17 #8

P: 41
Dear Phil

Thank you!

The name of my SubForm is "T_Sprache-Unterformular". There is no other information on it than just the languages in the combobox "cmbSprache" out of the table "T_Sprache".

The mainform where the checkbox "Checkbox Übersetzer" is, is called "MainForm".

I tried the code you sent me on the Combo and additionally on the main form. It did not work, therefore I tried to put the Code on the Subform instead of the main form. I did the same with codes like the following, without success:

If Me.cmbSprache = 1 Then
Me.Checkbox_Übersetzer = True
Else
Me.Checkbox_Übersetzer = False
End If

I tried different codes like that which I found in the internet...

Mike
Aug 7 '17 #9

PhilOfWalton
Expert 100+
P: 1,430
I'll try to get my head round the German

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3.     Übersetzer = T_Sprache-Unterformular.Form.RecordsetClone.RecordCount > 0
  4.  
  5. End Sub
  6.  
In even better way if this is so important would be to have a great big label called LblÜbersetzer with the Caption Übersetzer and change the code to

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3.     LblÜbersetzer.Visible  = T_Sprache-Unterformular.Form.RecordsetClone.RecordCount > 0
  4.  
  5. End Sub
  6.  
Phil
Aug 7 '17 #10

P: 41
Dear Phil

Thanks a lot for your answer. Do I have to put this VBA-Code into the checkbox and in the main form? Where would I have to put it in the Checkbox (by click or by what?)?

Thank you

Mike
Aug 8 '17 #11

PhilOfWalton
Expert 100+
P: 1,430
You need it in the On Current of your main form so that as each main form record is read, a check is made on the number of records in the subform.

On the Subform you need
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2.  
  3.     Me.Parent!Übersetzer = Me.RecordsetClone.RecordCount > 0
  4.  
  5. End Sub
  6.  
So here we see if there are any languages in the subform, and change the check box in the parent (MainForm) accordingly.

Phil
Aug 8 '17 #12

P: 41
Dear Phil

Thanks a lot, I have tried many codes, unfortunately without success:

Main Form
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. CheckBox Übersetzer = T_Sprache- Unterformular.Form.RecordsetClone.RecordCount > 0
  3. End Sub
  4.  
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Übersetzer = T_Sprache-Unterformular.Form.RecordsetClone.RecordCount > 0
  3. End Sub
  4.  
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. CheckBox_Übersetzer = T_Sprache- Unterformular.Form.cmbSpracheClone.RecordCount > 0
  3. End Sub
  4.  
The whole thing also on the subform with the basis of:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2. Me.Parent!Übersetzer = Me.RecordsetClone.RecordCount > 0
  3. End Sub
  4.  
I have also tried to put the name of the checkbox and of the other data in brackets, but it did not work out.

Can you tell me what I am doing wrong?

Thank you!

Mike
Aug 8 '17 #13

PhilOfWalton
Expert 100+
P: 1,430
Mike, I have no idea what you are doing wrong.

It certainly works on my test database.

Can I suggest to send me a copy of your Db (with sensitive information stripped out).

Use the Private Message Option

Phil
Aug 8 '17 #14

P: 41
Dear Phil

How can I send you an attachment in the Private Message Option? I could not find an option to make an attachment.

Mike
Aug 8 '17 #15

PhilOfWalton
Expert 100+
P: 1,430
Hi Mike

See your Private Messages

Phil
Aug 8 '17 #16

P: 41
Phil helped me to solve the problem, thanks a lot! The VBA-Codes are like that:

Main form

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. [Checkbox Übersetzer] = [T_Sprache-Unterformular].Form.RecordsetClone.RecordCount > 0
  3. End Sub

Subform

First VBA After Update:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2. Me.Parent![Checkbox Übersetzer] = Me.RecordsetClone.RecordCount > 0
  3. End Sub
Second VBA AfterDelConfirm
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterDelConfirm(Status As Integer)
  2. Me.Parent![Checkbox Übersetzer] = Me.RecordsetClone.RecordCount > 0
  3. End Sub
Thank you very much, Phil!!!
Aug 9 '17 #17

Post your reply

Sign in to post your reply or Sign up for a free account.