Autofilled field after chose value in another combobox

[AA Arens]

I have two tables, one consists of company info like name and phone
number.
Another table where I have to fill in the contact persons. Part of the
form is to choose the company he works for (From a list box). Then,
after I choosed the conpany, I want to have auto filled in the field
phone number from the 1st table. But, and that's why i want to have it
archieved in the table (so no redundancy): I want that the field
phonenumber can be editted as the company phone number is not always
the direct number from the contact person.

The method should be like:
Use a ComboBox for entering the Company and include a hidden column
that has the
phone number. In the AfterUpdate event of the ComboBox have code...
Me.ContactPhone = Me.Company.Column(n)
....where 'n' is the zero based position of the hidden phone number
column in the
ComboBox.

Now, I am halfway in that procedure.

My question:
How about the setting in the ContactPhone field.
Must it be like
Me.ContactPhone = Len(me!ContactPhone)
in GotFocus or BeforeUpdate?
Bart

[Bob Quintal]

"AA Arens" <ba***********@gmail.com> wrote in
news:11**********************@i40g2000cwc.googlegr oups.com:

I have two tables, one consists of company info like name and
phone number.
Another table where I have to fill in the contact persons.
Part of the form is to choose the company he works for (From a
list box). Then, after I choosed the conpany, I want to have
auto filled in the field phone number from the 1st table. But,
and that's why i want to have it archieved in the table (so no
redundancy): I want that the field phonenumber can be editted
as the company phone number is not always the direct number
from the contact person.

The method should be like:
Use a ComboBox for entering the Company and include a hidden
column that has the
phone number. In the AfterUpdate event of the ComboBox have
code... Me.ContactPhone = Me.Company.Column(n)
...where 'n' is the zero based position of the hidden phone
number column in the
ComboBox.

Now, I am halfway in that procedure.

My question:
How about the setting in the ContactPhone field.
Must it be like
Me.ContactPhone = Len(me!ContactPhone)
in GotFocus or BeforeUpdate?

You don't need anything in the ContactPhone if you wish to allow
it to be changed.

If you wish to prevent changing it when the company changes, and
I cannot see why you would need to do that, just put a condition
on your update in the combobox After Update event.

Company_AfterUpdate
IF len(me.Contactphone) & "" = 0 Then
Me.ContactPhone = Me.Company.Column(n)
ENDIF

--
Bob Quintal

PA is y I've altered my email address.

[Randy Harris]

Bob Quintal wrote:

You don't need anything in the ContactPhone if you wish to allow
it to be changed.

If you wish to prevent changing it when the company changes, and
I cannot see why you would need to do that, just put a condition
on your update in the combobox After Update event.

Company_AfterUpdate
IF len(me.Contactphone) & "" = 0 Then
Me.ContactPhone = Me.Company.Column(n)
ENDIF

A minor quibble, Bob.
IF len(me.Contactphone) & "" = 0 Then

I think that the concatenation should be inside the parentheses.

IF len(me.Contactphone & "") = 0 Then

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

[Bob Quintal]

Randy Harris <pl****@send.no.spam> wrote in
news:Iz******************@newssvr25.news.prodigy.n et:

Bob Quintal wrote:

You don't need anything in the ContactPhone if you wish to
allow it to be changed.

If you wish to prevent changing it when the company changes,
and I cannot see why you would need to do that, just put a
condition on your update in the combobox After Update event.

Company_AfterUpdate
IF len(me.Contactphone) & "" = 0 Then
Me.ContactPhone = Me.Company.Column(n)
ENDIF

A minor quibble, Bob.

IF len(me.Contactphone) & "" = 0 Then

I think that the concatenation should be inside the
parentheses.

IF len(me.Contactphone & "") = 0 Then

I know that the concatenation should be inside the parentheses,
from debugging similar errors I keep making.

--
Bob Quintal

PA is y I've altered my email address.

[AA Arens]

It works. I even can freely fill in the phone number if it differs from
the comopany number.
The only problem that occurs is that the comopany ID is now also added
to the comoany table. That;s not what i want.

How come it occurs?

Bart

[Bob Quintal]

"AA Arens" <ba***********@gmail.com> wrote in
news:11**********************@z34g2000cwc.googlegr oups.com:

It works. I even can freely fill in the phone number if it
differs from the comopany number.
The only problem that occurs is that the comopany ID is now
also added to the comoany table. That;s not what i want.

How come it occurs?

Bart

Do you mean that the company ID is added again to the company
table? This is strange, because company ID should be primary key to
the company table and that would prevent duplicates.

Or do you mean it's putting the company ID in the company phone
field?

Not enough information to diagnose the problem..

--
Bob Quintal

PA is y I've altered my email address.

[AA Arens]

Sorry, my question was not built perfectly.

I do have two tables:

Table 1 = Form 1: Company - co. related fields
Table 2 = Form 2: Customer- contact related fields.

My first message (all above) was all about the 2nd table/form. Now,
the problem I mentioned in my first message is solved.

This is my working solution of the first problem:

Private Sub Combo78_AfterUpdate()
Me.WorkPhone = Me.Combo78.Column(2)
If Len(Me.WorkPhone & "") = 0 Then
Me.WorkPhone = Me.Combo78.Column(2)
End If
Me.FaxNumber = Me.Combo78.Column(3)
If Len(Me.FaxNumber & "") = 0 Then
Me.FaxNumber = Me.Combo78.Column(3)
End If

Control Source combo box: CompanyName

And this is the Row source of the combo box:
SELECT [tblClients].[CustomerID], [tblClients].[CompanyName],
[tblClients].[PhoneNumber], [tblClients].[FaxNumber] FROM [tblClients]
ORDER BY [CompanyName];

But:
The contact ID, that is the number of the customer of table 2/form 2,
is added in the companyname column of the 1st table.
How?

I can't see why there is an order to add a record in the 1st table from
the 2nd form.

[Bob Quintal]

"AA Arens" <ba***********@gmail.com> wrote in
news:11**********************@i39g2000cwa.googlegr oups.com:

Sorry, my question was not built perfectly.

I do have two tables:

Table 1 = Form 1: Company - co. related fields
Table 2 = Form 2: Customer- contact related fields.

My first message (all above) was all about the 2nd
table/form. Now, the problem I mentioned in my first message
is solved.

This is my working solution of the first problem:

Private Sub Combo78_AfterUpdate()
Me.WorkPhone = Me.Combo78.Column(2)
If Len(Me.WorkPhone & "") = 0 Then
Me.WorkPhone = Me.Combo78.Column(2)
End If
Me.FaxNumber = Me.Combo78.Column(3)
If Len(Me.FaxNumber & "") = 0 Then
Me.FaxNumber = Me.Combo78.Column(3)
End If

Control Source combo box: CompanyName

And this is the Row source of the combo box:
SELECT [tblClients].[CustomerID], [tblClients].[CompanyName],
[tblClients].[PhoneNumber], [tblClients].[FaxNumber] FROM
[tblClients] ORDER BY [CompanyName];

But:
The contact ID, that is the number of the customer of table
2/form 2, is added in the companyname column of the 1st table.
How?

I can't see why there is an order to add a record in the 1st
table from the 2nd form.

just guessing here, but might your combobox be bound? to the
field in table1?

--
Bob Quintal

PA is y I've altered my email address.

[AA Arens]

Rob,

When I set up the combo box I choosed the data from Table 1 (field:
company name) as this tables included the company name, just to have
the combo list filled with this data.
What should I do? Customer ID?

Bart

[Bob Quintal]

"AA Arens" <ba***********@gmail.com> wrote in
news:11**********************@j33g2000cwa.googlegr oups.com:

Rob,

When I set up the combo box I choosed the data from Table 1
(field: company name) as this tables included the company
name, just to have the combo list filled with this data.
What should I do? Customer ID?

Bart

leave the combo bound to table 2, not table 1

--
Bob Quintal

PA is y I've altered my email address.

[AA Arens]

Thanks, Rob. That was the problem. I let the CustomerID data fill in in
the customerID column of table 2

Bob Quintal wrote:

"AA Arens" <ba***********@gmail.com> wrote in
news:11**********************@j33g2000cwa.googlegr oups.com:

Rob,

When I set up the combo box I choosed the data from Table 1
(field: company name) as this tables included the company
name, just to have the combo list filled with this data.
What should I do? Customer ID?

Bart

leave the combo bound to table 2, not table 1

--
Bob Quintal

PA is y I've altered my email address.