userform/combobox question

B

bryan

I have a user form which I populate using sql on a database with a variable
passed in. I display the results for selection in a combobox. What's
displayed is name and business type. Selection is returned for use. What I
also need to include in the sql is an id number for use as well in return
but, I do not want that displayed.

1) can I add the id into the combobox and not have it display or
2) how else can I accomplish this?

Here is what I have:
Public ClaimN As String

Private Sub UserForm_Activate()

strcharID = ClaimN
connection string>>>>>>

strquery1 = "Select entity_table_id, isnull(First_Name,'')as FN,
isnull(Last_Name,'')as LN, isnull(Contact,'')as MI, " & _
"States.State_ID " & _
"from Claimant_V cv left outer join " & _
"STATES on cv.State_ID = STATES.State_Row_ID " & _
"where cv.relation_sc like '%CL%' and cv.eff_end_date <>
cv.eff_start_date and cv.claim_id = '" + strcharID + "'"

Dim zd
Set zd = CreateObject("ADODB.Recordset")
zd.Open strquery1, objConn1, 2, 3
If Not zd.EOF Then
zd.MoveFirst

Do While Not zd.EOF
strty = Trim(zd("entity_table_id").Value)
strf = Trim(zd("FN").Value)
strL = Trim(zd("LN").Value)
strM = Trim(zd("MI").Value)


If strty = "1576" Then
strtp = "B"
Else
strtp = "I"
End If

If strf = "" Then
ComboBox1.AddItem strtp + " " + strL
Else
ComboBox1.AddItem strtp + " " + strf + " " + strL
End If


zd.MoveNext

Loop
ComboBox1.ListIndex = 0
End If
end sub
 
D

Doug Robbins - Word MVP

The ID can be included in the combobox in a column for which you set the
width to 0pt

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
 
B

bryan

What I have done with userforms and comboboxes has been pretty limited.
Is this column in the same combobox?
If so how do I create that cloumn?
 
D

Doug Robbins - Word MVP

Yes the column containing the ID would be in the same combobox. How are you
populating the combobox now as that will determine the best way of adding
the ID to the combobox.

See the following page of fellow MVP Greg Maxey's website :

http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

The same methods can be used for a combobox.

If that information is of use to you, please do consider contributing to the
maintenance of that website to ensure its continued availability.


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
 
J

Jean-Guy Marcil

bryan was telling us:
bryan nous racontait que :
What I have done with userforms and comboboxes has been pretty
limited. Is this column in the same combobox?
Yes.

If so how do I create that cloumn?
Click to expand...

Here is a simple example, create a userform with only one combobox to see
how it works:

===================
Private Sub UserForm_Initialize()

Dim i As Long

i = 0

With Me.ComboBox1
.ColumnCount = 2
.ColumnWidths = "40;80"
.BoundColumn = 2
.TextColumn = 2
For i = 0 To 2
.AddItem "ID_" & CStr(i + 1)
.List(i, 1) = "Name_" & CStr(i + 1)
Next
.ListIndex = 0
End With

End Sub
=========================

Here, the second column will display (.TextColumn property).
The default value will be the value from the second column (.BoundColumn
property).

If you do not want the user to see the ID, set the first value of
..ColumnWidths to 0 = "0, 120"
 
B

bryan

Here is how I am populating now. The strrel is the id I wish not to display:

strquery1 = "Select entity_table_id, isnull(First_Name,'')as FN,
isnull(Last_Name,'')as LN, isnull(Contact,'')as MI, " & _
"States.State_ID, rel_id " & _
"from Claimant_V cv left outer join " & _
"STATES on cv.State_ID = STATES.State_Row_ID " & _
"where cv.relation_sc like '%CL%' and cv.eff_end_date <>
cv.eff_start_date and cv.claim_id = '" + strcharID + "'"

Dim zd
Set zd = CreateObject("ADODB.Recordset")
zd.Open strquery1, objConn1, 2, 3
If Not zd.EOF Then
zd.MoveFirst

Do While Not zd.EOF
strty = Trim(zd("entity_table_id").Value)
strf = Trim(zd("FN").Value)
strL = Trim(zd("LN").Value)
strM = Trim(zd("MI").Value)
strrel = Trim(zd("rel_id").Value)



If strf = "" Then
ComboBox1.AddItem strrel + " " + strL
Else
ComboBox1.AddItem strrel + " " + strf + " " + strL
End If


zd.MoveNext

Loop
ComboBox1.ListIndex = 0
End If
 
B

bryan

A full of what I am doing now so that the strrel is returned for use later:
Sub Main()
........
Set UF = New UserForm1
UF.ClaimN = strcharID
UF.Show

strname = UF.ComboBox1.Text
.......
end sub
--------------------------------------------------
Public ClaimN As String

Private Sub UserForm_Activate()

strcharID = ClaimN

Set objConn1 = CreateObject("ADODB.Connection")
objConn1.Open "Provider=SQLOLEDB;Data
Source=xxxxx;Database=xxxxxxxxxx;UID=xxxxxxx;PWD=xx"
strquery1 = "Select entity_table_id, isnull(First_Name,'')as FN,
isnull(Last_Name,'')as LN, isnull(Contact,'')as MI, " & _
"States.State_ID, rel_id " & _
"from Claimant_V cv left outer join " & _
"STATES on cv.State_ID = STATES.State_Row_ID " & _
"where cv.relation_sc like '%CL%' and cv.eff_end_date <>
cv.eff_start_date and cv.claim_id = '" + strcharID + "'"

Dim zd
Set zd = CreateObject("ADODB.Recordset")
zd.Open strquery1, objConn1, 2, 3
If Not zd.EOF Then
zd.MoveFirst

Do While Not zd.EOF
strty = Trim(zd("entity_table_id").Value)
strf = Trim(zd("FN").Value)
strL = Trim(zd("LN").Value)
strM = Trim(zd("MI").Value)
strrel = Trim(zd("rel_id").Value)


If strf = "" Then
ComboBox1.AddItem strrel + " " + strL
Else
ComboBox1.AddItem strrel + " " + strf + " " + strL
End If

zd.MoveNext

Loop
ComboBox1.ListIndex = 0
End If

objConn1.Close
Set objConn1 = Nothing

If ComboBox1.ListCount = 0 Or ComboBox1.ListCount = 1 Then
Call CommandButton1_Click
End If

End Sub
-----------------------------------------------------------------
Private Sub CommandButton1_Click()

Me.Hide
End Sub
 
D

Doug Robbins - Word MVP

To add strRel into the second column of the ComboBox, use:

If strf = "" Then
ComboBox1.AddItem strrel + " " + strL
Else
ComboBox1.AddItem strrel + " " + strf + " " + strL
End If
ComboBox1.List(.ListCount - 1, 1) = strRel

You will need to have set the ColumnCount to 2, which can be done by using

ComboBox1.ColumnCount = 2

I would also suggest that you give a meaningful name to the ComboBox for
ease of reference/understanding of your code.



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
 
B

bryan

I have tried this and it will not run.
..Listcount needs a with statement, Listcount gets run time error.

I have also tried using Greg Maxey code and Jean-Guy code. With this
particular claim number there are 3 names that should appear. What I have
been getting is the last name listed 3 times.
Let's start over.
The code I have listed below works. The rel id and the name are both
displayed and returned for use in later sql statements. What I want is for
the rel id to NOT appear in the combobox but, still need retruned.

Sub getinfo()
...code..
strcharID = CStr(strCID)

'Claimant
'list claimants
Set UF = New UserForm1
UF.ClaimN = strcharID
UF.Show

strname = UF.ComboBox1.Text

pos2 = InStr(1, strname, " ")
strrelid = Mid(strname, 1, (pos2 - 1))
strname = Mid(strname, 10)
pos1 = InStrRev(strname, " ")
strCLN = Mid(strname, (pos1 + 1))
strCLN = Replace(strCLN, Chr(39), "")
strCFN = Mid(strname, 1, (pos1 - 1))

...code.. both the rel id and name are returned for further use.

USerform Code:
Public ClaimN As String

Private Sub UserForm_Activate()

strcharID = ClaimN




Set objConn1 = CreateObject("ADODB.Connection")
objConn1.Open "Provider=SQLOLEDB;Data
Source=CSCSQ3;Database=AC_DATA_PRD;UID=Sa;PWD=pcadmin28"
strquery1 = "Select entity_table_id, isnull(First_Name,'')as FN,
isnull(Last_Name,'')as LN, isnull(Contact,'')as MI, " & _
"States.State_ID, rel_id " & _
"from Claimant_V cv left outer join " & _
"STATES on cv.State_ID = STATES.State_Row_ID " & _
"where cv.relation_sc like '%CL%' and cv.eff_end_date <>
cv.eff_start_date and cv.claim_id = '" + strcharID + "'"

Dim zd
Set zd = CreateObject("ADODB.Recordset")
zd.Open strquery1, objConn1, 2, 3
If Not zd.EOF Then
zd.MoveFirst

Do While Not zd.EOF
strty = Trim(zd("entity_table_id").Value)
strf = Trim(zd("FN").Value)
strL = Trim(zd("LN").Value)
strM = Trim(zd("MI").Value)
strrel = Trim(zd("rel_id").Value)



If strf = "" Then
ComboBox1.AddItem strrel + " " + strL
Else
ComboBox1.AddItem strrel + " " + strf + " " + strL
End If


zd.MoveNext

Loop
ComboBox1.ListIndex = 0
End If

objConn1.Close
Set objConn1 = Nothing

If ComboBox1.ListCount = 0 Or ComboBox1.ListCount = 1 Then
Call CommandButton1_Click
End If

End Sub
Private Sub CommandButton1_Click()

Me.Hide
End Sub


Thanks for the help.
Bryan
 
B

bryan

Hey Jean-Guy,
I have tried this with my sql statment which loops to get all names and id's
to display. For this particular number passed in there are 3 names to appear.
I am getting the last one appearing 3 times. I want onlt the names to appear
for selection but, I need the name and id returned to main macro for further
use.
Help is greatly appreciated as I have limited working with userforms.
Thnaks,
Bryan

Here is my code:
Sub getinfo()
......code..
strcharID = CStr(strCID)

'Claimant
'list claimants
Set UF = New UserForm1
UF.ClaimN = strcharID
UF.Show

strname = UF.ComboBox1.Text

pos2 = InStr(1, strname, " ")
strrelid = Mid(strname, 1, (pos2 - 1))
strname = Mid(strname, 10)
pos1 = InStrRev(strname, " ")
strCLN = Mid(strname, (pos1 + 1))
strCLN = Replace(strCLN, Chr(39), "")
strCFN = Mid(strname, 1, (pos1 - 1))
....additional code.....
end sub
--------------------------------------------------------
Public ClaimN As String

Private Sub UserForm_Activate()

strcharID = ClaimN




Set objConn1 = CreateObject("ADODB.Connection")
objConn1.Open "Provider=SQLOLEDB;Data
Source=CSCSQx;Database=AC_DATA_xx;UID=xx;PWD=xxxxx"
strquery1 = "Select entity_table_id, isnull(First_Name,'')as FN,
isnull(Last_Name,'')as LN, isnull(Contact,'')as MI, " & _
"States.State_ID, rel_id " & _
"from Claimant_V cv left outer join " & _
"STATES on cv.State_ID = STATES.State_Row_ID " & _
"where cv.relation_sc like '%CL%' and cv.eff_end_date <>
cv.eff_start_date and cv.claim_id = '" + strcharID + "'"

Dim zd
Set zd = CreateObject("ADODB.Recordset")
zd.Open strquery1, objConn1, 2, 3
If Not zd.EOF Then
zd.MoveFirst

Do While Not zd.EOF
strty = Trim(zd("entity_table_id").Value)
strf = Trim(zd("FN").Value)
strL = Trim(zd("LN").Value)
strM = Trim(zd("MI").Value)
strrel = Trim(zd("rel_id").Value)



If strf = "" Then
ComboBox1.AddItem strrel + " " + strL
Else
ComboBox1.AddItem strrel + " " + strf + " " + strL
End If


zd.MoveNext

Loop
ComboBox1.ListIndex = 0
End If

objConn1.Close
Set objConn1 = Nothing

If ComboBox1.ListCount = 0 Or ComboBox1.ListCount = 1 Then
Call CommandButton1_Click
End If

End Sub
Private Sub CommandButton1_Click()

Me.Hide
End Sub
 
J

Jean-Guy Marcil

bryan was telling us:
bryan nous racontait que :
Hey Jean-Guy,
I have tried this with my sql statment which loops to get all names
and id's to display. For this particular number passed in there are 3
names to appear. I am getting the last one appearing 3 times. I want
onlt the names to appear for selection but, I need the name and id
returned to main macro for further use.
Help is greatly appreciated as I have limited working with userforms.
Thnaks,
Click to expand...

Do While Not zd.EOF
strty = Trim(zd("entity_table_id").Value)
strf = Trim(zd("FN").Value)
strL = Trim(zd("LN").Value)
strM = Trim(zd("MI").Value)
strrel = Trim(zd("rel_id").Value)



If strf = "" Then
ComboBox1.AddItem strrel + " " + strL
Else
ComboBox1.AddItem strrel + " " + strf + " " + strL
End If
Click to expand...

What you are doing here is adding one long string in one column.

If you want the ID# (strrel) to be part of the ComboBox, but hidden, you
need to use two columns.

Something like:

.AddItem strf & " " & strL
.List(i, 1) = strrel

Read my post again and try it just to see what is going on.

By the way, you should use the string concatenator "&" instead of "+" which
may be unpredictable if one of the element happens to be a number or a
variant type.
 
B

bryan

With this I am getting each listed twice. Should have 3 entries with this
number but, it is listing each twice.
Also the ComboBox1.ListIndex = 0 shows the fist strRel rather than the
first name in the combo box.
Lastly, What I return to the main macro needs to include the name and the
strRel id. How do I get the name and the rel id returned?

Public ClaimN As String
-------------------------------
Private Sub UserForm_Activate()

strcharID = ClaimN

Dim i As Long



Set objConn1 = CreateObject("ADODB.Connection")
objConn1.Open "Provider=SQLOLEDB;Data
Source=xxxxxx;Database=AC_DATA_xxx;UID=xxxx;PWD=xxxxxxxxxx"
strquery1 = "Select entity_table_id, isnull(First_Name,'')as FN,
isnull(Last_Name,'')as LN, isnull(Contact,'')as MI, " & _
"States.State_ID, rel_id " & _
"from Claimant_V cv left outer join " & _
"STATES on cv.State_ID = STATES.State_Row_ID " & _
"where cv.relation_sc like '%CL%' and cv.eff_end_date <>
cv.eff_start_date and cv.claim_id = '" + strcharID + "'"


Dim zd
Set zd = CreateObject("ADODB.Recordset")
zd.Open strquery1, objConn1, 2, 3
If Not zd.EOF Then
zd.MoveFirst

Do While Not zd.EOF
strty = Trim(zd("entity_table_id").Value)
strf = Trim(zd("FN").Value)
strL = Trim(zd("LN").Value)
strM = Trim(zd("MI").Value)
strRel = Trim(zd("rel_id").Value)
i = 0
With Me.ComboBox1
.ColumnCount = 2
.ColumnWidths = "80;0"
.BoundColumn = 2
.TextColumn = 2
For i = 0 To 2

If strf = "" Then
strNM = strL
Else
strNM = strf & " " & strL
End If

.AddItem strNM
.List(i, 1) = strRel

Next

End With

zd.MoveNext

Loop

End If
ComboBox1.ListIndex = 0

objConn1.Close
Set objConn1 = Nothing

If ComboBox1.ListCount = 0 Or ComboBox1.ListCount = 1 Then
Call CommandButton1_Click
End If

End Sub
--------------------------------------------------------------------------------
Private Sub CommandButton1_Click()

Me.Hide
End Sub
--------------------------------------------------------------------------------
 
J

Jean-Guy Marcil

bryan was telling us:
bryan nous racontait que :
With this I am getting each listed twice. Should have 3 entries with
this number but, it is listing each twice.
Click to expand...

I have a feeling that you did not actually try my code as I suggested and
tried to understand what it does.

If you did, then you apparently did not understand some of it... or I was
not clear enough.

So, one thing I did that you do NOT need to do is use this line:

For i = 0 To 2

I used that because, unlike you, in my example I did not have an actual
RecordSet and I did not feel like building one...

So, I needed a way to stoop the macro, so I figured that three loops would
be enough for you to see what is going on.

You actually control the number of loops by using:

Do While Not zd.EOF
 
B

bryan

I posted yesterday but, errored.
I seen the issue with listing twice, my apologies that post did not work.
I have used userforms but, basically to show info. Selection picked brought
back.


If I hide nothing I will get back the name and the id.
Since I am hiding the id, is there no way to bring this back to main macro?

Thanks,
Bryan


Apologies for not understanding
 
J

Jean-Guy Marcil

bryan was telling us:
bryan nous racontait que :
I posted yesterday but, errored.
I seen the issue with listing twice, my apologies that post did not
work. I have used userforms but, basically to show info. Selection
picked brought back.
Click to expand...

Sorry, but I have no idea what you mean here.
If I hide nothing I will get back the name and the id.
Since I am hiding the id, is there no way to bring this back to main
macro?
Click to expand...

Yes, as I stated, the ".List" property is a "Read" property as well as a
"Write" one, so you can read the content of the combobx with it, as in:

With Me.ComboBox1
myVariable = .List(.ListIndex, 0)
End With

to get the value of the item in the first column of the currently selected
item in the combobox.
Thanks,
Bryan


Apologies for not understanding
Click to expand...

You do not need to apologize if you tried my code and had problems
understanding it.
If that was the case, just ask specific question about the code I posted so
that I can help you understand it.

It is just that I got the feeling you did not try it as I suggested, i.e. by
creating a simple userform in a test document and run the code to see what
it does, as is.
 
B

bryan

When I select an item from the combo box, that info is returned to my main
macro via:
Set UF = New UserForm1
UF.ClaimN = strcharID
UF.Show
strname = UF.ComboBox1.Text

By having the id hidden I am only getting the name back.

I have this in my userform code which you supplied:
Private Sub CommandButton1_Click()

With Me.ComboBox1
myvariable = .List(.ListIndex, 0)
myvar = .List(.ListIndex, 1)
MsgBox myvariable
MsgBox myvar
End With

Me.Hide
End Sub

With this above code I am getting a run time error '381':
Could not get the list property. Invalid property array index.

I need the id hidden but, I also need it returned for further processing
Here is my whole userform code:
Public ClaimN As String

Private Sub UserForm_Activate()

strcharID = ClaimN

Dim i As Long
i = 0


Set objConn1 = CreateObject("ADODB.Connection")
objConn1.Open "Provider=SQLOLEDB;Data
Source=xxxx;Database=AC_DATA_xx;UID=xxxx;PWD=xxxxxxxx"
strquery1 = "Select entity_table_id, isnull(First_Name,'')as FN,
isnull(Last_Name,'')as LN, isnull(Contact,'')as MI, " & _
"States.State_ID, rel_id " & _
"from Claimant_V cv left outer join " & _
"STATES on cv.State_ID = STATES.State_Row_ID " & _
"where cv.relation_sc like '%CL%' and cv.eff_end_date <>
cv.eff_start_date and cv.claim_id = '" + strcharID + "'"


Dim zd
Set zd = CreateObject("ADODB.Recordset")
zd.Open strquery1, objConn1, 2, 3
If Not zd.EOF Then
zd.MoveFirst

Do While Not zd.EOF
strty = Trim(zd("entity_table_id").Value)
strf = Trim(zd("FN").Value)
strL = Trim(zd("LN").Value)
strM = Trim(zd("MI").Value)
strRel = Trim(zd("rel_id").Value)

With Me.ComboBox1
.ColumnCount = 2
.ColumnWidths = "80;0"
.BoundColumn = 2
.TextColumn = 2


If strf = "" Then
strNM = strL
Else
strNM = strf & " " & strL
End If

.AddItem strNM
.List(i, 1) = strRel



End With

zd.MoveNext

Loop

End If
'ComboBox1.ListIndex = 0

objConn1.Close
Set objConn1 = Nothing

If ComboBox1.ListCount = 0 Or ComboBox1.ListCount = 1 Then
Call CommandButton1_Click
End If

End Sub
Private Sub CommandButton1_Click()
With Me.ComboBox1
myvariable = .List(.ListIndex, 0)
myvar = .List(.ListIndex, 1)
MsgBox myvariable
MsgBox myvar
End With


Me.Hide
End Sub

Appreciate the help!
Bryan
 
J

Jean-Guy Marcil

bryan was telling us:
bryan nous racontait que :
When I select an item from the combo box, that info is returned to my
main macro via:
Set UF = New UserForm1
UF.ClaimN = strcharID
UF.Show
strname = UF.ComboBox1.Text
Click to expand...

Try:

With UF.Combobox1
strname = .List(.ListIndex,0) & " " & .List(.ListIndex, 1)
End With
By having the id hidden I am only getting the name back.

I have this in my userform code which you supplied:
Private Sub CommandButton1_Click()

With Me.ComboBox1
myvariable = .List(.ListIndex, 0)
myvar = .List(.ListIndex, 1)
MsgBox myvariable
MsgBox myvar
End With

Me.Hide
End Sub
Click to expand...

Why are you running code when the user clicks on the combobox?
With this above code I am getting a run time error '381':
Click to expand...

It means you are not setting up the columns/combobox properly.
Could not get the list property. Invalid property array index.

I need the id hidden but, I also need it returned for further
processing Here is my whole userform code:
Public ClaimN As String

Private Sub UserForm_Activate()
Click to expand...

You should place this code in the Initialize event, which runs only once,
when the Userform is created.
Activate runs every time the Userform is displayed, let's say after being
hidden and then shown again, for example.
strcharID = ClaimN

Dim i As Long
i = 0
Click to expand...

You set the counter to 0, good.
Set objConn1 = CreateObject("ADODB.Connection")
objConn1.Open "Provider=SQLOLEDB;Data
Source=xxxx;Database=AC_DATA_xx;UID=xxxx;PWD=xxxxxxxx"
strquery1 = "Select entity_table_id, isnull(First_Name,'')as FN,
isnull(Last_Name,'')as LN, isnull(Contact,'')as MI, " & _
"States.State_ID, rel_id " & _
"from Claimant_V cv left outer join " & _
"STATES on cv.State_ID = STATES.State_Row_ID " & _
"where cv.relation_sc like '%CL%' and cv.eff_end_date
<> cv.eff_start_date and cv.claim_id = '" + strcharID + "'"


Dim zd
Set zd = CreateObject("ADODB.Recordset")
zd.Open strquery1, objConn1, 2, 3
If Not zd.EOF Then
zd.MoveFirst

Do While Not zd.EOF
strty = Trim(zd("entity_table_id").Value)
strf = Trim(zd("FN").Value)
strL = Trim(zd("LN").Value)
strM = Trim(zd("MI").Value)
strRel = Trim(zd("rel_id").Value)

With Me.ComboBox1
.ColumnCount = 2
.ColumnWidths = "80;0"
.BoundColumn = 2
.TextColumn = 2
Click to expand...

With this here, as I stated before, you are setting the ID column to a value
of 0, thus hiding it, yet, because of .TextColumn = 2, you are telling the
compiler to display column 2, which you have just set to 0...

So, use :
.TextColumn = 1
If strf = "" Then
strNM = strL
Else
strNM = strf & " " & strL
End If

.AddItem strNM
.List(i, 1) = strRel
Click to expand...

See the "i" in the line above?
It never gets incremented in your code, so it is always 0.
This means that you re adding names in column 1, but rewriting the ID in
column 2 of the first item over and over.
When the loop is done, all the names are entered, but only the first name
has an ID, the ID of the last record, all others are Null, so when you try
to read the value of the second column, you get an error (Except for the
very first item).

As I explained before, you are running the loop with the "Do While" above,
based on the number of records in the RecordSet.

So, every time you add an item to the combobox (.AddItem), you have to
increment "i".

Add:

i = i + 1
 
J

Jean-Guy Marcil

bryan was telling us:
bryan nous racontait que :
Works great!
I appreciate all the help on this.............
Click to expand...

I took a while, but we got there!

Glad to see you got it working!
 
Top