Check Box Selection 1

[srpatel]

Hi,

Am a beginner with access development and require some help.

I have a form called 'frmSelectCode' which has Two Text Boxes and a
Checkbox. The text boxes are bound to the values from Table
'tblAllCodes'. In total there are about 66 fields.

On the main form, there is a Cmd Button that has an onClick Event,
which calls frmSelectCode. This opens up and a user is presented with
all the fields and checkboxes besides the field to choose from.

At present users can select as many check boxes as they want. I would
like this to be limited to have only one checkbox selected at one time.

Could someone point to the right direction.

Thanks
Shreekant

 

[TimTDP]

Set the enable property of each check box, except the first, to "No"
In the AfterUpdate event of the first check box set the enable property of the next check box to Yes..... and so on, until all check box's have been enabled

 

[srpatel]

Hi

On the form design i only have one checkbox. I think the way its working is that the checkboxes are being generated on depending on the number of fields from the table.

On my form, it's basically designed like below:

"textBox" | "textBox" | "CheckBox"

Also I am quite new to this, so please bear with my very basic questions, but what would I type up in the afterupdate event?

 

[MajP]

I interpret what you are asking differently. I think you want to select an Option Group from your controls toolbox. If you have an option group only one item can be selected at a time.

 

[srpatel]

If i Use an options group i will be creating options for 66 fields and they could be increasing to a higher number. Hence the current check box gets generated depending on the records within the table.

Is there a piece of code that i can put behind the check box to have to limited to one selection?

Thanks
Shreekant

 

[MajP]

I am going to bet that your database is not normalized. You have 66 fields that are either true or false, but only one could be true. Why not one field with 66 choices? Your design sounds bad. However, this may work. I have three boolean controls blnTF2,3,4.

Public Sub checkUncheck(theCheckBox As Access.Control)
  Dim myControl As Access.Control
  For Each myControl In Me.Controls
    If myControl.ControlType = acCheckBox Then
      myControl.Value = False
    End If
  Next myControl
  theCheckBox.Value = True
End Sub

Private Sub blnTF2_AfterUpdate()
   Call checkUncheck(ActiveControl)
End Sub

Private Sub blnTF3_Click()
  Call checkUncheck(ActiveControl)
End Sub

Private Sub blnTF4_AfterUpdate()
   Call checkUncheck(ActiveControl)
End Sub

 

[MajP]

They should all be afterupdate in my example not Click events. But seriously think about your design, as you said this may grow. Explain your tables, and we can help.

 

[srpatel]

Hey MajP,

I am having trouble using your code. Where does the Public Sub Code get placed?

Private Sub Selected_AfterUpdate()
Public Sub checkUncheck(Selected As Access.Control)
  Dim myControl As Access.Control
  For Each myControl In Me.Controls
    If myControl.ControlType = acCheckBox Then
      myControl.Value = False
    End If
  Next myControl
  Selected.Value = True
End Sub
End Sub


Private Sub Selected2_AfterUpdate()
   Call checkUncheck(ActiveControl)
End Sub

Private Sub Selected3_Click()
  Call checkUncheck(ActiveControl)
End Sub

Private Sub Selected4_AfterUpdate()
   Call checkUncheck(ActiveControl)
End Sub

I am quite a newbie to the VB coding environment. However thanks for all ya help

 

[MajP]

You have me totally confused. Do you have 66 fields or 66 records? I think you mean records.
Now that I reread this it sounds like you have one field, "Selected" with 66 records you could choose. But you only want them to choose one of the 66 records. That makes more sense in design. The above code was on the assumption that you had 66 fields that you could choose. Again give details on the table.

 

[srpatel]

Ok sorry for the confusion. There are 66 records within tblAllCodes.

tblAllCodesdetails:

LCSCode(PK) | LCSDesc | LCSCodeID | Selected (Format Yes/No, Checkbox)


frmSelectCode is a form which is linked to the main entry form, frmAssetEntry. The users have to hit the command button SelectCodes on the entry form, which brings up frmSelectCode. This displays the LCSCode, the LCSDesc and Selected.

So users at present can select as many of the checkboxes that appear. This needs to be changed to only selecting one checkbox. The checkboxes depend on I guess the number of records in tblAllCodes. If I removed 10 records then there will be ten less records appearing on the frmSelectCode.

Hope this gives abit more insight to the issue at hand and and I hope there is solution

 

[Ascentient]

Would it not be easier to use a listbox or combobox? This was you can limit them to only 1 (or several depending on the control) selection. Based on the selection you can then code/setup the rest of your form based on what they chose.

 

[MajP]

This code will allow you to select one record in a field. Not sure if it is a great design, but it works.

Private Sub Selected_AfterUpdate()
    Call checkUncheck2("Selected")
End Sub

Public Sub checkUncheck2(strFieldName)
  
  Dim rs As DAO.Recordset
  Dim lngCurrRec As Long
  lngCurrRec = Me.CurrentRecord
  Set rs = Me.Recordset
  rs.MoveFirst
  Do While Not rs.EOF
    rs.Edit
    If Not (rs.AbsolutePosition = lngCurrRec - 1) Then
      rs.Fields(strFieldName) = False
    End If
    rs.Update
    rs.MoveNext
 Loop
 Me.Requery
 End Sub

 

[srpatel]

I was thinking along the lines of a combo box but the users are not too happy with that being on the form as there are already about 4 they have to choose from.

I will try the code you have given.

Thanks for the help!

 

[srpatel]

Heya Majp

I added the code you provided to the AfterUpdate of the checkbox control on frmSelectCode.

I got the following error when selecting a checkbox:

Compile Error
User-Defined type not Defined and highlights

Public Sub checkUncheck2(strFieldName)

 

[MajP]

first change this to
Public Sub checkUncheck2(strFieldName as string)
Should not make a difference, but it is bad coding

Second ensure you have a reference to Data Access Objects

This is from the help file.

Set References to Type Libraries

You can set a reference from Microsoft Access while the Microsoft Visual Basic Editor is open, or you can set a reference in Visual Basic code.

Setting a Reference from Microsoft Access
To set a reference to an application's type library:

On the Tools menu, click References. The References command on the Tools menu is available only when a Module window is open and active in Design view.
Select the check boxes for those applications whose type libraries you want to reference.

 

[Ascentient]

End-users will be end-users. I can't imagine having that many text/check boxes on the form with the ability of the list being able to grow. If I understand correctly this list may grow, therefore causing you more work as more are added. (Maybe I missed something, but I am all about not having to do extra work if it can be prevented from the beginning.

 

[MajP]

Ascentient,
The above code solves that problem. It works like a combobox where the amount of items can grow with the amount of records.

 

[srpatel]

YIpppeeee,

Finally it works! Thanks so much, Perserverance! Two things to point out though,

The References i choose was:Microsoft DAO 3.6 Object Library.

When Selecting any of the check boxes, it selects it and then the like scrolls up and down then stops but works. Any ideas?

 

[Ascentient]

MajP,
Thanks for pointing that out. I missed the correlation of the "Selected" in the Sub and the field name.

 

[MajP]

add this at the beginning and end

DoCmd.Echo (False)
rs.MoveFirst

Do While Not rs.EOF
rs.Edit
If Not (rs.AbsolutePosition = lngCurrRec - 1) Then
rs.Fields(strFieldName) = False
End If
rs.Update
rs.MoveNext
Loop
Me.Requery
DoCmd.Echo (True)