Updating Field 2 Based On Field 1

[lccrews]

Hi and thanks for any help. I am using a subform datasheet for users to update Table1. Field1 is completed first. I'd like to run an update query or SQL statement in VBA to update Field2 based on what's in Field1. I've tried using the After Update Event of Field1 on the subform but to my knowledge, a record must be committed to the database before such events can be fired. So it only works half the time.
Is there a way to get this working differently?

Additional Info:
tbl_Formulas and tbl_RawMaterials are related by field [RawMaterial]. The subform datasheet the users are entering data onto is drawn from tbl_Formulas. Whenever a user enters a new [RawMaterial], I'd like the [MiscInfo] that exists on tbl_RawMaterials to automatically transfer to [MiscInfo] on tbl_Formulas.

tbl_RawMaterial
[RawMaterial] [MiscInfo]
123 - Sugar | Domino



tbl_Formulas
[RawMaterial] [MiscInfo]
123 - Sugar | (Automatically inserted)

[Micron]

a record must be committed to the database before such events can be fired

Most data controls have an After Update event, which should fire after every update (NB - I seem to recall that vba updates on a control don't fire this event) so it shouldn't just work some of the time unless maybe you're updating via vba. What you are likely thinking of is the After Update event for a form. Another constraint is that field2 cannot use an expression to accomplish this, and still be bound, so not telling us what's happening when it doesn't work isn't helping to diagnose.

As for part 2, you need to explain if your form controls are bound or not, and are you OK with doing this via code, because while DLookup can be used to get a value based on another, again, you can't bind that control to anything. The 'issue' seems to be the same as part 1 question - how to update a field based on another.

[lccrews]

Hi Micron,
Thanks for the fast response. I was referring to the After Update Event on the subform, not the main form. I have tried this before where I use VBA in the After Update Event to check the entered value against another. I talked about it in another forum post and June7 helped me figure out why it was only working occasionally and not all the time. According to her, records must be committed to the database before being called upon in VBA.

I'm okay with code, I'm a beginner, but I'm catching on quickly. Neither the subform nor main form are bound to anything.

Below is the VBA I wrote for another subform After Update Event. The problem is that it only works sometimes. I believe it has to do with when Access saves your work because as I said, June7 mentioned the record needing to be committed first before code would work. Can you verify that?

Private Sub Weight_AfterUpdate()

DoCmd.Save


If DLookup("SumOfWeight", "qry_BP40_BlendingCheck_Pt1") > DLookup("Quantity", "qry_BP40_BlendingCheck_Pt2") Then
Output = MsgBox("Mix exceeds quantity from formula.", vbCritical, "Error") = vbAbort
Me.Weight.Value = "0"


End If


End Sub

[June7]

Why are you duplicating data between tables? Why is MiscInfo in both tables?

You want to create new 'lookup' record on-the-fly during data entry? Use combobox NotInList event.

DoCmd.Save does not save record, it saves form.

DoCmd.RunCommand acCmdSaveRecord

If the form AfterUpdate event is triggered then the record has been saved and code to save is not needed. But if you put code to save in a control's AfterUpdate, that should work.

Record is committed to table when: 1. close table/query/form; or 2. move to another record; or 3. run code to save

[lccrews]

June I have no idea how you do it but every time we converse I have an epiphany....

I don't need this fix. I have a relationship between RawMaterial on tbl_RawMaterial and tbl_Formulas. I can just call the two tables in a query and get the information displayed that way.

Another question is raised though. While I can delete [MiscInfo] from tbl_Formulas, tbl_RawMaterial isn't 'complete' some of the Misc Info isn't filled in yet and I rely on recipe formulators to fill in that data. So rather than writing the data in a redundant fashion, I should be trying to figure out how to fill in [MiscInfo] with what the user inputs.

[June7]

Don't understand. If textbox is bound to MiscInfo then whatever is typed into textbox passes into field MiscInfo.

What the heck is a 'recipe formulator' - a person doing data entry?

[lccrews]

Yes, a person doing data entry. Because there are two tables providing information I had to use a tmp table "tmp_Formula" to make changes to formulas and write to ones. Data from tmp_Formula is then transferred to respective fields on tbl_RawMaterial and tbl_Formulas. There is no text box. This is a subform datasheet. Whatever is typed into it is typed onto tmp_Formula. I have already figured out how to fix this. I'm just going to write an SQL in VBA to update tbl_RawMaterial.[MiscInfo] = tmp_Formula.[MiscInfo]. I think that will work. Thanks for the epiphany!

[Micron]

My whole answer was predicated on updating controls (fields) not forms. You only mentioned fields, so nothing I wrote applies to what you seem to really be doing. Seems you are OK now anyway.