how to store CHECKBOX AND OPTION VALUE IN A ACCESS DATABASE USING ADODC VB6

[Siddhanth]

I Have 3 check box I.e
Hobbies [] Dancing (check 1)
[] Singing (check 2)
[] Drawing(Check 3)
And
Gender () Male () Female

I want to store both single and multiple check box value

Update is my commandbutton for storing the datas

I have also tried this coding but it didn't work

Private Sub check1_click()
If check1.value = True then
Abodc1.Recordset.update
Hobbies=Dancing
Else if check2.value=false then
Else
Check 3.value=false
End if

End sub


Private Sub check1_click()
If check2.value = True then
Abodc1.Recordset.update
Hobbies=Singing
Else if check3.value=false then
Else
Check 1.value=false
End if

End sub



Private Sub check1_click()
If check3.value = True then
Abodc1.Recordset.update
Hobbies=Drawing
Else if check1.value=false then
Else
Check 2.value=false
End if

End sub

FOR OPTIONS BUTTON


Private Sub check1_click()
If option 1.value = True then
Abodc1.Recordset.update
Gender=Male
End if

End sub
Private Sub check1_click()
If option 2.value = True then
Abodc1.Recordset.update
Gender=Female
End if

End sub

[DataMiser]

Well you would need to have a table set up in your database to hold these values then you populate those values as needed.
I see multiple calls to update the recordset but no clue as to what fields are there, if they are bound, what type they may be nor what issue you are actually having

[Siddhanth]

I just need to know that what do i need to include here
Hobbies=dancing
Means hobbies is my field in a table how to set up

Table name is details and below are its fields
Name
Roll_no
Gender
Hobbies
Address
Country
Phone_no
Student_pic

Students details.mdb is my database

I want to store the value of both checkbox and option button

[Phill.W]

> "Hobbies"

That's a Poor Design.

Basic Data Normalisation Rules should tell you to move repeated elements into a sub-table.

You should have something more like this:

Table Students
   ID
   Name
   Roll_no
   Gender
   Address
   Country
   Phone_no
   Student_pic

Table Student_Hobbies 
   ID 
   Student_ID   [Foreign Key -> Students.ID] 
   Hobby

Or, viewed in Access:

select * 
from   Students ; 

+----+------------------+---------+--------+---------+---------+----------+-------------+ 
| ID | Name             | Roll_no | Gender | Address | Country | Phone_no | Student_pic | 
+----+------------------+---------+--------+---------+---------+----------+-------------+ 
|  1 | Fred Flintstone  |       1 | Male   | Bedrock | USA     | 1        | -           | 
|  2 | Wilma Flintstone |       2 | Female | Bedrock | USA     | 1        | -           | 
+----+------------------+---------+--------+---------+---------+----------+-------------+ 

select * 
from   Student_hobbies ; 

+----+------------+---------+ 
| ID | Student_ID | Hobby   | 
+----+------------+---------+ 
| 22 |          1 | Dancing | 
| 33 |          2 | Dancing | 
| 44 |          2 | Drawing | 
+----+------------+---------+

Regards, Phill W.

[westconn1]

and if they can select multiple hobbies, then perhaps each hobby should be a boolean field in a hobbies table

[Phill.W]

> "... perhaps each hobby should be a boolean field in a hobbies table"

No; the presence of a record in the Student_hobbies table indicates that the Student has that Hobby.

In my example that Student #1 (Fred) has only dances whilst Student #2 (Wilma) dances and draws. Nobody sings.

Perhaps I should have included a "master" Hobbies table that names each Hobby; then the Student_hobbies table would include only [the IDs of] the Student and the relevant Hobby.

select * 
from   Students ; 

+----+------------------+---------+--------+---------+---------+----------+-------------+ 
| ID | Name             | Roll_no | Gender | Address | Country | Phone_no | Student_pic | 
+----+------------------+---------+--------+---------+---------+----------+-------------+ 
|  1 | Fred Flintstone  |       1 | Male   | Bedrock | USA     | 1        | -           | 
|  2 | Wilma Flintstone |       2 | Female | Bedrock | USA     | 1        | -           | 
+----+------------------+---------+--------+---------+---------+----------+-------------+ 

select  * 
from Hobbies ; 

+-----+---------+ 
| ID  | Name    | 
+-----+---------+ 
| 777 | Dancing | 
| 888 | Drawing | 
| 999 | Singing | 
+-----+---------+ 

select * 
from   Student_hobbies ; 

+----+------------+----------+ 
| ID | Student_ID | Hobby_ID | 
+----+------------+----------+ 
| 22 |          1 |      777 | 
| 33 |          2 |      777 | 
| 44 |          2 |      888 | 
+----+------------+----------+

Regards, Phill W.

[Siddhanth]

I made some changes in the coding and now it's saying object required 404
I don't know what is object
If check1.value =1 then
Adodc1.recordset.update
Hobbies="Drawing"
Else if check2.value =2 then
Else
Check3.value=2
End if
End sub

So the HOBBIES is a field A ND ITS CHECKBOX es are Drawing singing and dancing
So when I run the form in vb and click on drawing I want that drawing to be stored in a Access database
Table that I have created. Not using SQL statement using conditional statement in vb form

[DataMiser]

Object in this case is referring to the adodc1.recordset or one of those check boxes The code should have highlighted the part that is an issue.

You also have a space in the ElseIF statement that should not be there.

I suppose you could have a large text field and store more than one hobby in it as text but that is not a very good way to do it.
Either you need to use a separate table as was shown to you earlier or you need to add multiple fields to your existing table.

So using the latter option let's say you have 5 checkboxes for hobbies rather than having one field for hobbies you have 5 fields one for each hobby and they are directly related to the check boxes so one or more of them can be set to true.

There are other options as well but post #6 shows a good structure.
You also need to give us some idea of the things you do not understand. I am guessing you do not have any experience working with ADO and databases.

[Tonnus]

You could create an invisible textbox, set the datasource and datafield to Hobbies and then update the value based on the checkbox. But you will need to translate the Hobbies back to checkbox when loading the data.

[Schmidt]

This can be eased quite a bit, in the following way:
- just make your Hobbies-Field a 32Bit Integer (which can then hold up to 32 different Hobbies max.)
- develop your own (Project-Private) UserControl which is bound to that Integer-Field

As for the UserControl in question - here's the needed code to put into it:

Option Explicit

Public Enum enmHobbies
  hobDrawing = 1
  hobSinging = 2
  hobDancing = 4
  'a.s.o with 8, 16, 32 (powers of two)
End Enum

Private WithEvents Drawing As CheckBox
Private WithEvents Singing As CheckBox
Private WithEvents Dancing As CheckBox

Private Sub UserControl_Initialize()
  ScaleMode = vbPixels
  Set Drawing = Controls.Add("VB.CheckBox", "Drawing")
      Drawing.Caption = "Drawing": Drawing.Visible = True: Drawing.Move 10, 0, 200, 22
  Set Singing = Controls.Add("VB.CheckBox", "Singing")
      Singing.Caption = "Singing": Singing.Visible = True: Singing.Move 10, 25, 200, 22
  Set Dancing = Controls.Add("VB.CheckBox", "Dancing")
      Dancing.Caption = "Dancing": Dancing.Visible = True: Dancing.Move 10, 50, 200, 22
End Sub
 
Public Property Get Hobbies() As enmHobbies
'Attribute Hobbies.VB_MemberFlags = "24"
  Hobbies = Hobbies Or Drawing.Value * hobDrawing
  Hobbies = Hobbies Or Singing.Value * hobSinging
  Hobbies = Hobbies Or Dancing.Value * hobDancing
End Property
Public Property Let Hobbies(ByVal RHS As enmHobbies)
  Drawing.Value = -CBool(RHS And hobDrawing)
  Singing.Value = -CBool(RHS And hobSinging)
  Dancing.Value = -CBool(RHS And hobDancing)
  PropertyChanged "Hobbies"
End Property

Private Sub Dancing_Click()
  Hobbies = Hobbies 'trigger the Change-Info
End Sub
Private Sub Drawing_Click()
  Hobbies = Hobbies 'trigger the Change-Info
End Sub
Private Sub Singing_Click()
  Hobbies = Hobbies 'trigger the Change-Info
End Sub

You will need to ensure the following on that UserControl (after pasting above code into it):
- Set its DataBindingBehaviour to [1 - vbSimpleBound] (in the UserControls-PropertyGrid)
- then go into the UserControl-Code, place the Cursor on the Property-Get Method-Name 'Hobbies'
- ... and start the Extras>ProcedureAttributes-Dialogue
- ... in that Dialogue, press the "Advanced >>"-Button and set the two Checks "Property is data bound" and "This property binds to DataField"

HTH

Olaf