Solved Update Record in Table with Combo Box Selection (1 Viewer)

RonieB

New member
Local time
Today, 07:52
Joined
Jun 1, 2022
Messages
22
I have a form with a subform that I would like a combo box on the form to update as records are added to a table via a subform. I used the following code for the subform event AFTER UPDATE, but I get an error that says it cannot find the field. Any suggestions on how to correct so that when data is entered on the subform it adds the seletion per record on the subform?

Private Sub SSOW_AfterUpdate()
Me!SEsIni = [Forms]![frm_WI_Entry].Combo51
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:52
Joined
Feb 19, 2002
Messages
42,970
If Access says it can't find the field, it means what it says. You almost certainly have a typo. Also, when refering to the parent form, use Me.Parent.somecontrolname rather than the external form of reference of Forms!yourform!somecontrolname

The form's AfterUpdate event runs AFTER the record is saved so it is too late. The horses have left the barn. If you want to add data to a record before it is saved, use the Form's BeforeUpdate event.

If it is the FK that you are trying to add to the record, Access will do that for you. Just set the master/child links on the subform control and the FK will automatgically be populated whenever a new record is created in the subform.

If this is a different piece of data, you need to rethink your table designs. Any value that must be stored in every subform record does not belong in that table. It belongs in the parent table.

Also, don't be sloppy. ALWAYS give your controls meaningful names as soon as you add them to a form. If you wait until after you have added event code, you will orphan the code. Doesn't mean you shouldn't fix the problem, just makes it more work. It is never too late to fix poor names.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:52
Joined
Feb 19, 2002
Messages
42,970
the form is fProjIndex and I am trying to add the initials to the records in the subform for each line added.
I guess you didn't read my response.

If you are trying to track who updates the subform record each time it is changed, not just when a record is created, then when they log in, hide your log in form rathe rather than closing it. That way you can always reference the log in credentials from anywhere in the app. If you don't have a log in form, you can use Environ("UserName") in the SUBFORM's BeforeUpdate event. NOT from the main form.
 
Last edited:

RonieB

New member
Local time
Today, 07:52
Joined
Jun 1, 2022
Messages
22
yes, I did read it. I just didn't understand so thought showing it to you might help me understand. Thanks anyway.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:52
Joined
Feb 19, 2002
Messages
42,970
Me looking at your database isn't going to help you to understand how it needs to change but I do have a better idea of exactly what is causing the problem with the code you are using. You are very new here. Welcome, by the way:) So I have no idea what you know or how to explain things to you. Let me try again and be more specific as to what you are changing

I did look at the database. The problem with me looking at the database is that although I can see what you are trying to do, I have no way of knowing what your business rules are and that is critical in most cases so me just fixing the code so it works isn't going to help you because I can't tell if you've thought through the business rules in detail. As I explained in my reply. If only the initial Estimator's Initials are relevant, then they belong ONLY in the main record. They do not need to be duplicated in the detail records if the values will always all be the same.

You have a combo box on the main form where the user picks his initials out of the list. I see now that the combo is unbound so there is no record kept in the mainform of who changed/added what. Think about the logic of that. If person A adds the main form and a few child records and we use your plan, the logic works if he has remembered to pick his initials and he didn't pick someone else's by accident or on purpose.

Now person B comes along and changes something. Does he change the Estimator Initials? Should that change the initials of the entries made by the other person or just on new records that he adds? What if you wanted to record his initials in the records he changed? How would you do that if he didn't change the SSOW field? The code would need to be in a different event. The subform's BeforeUpdate event would be the proper event.

The crux of the problem is a new "feature" that has recently appeared in Access. This "feature" used to exist only for reports. Now the disease has spread to forms also. The "feature" is that you need to bind a control to any column you want to reference in VBA. The control doesn't need to be visible and you can make it very tiny but it has to be on the subform. So if you add SEsIni to the subform, your problem should disappear.

Me.SEsIni = Me.Parent.Combo51

You did understand what I said about proper naming of controls, right?

So, adding the hidden control in the subform will "fix" the problem as long as you move the code to the subform's BeforeUpdate event so it will update changed records as well as new records. Keep in mind that there is no validation of any kind on either form. Therefore, there is no requirement for the user to choose his initials. There is no requirement in the subform to enter a value in SSOW. There is no validation in the subform to ensure that a parent record has been added before the user attempts to add a child record. The ProjIdxID is not defined as required on the tbl_SOW so it is possible to add orphan records, and the list goes on.

However, rather than relying on the user to remember to change the initials, it would be far better for you to automatically populate using UserID (if it is unique) or SecurityID based on information gathered from the log in.

In the vast majority of tables in my applications, I include three fields
CreateDT
UpdateDT
UpdateBy

The CreateDT is set to default to Now(). The other two fields are populated in each form's BeforeUpdate event as the last two lines of code in the procedure. I hide the log in form rather than closing it so I can refer to it throughout the application. It also allows me to run code when the application shuts down since the hidden login form will be the last form to close. This technique is by no means a change log. That is a different animal. This is just a simple way to identify when a record was last changed and who changed it.

Me.UpdateDT = Now()
Me.UpdateBy = Forms!frmLogin!txtUserID
 
Last edited:

RonieB

New member
Local time
Today, 07:52
Joined
Jun 1, 2022
Messages
22
Thank you for the time and effort of explaining this and I understand greatly now. I had added the est initials to the subform but that didn't work for me but now I understand why. The subform will have many records added for the main number. The same est will do that and have been conditioned to do this. But Isee your point and will adjust.
Again, thank you so much for the assist.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:52
Joined
Feb 19, 2002
Messages
42,970
You're welcome:) Getting the code into the correct event is half the battle. Understanding the form's BeforeUpdate event and its purpose is critical to controlling forms.
 

Users who are viewing this thread

Top Bottom