Code for checkboxes

[sandrothaver]

Hi!

I have checkboxes for MALE and FEMALE (my form has to mirror my data collection form, hence no drop-downs). What code do I put behind the checkboxes to allow the query to reflect the values in one GENDER field (which I will create in the table with drop-downs) with the actual values - male and female, instead of the MALE and FEMALE fields that the checkboxes generate in a query with 1's. Also, how do I ensure that the user is only allowed to select one of male or female per record.

Thanks
Sandro

 

[ErikSnoek]

Use an option group box.

 

[khawar]

what is your table structure i-e field names

 

[sandrothaver]

I've tried an option group, but have the same problem when querying -male and female are listed as separate fields with numbers as values.

 

[ErikSnoek]

You just need the field "Gender" in your table and use that as control source for your option group. Then "Gender" will get a value of 1 or 2 (unless you specify different values in the checkboxes). For example 1 being male and 2 being female.

 

[khawar]

tell me your table field names amd data types i thing you have two different fields in table

 

[khawar]

EricSnoek's solution is perfect for your scenerio

 

[sandrothaver]

So using an option group with control source being the field 'gender' will give me 'male' and 'female' values in my gender field for the query, instead of numbers cos that's the problem?

 

[khawar]

option group cannot give you the text thats why I was asking your field types but you can use combo box in that case
If you donot want to use combo you have to do some work around with the check boxes and check boxes has to be unbound on the form

 

[khawar]

One more thing even it is a number in the field you can still have text "Male" or "Female" in the query by using iif statement

 

[sandrothaver]

Please give me an example of the code I would have to use ie. 'if' statement. If the checkbox is unbound on the form, how will it store the text values in the gender field?

 

[khawar]

download the attached sample
I used
1 - option group on the form
2 - Number field in the table (1 for male, 2 for Female)
3 - Query1 takes data from table1 and shows Male and female instead of numbers

 

[khawar]

Sample 2 using unbound frame(checkboxes) stores text in the field
Check form events

But i will advise you to use the previous method because this one is difficult to maintain and there are more chances of error

 

[sandrothaver]

Hi Khawar

I've been trying Sample 2 and I'm almost there. I still get numbers but I think I'm close. Please check the code below for me and tell me why I still get numbers. I've adapted the Gender example for another one. I want to display the 11 names shown below in my query.

Thanks!

Private Sub Form_Current()
If District = "Amajuba" Then
Me.Frame181 = 1
ElseIf District = "eThekwini" Then
Me.Frame181 = 2
ElseIf District = "Ilembe" Then
Me.Frame181 = 3
ElseIf District = "Sisonke" Then
Me.Frame181 = 4
ElseIf District = "Ugu" Then
Me.Frame181 = 5
ElseIf District = "Umgungungdlovu" Then
Me.Frame181 = 6
ElseIf District = "Umkhanyakhude" Then
Me.Frame181 = 7
ElseIf District = "Umzinyathi" Then
Me.Frame181 = 8
ElseIf District = "Uthukela" Then
Me.Frame181 = 9
ElseIf District = "Uthungulu" Then
Me.Frame181 = 10
ElseIf District = "Zululand" Then
Me.Frame181 = 11
Else
Me.Frame181 = Null
End If
End Sub

Private Sub Frame181_AfterUpdate()
If Me.Frame181 = 1 Then
Me.District = "Amajuba"
Me.District = "eThekwini"
Me.District = "Ilembe"
Me.District = "Sisonke"
Me.District = "Ugu"
Me.District = "Umgungungdlovu"
Me.District = "Umkhanyakhude"
Me.District = "Umzinyathi"
Me.District = "Uthukela"
Me.District = "Uthungulu"
Me.District = "Zululand"
Else
End If
End Sub

 

[khawar]

Private Sub Frame181_AfterUpdate()
If Me.Frame181 = 1 Then
Me.District = "Amajuba"
Me.District = "eThekwini"
Me.District = "Ilembe"
Me.District = "Sisonke"
Me.District = "Ugu"
Me.District = "Umgungungdlovu"
Me.District = "Umkhanyakhude"
Me.District = "Umzinyathi"
Me.District = "Uthukela"
Me.District = "Uthungulu"
Me.District = "Zululand"
Else
End If
End Sub

Assign individual values here

 

[sandrothaver]

Don't know what you mean. I'm very new to code.

 

[khawar]

Like this

Private Sub Frame181_AfterUpdate()
If Me.Frame181 = 1 Then
Me.District = "Amajuba"

elseif Me.Frame181 = 2 Then
Me.District = "eThekwini"

...................... and so on

Else
End If
End Sub

 

[sandrothaver]

Hi

I've done that. Please see code below. In the form, when I click on 'Amajuba', the text dissplays in the District field, but when i click on the rest - from eThekwini to zululand, numbers 2 to 11 dissplays in the district field. Seems like a minor problem with the code??

Private Sub Form_Current()
If District = "Amajuba" Then
Me.Frame181 = 1
ElseIf District = "eThekwini" Then
Me.Frame181 = 2
ElseIf District = "Ilembe" Then
Me.Frame181 = 3
ElseIf District = "Sisonke" Then
Me.Frame181 = 4
ElseIf District = "Ugu" Then
Me.Frame181 = 5
ElseIf District = "Umgungungdlovu" Then
Me.Frame181 = 6
ElseIf District = "Umkhanyakhude" Then
Me.Frame181 = 7
ElseIf District = "Umzinyathi" Then
Me.Frame181 = 8
ElseIf District = "Uthukela" Then
Me.Frame181 = 9
ElseIf District = "Uthungulu" Then
Me.Frame181 = 10
ElseIf District = "Zululand" Then
Me.Frame181 = 11
Else
Me.Frame181 = Null
End If
End Sub

Private Sub Frame181_AfterUpdate()
If Me.Frame181 = 1 Then
Me.District = "Amajuba"
ElseIf MeFrame181 = 2 Then
Me.District = "eThekwini"
ElseIf MeFrame181 = 3 Then
Me.District = "Ilembe"
ElseIf MeFrame181 = 4 Then
Me.District = "Sisonke"
ElseIf MeFrame181 = 5 Then
Me.District = "Ugu"
ElseIf MeFrame181 = 6 Then
Me.District = "Umgungungdlovu"
ElseIf MeFrame181 = 7 Then
Me.District = "Umkhanyakhude"
ElseIf MeFrame181 = 8 Then
Me.District = "Umzinyathi"
ElseIf MeFrame181 = 9 Then
Me.District = "Uthukela"
ElseIf MeFrame181 = 10 Then
Me.District = "Uthungulu"
ElseIf MeFrame181 = 11 Then
Me.District = "Zululand"
Else
End If
End Sub

 

[khawar]

dot is missing in your statement
use Me.Frame181 instead of MeFrame181

 

[sandrothaver]

Ahh! Stupid me! It works now. Thanks very much for your time!