Populate recordsource of combobox with query result 1

[MA04]

Hi all,

I want to populate a combobox recordsource with a query result, by using recordsets. I have done this using a query but is too slow.

Me.AddressCombobox.RowSourceType = "Table/Query"
Me.AddressCombobox.RowSource = "SELECT DISTINCTROW [Query3].[field1] FROM [Query3];"

The user enters a code in one unbound textbox and then the combobox is populated with results. I want to be able to do this with recordsets, thanks in advance for any help.

M-.

 

[Crowley16]

what makes you think a recordset would be faster? the speed will depend on exactly what you're pulling out, i.e. query3, so if the recordset is pulling out the same info in the same way, it'll take just as long, if not longer...

if you really wanted to use a recordset, then you would have to set your rowsource to "value List" and then populate the list by stepping through the recordset and adding each item with combobox.addItem...

--------------------
Procrastinate Now!

 

[MA04]

Hi,

Thanks for the reply, I have been trying to use recordsets to build the rowsource, this is what i have so far:

strSql = "Select A1 From Address Where A4 = '" & Forms!DOMESTIC!EnterPostcode & "' ;"
' Open recordset
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset(strSql, dbOpenForwardOnly)
' Test whether any records were returned
     If rs.RecordCount > 0 Then
        ' Go to the first record
        Do While Not rs.EOF
           ' If we're not at the last record go to the next one
           strSql = rs(0)
               If Not rs.EOF Then
                  rs.MoveNext
               End If
        Loop
        
        Me.AddressCombo.RowSourceType = "Value List"
        Me.AddressCombo.RowSource = strSql
    Else
' No records matched
        Me.AddressCombo.RowSource = ""
    End If
' Destroy all object variables
    rs.Close
    Set rs = Nothing
    Set db = Nothing

At the moment the code only displays one record, not all, any ideas?

M-.

 

[Crowley16]

lol...

put a addresscombo.additem rs.fields("A1") in your do loop...

also, you don't need to check for .eof inside the loop, that's what the do bit is for...

--------------------
Procrastinate Now!

 

[MA04]

Hi,

Thanks again but I get an error when I enter addresscombo.additem rs.fields("A1") inside the loop, i get error compile error: method or data member not found, highlighting the additem part.

Any ideas?

M-.

 

[Crowley16]

are you sure addresscombo is a combobox? it's name is spelt correctly? you're not referencing another control on another form?

--------------------
Procrastinate Now!

 

[MA04]

AddressCombo is a combobox and is spelt correctly and I am not referencing another control on another form. I am using access 97 could that attribute, although on the help files additem method is listed.

i.e. syntax
expression.AddItem(Text, Index)

expression . An expression that returns a CommandBarComboBox object.

M-.

 

[PHV]

And what about this ?
Me.AddressCombobox.RowSourceType = "Table/Query"
Me.AddressCombobox.RowSource = "Select A1 From Address Where A4 = '" & Forms!DOMESTIC!EnterPostcode & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MA04]

Thanks PH, the thing is because my form is unbound which it needs to be for other operation, using a query to build the recordsource of the combobox is still a bit slow. I think what Crowley16 suggested using recordsets and making the combobox record source type a "Value list" will speed up the process. I will try to look for a solution through this, any help appreciated.

Thanks in advance,

M-.

 

[TheAceMan1]

How are ya MA04 . . . . .

In order of [blue]speed of execution[/blue] in Microsoft Access we have:
[ol][li]Compiled, optimized Query.[/li]
[li]Noncompiled, nonoptimized Query.[/li]
[li]SQL in VBA (has to be parsed then executed, and is never optimized).[/li]
[li]VBA[/li][/ol]
Note: In the above, [blue]Query[/blue] are those you setup in the [blue]Query Window[/blue] of the database (and no other).

[purple]The point is: By attempting recordset your already going downscale in speed![/purple]

The fastest execution you'll achieve will be with an [blue]Optimized Query[/blue] as the RowSource of the combobox:

[blue]Me.AddressCombobox.RowSource = "[purple][b]QueryName[/b][/purple]"[/blue]

I believe it was [blue]Crowley16[/blue] who touched on what appears to be the real problem . . . . [blue]Query3[/blue] . . . which is a subquery in the SQL. Getting this optimized or more efficient [blue]appears to be your only shot at increasing speed.[/blue] So, unless its proprietary, post the SQL of Query3 so we can have a look.

If you insist on being disappointed by recordset, try this:

[blue]   Dim strSQL As String, rs As Recordset, Pack As String

   Set db = DBEngine(0)(0)
   strSQL = "Select A1 " & _
            "From Address " & _
            "Where A4 = '" & Forms!DOMESTIC!EnterPostcode & "' ;"
   Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)
   
   If Not rs.BOF Then
      Do
         If Pack <> "" Then
            Pack = Pack & ";" & rs(0)
         Else
            Pack = rs(0)
         End If
         
         rs.MoveNext
      Loop Until rs.EOF
      
      Me.AddressCombo.RowSourceType = "Value List"
      Me.AddressCombo.RowSource = Pack
   Else
      Me.AddressCombo.RowSource = ""
   End If
   
   Set rs = Nothing
   Set db = Nothing[/blue]

See Ya! . . . . . .

 

[MA04]

Thanks for that Ace, sorry for the delay I just read the thread, i ended up using the query route of building the rowsource. I speeded up the second part of my process which compensated for it. which was that when the rowsource gets populated the user selects one option and 4 other textboxes get populated with corresponding fields in that record, I created 4 columns in the rowsource and then numbered them to their corresponding textboxes.

To finalise recordsets did not make a great impact, infact the above code Ace provided does work but is about the same speed as the query route. Sometimes the simple route is the best option, this i think was one of them.

Thanks for all the help guys,
M-.

 

[TheAceMan1]

MA04 . . . . .

Realize . . . you won't really see the differences in speed until you get into [blue]larger record counts[/blue]. So as your data grows [blue]remember this![/blue]

See Ya! . . . . . .