Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


combo with multible record sources

Posted on 2005-04-12
Medium Priority
Last Modified: 2008-03-17
Hello experts,
Trying to save a little form 'real estate'.  I have several combo boxes with different record sources.  Is it possible to use the same combo for each one, and using different record sources and combo name?

cboQuickSearchItemA with record source
SELECT tblAssets.AssetID, tblAssets.chrDescription, [LastName] & ", " & [FirstName] AS Name FROM tblPersonnel INNER JOIN tblAssets ON tblPersonnel.PersonnelID=tblAssets.PersonnelID WHERE (((tblAssets.chrDescription)="aaa")) ORDER BY [LastName] & ", " & [FirstName];

cboQuickSearchItemB with record source
SELECT tblAssets.AssetID, tblAssets.chrDescription, [LastName] & ", " & [FirstName] AS Name FROM tblPersonnel INNER JOIN tblAssets ON tblPersonnel.PersonnelID=tblAssets.PersonnelID WHERE (((tblAssets.chrDescription)="bbb")) ORDER BY [LastName] & ", " & [FirstName];

Seems like I saw this mentioned in earlier posts but could not find it.  
If this is possible, request procedure to accomplish this or at least get me in the general direction.

This is the after update event that populates cboQuickSearchItemA (ItemB similar):
          Private Sub cboQuickSearchItemA_AfterUpdate()
    If Not IsNull(Me!cboQuickSearchItemA) Then
        DoCmd.FindRecord Me!cboQuickSearchItemA
    End If
End Sub

In advance, thanks
Question by:zubin6220
LVL 66

Expert Comment

by:Jim Horn
ID: 13762357
Hi zubin6220,

Yes, as long as you use Me.cboQuickSearchItemA.Requery after re-assigning your recordsource, handling the .ControlSource (much better if it is unbound) and handle the cosmetics of your form so you don't confuse the <expletive deleted> out of your users.

Using a tab control with logical groupings of controls on your tabs also works wonders for real estate issues.

Hope this helps.

Expert Comment

ID: 13762637
You can use the same combo box by changing the specification for it's record source, but it seems you still need another control to give the user the option for which set of data they want to search (e.g. "aaa" or "bbb").  

Author Comment

ID: 13763248
yes, I have cmd controls for each set of data.  Just not sure how to 'change the spec'.
can I please get an example
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.


Expert Comment

ID: 13763936
As i said earlier, you need some way for the user to select which set of data they want in the combo.  Let's assume you have another combo box with the values "aaa", "bbb", "ccc", etc.  When the user clicks on one of them, you could run code that is similar to this:

Private Sub cboChooseDescription_AfterUpdate()
    Dim strSQL As String
    Dim strSelection As String
    strSelection = Me.cboChooseDescription
    strSQL = "SELECT tblAssets.AssetID, tblAssets.chrDescription, " _
           & "[LastName] & ', ' & [FirstName] AS Name " _
           & "FROM tblPersonnel INNER JOIN tblAssets " _
           & "ON tblPersonnel.PersonnelID=tblAssets.PersonnelID " _
           & "WHERE tblAssets.chrDescription = '" & strSelection & "'" _
           & " ORDER BY [LastName], [FirstName];"
    Me.cboQuickSearchItem.RowSource = strSQL
End Sub

Expert Comment

ID: 13763965
The above code would populate the combo box named "cboQuickSearchItem" with the list that was selected in the combo box "cboChooseDescription."
LVL 17

Expert Comment

ID: 13765040
If I understand you, how about:

cboNewQuickSearch with record source

SELECT tblAssets.AssetID, tblAssets.chrDescription, [LastName] & ", " & [FirstName] AS Name FROM tblPersonnel INNER JOIN tblAssets ON tblPersonnel.PersonnelID=tblAssets.PersonnelID WHERE (tblAssets.chrDescription="aaa") AND (tblAssets.chrDescription="bbb")  ORDER BY [LastName] & ", " & [FirstName];

Expert Comment

ID: 13765931
No, that won't work.  For one thing, that SQL statement would return no records; chrDescription can't be both "aaa" AND "bbb".  It will be one or the other.

I was trying to say that you can do your task (if i understand it correctly) with two combo boxes: one to list the chrDescriptions ("aaa" or "bbb", etc) and one to list the other stuff (Name, etc).  The list of the second combo would be determined by the choice made in the first combo.

Author Comment

ID: 13769842
This is not a case of one combo opening another.  I have separate command buttons for each calling separate combo boxes of course with their respective record sources.  Want my question was, was to be able to use just one combo instead of multible combos that are called when item command button is clicked.

Accepted Solution

med39 earned 1200 total points
ID: 13773032
The code that i gave you will accomplish your goal.  You can use one combo box.  A combo box has a RowSource property, which can be an SQL statement.  You want to change the SQL statement depending on which "chrDescription" the user selects.  Then put the SQL statement into the RowSource property for the combo box.  This is the statement that does that:

Me.cboQuickSearchItem.RowSource = strSQL

The strategy, again, is to manipulate the SQL statement that you will push into the RowSource.


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question