Getting Data from Main Form

[Ajay]

Using Access 2000:
I need to add Client firms and their Salesmen to Clients and Staff tables.
I created form frmAddClient, where I add the information and two phone
numbers for the Client firm.
In the AddStaff subform, where I add the information for Sales staff, I
need the Client firm's phone numbers appear as default, so that I can
change them only where appropriate.

Can someone help this newbie with how the Control Source property should
be written?
Thanks a bunch.
Ajay.

 

[Steve Schapel]

Ajay,

I don't think the Control Source is applicable here.

There are a couple of approaches you could take here.

How I would probably do it, is to enter the Staff phone number *only if*
it is different from the Client phone number, otherwise leave it blank.

In this case, whenever you need a phone number for a staff member, for
example in your reports, you can use something like this in the query:
Nz([Staff].[Phone],[Clients].[Phone])

Alternatively, you can use code to assign the Client phone number to the
Staff phone number field. This would be my second choice. Even then, I
would not do it the way you suggested, where the Client number is
entered for the Staff, and then you have to edit it as required. Once
again, leave the Staff phone number blank if it is the same as the
Client number, and then, on the Before Update event of the subform, code
something like this:
If IsNull(Me.Phone) Then
Me.Phone = Me.Parent.Phone
End If

 

[Ajay]

Many thanks for your prompt suggestion, Steve.
I would have preferred to have the phone numbers appear as I originally
stated, yet I would accept your suggestion.
I am afraid I may not be coding it the correct way and may be you can
spot my error/s.
I opened the AddStaff subform in design view. Right-clicked on the input
boxes to select Build event... and added the following VB code:

Private Sub Phone_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Phone) Then
Me.Phone = Me.Parent.MainPhone
End If
End Sub

Private Sub AltPhone_BeforeUpdate(Cancel As Integer)
If IsNull(Me.AltPhone) Then
Me.AltPhone = Me.Parent.WorkPhone
End If
End Sub

The phone fields in Staff table remain blank before, during and after
entering the info into other Client table as well as other Staff table
fields.
Thanks again.
Ajay

 

[Steve Schapel]

Ajay,

I am not sure what the problem here could be.

However, please note that this idea will only work if:
- AddStaff form is a subform on the form where the Client data is current.
- The data is being entered via this subform. Entering via the subform
if opened stand-alone, or entering directly to the table, will not work.
- This code needs to be on the Before Update event of the AddStaff
*form* itself, not the Before Update event of the textboxes on the form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Phone) Then
Me.Phone = Me.Parent.MainPhone
End If
If IsNull(Me.AltPhone) Then
Me.AltPhone = Me.Parent.WorkPhone
End If
End Sub