Automatically activate checkbox when Combobox-field is not empty

[MikeMikeCH]

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

[PhilOfWalton]

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

[MikeMikeCH]

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

[PhilOfWalton]

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

[MikeMikeCH]

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

[PhilOfWalton]

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

ChkTranslater = [LanguageSubForm].[Form].RecordsetClone.RecordCount > 0
 

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

[MikeMikeCH]

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

[PhilOfWalton]

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

[MikeMikeCH]

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

[PhilOfWalton]

I'll try to get my head round the German

Private Sub Form_Current()
 
    Übersetzer = T_Sprache-Unterformular.Form.RecordsetClone.RecordCount > 0
 
End Sub
 

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

Private Sub Form_Current()
 
    LblÜbersetzer.Visible  = T_Sprache-Unterformular.Form.RecordsetClone.RecordCount > 0
 
End Sub
 

Phil

[MikeMikeCH]

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

[PhilOfWalton]

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

Private Sub Form_AfterUpdate()
 
    Me.Parent!Übersetzer = Me.RecordsetClone.RecordCount > 0
 
End Sub
 

So here we see if there are any languages in the subform, and change the check box in the parent (MainForm) accordingly.

Phil

[MikeMikeCH]

Dear Phil

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

Main Form

Private Sub Form_Current()
CheckBox Übersetzer = T_Sprache- Unterformular.Form.RecordsetClone.RecordCount > 0
End Sub
 

Private Sub Form_Current()
Übersetzer = T_Sprache-Unterformular.Form.RecordsetClone.RecordCount > 0
End Sub
 

Private Sub Form_Current()
CheckBox_Übersetzer = T_Sprache- Unterformular.Form.cmbSpracheClone.RecordCount > 0
End Sub
 

The whole thing also on the subform with the basis of:

Private Sub Form_AfterUpdate()
Me.Parent!Übersetzer = Me.RecordsetClone.RecordCount > 0
End Sub
 

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

[PhilOfWalton]

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

[MikeMikeCH]

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

[PhilOfWalton]

Hi Mike

See your Private Messages

Phil

[MikeMikeCH]

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

Main form

Private Sub Form_Current()
[Checkbox Übersetzer] = [T_Sprache-Unterformular].Form.RecordsetClone.RecordCount > 0
End Sub

Subform

First VBA After Update:

Private Sub Form_AfterUpdate()
Me.Parent![Checkbox Übersetzer] = Me.RecordsetClone.RecordCount > 0
End Sub

Second VBA AfterDelConfirm

Private Sub Form_AfterDelConfirm(Status As Integer)
Me.Parent![Checkbox Übersetzer] = Me.RecordsetClone.RecordCount > 0
End Sub

Thank you very much, Phil!!!