Solved Update Record on Change in Check Box (1 Viewer)

RonieB

New member
Local time
Today, 17:42
Joined
Jun 1, 2022
Messages
22
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

You've got your good things, and you've got mine.
Local time
Today, 18:42
Joined
May 21, 2018
Messages
8,463
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

Code:
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

You've got your good things, and you've got mine.
Local time
Today, 18:42
Joined
May 21, 2018
Messages
8,463
I assume you want to change all the child records.

Code:
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

New member
Local time
Today, 17:42
Joined
Jun 1, 2022
Messages
22
Perfect!!! Thank you so much for the assist! :)
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:42
Joined
Sep 21, 2011
Messages
14,048
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

You've got your good things, and you've got mine.
Local time
Today, 18:42
Joined
May 21, 2018
Messages
8,463
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:42
Joined
Feb 19, 2002
Messages
42,976
The design of the form is confusing and there is no data validation. You are taking unbound values from the parent form and using them to populate fields in the subform if the subform gets updated. However, you are not validating the data. You are just blindly copying nothing or something to the subform. For example, you seem to have a default for Estimator of XXXX. Do you really want that to be added to every new subform record or do you want the person to actually choose a name? You need to at least ensure that there are values to be copied. Also, if people do their own data entry, they shouldn't have to choose their name at all. YOU should be automatically populating their UserIDs (Initials are a poor choice because they can be duplicated although you can display as initials if the initials are unique) based on who is logged in. You are also duplicating other data but I didn't investigate that.

Your code contains multiple refreshes. Refresh and Requery have specific tasks but as a SIDE EFFECT before performing their assigned task, they must force the current record to be saved. If you want to save a record, do it explicitly. Do not rely on a side effect of a different command. You have no control over the decisions MS makes internally and at some point, the "side effect" may not be to save. This is very poor (and dangerous) programming practice. Logically, you don't actually want to save a record at all until the data is complete. If you had proper validation code, the save would fail if you saved before all the required data had been collected and verified so I recommend removing ALL the refreshes. Access will save the record when you leave it. You do not need to expressly save it. Access has a personal mission to not let data go unsaved so most people new to Access, especially experienced developers who don't understand the "Access way" get in trouble with this concept and think they need to control forcing a save. But, in fact, you need to work to PREVENT saving should the data be invalid or the user change his mind and let Access always try to save. Your validation code belongs mostly in the FORM's BeforeUpdate event. That is the last event that runs before a record gets saved and all you have to do to prevent a record from being saved is to use:

Cancel = True

So, do the validation and set the Cancel argument to True and exit the sub. The record will be left dirty so the user can fix the problem and try again. Only in very rare cases would you ever use Me.Undo to remove what the user typed. I don't use it to remove invalid data. The user needs to see what he typed. I would only use Me.Undo if I was going to prevent the user from saving anything, including valid data. So if a user is not authorized to update/add/delete, I use Me.Undo. Otherwise, I leave the bad data to be corrected.

As others have mentioned, If the type is mutually exclusive (I.e. only one value at a time), then the proper technique is to use an option group or a combo rather than separate checkboxes. On a subform, a combo is preferred since it takes less screen real estate.

I would also rethink the presentation. An Access form is not a web page. It shouldn't be huge and have to scroll in all directions. This makes it much harder for the user to work with because he probably can't see everything he needs to see at one time. Rather than having two huge subforms, you might consider using a tab control so you can show one subform or the other depending on which tab is selected. I would also consider making the Estimate data subform several rows high so it can be viewed without horizontal scrolling. Vertical scrolling from one record to the next is perfectly rational. I'm sure you can organize the individual fields so they are clumped by relevance to help the user focus.

And finally, it is really important to your future sanity and that of anyone who works with the database for you to give every control added to a form a meaningful name so some late night you don't have to go searching for what chk40 is or text382. AND it is important to do this BEFORE you actually use the control because after you use it in code, changing becomes a PITA. Doesn't mean you shouldn't still make the change. Just means it is more work.
 
Last edited:

RonieB

New member
Local time
Today, 17:42
Joined
Jun 1, 2022
Messages
22
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.
 
Last edited:

RonieB

New member
Local time
Today, 17:42
Joined
Jun 1, 2022
Messages
22
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 :)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:42
Joined
Feb 19, 2002
Messages
42,976
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.
 

RonieB

New member
Local time
Today, 17:42
Joined
Jun 1, 2022
Messages
22
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. :)
 

Users who are viewing this thread

Top Bottom