Autofilled field after chose value in another combobox

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

re: Autofilled field after chose value in another combobox

"AA Arens" <bartvandongen@gmail.com> wrote in
news:1141568295.449582.107870@i40g2000cwc.googlegr oups.com:
[color=blue]
> 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?
>[/color]
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.

re: Autofilled field after chose value in another combobox

Bob Quintal wrote:[color=blue][color=green]
>>[/color]
> 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
>[/color]

A minor quibble, Bob.
[color=blue]
>IF len(me.Contactphone) & "" = 0 Then[/color]

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.

re: Autofilled field after chose value in another combobox

Randy Harris <please@send.no.spam> wrote in
news:IzFOf.37253$Jd.37070@newssvr25.news.prodigy.n et:
[color=blue]
> Bob Quintal wrote:[color=green][color=darkred]
>>>[/color]
>> 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
>>[/color]
>
> A minor quibble, Bob.
>[color=green]
> >IF len(me.Contactphone) & "" = 0 Then[/color]
>
> I think that the concatenation should be inside the
> parentheses.
>
> IF len(me.Contactphone & "") = 0 Then
>[/color]

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.

re: Autofilled field after chose value in another combobox

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

re: Autofilled field after chose value in another combobox

"AA Arens" <bartvandongen@gmail.com> wrote in
news:1142090878.976094.131120@z34g2000cwc.googlegr oups.com:
[color=blue]
> 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
>[/color]
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.

re: Autofilled field after chose value in another combobox

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.

re: Autofilled field after chose value in another combobox

"AA Arens" <bartvandongen@gmail.com> wrote in
news:1142156169.551995.147090@i39g2000cwa.googlegr oups.com:
[color=blue]
> 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.
>[/color]
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.

re: Autofilled field after chose value in another combobox

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

re: Autofilled field after chose value in another combobox

"AA Arens" <bartvandongen@gmail.com> wrote in
news:1142177961.305290.204940@j33g2000cwa.googlegr oups.com:
[color=blue]
> 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
>[/color]
leave the combo bound to table 2, not table 1



--
Bob Quintal

PA is y I've altered my email address.

re: Autofilled field after chose value in another combobox

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

Bob Quintal wrote:[color=blue]
> "AA Arens" <bartvandongen@gmail.com> wrote in
> news:1142177961.305290.204940@j33g2000cwa.googlegr oups.com:
>[color=green]
> > 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
> >[/color]
> leave the combo bound to table 2, not table 1
>
>
>
> --
> Bob Quintal
>
> PA is y I've altered my email address.[/color]