I have a form with a List box on it. I would like to use the Office Assistant as an Option Group to fill in the list. Everything works except one thing. After a user selects an option, the Assistant disappears. I would like it to stay on the screen so the user can select another option if he wants to. He'll click Cancel on the Assistant to close the Assistant. All the code I write throws it into a loop and I have to end Access to get out.
So, how can I keep the Assistant on the Screen? My code follows:
Option Explicit
Option Compare Database
Dim ball As Balloon
Dim intRetValue As Integer
Sub playballon()
Begin_Again: Set ball = Assistant.NewBalloon
With ball
.BalloonType = msoBalloonTypeButtons
.Icon = msoIconAlertInfo
.Button = msoButtonSetCancel
.Heading = "Contrator Info Search"
.Text = "Please select a Search Type"
.Labels(1).Text = "Search by Company"
.Labels(2).Text = "Search by Name"
.Labels(3).Text = "Search by State"
intRetValue = .Show
End With
SetUpListBox
End Sub
Sub SetUpListBox()
If intRetValue = 1 Then
[By Search Type].RowSource = ""
[Search Text].Caption = "Select the Company to Search For "
[By Search Type].ColumnCount = 3
[By Search Type].ColumnWidths = "2 in;.25 in;1 in"
[By Search Type].BoundColumn = 3
[By Search Type].RowSource = "SELECT [Company], [State], [Name1] FROM Contractors WHERE (Not [Company] Is Null) ORDER BY [Company], State"
ElseIf intRetValue = 2 Then
[By Search Type].RowSource = ""
[Search Text].Caption = "Select the Name to Search For "
[By Search Type].ColumnCount = 3
[By Search Type].ColumnWidths = "1.5 in;2 in;.25 in"
[By Search Type].BoundColumn = 3
[By Search Type].RowSource = "SELECT [Name1], [Company], [State] FROM Contractors WHERE (Not [Name1] Is Null) ORDER BY [Name1], [state]"
ElseIf intRetValue = 3 Then
[By Search Type].RowSource = ""
[Search Text].Caption = "Select the State to Search For "
[By Search Type].ColumnCount = 3
[By Search Type].ColumnWidths = ".25 in;2 in;1 in"
[By Search Type].BoundColumn = 3
[By Search Type].RowSource = "SELECT [state],[company], [name1] FROM Contractors WHERE (Not state Is Null) ORDER BY [state],[company]"
End If
End Sub
Private Sub Form_Open(Cancel As Integer)
playballon
End Sub
So, how can I keep the Assistant on the Screen? My code follows:
Option Explicit
Option Compare Database
Dim ball As Balloon
Dim intRetValue As Integer
Sub playballon()
Begin_Again: Set ball = Assistant.NewBalloon
With ball
.BalloonType = msoBalloonTypeButtons
.Icon = msoIconAlertInfo
.Button = msoButtonSetCancel
.Heading = "Contrator Info Search"
.Text = "Please select a Search Type"
.Labels(1).Text = "Search by Company"
.Labels(2).Text = "Search by Name"
.Labels(3).Text = "Search by State"
intRetValue = .Show
End With
SetUpListBox
End Sub
Sub SetUpListBox()
If intRetValue = 1 Then
[By Search Type].RowSource = ""
[Search Text].Caption = "Select the Company to Search For "
[By Search Type].ColumnCount = 3
[By Search Type].ColumnWidths = "2 in;.25 in;1 in"
[By Search Type].BoundColumn = 3
[By Search Type].RowSource = "SELECT [Company], [State], [Name1] FROM Contractors WHERE (Not [Company] Is Null) ORDER BY [Company], State"
ElseIf intRetValue = 2 Then
[By Search Type].RowSource = ""
[Search Text].Caption = "Select the Name to Search For "
[By Search Type].ColumnCount = 3
[By Search Type].ColumnWidths = "1.5 in;2 in;.25 in"
[By Search Type].BoundColumn = 3
[By Search Type].RowSource = "SELECT [Name1], [Company], [State] FROM Contractors WHERE (Not [Name1] Is Null) ORDER BY [Name1], [state]"
ElseIf intRetValue = 3 Then
[By Search Type].RowSource = ""
[Search Text].Caption = "Select the State to Search For "
[By Search Type].ColumnCount = 3
[By Search Type].ColumnWidths = ".25 in;2 in;1 in"
[By Search Type].BoundColumn = 3
[By Search Type].RowSource = "SELECT [state],[company], [name1] FROM Contractors WHERE (Not state Is Null) ORDER BY [state],[company]"
End If
End Sub
Private Sub Form_Open(Cancel As Integer)
playballon
End Sub