pulling data from a separate table

[Guest]

I have two tables, one for parents and one for children. Each table has a
field for the surname. I am trying to store two surnames in the event they
are different, one for parents and one for the children. There is a one to
many relationship linked by a phone number field. As new children are
entered I would like to have the parents surname prefilled so, during data
entry, the surname doesn't have to be reentered in the event it is the same.

I've seen similar postings for saving calculations with the suggestion to
simply use the calculation and not store the data. The only way I can think
of to take this approach is to have some kind of statement that checks the
data in the surname field from the children's table and display the surname
from the parent's table in the event there is no surname entered in the
children's table otherwise display the data from the children's table. I
haven't the faintest clue as to how to do that.

Any suggestions?

 

[Troy]

In the Form Current Event of the Subform, add some code that fills in the
value automatically.

Private Sub Form_Current()
If Me.NewRecord Then
txtSurName = Me.Parent.Surname
End If
End Sub

Remember that this code is in the subform and not the main form.
--
Troy

Troy Munford
Development Operations Manager
FMS, Inc.
www.fmsinc.com


I have two tables, one for parents and one for children. Each table has a
field for the surname. I am trying to store two surnames in the event they
are different, one for parents and one for the children. There is a one to
many relationship linked by a phone number field. As new children are
entered I would like to have the parents surname prefilled so, during data
entry, the surname doesn't have to be reentered in the event it is the same.

I've seen similar postings for saving calculations with the suggestion to
simply use the calculation and not store the data. The only way I can think
of to take this approach is to have some kind of statement that checks the
data in the surname field from the children's table and display the surname
from the parent's table in the event there is no surname entered in the
children's table otherwise display the data from the children's table. I
haven't the faintest clue as to how to do that.

Any suggestions?

 

[Guest]

I'm having trouble with the syntax. Could you please use the following table
and field names:

familydata has the field fsname
swimmers has the field slname

 

[Troy]

You're referring to table field names, but the code is referring to Form
control names. Hopefully, you've followed good programming and database
techniques and have named your controls something different from the Field
name it is bound to.

Thus, your text box on the parent form would be named "txtfsname" and the
text box on the subform would be named "txtslname".

When the code refers to Me.Parent, it is not referring the "table of data
containing parent names". It is referring to the parent form that the
subform is located on. Remember that every control on a form has a parent of
the form itself. Thus, a subform control (and the subform contained therein)
will also have a parent value of the main form.

So, using your naming conventions, the Subform would have this code:

Private Sub Form_Current()
If Me.NewRecord Then
txtslname = Me.Parent.txtfsname
End If
End Sub

HTH
--
Troy

Troy Munford
Development Operations Manager
FMS, Inc.
www.fmsinc.com


I'm having trouble with the syntax. Could you please use the following
table
and field names:

familydata has the field fsname
swimmers has the field slname

 

[Guest]

Troy

I'm sorry I didn't respond right away, things got backed up and I had to
put this aside. I tried the coding that you suggested but had trouble with a
run time error. In my frustration I tried something else that works out
great. I put a text box in the form, labeled it holdname, set the control
source as the surname from the first table and set "visible" to no in the
format options. Then I set the default value of the the lastname field on
the second table to the value in the hold field.
Your coding probably does the job more efficiently but the runtime error
left me out to sea.
Thanks anyway