zubin6220
asked on
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=t
cboQuickSearchItemB with record source
SELECT tblAssets.AssetID, tblAssets.chrDescription, [LastName] & ", " & [FirstName] AS Name FROM tblPersonnel INNER JOIN tblAssets ON tblPersonnel.PersonnelID=t
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_AfterU
Me!AssetID.SetFocus
If Not IsNull(Me!cboQuickSearchIt
DoCmd.FindRecord Me!cboQuickSearchItemA
End If
End Sub
In advance, thanks
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=t
cboQuickSearchItemB with record source
SELECT tblAssets.AssetID, tblAssets.chrDescription, [LastName] & ", " & [FirstName] AS Name FROM tblPersonnel INNER JOIN tblAssets ON tblPersonnel.PersonnelID=t
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_AfterU
Me!AssetID.SetFocus
If Not IsNull(Me!cboQuickSearchIt
DoCmd.FindRecord Me!cboQuickSearchItemA
End If
End Sub
In advance, thanks
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").
ASKER
yes, I have cmd controls for each set of data. Just not sure how to 'change the spec'.
can I please get an example
can I please get an example
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_After
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=t
& "WHERE tblAssets.chrDescription = '" & strSelection & "'" _
& " ORDER BY [LastName], [FirstName];"
Me.cboQuickSearchItem.RowS
End Sub
Private Sub cboChooseDescription_After
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=t
& "WHERE tblAssets.chrDescription = '" & strSelection & "'" _
& " ORDER BY [LastName], [FirstName];"
Me.cboQuickSearchItem.RowS
End Sub
The above code would populate the combo box named "cboQuickSearchItem" with the list that was selected in the combo box "cboChooseDescription."
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=t
cboNewQuickSearch with record source
SELECT tblAssets.AssetID, tblAssets.chrDescription, [LastName] & ", " & [FirstName] AS Name FROM tblPersonnel INNER JOIN tblAssets ON tblPersonnel.PersonnelID=t
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes, as long as you use Me.cboQuickSearchItemA.Req
Using a tab control with logical groupings of controls on your tabs also works wonders for real estate issues.
Hope this helps.
-Jim