Solved Update Record on Change in Check Box

[RonieB]

I have a form that when one of three boxes is checked it adds the value to a field on the subform. How do I update the field to the new value if the user changes it after it has already added the previous selection?
My code to add the value is:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Parent.Check40 = True Then
Me.SItemType.Value = "A"
Else
If Me.Parent.Check36 = True Then
Me.SItemType.Value = "B"
Else
If Me.Parent.Check38 = True Then
Me.SItemType.Value = "U"
Else
Me.SItemType.Value = Null
End If
End If
End If
Me.SEstIni = Me.Parent.Combo51
End Sub
(Yes, I need to rename the boxes for structure but am testing in one database and adding to database being used with correct structure when it works properly)

 

[MajP]

Not sure I understand your set up. Does the user pick a checkbox for each record? I am trying to understand why this is in the before update and not after picking a selection. If that is the case it should update on any change.
Also I would think this is an option group and not individual checkboxes. You can only pick one checkbox at a time.
If that is the case you use the value of the option group more like

Select case optGroupChoice.value
  case 1
   me.SitemType.value = "A"
case 2
  Me.SitemType.value = "B"
Case 3
  me.SitemItemType.value = "C"
...
end select

Can you post the db so we can understand what your setup is?

 

[MajP]

I assume you want to change all the child records.

Private Sub Check36_AfterUpdate()
  UpdateChildRecords
End Sub

Private Sub Check38_AfterUpdate()
  UpdateChildRecords
End Sub

Private Sub Check40_AfterUpdate()
  UpdateChildRecords
End Sub
Private Sub

Private Sub UpdateChildRecords()
  Dim strSql As String
  Dim code As String
  If Not IsNull(Me.ProjIdxID) Then
   If Check40 = True Then
      code = "A"
   Else
   If Check36 = True Then
      code = "B"
   Else
   If Check38 = True Then
      code = "U"
    Else
      code = "Null"
    End If
    End If
    End If
 
  If code = "Null" Then
    strSql = "Update tbl_Sow SET Sitemtype = Null where ProjIdxID = " & Me.ProjIdxID
  Else
    strSql = "Update tbl_Sow SET Sitemtype = '" & code & "' where ProjIdxID = " & Me.ProjIdxID
  End If
  'Debug.Print strSql
  Me.sfrm_EsimateEntry.Form.Requery
  CurrentDb.Execute strSql
  End If
End Sub

 

[RonieB]

Perfect!!! Thank you so much for the assist!

 

[Gasman]

PMFJI,
As MajP mentioned, if only one option should be available, then an option group is normally used.?
What if the user checks all 3 controls? or do you have code that clears the other checkboxes when one is selected?

 

[MajP]

This makes sense only if it is hierarchical. If A and B are both chosen then store A. If using an option group you will need a 4th choice "A, B, U, N/A". If not once you choose a value you cannot remove it. You can only change the value.

 

[RonieB]

I marked this as solved because it is doing what I want it to do. I do have a lot of structure reconsiderations. I am in process of changing the form to a Tabbed Form because all of the data for each item needs to be seen during the process. The initials and item type are needed to delineate between who did the data input and what type of item it is. I initially used combo box but thought it was a wrong choice as I could not get it to work for me. the XXX default is so when I put the lock feature back in, if it is XXX then lock form until initials are chosen. I took it out while I was working on this because it kept locking me out when I would change something. I am no expert, so these are trial and error for me. I do a lot of searching and trying examples of other solved issues in the development database. I am not fluent in VBA language and have a hard time explaining what I am trying to do. While I understand your skills and knowledge probably make you cringe at someone like me, this is how I am learning. I do appreciate all of the input and advice.

 

[RonieB]

I took all of the refresh out and it is still working. I thought I had to save for it to be seen. I appreciate the advice and encouragement

 

[RonieB]

You're welcome. I saw the thread was marked solved but since i took the time to look at the database, I decided to report my findings.
Good luck.

Reconsider using a pick list to have the person doing the data entry identify himself. You can do it for him to avoid mistakes. And if you are doing it for security, you would never give the user a combo because that gives an unauthorized person 50% of what he needs to log in and probably the information required to social engineer the rest.

Got it and thank you again.