copy data from a recordset to a common box vba access 2007

[JoseMarques]

I've sql database and a access front end.
In a form i'm trying to copy data from 3 distinct recordsets to a commonbox.
I'm using this code to copy the data from one recordset to the common box :

Set iconn = New ADODB.Connection
iconn.ConnectionString = string1
iconn.Open
Set rs = New ADODB.Recordset
str = "SELECT Nome FROM EntidadesGestoras Order By Nome;"
rs.Open str, iconn, adOpenDynamic, adLockOptimistic
With Me.Nome1
For j = .ListCount - 1 To 0 Step -1
.RemoveItem (lListIndex)
Next j
End With
Me.Nome1 = ""
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
i = 0
With Me.Nome1
Do While Not rs.EOF
strnome = rs!Nome
.AddItem strnome, Index:=i
i = i + 1
rs.MoveNext
Loop
End With
End If
rs.Close
Set rs = Nothing
iconn.Close
Set iconn = Nothing

I call this function for the 3 distinct recordsets.
Works fine with 32767 characters in the List but when it's more gives a error.

error:
Run time error 6015
combobox: Can't add this item, index is too large

Is there any way to expand the list to more characters?
If not how can I make the copy of the data from the 3 recordsets to the common box?

 

[SkipVought]

Why not build a temporary table?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JoseMarques]

I am using sql database and a front-end access. If I use a temporary table that will increase the time.
So I'm not interested in creating a temporary table!
Thanks any way!

 

[dhookom]

Can we assume you mean "combo box" rather than "common box"?
Why use a recordset? It seems like the simplest solution is to use a pass-through query as the row source of your combo box. Your recordset will never be more efficient than a p-t query.

Duane
Hook'D on Access
MS Access MVP

 

[JoseMarques]

Yes dhookom !!!

 

[JoseMarques]

dhookom i'm trying to do that using vba code can you help?

 

[dhookom]

How far did you get? Did you create a pass-through query? Did you set the SQL to "SELECT Nome FROM EntidadesGestoras Order By Nome"? Did you set this as the row source?

Duane
Hook'D on Access
MS Access MVP

 

[JoseMarques]

Yes but I have a problem when I try to use the clause where!
Besides I need to use at least 2 different queries in the same combo box because one of the queries is of different use.
I'm trying to do this using the vba code:

With Me.Nome1
.RowSourceType = "Table/Query"
.RowSource = query2
end with
...
With Me.Nome1
.RowSourceType = "Table/Query"
.RowSource = query1
end with

Don't gives compilation error but don't works.
I'm getting completely lost and out of ideas!
Help!!!

 

[dhookom]

What do you mean by "don't works"? Did you have the query names in quotes or is that your actual code? What is the SQL of the queries?

What determines the different row sources?

Duane
Hook'D on Access
MS Access MVP

 

[JoseMarques]

The Querys are this:

query1 = "SELECT Nome FROM EntidadesGestoras Order By Nome;"

query2 = "SELECT Nome FROM Técnicos Order By Nome;"

 

[dhookom]

JoseMarques,
I asked four questions and you answered one.

Duane
Hook'D on Access
MS Access MVP

 

[JoseMarques]

Data do not appear in combo box!

 

[dhookom]

You have now answered two of the four questions.

Duane
Hook'D on Access
MS Access MVP

 

[JoseMarques]

What determines the different row sources?
The tables:
- EntidadesGestoras
- Técnicos
Did you have the query names in quotes or is that your actual code?
The query names is in quotes

 

[dhookom]

I was wondering why you need to change the row source? What is the difference between the two (or more) tables?
I would set the Row Source Type while in design view so you don't have to code it. Then run code like:

   With Me.Nome1 
      .RowSource = "SELECT [Nome] FROM EntidadesGestoras Order By [Nome];"
   End With

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Use an UNION query as the RowSource.

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