setting value of combo box

[Bob Wickham]

I have tried many suggestions on how to get this to work but so far no
success.
I have a form with just one table as its record source.
I have two combo boxes
Combo1 is Client_ID. Its row source is SELECT tblClient.ClientID,
tblClient.Client FROM tblClient;
Combo2 is Plan_Writer_ID. Its row source is SELECT
tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer FROM tblPlanWriter;
Both these boxes display text but am I right in assuming they expect a
number.

I have a text box Text1, that has a DLookup as its control source:
=DLookUp("MaxOfPlan_Writer_ID","qryMost_Recent_Planwriter_3","[Client]=Form![Client]")
The purpose of this DLookup is to find the most recent plan writer that did
work for the client selected in Combo1.
Works great, returns a number, being the ID number of the appropriate plan
writer.

Now the challenge.
When creating a new record with this form, first I select a Client in
Combo1.
Text1, with the DLookup, displays a number being the ID of the Plan writer
who last did work for the selected client.

How do I get Combo2 to automaticaly display the same as Text1.

Its imperative that Combo2 remain linked so that I may choose another Plan
writer if I wish.

If I hard code a number 2 into the Default Value of Combo2 its displays a
Plan writers name, so I'm sure its expecting a number.
But if Text1 displays a number 2 and I put something like Me.Combo2 =
Me.Text1 in the After Update event of Text1, I cant get Combo2 to display
anything.

This has been frustrating me for weeks and I really need some assistance.

Thanks

Bob

 

[UpRider]

In the after_update event of Combo1, set the value of Combo2 to the proper
value of its bound column.
Something like
Combo2.column(x) =
DLookUp("MaxOfPlan_Writer_ID","qryMost_Recent_Planwriter_3","[Client]=Form![Client]")

If the bound column is the writer_Name, change the Lookup's first field to
find the name. You may have to add that field to the query if it's not
already included.

HTH, UpRider

 

[Wolfgang Kais]

Hello Bob.

I have tried many suggestions on how to get this to work but so far
no success.
I have a form with just one table as its record source.
I have two combo boxes
Combo1 is Client_ID. Its row source is
SELECT tblClient.ClientID, tblClient.Client FROM tblClient;
Combo2 is Plan_Writer_ID. Its row source is
SELECT tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer
FROM tblPlanWriter;
Both these boxes display text but am I right in assuming they
expect a number.

I have a text box Text1, that has a DLookup as its control source:
=DLookUp("MaxOfPlan_Writer_ID","qryMost_Recent_Planwriter_3",
"[Client]=Form![Client]")
The purpose of this DLookup is to find the most recent plan writer
that did work for the client selected in Combo1.
Works great, returns a number, being the ID number of the
appropriate plan writer.

Now the challenge.
When creating a new record with this form, first I select a Client
in Combo1.
Text1, with the DLookup, displays a number being the ID of the Plan
writer who last did work for the selected client.

How do I get Combo2 to automaticaly display the same as Text1.

Its imperative that Combo2 remain linked so that I may choose
another Plan writer if I wish.

If I hard code a number 2 into the Default Value of Combo2 its
displays a Plan writers name, so I'm sure its expecting a number.
But if Text1 displays a number 2 and I put something like
Me.Combo2 = Me.Text1 in the After Update event of Text1, I cant
get Combo2 to display anything.

This has been frustrating me for weeks and I really need some
assistance.

First: Have you tried to use the DMax function instead of DLookup?
Also use the ClientID as criterion:
=DMax("Plan_Writer_ID","tblPlanWriter","Client_ID="&Form!Client_ID")

The problem is that when the new value for Text1 is calculated,
no event is fired. Therefore, you should write the value for Combo2
in the AfterUpdate event of Combo1. Use the same formula as in
Text1. Or, as I would suggest, change both values of Text1 and
Combo2 in the event procedure of Combo1. You can use a variable
to store the value of the DLookup or DMax function and use it twice,
so the value doesn't have to be calculated twice. Be sure to lock
Text1 (data properties) so the user can't change the value hinself.

 

[Bob]

Thankyou both for your replies.
I've been busy at work so couldn't get back to you sooner.
I'll try your suggestions but they look very similar to other things I
have tried.
Thanks.

Bob