Look up a field on a subform

[andrewf10]

OK, this is the third and last major complaint my users have with my database.

I’ve got a master form (frmMaterial_Master), which sits on tblMaterial_Master. The only field on this table is txtMaterial.

I then have 17 other main forms, of which frmMaterial_Master is a sub-form of them all. The thinking is that the user ‘reserves’ a number by filling in txtMaterial on frmMaterial_Master at the top and then enters this number again just below so that this value goes on whichever of the other 17 main forms/tables they’re using.

Question is…is there some piece of VBA code I can add which will take the active value of txtMaterial in frmMaterial_Master and pull it into the corresponding field on the underlying form. At the moment, users are not always retyping the value correctly and this is causing chaos.

All forms are modal – only 1 form can be active on a PC at any one time.

Can someone please help and save this mans sanity?
Thanks

 

[WayneRyan]

Andrew,

Just set the default value for the field in
the subform to:

=Forms![YourMainForm]![YourField]

If you wanted, you can make it invisible so
that they don't mess with it. They already
see it on the top form.

Wayne

 

[andrewf10]

Wayne,

Had a crack at this but had no joy. I think it could be the fact that the subform must feed into all the main form and not the other way around which would be normal!

After a bit of messing around with PaddyIrishMans Unload code he sent me the other day, I got it to work.

Basically when the Material field on the subform is updated, it will populate the Material field on whichever open form contains the subform.



Private Sub Material_AfterUpdate()

'Local declarations
Dim myForm As Form 'Variable to use in Form Collection Loop
Dim strMaterial As String 'Variable to hold value of Material

On Error GoTo Err_UnloadValue_Click


'Set the focus & get the value from the Material field
Material.SetFocus
strMaterial = Material.Text

'Loop thru all of the currently open forms
For Each myForm In Forms
'If the Form has a different name than me then set the value of Material
If myForm.Name <> Me.Name Then
myForm.Material.SetFocus
myForm.Material.Text = strMaterial
End If
Next


Exit_UnloadValue_Click:
Exit Sub

Err_UnloadValue_Click:
MsgBox Err.Description
Resume Exit_UnloadValue_Click

End Sub

 

[andrewf10]

Pat, I've put your code on the Form_Current() event of the form (one of the 17 main I mention above) but am getting this message:

"The expresion you entered has an invalid reference to the Parent property"

Where am I going wrong this time?