howto sort combo box lastname firstname 3

[miscluce]

I have a combo box that lists only the firstnames in no specific order. my settings for the combo box are:

column count="4"
column widths= 0";1";1";0"
row source= "dbo_tblName"
row source type=" table/query"
bound column ="1"

and when you click on a selection it fills my textboxes on my form with this code.

Private Sub Combo22_AfterUpdate()
Me.txtNameId = Combo22.Column(0)
Me.txtFirstName = Combo22.Column(1)
Me.txtLastName = Combo22.Column(2)
Me.txtTitle = Combo22.Column(3)
End Sub

can someone tell me how I can display the Lastname, FirstName in Ascending order in the combo box 22 and still allow the user to select a name to fill in the textboxes on the form?

 

[zevw]

Can I see the query (sql) behind this combo box, please!

 

[miscluce]

there isnt any query, what I listed is pretty much it.

my row source= "dbo_tblName" is a table on my SQL server

oh and my control source property is "blank"

I did try to change my rowsource to this:

row source = SELECT dbo_tblName.fldLastName, dbo_tblName.fldFirstName FROM dbo_tblName ORDER BY dbo_tblName.fldLastName, dbo_tblName.fldFirstName;

what that did was:
list only the first names in no specific order in the combo box and when the user selects one of them it fills the (firstname in the Name ID textbox) and the (lastname in the firstname textbox) and there is nothing in the lastname textbox or title text box.

this is weird??

 

[zevw]

try this, it should work

column count="5"
column widths= 0";2";0";0";0"
row source= sql string below
row source type="table/query"
bound column ="1"

I dont know what your
ID field is so named it dbo_tblName.Id
Title field is so named it dbo_tblName.Title

row source = SELECT dbo_tblName.Id, dbo_tblName.fldLastName & " " & dbo_tblName.fldFirstName, dbo_tblName.fldLastName, dbo_tblName.fldFirstName, dbo_tblName.Title FROM dbo_tblName ORDER BY dbo_tblName.fldLastName, dbo_tblName.fldFirstName

Private Sub Combo22_AfterUpdate()
Me.txtNameId = Combo22.Column(0)
Me.txtFirstName = Combo22.Column(2)
Me.txtLastName = Combo22.Column(3)
Me.txtTitle = Combo22.Column(4)
End Sub

 

[miscluce]

great. now I have the last name sorted in ascending order and it fills in my text boxes when I select a last name.

but how come my the combo box only has the lastname populated and not the first name as well.

also, why is the column count to 5 when I only have 4 columns total. 0,1,2,3 columns counts

 

[PHV]

column count="4"
column widths= 0";1";1";0"
row source= [!]"SELECT fldNameId,fldLastName,fldFirstName,fldTitle FROM dbo_tblName ORDER BY 2,3"[/!]
row source type=" table/query"
bound column ="1"

and when you click on a selection it fills my textboxes on my form with this code.

Private Sub Combo22_AfterUpdate()
Me.txtNameId = Combo22.Column(0)
Me.txtFirstName = Combo22.Column(1)
Me.txtLastName = Combo22.Column(2)
Me.txtTitle = Combo22.Column(3)
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[miscluce]

great. thanks it works with this:

column widths= 0";1";1";0"

but not this: (which only displays the lastname)
column widths= 0";2";2";0"

this was puzzling me for days. whats the difference?

 

[zevw]

Because your List Width is set to 2", if you will set it to 4" you will see both.

My Idea was that you see the last and first name combined and hide the other columns in that case you have 5 columns not 4.

and assign the values to the hiddedn columns

id to column 0
first to column 2
last to column 3
title to column 4

 

[miscluce]

got it. thanks

 

[joebb3]

zevw,

Here's a star!!! The tip for how to display multiple fields INSIDE the combo box via the SELECT statement was EXACTLY what I needed!

I also surrounded the second column in a UCASE(...) to even out the data displayed in the dropdown.

Thanx again!