Default value of combo box based on another that uses DLookUp - continued

[Bob Wickham]

Ken Snell was kind enough to offer me some assistance here (repeated below)
but he has had leave town for a week and I'm hoping another knowledgable
person can help me.

The sql of the query in the DLookup is:

SELECT tblPlanWriter.Plan_Writer, tblPlan.Client, Max(tblPlan.Date_Received)
AS MaxOfDate_Received
FROM tblPlanWriter INNER JOIN tblPlan ON tblPlanWriter.Plan_Writer_ID =
tblPlan.Assigned_To_PlanWriter
GROUP BY tblPlanWriter.Plan_Writer, tblPlan.Client
ORDER BY Max(tblPlan.Date_Received) DESC;

I think my problem is mis-matched data types, string and number, although no
error message pops up to say so.
The combo box I want to have show the same value as the other just remains
blank.


*******************************************************

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]

See your first post for my answer

Ken Snell was kind enough to offer me some assistance here (repeated below)
but he has had leave town for a week and I'm hoping another knowledgable
person can help me.

The sql of the query in the DLookup is:

SELECT tblPlanWriter.Plan_Writer, tblPlan.Client, Max(tblPlan.Date_Received)
AS MaxOfDate_Received
FROM tblPlanWriter INNER JOIN tblPlan ON tblPlanWriter.Plan_Writer_ID =
tblPlan.Assigned_To_PlanWriter
GROUP BY tblPlanWriter.Plan_Writer, tblPlan.Client
ORDER BY Max(tblPlan.Date_Received) DESC;

I think my problem is mis-matched data types, string and number, although no
error message pops up to say so.
The combo box I want to have show the same value as the other just remains
blank.

*******************************************************

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.

[quoted text clipped - 83 lines]