Open form and fill in a txt box

G

Guest

have a form that I created that is to add a new employee to our database.
We deal with insurance, so I want to be able to add a dependant to that
person's record. I have a form for new employee and a form for new dependent.
Once a person has filled in the new employee information, they should click
on a button that says add dependent. When the add dependent form opens, I
want the parent information to be automatically filled in.

For example, I have 2 tables: EmployeeInfo and DependentInfo. The
DependentInfo has a field called ParentSSN which is linked to the SSN
field in EmployeeInfo. When you add a new employee, you have to fill in SSN
in a txt box, and when you go to the add dependent form, I want the field
ParentSSN (its a txt box linked to the DpendentInfo table) to
be automatically filled in with the SSN from the new employee screen.

Can anyone help me with this?

Thank you!
 
A

Al Camp

Sara,
It would be much easier to make your Dependent form a subform of your
Employee form, not a separate form.
If the 2 tables are linked One to Many via the SSNs (an Employee can have
more than one dependent), and the Main Employee form is linked to the
Dependent subform (Parent/Child) via ParentSSN to DependentSSN, any
dependent records added to the dependent subform will automatically have the
ParentSSN filled in with the assocaited EmployeeSSN.
That's all handled by the table relationships, and the Parent/Child link
from the Main to the Subform.
 
G

Guest

One way to do this would be to use the OpenArgs argument of the OpenForm
method to pass the SSN to the dependant form. Another would be to have the
dependant form refer to the Employee form for the information. The second
way will only work if the Employee form stays open while you are in the
dependant form.

My suggestion would be to use the OpenArgs way. Now, to make it
automatically fill the SSN field for the dependant table would be to use the
Default Value property of the SSN control on the dependant form. The trick
is, the Default Value can't use a variable, but it can use a formula. What I
would do is create a Static Function to hold the SSN. I would load the SSN
value from the OpenArgs in the form Load event, and set the Default Value
property to that function.

Here is a function that will do it:

Static Function GetSSN(Optional ByVal varNewSSN As Variant) As String
Dim varSSN As Variant

If Not IsMissing(varNewSSN) Then
varSSN = varNewSSN
End If
GetSSN = varSSN
End Function

When you open the dependant form:

DoCmd.OpenForm "frmDependant", , , , , , Me.txtSSN


In the Load event of your dependant form:

If Not IsNull(Me.OpenArgs) Then
GetSSn(Me.OpenArgs)
End If

The for the default value property of the SSN control on the dependant form

GetSSN()

"S
 
G

Guest

Al's suggestion is really the best way to do it. My suggestion was
predicated on you having already set up your forms to behave like you want or
that you prefer separate forms.
 
G

Guest

Ok I figured that out too, but how do you make one tab dependent on one
table, and the other tab dependent on another table?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top