Unbound vs. Bound Combo boxes

[patman06]

Could one off you fine people that knows a lot more than I do direct me to some information on the pros and cons of using unbound and bound combo boxes. And when to use them? I have been spending a lot of time reading these forums but feel like I have a major gap in the fundamentals of Access.

 

[Pat Hartman]

Unbound combos are used primarially for searching and bound combos are used for updating. A single form will frequently have both a bound CustomerID combo and an unbound one. The bound control is used when you are adding a record and the unbound one is used when you want to navigate to a specific customer record.

 

[Simon_MT]

I generally use unbound combi-boxes to be more efficient and then call , for example:

Function LookupClientsCategory()

    With Screen.ActiveControl
        .RowSource = "SELECT [Clients Category].Category, [Clients Category].[Category Desc] FROM [Clients Category] ORDER BY [Clients Category].Category;"
        Call ListDisplay
    End With

End Function

Simon

 

[Pat Hartman]

Having embedded SQL strings in VBA is LESS efficient since it requires Access to recalculate the query's access plan each time it runs whereas, the access plan for a querydef is calculated the first time it is used and then saved for later use. If you use a lot of embedded SQL, you may also see database bloat since it takes a lot of workspace to calculate how best to retrieve data and Access doesn't recover used workspace unless you compact.

Also, user written code to loop through a recordset and load the item array will be slower than the internal Access code. So I wouldn't call unbound RowSources more efficient. I would call them less efficient.

I also think the OP was asking about the ControlSource rather than the RowSource. But since he didn't specify, there is no way to really know.

 

[patman06]

I was referring to the Control Source. I have seen both bound and unbound in snippets of code but without the context of the rest of the code it is hard to figure out. I have continued with the unbound to look up data and bound to change data.
Thanks for the confirmation.