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!

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.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions



"Sara" <(E-Mail Removed)> wrote in message
news:B244298F-FA4F-4318-9214-(E-Mail Removed)...
> 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!
>
>

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
ara" wrote:

> 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!
>
>

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.

"Al Camp" wrote:

> 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.
> --
> hth
> Al Camp
> Candia Computer Consulting - Candia NH
> http://home.comcast.net/~cccsolutions
>
>
>
> "Sara" <(E-Mail Removed)> wrote in message
> news:B244298F-FA4F-4318-9214-(E-Mail Removed)...
> > 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!
> >
> >
>
>
>

Being that I'm somewhat of a newbie to Access....how do I create a subform?

"Klatuu" wrote:

> 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.
>
> "Al Camp" wrote:
>
> > 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.
> > --
> > hth
> > Al Camp
> > Candia Computer Consulting - Candia NH
> > http://home.comcast.net/~cccsolutions
> >
> >
> >
> > "Sara" <(E-Mail Removed)> wrote in message
> > news:B244298F-FA4F-4318-9214-(E-Mail Removed)...
> > > 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!
> > >
> > >
> >
> >
> >

Ok so I see how to create a subform. Is there a way to make NewEmployee form
on one tab and the NewDependent form on another tab and you can just switch
from one to the next by clicking on a tab?

"Sara" wrote:

> Being that I'm somewhat of a newbie to Access....how do I create a subform?
>
> "Klatuu" wrote:
>
> > 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.
> >
> > "Al Camp" wrote:
> >
> > > 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.
> > > --
> > > hth
> > > Al Camp
> > > Candia Computer Consulting - Candia NH
> > > http://home.comcast.net/~cccsolutions
> > >
> > >
> > >
> > > "Sara" <(E-Mail Removed)> wrote in message
> > > news:B244298F-FA4F-4318-9214-(E-Mail Removed)...
> > > > 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!
> > > >
> > > >
> > >
> > >
> > >

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?

"Sara" wrote:

> Ok so I see how to create a subform. Is there a way to make NewEmployee form
> on one tab and the NewDependent form on another tab and you can just switch
> from one to the next by clicking on a tab?
>
> "Sara" wrote:
>
> > Being that I'm somewhat of a newbie to Access....how do I create a subform?
> >
> > "Klatuu" wrote:
> >
> > > 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.
> > >
> > > "Al Camp" wrote:
> > >
> > > > 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.
> > > > --
> > > > hth
> > > > Al Camp
> > > > Candia Computer Consulting - Candia NH
> > > > http://home.comcast.net/~cccsolutions
> > > >
> > > >
> > > >
> > > > "Sara" <(E-Mail Removed)> wrote in message
> > > > news:B244298F-FA4F-4318-9214-(E-Mail Removed)...
> > > > > 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!
> > > > >
> > > > >
> > > >
> > > >
> > > >

Nevermind....got it figured out. Man I'm on a roll today.

"Sara" wrote:

> 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?
>
> "Sara" wrote:
>
> > Ok so I see how to create a subform. Is there a way to make NewEmployee form
> > on one tab and the NewDependent form on another tab and you can just switch
> > from one to the next by clicking on a tab?
> >
> > "Sara" wrote:
> >
> > > Being that I'm somewhat of a newbie to Access....how do I create a subform?
> > >
> > > "Klatuu" wrote:
> > >
> > > > 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.
> > > >
> > > > "Al Camp" wrote:
> > > >
> > > > > 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.
> > > > > --
> > > > > hth
> > > > > Al Camp
> > > > > Candia Computer Consulting - Candia NH
> > > > > http://home.comcast.net/~cccsolutions
> > > > >
> > > > >
> > > > >
> > > > > "Sara" <(E-Mail Removed)> wrote in message
> > > > > news:B244298F-FA4F-4318-9214-(E-Mail Removed)...
> > > > > > 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!
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >

This worked great for me, Klatuu!

Thanks!!