I am trying to set up a form which uses a list box drawing its information from a number of queries. In order to select the query from which the list box sources the information it displays the user clicks on one of several option boxes causing the information shown in the listbox to change. The option group is called "SearchType" and the list box "srcresult". I have tried using the following code for the "after update" event of the option group:
Private Sub SearchType_AfterUpdate()
On Error GoTo SearchType_Err
If [SearchType].Value = 1 Then
[srcresult].RowSource = ""
[srcresult].ColumnCount = 4
[srcresult].ColumnWidths = "1 in;1 in;1 in;0 in"
[srcresult].BoundColumn = 4
[srcresult].RowSource = "SELECT Everything.[Personal Details_ID], Everything.Title, Everything.Surname, Everything.Expires FROM Everything;"
ElseIf [SearchType].Value = 2 Then
[srcresult].RowSource = ""
[srcresult].ColumnCount = 4
[srcresult].ColumnWidths = "1.5 in;.8 in;.7 in;0 in"
[srcresult].BoundColumn = 4
[srcresult].RowSource = "src_surname"
ElseIf [SearchType].Value = 3 Then
[srcresult].RowSource = ""
[srcresult].ColumnCount = 3
[srcresult].ColumnWidths = "1 in;1.1 in;.9 in"
[srcresult].BoundColumn = 1
[srcresult].RowSource = "src_expiry"
End If
Exit Sub
End Sub
Only problem is that when I click on one of the option boxes I then get presented with the following error message:
"The expression After Update you entered as the event property setting produced the following error: A problem occurred while MOC Admin 2.1 was communicating with the OLE server or ActiveX control.
*The function may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]
*There may have been an error evaluating the function, event or macro."
There's bound to be something blindingly obvious here but I can't see it. I'm working from a template and not very experienced with VB developing. Any ideas? Anything else I need to mention? Thanks (in advance...)
Private Sub SearchType_AfterUpdate()
On Error GoTo SearchType_Err
If [SearchType].Value = 1 Then
[srcresult].RowSource = ""
[srcresult].ColumnCount = 4
[srcresult].ColumnWidths = "1 in;1 in;1 in;0 in"
[srcresult].BoundColumn = 4
[srcresult].RowSource = "SELECT Everything.[Personal Details_ID], Everything.Title, Everything.Surname, Everything.Expires FROM Everything;"
ElseIf [SearchType].Value = 2 Then
[srcresult].RowSource = ""
[srcresult].ColumnCount = 4
[srcresult].ColumnWidths = "1.5 in;.8 in;.7 in;0 in"
[srcresult].BoundColumn = 4
[srcresult].RowSource = "src_surname"
ElseIf [SearchType].Value = 3 Then
[srcresult].RowSource = ""
[srcresult].ColumnCount = 3
[srcresult].ColumnWidths = "1 in;1.1 in;.9 in"
[srcresult].BoundColumn = 1
[srcresult].RowSource = "src_expiry"
End If
Exit Sub
End Sub
Only problem is that when I click on one of the option boxes I then get presented with the following error message:
"The expression After Update you entered as the event property setting produced the following error: A problem occurred while MOC Admin 2.1 was communicating with the OLE server or ActiveX control.
*The function may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]
*There may have been an error evaluating the function, event or macro."
There's bound to be something blindingly obvious here but I can't see it. I'm working from a template and not very experienced with VB developing. Any ideas? Anything else I need to mention? Thanks (in advance...)