need help with option group - combobox update 1

[tony9bb]

I posted this in the Microsoft Access Other forum, which was the wrong place. Sorry.

I'm not a "seasoned" VBA coder but I'm learning through this forum. Now I need to ask a question.

I have a form with a combobox and an option group. I have the following code in the After_Update of Frame81:
********************************
Private Sub Frame81_AfterUpdate()
'This procedure selects the status of the equipment
'All equipment is set to "Not Used" previously, by default
'Only the equipment used is changed

Dim tbl As TableDef
Dim db As Database
Dim rst As Recordset
Dim strEquipment, strStatus As String
Set db = CurrentDb()
Set tbl = db.TableDefs("tblProjects&quot
Set rst = tbl.OpenRecordset
'Sets the equipment name by what was selected in the combobox
strEquipment = Me.EquipChoice
'Find the Status from the optiongroup
strStatus = Choose(Me.Frame81, "Not Used", "Active", "Inactive&quot
'Update the record
With rst
.Edit
rst(strEquipment) = strStatus
.Update
End With
***************************************
My idea is that the user selects a choice from a combobox, then selects whether the selected choice is
1)Not Used
2) Active
3)Inactive
from the option group.
I then update the record according to the selection in the option group.

The problem is: When I select a second choice in the combobox, then select the option group choice, I get an information box telling me the data has been edited by another user and that I should re-edit the record.

I thought the ".update" would prevent this from happening.
How can I allow multiple selections in the combobox AND the option group without the message coming up. Am I doing something wrong/incomplete??

thanks
tony

 

[chirpyform]

I'm not completely sure that i've understood but if you have on the form a combobox and an option groupe and you want all the selected items to take the value selected in the option group then try:

Private Sub Frame81_AfterUpdate()
'This procedure selects the status of the equipment
'All equipment is set to "Not Used" previously, by default
'Only the equipment used is changed

Dim db As Database
dim ctl as control '**********
dim varElt as variant
Dim strEquipment, strStatus As String
Set db = CurrentDb()
'Sets the equipment name by what was selected in the combobox

Set ctl = Me!EquipChoice

If ctl.ItemsSelected.count = 0 Then Exit Sub
'Find the Status from the optiongroup
strStatus = optiongroupe.Value

For Each varElt In ctl.ItemsSelected
db.execute "UPDATE tblProjects SET " & ctl.Column(11, varElt) & " = '" & strStatus & "'
Next varElt
End With


Or something along those lines

 

[tony9bb]

Thanks for the reply chirpyform.
Sorry I wasn't clear enough.

You gave me an idea to change a few things around then this will work. I was using a single combobox for all the choices, but if I use two comboboxes (one for Active and another for Inactive) I can use your suggestion.

Am I right in this thinking using your suggestion, or can I still use one combobox?

The scenario is:
A construction crew must file a daily report. In this report they must include what equipment was used on site. The equipment is a fixed list (15 items)so I have a Project table (tblProjects) with each item as a field. By default (when a new project is created) I set each item to "Not Used". So the user only has to identify which one was used and whether it was active or inactive. I then can generate a report using this information later.

thanks
tony

 

[chirpyform]

Use checkBoxes / List box(multiple choice set as yes) for the tools and then an option group using radio buttons for active and inactive then you could use what I said above. The usage would be to select all the tools that were active and choose the option "active" and then choose all the tools that were inactive with the option "not active".
Some helpful code for selectioning or unselectioning all the tools:

Private Sub selectAll_Click()
Dim ctl As control
Dim i As Integer
Set ctl = Me!SFGList
For i = 0 To ctl.ListCount - 1
ctl.Selected(i) = True '/ False depending on whether you want to selection or unselection
Next i
End Sub

 

[tony9bb]

EXCELLENT!!!!!
Works Great.
Thanks for the help.

tony