populate combobox using vba and ado

[agrawal.solutions]

Hello Friends
I am asking a very silly question but i dont find any solution fo
this..
I am selectiong a recordset and want to populate a combobox where id
would be inviseble and the content would.
I am getting the recordset and the no of record but then i am unble to
populate the combobox.
I have already tried all the function starting form
for each x in ....
and while...wend and do....loop
I am providing my code here

Private Sub cmb_pc_GotFocus()

Dim rs As ADODB.Recordset
Dim conn As ADODB.Connection
Dim sqlQr5 As String 'Our SQL query

Set conn = CurrentProject.Connection 'Access connection
Set rs = CreateObject("ADODB.Recordset")
sqlQr5 = " SELECT dbo_ProfitCenter.Profit_Center_Code,
dbo_ProfitCenter.Profit_Center "
sqlQr5 = sqlQr5 & "FROM dbo_ProfitCenter "
sqlQr5 = sqlQr5 & "ORDER BY dbo_ProfitCenter.Profit_Center "

rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open sqlQr5, conn
Value = rs.RecordCount
If Not rs.EOF Then
Do
*#*#*#*#*#*#*#*#*#*#*i want code for this place
rs.MoveNext
Loop Until rs.EOF
my combox name is "cmb_pc"
I tried additem but it dont works in vba
Any help would be great

[Lyle Fairfield]

You could use GetString() and RowSource. I like this because it's easy
to modify the RowSource (a string) in code; This is an example of how I
have done this. It's more complicated than needs be for most cases but
you can see what I did although it does not show modifying the
RowSource string after creating it.

Private Sub Form_Load()
Dim TSQL As String
Dim ID As Long
TSQL = "SELECT fldSchoolID, fldSchoolName FROM dbo.tblSchools ORDER
BY fldSchoolName"
ID = LoginID()
If ID <> 0 Then
TSQL = Replace(TSQL, "ORDER BY", "WHERE fldSchoolID = " & ID &
" ORDER BY")
End If
lstSchools.RowSource =
CurrentProject.Connection.Execute(TSQL).GetString( , , ",", ",")
lstPrograms.RowSource = CurrentProject.Connection.Execute("SELECT
fldProgramID, fldProgramName FROM dbo.tblPrograms ORDER BY
fldProgramName").GetString(, , ",", ",")
' snips
End Sub

[dev.amit]

My problem is not solved !

[Garth Harvey]

If I have got the idea right, you have a table Profitcenter with a code
field and name field and you want to populate a combo box with them.
Before you loop through the table set the rowsource to nothing and then
get the pairs of fields from each record in the table.

combo.rowsource = ""
rs.movefirst
while not rs.eof
combo.rowsource = combo.rowsource & rs.fields("1stfield") & ";" &
rs.fields("2ndfield") & ";"
rs.movenext
wend

This should populate your rowsource with pairs of fields assuming you
have set numberofcolumns to 2 and set the widths to 0 and something and
set bound column to 1

Hope that helps?



*** Sent via Developersdex http://www.developersdex.com ***

[Larry Linson]

"dev.amit" <ag***************@gmail.com> wrote

My problem is not solved !

At best, with all the information laid out in detail, remote debugging is
not easy. With uninformative responses such as this, you make it either much
more difficult, or impossible, for those who are trying to assist you.

Larry Linson
Microsoft Access MVP

[David W. Fenton]

"Larry Linson" <bo*****@localhost.not> wrote in
news:qvmUf.3651$4N1.2823@trnddc06:

"dev.amit" <ag***************@gmail.com> wrote

My problem is not solved !

At best, with all the information laid out in detail, remote
debugging is not easy. With uninformative responses such as this,
you make it either much more difficult, or impossible, for those
who are trying to assist you.

My question is:

Why would anyone use ADO to populate a combobox? I've never needed
to do that, so can't quite see why I should attempt to wade through
the code to figure out why it's not working. Use SQL to populate
your combobox and be done with it.

In any event, it seemed like the main problem had nothing to do with
the rowsource, but with a failure to understand the basic of Access
combo boxes, where you bind a column to a field and hide that value
by setting that column's width to 0.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[dev.amit]

Sorry Grath After ur help also still i am no getting the sintax right
it is not working properly
if u can make it more specific i would be happy
I am able to select the rows to record sheet
I am able to populate if i am taking single value at a time but unable
to populate both id and its content
Please also specify how i would get the id if i choose one form the
combobox
Take Care
Thanks Dear
Garth Harvey wrote:

If I have got the idea right, you have a table Profitcenter with a code
field and name field and you want to populate a combo box with them.
Before you loop through the table set the rowsource to nothing and then
get the pairs of fields from each record in the table.

combo.rowsource = ""
rs.movefirst
while not rs.eof
combo.rowsource = combo.rowsource & rs.fields("1stfield") & ";" &
rs.fields("2ndfield") & ";"
rs.movenext
wend

This should populate your rowsource with pairs of fields assuming you
have set numberofcolumns to 2 and set the widths to 0 and something and
set bound column to 1

Hope that helps?



*** Sent via Developersdex http://www.developersdex.com ***

[dev.amit]

Dear Mr. David
You might be right why anyone take so much pain to populate a combo by
using code.
We can do it very easily using access queary.
But the problem i am having is the performance
I am dealing with huge database from sqlserver and oracle
simultaneously
for the better performance shake i need it
Love
Amit

[GarthLeicester]

It does seem that you are not familiar with the properties of a combo
box. the columncount must be 2, the columnwidths should look like
0cm;2cm (or inches) and the boundcolumn should be 1. The rowsourcetype
needs to be valuelist.
It has already been pointed out that this is a big hammer for a small
nail.
Why not set the rowsource type to table/query and put the sql in the
rowsource.
Garth
*** Sent via Developersdex http://www.developersdex.com ***

[dev.amit]

Thanks Dear
But i want to run it from the vba
and i am not having problem in the combox properties
i am having problem in the vba syntex
after having the recordset and then putting it into the combobox.
Take cate
love
Amit

[David W. Fenton]

"dev.amit" <ag***************@gmail.com> wrote in
news:11**********************@v46g2000cwv.googlegr oups.com:

You might be right why anyone take so much pain to populate a
combo by using code.
We can do it very easily using access queary.
But the problem i am having is the performance
I am dealing with huge database from sqlserver and oracle
simultaneously
for the better performance shake i need it

Populating a combo box from a "huge database" sounds problematic.
How many items are you trying to put in your combo box? Anything
over 100 is user-hostile, and I couldn't imagine why 100 records
would be slow, no matter how you were retrieving the data.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"dev.amit" <ag***************@gmail.com> wrote in
news:11*********************@j33g2000cwa.googlegro ups.com:

But i want to run it from the vba
and i am not having problem in the combox properties
i am having problem in the vba syntex
after having the recordset and then putting it into the combobox.

You're aware that there's a limit to the length of the string you
can place in the rowsource via the method you're using? I believe
it's 2048 characters.

You may want to look into writing a function to populate your
listbox, but if you're having trouble with doing it this way, you'll
find the function approach even more mystifying.

I think that Garth was right in saying that you don't seem to
understand the basic properties of the combo box if you're having
problems getting the data to display correctly.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Lyle Fairfield]

You're populating your combo box from huge databases in MS-SQL and in
Oracle simultaneously?

Performance is a problem?

You don't mention this in your intitial post?

You know diddley-squat about ADO (evident from your code) but you have
decided that loading your Combo-Box from an ADO recordset will help
with this problem?

You dismiss the first attempt to help you with: "My problem is not
solved !" and no further information?

May I suggest that you post your question to alt.painless.lobotomies? I
believe they can help you over there.

Hope this helps!

[Lyle Fairfield]

BTW this works for me in Access 2003 (as does the first solution I gave
you when the RowSource is not huge!):

Dim r As ADODB.Recordset
Set r = New ADODB.Recordset
With r
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.Open "SELECT Employees.EmployeeID, LastName & ', ' & FirstName AS
Name FROM Employees ORDER BY Employees.LastName, Employees.FirstName"
End With
Set Me.cboReportsTo.Recordset = r

[dev.amit]

Thanks to all of u.
I dont need to populate the combo form the code.
The client is now saying the performance is fine.
Thanks all to u.
TakeCare
Love
Amit

[RoyVidar]

David W. Fenton wrote in message
<Xn**********************************@127.0.0.1> :

You're aware that there's a limit to the length of the string you
can place in the rowsource via the method you're using? I believe
it's 2048 characters.

I think that's the 2000 version, and prior versions.

Starting with the 2002 version, I think the limit is 32 750 characters.

But who would want to use that much ;-)

--
Roy-Vidar

[Bri]

RoyVidar wrote:

David W. Fenton wrote in message
<Xn**********************************@127.0.0.1> :

You're aware that there's a limit to the length of the string you
can place in the rowsource via the method you're using? I believe
it's 2048 characters.

I think that's the 2000 version, and prior versions.

Starting with the 2002 version, I think the limit is 32 750 characters.

But who would want to use that much ;-)

That's a lot of characters. I have had problems with hitting the 2048
character limit a few times on the RecordSource property. I have a very
comprehensive search form that builds the SQL string dynamically for
finding the results. If the user picks too many search terms in the
criteria, the SQL string can exceed 2048 characters as adding a new
search term usually means adding in a related Table with a Join and the
additional WHERE criteria.

--
Bri