I created a form that displays fields from multiple tables. The thing common in each table is an EmployeeID field. The EMPLOYEES table auto generates a value for the EmployeeID field and on the form I need to copy that value into two other textfields on the form when it is generated.



So, if you follow me, the EMPLOYEE_STATS table has an EMPLOYEE_ID field and so does the LOCATION table have an EMPLOYEE_ID field. The relationship is 1 to 1 from table EMPLOYEES to both EMPLOYEE_STATS and LOCATION tables.

How do I copy the EMPLOYEE_ID to the same two labelled textfields on the form? I'm assuming I must use VBA to do this but require instruction. I tried noodling around with it, but came across a very frustratiing restriction in VBA. It seems to have a rule that you can't reference other objects on the form because they currently don't have the focus. How crummy is that? There must be a way around that!

So then I decided to create a Module with the following subroutine in it:

Public Sub COPY_EMPLOYEE_ID(ByRef field As TextBox)
Dim id As String
id = Form_EMPLOYEES.EMPLOYEES_EMPLOYEE_ID.Text
field.Text = id
End Sub

And from the EMPLOYEE_STATS_EMPLOYEE_ID_GotFocus() event method I wrote:

Private Sub EMPLOYEE_STATS_EMPLOYEE_ID_GotFocus()
Call Module1.COPY_EMPLOYEE_ID (Form_EMPLOYEES.EMPLOYEE_STATS_EMPLOYEE_ID)
End Sub

Once again I'm foiled by Access. I keep getting an error message from the one line in the GotFocus method stating: Runtime Error 424 Object Required. Well hell! I can't be more specific than that! Or, I get the message from the COPY_EMPLOYEE_ID method stating, once again, that you can't reference an object unless it has the focus. I hate that!

Please advise,

Alan