combo with multible record sources

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?
Example:

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()
    Me!AssetID.SetFocus
    If Not IsNull(Me!cboQuickSearchItemA) Then
        DoCmd.FindRecord Me!cboQuickSearchItemA
    End If
End Sub

In advance, thanks
zubin6220Asked:
Who is Participating?
 
med39Commented:
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.

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
-Jim
0
 
med39Commented:
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").  
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
zubin6220Author Commented:
yes, I have cmd controls for each set of data.  Just not sure how to 'change the spec'.
can I please get an example
0
 
med39Commented:
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
0
 
med39Commented:
The above code would populate the combo box named "cboQuickSearchItem" with the list that was selected in the combo box "cboChooseDescription."
0
 
ArjiCommented:
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];
0
 
med39Commented:
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.
0
 
zubin6220Author Commented:
Med39,
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.