Default value of combo box based on another that uses DLookUp

[Bob Wickham]

Hi,
I have a problem I'm trying to overcome regarding just one form which gets
its data from 1 table.

I'm trying to set the default value of a combo box to be the same as another
combo box on the same form.
The scenario is:
I have a form for entering info about financial planning Statements Of
Advice.
Each client has a Plan Writer prepare their plan.
To find the plan writer that last prepared the plan for a client I have a
combo box with a DLookUp as its Control Source.
=DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=Form![Client]")

I'm using a query so as to find the most recent record for a client.

Works wonderfully! but this combo can only be used for display.

Now the part thats causing me grief.

I want the value returned by that DLookUp to be the default value for
another combo box with the Row Source of
SELECT tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer FROM
tblPlanWriter;

The second combo must remain editable so that another plan writer can be
chosen if necessary

I've played around with all sorts of things, using the Expression Builder,
borrowing bits of code from all over the net, Before Update, After Update.

I cant find anything on the internet the same as what I'm attempting, which
makes me wonder if what I want is even possible.

Thankyou

Bob

 

[Ken Snell \(MVP\)]

You need to set the Value of that second combo box to the desired "default",
and not use an expression in its ControlSource property.

Assuming that you want this to be done after the user selects the value in
the first combo box (what is its name?), you can use the AfterUpdate event
of that first combo box to do this (the code example assumes that the Value
of the first combo box is a numeric value, not a text string value):

Private Sub SecondComboBoxName_AfterUpdate()
Me.SecondComboBoxName.Value = _
DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=" & _
Me.FirstCombBoxName.Value)
End Sub

 

[Bob Wickham]

The user doesn't select the value in the first combo(cboDefault_Planwriter).
It is calculated by a DLookUp that finds the most recent Plan writer for the
client that a user does select in another combo box (cboClient)
The second combo (cboPlanwriter) is bound to a table so that the user can
choose another planwriter if the one returned by cboDefault_Planwriter is
not suitable

I'll try removing its control source and post back after work.

Thanks

Bob

You need to set the Value of that second combo box to the desired
"default", and not use an expression in its ControlSource property.

Assuming that you want this to be done after the user selects the value in
the first combo box (what is its name?), you can use the AfterUpdate event
of that first combo box to do this (the code example assumes that the
Value of the first combo box is a numeric value, not a text string value):

Private Sub SecondComboBoxName_AfterUpdate()
Me.SecondComboBoxName.Value = _
DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=" & _
Me.FirstCombBoxName.Value)
End Sub

--

Ken Snell
<MS ACCESS MVP>

Hi,
I have a problem I'm trying to overcome regarding just one form which
gets
its data from 1 table.

I'm trying to set the default value of a combo box to be the same as
another
combo box on the same form.
The scenario is:
I have a form for entering info about financial planning Statements Of
Advice.
Each client has a Plan Writer prepare their plan.
To find the plan writer that last prepared the plan for a client I have a
combo box with a DLookUp as its Control Source.
=DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=Form![Client]")

I'm using a query so as to find the most recent record for a client.

Works wonderfully! but this combo can only be used for display.

Now the part thats causing me grief.

I want the value returned by that DLookUp to be the default value for
another combo box with the Row Source of
SELECT tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer FROM
tblPlanWriter;

The second combo must remain editable so that another plan writer can be
chosen if necessary

I've played around with all sorts of things, using the Expression
Builder,
borrowing bits of code from all over the net, Before Update, After
Update.

I cant find anything on the internet the same as what I'm attempting,
which
makes me wonder if what I want is even possible.

Thankyou

Bob

 

[Ken Snell \(MVP\)]

I'm headed out of town for a week, so I'm hoping that another person will be
able to pick up for me in this thread; I apologize.

But I did note an error in my generic code. The code should be for the first
combo box's After Update event:

Private Sub FirstComboBoxName_AfterUpdate()
Me.SecondComboBoxName.Value = _
DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=" & _
Me.FirstCombBoxName.Value)
End Sub

--

Ken Snell
<MS ACCESS MVP>

The user doesn't select the value in the first
combo(cboDefault_Planwriter). It is calculated by a DLookUp that finds the
most recent Plan writer for the client that a user does select in another
combo box (cboClient)
The second combo (cboPlanwriter) is bound to a table so that the user can
choose another planwriter if the one returned by cboDefault_Planwriter is
not suitable

I'll try removing its control source and post back after work.

Thanks

Bob

You need to set the Value of that second combo box to the desired
"default", and not use an expression in its ControlSource property.

Assuming that you want this to be done after the user selects the value
in the first combo box (what is its name?), you can use the AfterUpdate
event of that first combo box to do this (the code example assumes that
the Value of the first combo box is a numeric value, not a text string
value):

Private Sub SecondComboBoxName_AfterUpdate()
Me.SecondComboBoxName.Value = _
DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=" & _
Me.FirstCombBoxName.Value)
End Sub

--

Ken Snell
<MS ACCESS MVP>

Hi,
I have a problem I'm trying to overcome regarding just one form which
gets
its data from 1 table.

I'm trying to set the default value of a combo box to be the same as
another
combo box on the same form.
The scenario is:
I have a form for entering info about financial planning Statements Of
Advice.
Each client has a Plan Writer prepare their plan.
To find the plan writer that last prepared the plan for a client I have
a
combo box with a DLookUp as its Control Source.
=DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=Form![Client]")

I'm using a query so as to find the most recent record for a client.

Works wonderfully! but this combo can only be used for display.

Now the part thats causing me grief.

I want the value returned by that DLookUp to be the default value for
another combo box with the Row Source of
SELECT tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer FROM
tblPlanWriter;

The second combo must remain editable so that another plan writer can be
chosen if necessary

I've played around with all sorts of things, using the Expression
Builder,
borrowing bits of code from all over the net, Before Update, After
Update.

I cant find anything on the internet the same as what I'm attempting,
which
makes me wonder if what I want is even possible.

Thankyou

Bob

 

[Bob Wickham]

Thankyou Ken,

I now have the following in the AfterUpdate event of the first combo

Private Sub Default_Planwriter_AfterUpdate()
Me.PlanWriter.Value = _
DLookup("Plan_Writer", "qryDefault_Planwriter", "[Client]=" & _
Me.Default_Planwriter.Value)
End Sub

The control source of the first combo (Default_Planwriter) remains
=DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=Form![Client]")
which is a String

I have deleted the Control source of the second combo (PlanWriter) so now it
is unbound but its Row Source remains
SELECT tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer FROM
tblPlanWriter;

The primary key here, Plan_Writer_ID is a AutoNumber so does that mean this
sql is returning a number.

If so, do I need to convert my text string to a number or maybe, my number
to a string.

Bob

I'm headed out of town for a week, so I'm hoping that another person will
be able to pick up for me in this thread; I apologize.

But I did note an error in my generic code. The code should be for the
first combo box's After Update event:

Private Sub FirstComboBoxName_AfterUpdate()
Me.SecondComboBoxName.Value = _
DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=" & _
Me.FirstCombBoxName.Value)
End Sub

--

Ken Snell
<MS ACCESS MVP>

The user doesn't select the value in the first
combo(cboDefault_Planwriter). It is calculated by a DLookUp that finds
the most recent Plan writer for the client that a user does select in
another combo box (cboClient)
The second combo (cboPlanwriter) is bound to a table so that the user can
choose another planwriter if the one returned by cboDefault_Planwriter is
not suitable

I'll try removing its control source and post back after work.

Thanks

Bob

You need to set the Value of that second combo box to the desired
"default", and not use an expression in its ControlSource property.

Assuming that you want this to be done after the user selects the value
in the first combo box (what is its name?), you can use the AfterUpdate
event of that first combo box to do this (the code example assumes that
the Value of the first combo box is a numeric value, not a text string
value):

Private Sub SecondComboBoxName_AfterUpdate()
Me.SecondComboBoxName.Value = _
DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=" & _
Me.FirstCombBoxName.Value)
End Sub

--

Ken Snell
<MS ACCESS MVP>



Hi,
I have a problem I'm trying to overcome regarding just one form which
gets
its data from 1 table.

I'm trying to set the default value of a combo box to be the same as
another
combo box on the same form.
The scenario is:
I have a form for entering info about financial planning Statements Of
Advice.
Each client has a Plan Writer prepare their plan.
To find the plan writer that last prepared the plan for a client I have
a
combo box with a DLookUp as its Control Source.
=DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=Form![Client]")

I'm using a query so as to find the most recent record for a client.

Works wonderfully! but this combo can only be used for display.

Now the part thats causing me grief.

I want the value returned by that DLookUp to be the default value for
another combo box with the Row Source of
SELECT tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer FROM
tblPlanWriter;

The second combo must remain editable so that another plan writer can
be
chosen if necessary

I've played around with all sorts of things, using the Expression
Builder,
borrowing bits of code from all over the net, Before Update, After
Update.

I cant find anything on the internet the same as what I'm attempting,
which
makes me wonder if what I want is even possible.

Thankyou

Bob

 

[Bob Wickham]

As Ken is busy I have reposted this thread in the hope that someone else may
help me.
Thankyou

Thankyou Ken,

I now have the following in the AfterUpdate event of the first combo

Private Sub Default_Planwriter_AfterUpdate()
Me.PlanWriter.Value = _
DLookup("Plan_Writer", "qryDefault_Planwriter", "[Client]=" & _
Me.Default_Planwriter.Value)
End Sub

The control source of the first combo (Default_Planwriter) remains
=DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=Form![Client]")
which is a String

I have deleted the Control source of the second combo (PlanWriter) so now
it is unbound but its Row Source remains
SELECT tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer FROM
tblPlanWriter;

The primary key here, Plan_Writer_ID is a AutoNumber so does that mean
this sql is returning a number.

If so, do I need to convert my text string to a number or maybe, my number
to a string.

Bob

I'm headed out of town for a week, so I'm hoping that another person will
be able to pick up for me in this thread; I apologize.

But I did note an error in my generic code. The code should be for the
first combo box's After Update event:

Private Sub FirstComboBoxName_AfterUpdate()
Me.SecondComboBoxName.Value = _
DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=" & _
Me.FirstCombBoxName.Value)
End Sub

--

Ken Snell
<MS ACCESS MVP>

The user doesn't select the value in the first
combo(cboDefault_Planwriter). It is calculated by a DLookUp that finds
the most recent Plan writer for the client that a user does select in
another combo box (cboClient)
The second combo (cboPlanwriter) is bound to a table so that the user
can choose another planwriter if the one returned by
cboDefault_Planwriter is not suitable

I'll try removing its control source and post back after work.

Thanks

Bob

You need to set the Value of that second combo box to the desired
"default", and not use an expression in its ControlSource property.

Assuming that you want this to be done after the user selects the value
in the first combo box (what is its name?), you can use the AfterUpdate
event of that first combo box to do this (the code example assumes that
the Value of the first combo box is a numeric value, not a text string
value):

Private Sub SecondComboBoxName_AfterUpdate()
Me.SecondComboBoxName.Value = _
DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=" & _
Me.FirstCombBoxName.Value)
End Sub

--

Ken Snell
<MS ACCESS MVP>



Hi,
I have a problem I'm trying to overcome regarding just one form which
gets
its data from 1 table.

I'm trying to set the default value of a combo box to be the same as
another
combo box on the same form.
The scenario is:
I have a form for entering info about financial planning Statements Of
Advice.
Each client has a Plan Writer prepare their plan.
To find the plan writer that last prepared the plan for a client I
have a
combo box with a DLookUp as its Control Source.
=DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=Form![Client]")

I'm using a query so as to find the most recent record for a client.

Works wonderfully! but this combo can only be used for display.

Now the part thats causing me grief.

I want the value returned by that DLookUp to be the default value for
another combo box with the Row Source of
SELECT tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer FROM
tblPlanWriter;

The second combo must remain editable so that another plan writer can
be
chosen if necessary

I've played around with all sorts of things, using the Expression
Builder,
borrowing bits of code from all over the net, Before Update, After
Update.

I cant find anything on the internet the same as what I'm attempting,
which
makes me wonder if what I want is even possible.

Thankyou

Bob

 

[jrmask via AccessMonster.com]

Default value or value? if the first combo is working correctly then in the
AfterUpdate event set the second combo to equal the first (Me.Combo2 = Me.
Combo1). Then leave your lookup to the table in the second combo so that if a
person needs they can change the value.

Hi,
I have a problem I'm trying to overcome regarding just one form which gets
its data from 1 table.

I'm trying to set the default value of a combo box to be the same as another
combo box on the same form.
The scenario is:
I have a form for entering info about financial planning Statements Of
Advice.
Each client has a Plan Writer prepare their plan.
To find the plan writer that last prepared the plan for a client I have a
combo box with a DLookUp as its Control Source.
=DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=Form![Client]")

I'm using a query so as to find the most recent record for a client.

Works wonderfully! but this combo can only be used for display.

Now the part thats causing me grief.

I want the value returned by that DLookUp to be the default value for
another combo box with the Row Source of
SELECT tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer FROM
tblPlanWriter;

The second combo must remain editable so that another plan writer can be
chosen if necessary

I've played around with all sorts of things, using the Expression Builder,
borrowing bits of code from all over the net, Before Update, After Update.

I cant find anything on the internet the same as what I'm attempting, which
makes me wonder if what I want is even possible.

Thankyou

Bob

 

[Bob Wickham]

Thanks for replying, Richard.

Default value or value?
Value. I want Combo2 to show what Combo1 is showing.
But everything I've tried has not automatically entered anything into
Combo2.

From your suggestion, the AfterUpdate of Combo1 now reads:
Private Sub Default_Planwriter_AfterUpdate()
Me.PlanWriter = Me.Default_Planwriter
End Sub

Combo1(Default_Planwriter) displays the result of the DLookup, as it should.
Combo2(PlanWriter) does not display anything, nor do I get an error.

In fact the only way I can get Combo2 to display the name of a PlanWriter
automatically, is to hard code the PlanWriters numerical ID into the Default
Value property. Hard coding the actual name of the person does not display
anything.

So, do you think I'm right in assuming that Combo1 is returning a string and
Combo2 is expecting an integer.

Bob

Default value or value? if the first combo is working correctly then in
the
AfterUpdate event set the second combo to equal the first (Me.Combo2 = Me.
Combo1). Then leave your lookup to the table in the second combo so that
if a
person needs they can change the value.

Hi,
I have a problem I'm trying to overcome regarding just one form which gets
its data from 1 table.

I'm trying to set the default value of a combo box to be the same as
another
combo box on the same form.
The scenario is:
I have a form for entering info about financial planning Statements Of
Advice.
Each client has a Plan Writer prepare their plan.
To find the plan writer that last prepared the plan for a client I have a
combo box with a DLookUp as its Control Source.
=DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=Form![Client]")

I'm using a query so as to find the most recent record for a client.

Works wonderfully! but this combo can only be used for display.

Now the part thats causing me grief.

I want the value returned by that DLookUp to be the default value for
another combo box with the Row Source of
SELECT tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer FROM
tblPlanWriter;

The second combo must remain editable so that another plan writer can be
chosen if necessary

I've played around with all sorts of things, using the Expression Builder,
borrowing bits of code from all over the net, Before Update, After Update.

I cant find anything on the internet the same as what I'm attempting,
which
makes me wonder if what I want is even possible.

Thankyou

Bob

 

[jrmask via AccessMonster.com]

Your assumption is correct. Are the two combos that far apart on the form
that you would need to show the last writer in the second combo or is it for
convenience? Let me play with this a little and come up with the correct way
to display the way you want.

Thanks for replying, Richard.

Default value or value?
Value. I want Combo2 to show what Combo1 is showing.
But everything I've tried has not automatically entered anything into
Combo2.

From your suggestion, the AfterUpdate of Combo1 now reads:
Private Sub Default_Planwriter_AfterUpdate()
Me.PlanWriter = Me.Default_Planwriter
End Sub

Combo1(Default_Planwriter) displays the result of the DLookup, as it should.
Combo2(PlanWriter) does not display anything, nor do I get an error.

In fact the only way I can get Combo2 to display the name of a PlanWriter
automatically, is to hard code the PlanWriters numerical ID into the Default
Value property. Hard coding the actual name of the person does not display
anything.

So, do you think I'm right in assuming that Combo1 is returning a string and
Combo2 is expecting an integer.

Bob

Default value or value? if the first combo is working correctly then in
the

[quoted text clipped - 42 lines]

 

[jrmask via AccessMonster.com]

In your 2nd combo what is the column count and column widths? I would bet
it's set to 2 and 0:1" respectively. change the column width to 1";1" and see
if it displays, if not do this

change your rowsource for the second combo to;
SELECT tblPlanWriter.Plan_Writer FROM
tblPlanWriter;

column count to 1
column width to 1"
bound column to 1
this is not the preferred way to store data but it should work.

Your assumption is correct. Are the two combos that far apart on the form
that you would need to show the last writer in the second combo or is it for
convenience? Let me play with this a little and come up with the correct way
to display the way you want.

Thanks for replying, Richard.

[quoted text clipped - 26 lines]

 

[jrmask via AccessMonster.com]

In your 2nd combo what is the column count and column widths? I would bet
it's set to 2 and 0:1" respectively. change the column width to 1";1" and see
if it displays, if not do this

change your rowsource for the second combo to;
SELECT tblPlanWriter.Plan_Writer FROM
tblPlanWriter;

column count to 1
column width to 1"
bound column to 1
this is not the preferred way to store data but it should work.

Your assumption is correct. Are the two combos that far apart on the form
that you would need to show the last writer in the second combo or is it for
convenience? Let me play with this a little and come up with the correct way
to display the way you want.

Thanks for replying, Richard.

[quoted text clipped - 26 lines]

 

[Bob Wickham]

In your 2nd combo what is the column count and column widths? I would bet

it's set to 2 and 0:1" respectively

Yes it was.

change the column width to 1";1" and see if it displays

If I use the combo manually, it displays the 2 columns
But it displays a zero (0) otherwise

change your rowsource for the second combo to;
SELECT tblPlanWriter.Plan_Writer FROM
tblPlanWriter;

column count to 1
column width to 1"
bound column to 1

Displays a zero (0) even though no ID of zero (0) exists
If I then try to use the combo by manually selecting an entry I get an error
message saying I may have entered text into a numeric field..

To clarify things a little, the second combo, PlanWriter, is bound to a
table called tblPlan. But that field of tblPlan looks up another table
called tblPlanWriter.

I've spent many, many frustrating hours on this trying every way I can
think of, and even though both combo boxes work individually, there must be
something very wrong with my design as none of the logical solutions even
comes close.

In your 2nd combo what is the column count and column widths? I would bet
it's set to 2 and 0:1" respectively. change the column width to 1";1" and
see
if it displays, if not do this

change your rowsource for the second combo to;
SELECT tblPlanWriter.Plan_Writer FROM
tblPlanWriter;

column count to 1
column width to 1"
bound column to 1
this is not the preferred way to store data but it should work.

Your assumption is correct. Are the two combos that far apart on the form
that you would need to show the last writer in the second combo or is it
for
convenience? Let me play with this a little and come up with the correct
way
to display the way you want.

Thanks for replying, Richard.

[quoted text clipped - 26 lines]

Bob

 

[Bob Wickham]

Its for convenience and to look professional. 9 times out of 10 the last
Planwriter used will be the one you will want to use next time, so I want
the combo box that allows you to choose a Planwriter to default to the last
Planwriter used for that client.

Your assumption is correct. Are the two combos that far apart on the form
that you would need to show the last writer in the second combo or is it
for
convenience? Let me play with this a little and come up with the correct
way
to display the way you want.

Thanks for replying, Richard.

Default value or value?
Value. I want Combo2 to show what Combo1 is showing.
But everything I've tried has not automatically entered anything into
Combo2.

From your suggestion, the AfterUpdate of Combo1 now reads:
Private Sub Default_Planwriter_AfterUpdate()
Me.PlanWriter = Me.Default_Planwriter
End Sub

Combo1(Default_Planwriter) displays the result of the DLookup, as it
should.
Combo2(PlanWriter) does not display anything, nor do I get an error.

In fact the only way I can get Combo2 to display the name of a PlanWriter
automatically, is to hard code the PlanWriters numerical ID into the
Default
Value property. Hard coding the actual name of the person does not display
anything.

So, do you think I'm right in assuming that Combo1 is returning a string
and
Combo2 is expecting an integer.

Bob

Default value or value? if the first combo is working correctly then in
the

[quoted text clipped - 42 lines]

Bob

 

[Ken Snell \(MVP\)]

Bob -

I'm a bit confused. If you have an expression for the first combo box's
Control Source property, then you're not going to be able to actually make a
selection in that first combo box -- and thus the AfterUpdate event of that
combo box will not occur, and thus the code will not run to set the second
combo box to the desired value.

Normally, when using "matched" combo boxes, you want to be able to make a
selection in the first combo box so that the second combo box is "set" in
some way based on the selection made in the first combo box.

Perhaps it would be good if we step backwards a few steps and let you tell
us exactly what you're wanting to accomplish on this form? Also, is the
Client field a numeric field or a text field?

--

Ken Snell
<MS ACCESS MVP>

Thankyou Ken,

I now have the following in the AfterUpdate event of the first combo

Private Sub Default_Planwriter_AfterUpdate()
Me.PlanWriter.Value = _
DLookup("Plan_Writer", "qryDefault_Planwriter", "[Client]=" & _
Me.Default_Planwriter.Value)
End Sub

The control source of the first combo (Default_Planwriter) remains
=DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=Form![Client]")
which is a String

I have deleted the Control source of the second combo (PlanWriter) so now
it is unbound but its Row Source remains
SELECT tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer FROM
tblPlanWriter;

The primary key here, Plan_Writer_ID is a AutoNumber so does that mean
this sql is returning a number.

If so, do I need to convert my text string to a number or maybe, my number
to a string.

Bob

I'm headed out of town for a week, so I'm hoping that another person will
be able to pick up for me in this thread; I apologize.

But I did note an error in my generic code. The code should be for the
first combo box's After Update event:

Private Sub FirstComboBoxName_AfterUpdate()
Me.SecondComboBoxName.Value = _
DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=" & _
Me.FirstCombBoxName.Value)
End Sub

--

Ken Snell
<MS ACCESS MVP>

The user doesn't select the value in the first
combo(cboDefault_Planwriter). It is calculated by a DLookUp that finds
the most recent Plan writer for the client that a user does select in
another combo box (cboClient)
The second combo (cboPlanwriter) is bound to a table so that the user
can choose another planwriter if the one returned by
cboDefault_Planwriter is not suitable

I'll try removing its control source and post back after work.

Thanks

Bob

You need to set the Value of that second combo box to the desired
"default", and not use an expression in its ControlSource property.

Assuming that you want this to be done after the user selects the value
in the first combo box (what is its name?), you can use the AfterUpdate
event of that first combo box to do this (the code example assumes that
the Value of the first combo box is a numeric value, not a text string
value):

Private Sub SecondComboBoxName_AfterUpdate()
Me.SecondComboBoxName.Value = _
DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=" & _
Me.FirstCombBoxName.Value)
End Sub

--

Ken Snell
<MS ACCESS MVP>



Hi,
I have a problem I'm trying to overcome regarding just one form which
gets
its data from 1 table.

I'm trying to set the default value of a combo box to be the same as
another
combo box on the same form.
The scenario is:
I have a form for entering info about financial planning Statements Of
Advice.
Each client has a Plan Writer prepare their plan.
To find the plan writer that last prepared the plan for a client I
have a
combo box with a DLookUp as its Control Source.
=DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=Form![Client]")

I'm using a query so as to find the most recent record for a client.

Works wonderfully! but this combo can only be used for display.

Now the part thats causing me grief.

I want the value returned by that DLookUp to be the default value for
another combo box with the Row Source of
SELECT tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer FROM
tblPlanWriter;

The second combo must remain editable so that another plan writer can
be
chosen if necessary

I've played around with all sorts of things, using the Expression
Builder,
borrowing bits of code from all over the net, Before Update, After
Update.

I cant find anything on the internet the same as what I'm attempting,
which
makes me wonder if what I want is even possible.

Thankyou

Bob

 

[Bob Wickham]

Hello Ken,

The first combo box is called Client. Its Control Source is the Client field
in a table called tblPlan.
That Clent field looks up another table called tblClient.
Therefore the Row Source of the Client combo box on the form is SELECT
tblClient.ClientID, tblClient.Client FROM tblClient ORDER BY
tblClient.Client;
I can make a selection in the combo box of any of the Clients present in the
Client table.
The column count of the combo box is 2 but the Bound Column is 1. Column 1
is an AutoNumber so I think that means the Client field is numeric.
A client visits this Financial Planning business to have a financial plan
designed for them. Some plans are short term proposals so the client comes
back in say 6 months and wants another plan designed.
The form causing all the grief here is called frmNewPlan. As well as the
Client combo box there is a Combo box called PlanWriter.
The people who require this database would like the PlanWriter combo to
default to the whoever the PlanWriter was who most recently designed a plan
for the Client selected in the Client combo box.
I am able to determine this by a series of 3 queries on queries.
Using DLookUp("MaxOfPlan_Writer_ID
","qryMost_Recent_Planwriter_3","[Client]=Form![Client]") in a hidden text
box, I can display the numeric ID of the PlanWriter.
So, I'm fairly sure my logic is correct.
I need to be able to make the PlanWriter combo display the PlanWriter name
matching the ID displayed in the text box.
The PlanWriter combo is similar to the Client combo in that its Control
Source is a field in tblPlan and its Row Source is
SELECT tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer,
tblPlanWriter.Active FROM tblPlanWriter WHERE (((tblPlanWriter.Active)=Yes))
ORDER BY tblPlanWriter.Plan_Writer;
with a column count of 2 and a bound column of 1.

I've tried all the methods suggested in this forum, for example, PlanWriter
combo = TextBox in the After Update event of the Text box but so far the
PlanWriter combo remains blank. All the answers offered by some very kind
people seem to be saying to me, "This is really straight forward stuff,
whats the matter?"

But still, no luck.

Bob

Bob -

I'm a bit confused. If you have an expression for the first combo box's
Control Source property, then you're not going to be able to actually make
a selection in that first combo box -- and thus the AfterUpdate event of
that combo box will not occur, and thus the code will not run to set the
second combo box to the desired value.

Normally, when using "matched" combo boxes, you want to be able to make a
selection in the first combo box so that the second combo box is "set" in
some way based on the selection made in the first combo box.

Perhaps it would be good if we step backwards a few steps and let you tell
us exactly what you're wanting to accomplish on this form? Also, is the
Client field a numeric field or a text field?

--

Ken Snell
<MS ACCESS MVP>

Thankyou Ken,

I now have the following in the AfterUpdate event of the first combo

Private Sub Default_Planwriter_AfterUpdate()
Me.PlanWriter.Value = _
DLookup("Plan_Writer", "qryDefault_Planwriter", "[Client]=" & _
Me.Default_Planwriter.Value)
End Sub

The control source of the first combo (Default_Planwriter) remains
=DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=Form![Client]")
which is a String

I have deleted the Control source of the second combo (PlanWriter) so now
it is unbound but its Row Source remains
SELECT tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer FROM
tblPlanWriter;

The primary key here, Plan_Writer_ID is a AutoNumber so does that mean
this sql is returning a number.

If so, do I need to convert my text string to a number or maybe, my
number to a string.

Bob

I'm headed out of town for a week, so I'm hoping that another person
will be able to pick up for me in this thread; I apologize.

But I did note an error in my generic code. The code should be for the
first combo box's After Update event:

Private Sub FirstComboBoxName_AfterUpdate()
Me.SecondComboBoxName.Value = _
DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=" & _
Me.FirstCombBoxName.Value)
End Sub

--

Ken Snell
<MS ACCESS MVP>


The user doesn't select the value in the first
combo(cboDefault_Planwriter). It is calculated by a DLookUp that finds
the most recent Plan writer for the client that a user does select in
another combo box (cboClient)
The second combo (cboPlanwriter) is bound to a table so that the user
can choose another planwriter if the one returned by
cboDefault_Planwriter is not suitable

I'll try removing its control source and post back after work.

Thanks

Bob

You need to set the Value of that second combo box to the desired
"default", and not use an expression in its ControlSource property.

Assuming that you want this to be done after the user selects the
value in the first combo box (what is its name?), you can use the
AfterUpdate event of that first combo box to do this (the code example
assumes that the Value of the first combo box is a numeric value, not
a text string value):

Private Sub SecondComboBoxName_AfterUpdate()
Me.SecondComboBoxName.Value = _
DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=" & _
Me.FirstCombBoxName.Value)
End Sub

--

Ken Snell
<MS ACCESS MVP>



Hi,
I have a problem I'm trying to overcome regarding just one form which
gets
its data from 1 table.

I'm trying to set the default value of a combo box to be the same as
another
combo box on the same form.
The scenario is:
I have a form for entering info about financial planning Statements
Of
Advice.
Each client has a Plan Writer prepare their plan.
To find the plan writer that last prepared the plan for a client I
have a
combo box with a DLookUp as its Control Source.
=DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=Form![Client]")

I'm using a query so as to find the most recent record for a client.

Works wonderfully! but this combo can only be used for display.

Now the part thats causing me grief.

I want the value returned by that DLookUp to be the default value for
another combo box with the Row Source of
SELECT tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer FROM
tblPlanWriter;

The second combo must remain editable so that another plan writer can
be
chosen if necessary

I've played around with all sorts of things, using the Expression
Builder,
borrowing bits of code from all over the net, Before Update, After
Update.

I cant find anything on the internet the same as what I'm attempting,
which
makes me wonder if what I want is even possible.

Thankyou

Bob

 

[Ken Snell \(MVP\)]

OK - now I see what you want. Essentially, you want to "set" the PlanWriter
combo box to have the value displayed in the hidden textbox as the initial
selection. In actuality, though, we can dispense with the hidden textbox and
do what you seek directly.

I would use the Current event of the frmNewPlan form to do this:

Private Sub Form_Current()
If Me.NewRecord = True Then Me.PlanWriter.Value = _
DLookUp("MaxOfPlan_Writer_ID","qryMost_Recent_Planwriter_3", _
"[Client]=" & [Client])
End Sub

The above code will cause the PlanWriter combo box to be assigned the value
from the DLookup function whenever the form is moved to a different record
AND that different record is a New Record (not one already created).
--

Ken Snell
<MS ACCESS MVP>

Hello Ken,

The first combo box is called Client. Its Control Source is the Client
field in a table called tblPlan.
That Clent field looks up another table called tblClient.
Therefore the Row Source of the Client combo box on the form is SELECT
tblClient.ClientID, tblClient.Client FROM tblClient ORDER BY
tblClient.Client;
I can make a selection in the combo box of any of the Clients present in
the Client table.
The column count of the combo box is 2 but the Bound Column is 1. Column 1
is an AutoNumber so I think that means the Client field is numeric.
A client visits this Financial Planning business to have a financial plan
designed for them. Some plans are short term proposals so the client comes
back in say 6 months and wants another plan designed.
The form causing all the grief here is called frmNewPlan. As well as the
Client combo box there is a Combo box called PlanWriter.
The people who require this database would like the PlanWriter combo to
default to the whoever the PlanWriter was who most recently designed a
plan for the Client selected in the Client combo box.
I am able to determine this by a series of 3 queries on queries.
Using DLookUp("MaxOfPlan_Writer_ID
","qryMost_Recent_Planwriter_3","[Client]=Form![Client]") in a hidden text
box, I can display the numeric ID of the PlanWriter.
So, I'm fairly sure my logic is correct.
I need to be able to make the PlanWriter combo display the PlanWriter name
matching the ID displayed in the text box.
The PlanWriter combo is similar to the Client combo in that its Control
Source is a field in tblPlan and its Row Source is
SELECT tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer,
tblPlanWriter.Active FROM tblPlanWriter WHERE
(((tblPlanWriter.Active)=Yes)) ORDER BY tblPlanWriter.Plan_Writer;
with a column count of 2 and a bound column of 1.

I've tried all the methods suggested in this forum, for example,
PlanWriter combo = TextBox in the After Update event of the Text box but
so far the PlanWriter combo remains blank. All the answers offered by some
very kind people seem to be saying to me, "This is really straight forward
stuff, whats the matter?"

But still, no luck.

Bob

Bob -

I'm a bit confused. If you have an expression for the first combo box's
Control Source property, then you're not going to be able to actually
make a selection in that first combo box -- and thus the AfterUpdate
event of that combo box will not occur, and thus the code will not run to
set the second combo box to the desired value.

Normally, when using "matched" combo boxes, you want to be able to make a
selection in the first combo box so that the second combo box is "set" in
some way based on the selection made in the first combo box.

Perhaps it would be good if we step backwards a few steps and let you
tell us exactly what you're wanting to accomplish on this form? Also, is
the Client field a numeric field or a text field?

--

Ken Snell
<MS ACCESS MVP>

Thankyou Ken,

I now have the following in the AfterUpdate event of the first combo

Private Sub Default_Planwriter_AfterUpdate()
Me.PlanWriter.Value = _
DLookup("Plan_Writer", "qryDefault_Planwriter", "[Client]=" & _
Me.Default_Planwriter.Value)
End Sub

The control source of the first combo (Default_Planwriter) remains
=DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=Form![Client]")
which is a String

I have deleted the Control source of the second combo (PlanWriter) so
now it is unbound but its Row Source remains
SELECT tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer FROM
tblPlanWriter;

The primary key here, Plan_Writer_ID is a AutoNumber so does that mean
this sql is returning a number.

If so, do I need to convert my text string to a number or maybe, my
number to a string.

Bob


I'm headed out of town for a week, so I'm hoping that another person
will be able to pick up for me in this thread; I apologize.

But I did note an error in my generic code. The code should be for the
first combo box's After Update event:

Private Sub FirstComboBoxName_AfterUpdate()
Me.SecondComboBoxName.Value = _
DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=" & _
Me.FirstCombBoxName.Value)
End Sub

--

Ken Snell
<MS ACCESS MVP>


The user doesn't select the value in the first
combo(cboDefault_Planwriter). It is calculated by a DLookUp that finds
the most recent Plan writer for the client that a user does select in
another combo box (cboClient)
The second combo (cboPlanwriter) is bound to a table so that the user
can choose another planwriter if the one returned by
cboDefault_Planwriter is not suitable

I'll try removing its control source and post back after work.

Thanks

Bob

You need to set the Value of that second combo box to the desired
"default", and not use an expression in its ControlSource property.

Assuming that you want this to be done after the user selects the
value in the first combo box (what is its name?), you can use the
AfterUpdate event of that first combo box to do this (the code
example assumes that the Value of the first combo box is a numeric
value, not a text string value):

Private Sub SecondComboBoxName_AfterUpdate()
Me.SecondComboBoxName.Value = _
DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=" & _
Me.FirstCombBoxName.Value)
End Sub

--

Ken Snell
<MS ACCESS MVP>



Hi,
I have a problem I'm trying to overcome regarding just one form
which gets
its data from 1 table.

I'm trying to set the default value of a combo box to be the same as
another
combo box on the same form.
The scenario is:
I have a form for entering info about financial planning Statements
Of
Advice.
Each client has a Plan Writer prepare their plan.
To find the plan writer that last prepared the plan for a client I
have a
combo box with a DLookUp as its Control Source.
=DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=Form![Client]")

I'm using a query so as to find the most recent record for a client.

Works wonderfully! but this combo can only be used for display.

Now the part thats causing me grief.

I want the value returned by that DLookUp to be the default value
for
another combo box with the Row Source of
SELECT tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer FROM
tblPlanWriter;

The second combo must remain editable so that another plan writer
can be
chosen if necessary

I've played around with all sorts of things, using the Expression
Builder,
borrowing bits of code from all over the net, Before Update, After
Update.

I cant find anything on the internet the same as what I'm
attempting, which
makes me wonder if what I want is even possible.

Thankyou

Bob

 

[Bob Wickham]

Hi Ken,

No, I cant get that to work either.
The DLookUp code in your sub is exactly the same as the DLookUp in the
Control Source of my hidden text box.
I know my combo box, Plan_Writer_ID (Not PlanWriter as in your code, but I
change your code to suit) is numeric because if I set its default value to a
number, say 14, it displays the name of a planwriter.
I thought maybe the problem lies in the fact that the combo box is bound so
I inserted an Unbound text box and changed the code accordingly, but still
no luck.

I'm afraid I'm out of options.

Thanks

Bob

OK - now I see what you want. Essentially, you want to "set" the
PlanWriter combo box to have the value displayed in the hidden textbox as
the initial selection. In actuality, though, we can dispense with the
hidden textbox and do what you seek directly.

I would use the Current event of the frmNewPlan form to do this:

Private Sub Form_Current()
If Me.NewRecord = True Then Me.PlanWriter.Value = _
DLookUp("MaxOfPlan_Writer_ID","qryMost_Recent_Planwriter_3", _
"[Client]=" & [Client])
End Sub

The above code will cause the PlanWriter combo box to be assigned the
value from the DLookup function whenever the form is moved to a different
record AND that different record is a New Record (not one already
created).
--

Ken Snell
<MS ACCESS MVP>

Hello Ken,

The first combo box is called Client. Its Control Source is the Client
field in a table called tblPlan.
That Clent field looks up another table called tblClient.
Therefore the Row Source of the Client combo box on the form is SELECT
tblClient.ClientID, tblClient.Client FROM tblClient ORDER BY
tblClient.Client;
I can make a selection in the combo box of any of the Clients present in
the Client table.
The column count of the combo box is 2 but the Bound Column is 1. Column
1 is an AutoNumber so I think that means the Client field is numeric.
A client visits this Financial Planning business to have a financial plan
designed for them. Some plans are short term proposals so the client
comes back in say 6 months and wants another plan designed.
The form causing all the grief here is called frmNewPlan. As well as the
Client combo box there is a Combo box called PlanWriter.
The people who require this database would like the PlanWriter combo to
default to the whoever the PlanWriter was who most recently designed a
plan for the Client selected in the Client combo box.
I am able to determine this by a series of 3 queries on queries.
Using DLookUp("MaxOfPlan_Writer_ID
","qryMost_Recent_Planwriter_3","[Client]=Form![Client]") in a hidden
text box, I can display the numeric ID of the PlanWriter.
So, I'm fairly sure my logic is correct.
I need to be able to make the PlanWriter combo display the PlanWriter
name matching the ID displayed in the text box.
The PlanWriter combo is similar to the Client combo in that its Control
Source is a field in tblPlan and its Row Source is
SELECT tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer,
tblPlanWriter.Active FROM tblPlanWriter WHERE
(((tblPlanWriter.Active)=Yes)) ORDER BY tblPlanWriter.Plan_Writer;
with a column count of 2 and a bound column of 1.

I've tried all the methods suggested in this forum, for example,
PlanWriter combo = TextBox in the After Update event of the Text box but
so far the PlanWriter combo remains blank. All the answers offered by
some very kind people seem to be saying to me, "This is really straight
forward stuff, whats the matter?"

But still, no luck.

Bob

Bob -

I'm a bit confused. If you have an expression for the first combo box's
Control Source property, then you're not going to be able to actually
make a selection in that first combo box -- and thus the AfterUpdate
event of that combo box will not occur, and thus the code will not run
to set the second combo box to the desired value.

Normally, when using "matched" combo boxes, you want to be able to make
a selection in the first combo box so that the second combo box is "set"
in some way based on the selection made in the first combo box.

Perhaps it would be good if we step backwards a few steps and let you
tell us exactly what you're wanting to accomplish on this form? Also, is
the Client field a numeric field or a text field?

--

Ken Snell
<MS ACCESS MVP>



Thankyou Ken,

I now have the following in the AfterUpdate event of the first combo

Private Sub Default_Planwriter_AfterUpdate()
Me.PlanWriter.Value = _
DLookup("Plan_Writer", "qryDefault_Planwriter", "[Client]=" & _
Me.Default_Planwriter.Value)
End Sub

The control source of the first combo (Default_Planwriter) remains
=DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=Form![Client]")
which is a String

I have deleted the Control source of the second combo (PlanWriter) so
now it is unbound but its Row Source remains
SELECT tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer FROM
tblPlanWriter;

The primary key here, Plan_Writer_ID is a AutoNumber so does that mean
this sql is returning a number.

If so, do I need to convert my text string to a number or maybe, my
number to a string.

Bob


I'm headed out of town for a week, so I'm hoping that another person
will be able to pick up for me in this thread; I apologize.

But I did note an error in my generic code. The code should be for the
first combo box's After Update event:

Private Sub FirstComboBoxName_AfterUpdate()
Me.SecondComboBoxName.Value = _
DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=" & _
Me.FirstCombBoxName.Value)
End Sub

--

Ken Snell
<MS ACCESS MVP>


The user doesn't select the value in the first
combo(cboDefault_Planwriter). It is calculated by a DLookUp that
finds the most recent Plan writer for the client that a user does
select in another combo box (cboClient)
The second combo (cboPlanwriter) is bound to a table so that the user
can choose another planwriter if the one returned by
cboDefault_Planwriter is not suitable

I'll try removing its control source and post back after work.

Thanks

Bob

You need to set the Value of that second combo box to the desired
"default", and not use an expression in its ControlSource property.

Assuming that you want this to be done after the user selects the
value in the first combo box (what is its name?), you can use the
AfterUpdate event of that first combo box to do this (the code
example assumes that the Value of the first combo box is a numeric
value, not a text string value):

Private Sub SecondComboBoxName_AfterUpdate()
Me.SecondComboBoxName.Value = _
DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=" & _
Me.FirstCombBoxName.Value)
End Sub

--

Ken Snell
<MS ACCESS MVP>



Hi,
I have a problem I'm trying to overcome regarding just one form
which gets
its data from 1 table.

I'm trying to set the default value of a combo box to be the same
as another
combo box on the same form.
The scenario is:
I have a form for entering info about financial planning Statements
Of
Advice.
Each client has a Plan Writer prepare their plan.
To find the plan writer that last prepared the plan for a client I
have a
combo box with a DLookUp as its Control Source.
=DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=Form![Client]")

I'm using a query so as to find the most recent record for a
client.

Works wonderfully! but this combo can only be used for display.

Now the part thats causing me grief.

I want the value returned by that DLookUp to be the default value
for
another combo box with the Row Source of
SELECT tblPlanWriter.Plan_Writer_ID, tblPlanWriter.Plan_Writer FROM
tblPlanWriter;

The second combo must remain editable so that another plan writer
can be
chosen if necessary

I've played around with all sorts of things, using the Expression
Builder,
borrowing bits of code from all over the net, Before Update, After
Update.

I cant find anything on the internet the same as what I'm
attempting, which
makes me wonder if what I want is even possible.

Thankyou

Bob

 

[Ken Snell \(MVP\)]

There has to be some design item on your form/setup that we've neglected to
ask about...

If you're able and willing, could you email me a "stripped down" database
file, with instructions for which form to look at and what steps to do on
that form that "should" give you the desired result if all were working ok,
in a zip file? You can find an email address for me at
www.cadellsoftware.org .

I'd like to take a direct look at your setup. From what you've described you
want to achieve, it should be doable.