Combo Box / Record Source

[Daryl]

I've written code that allows user to create a new record source for my form based on the combo box selection. It works and here’s the code:

Private Sub cboState_AfterUpdate()
On Error GoTo Err_cboState_AfterUpdate
'Purpose: Change the form's Recordsource to only records from the selected state
Dim sSQL As String
Dim bWasFilterOn As Boolean
'Save the FilterOn state. (It's lost during RecordSource change.)
bWasFilterOn = Me.FilterOn
'Change the RecordSource
If IsNull(Me.ActiveControl) Then
If Me.RecordSource <> "tblForms" Then
Me.RecordSource = "tblForms"
End If
Else
sSQL = "SELECT tblForms.* FROM tblForms " & _
"INNER JOIN tblFormValidity ON (tblForms.Form_vdt = tblFormValidity.Form_vdt) " & _
"AND (tblForms.Form_nm = tblFormValidity.Form_nm) " & _
"WHERE (((tblFormValidity.State)=[Forms]![frmGAIFormsInformation]![cboState]))"
Me.RecordSource = sSQL
End If
'Apply the filter again, if it was on
If bWasFilterOn And Not Me.FilterOn Then
Me.FilterOn = True
End If
Exit_cboState_AfterUpdate:
Exit Sub
Err_cboState_AfterUpdate:
MsgBox Err.Number & ": " & Err.Description, vbInformation, Me.Module.Name & " .cboState_AfterUpdate"
Resume Exit_cboState_AfterUpdate
End Sub

Here’s where I need help. I added a new item for the combo box selection (there are no records relating to it with the hopes of restoring my original record source. I’ve tried adding the following code, which hasn’t helped, but I think it’s in the right direction.

'Check if cboState is a fictitious state
If [Forms]![frmGAIFormsInformation]![cboState] = "MU" Then
Me.RecordSource = "tblForms"
End If

Any help is greatly appreciated.

 

[Daryl]

In case anyone is interested, I solved it! Here's the new code I had to write before the Else statement. As you can see, it needed an ElseIf statement.

If Me.ActiveControl = "MU" Then
If Me.RecordSource <> "tblForms" Then
Me.RecordSource = "tblForms"
End If
ElseIf Me.ActiveControl = Null Then
If Me.RecordSource <> "tblForms" Then
Me.RecordSource = "tblForms"
End If

Daryl

 

[Fornatian]

This would be slightly quicker:

If Me.ActiveControl = "MU" OR Me.ActiveControl = Null Then
If Me.RecordSource <> "tblForms" Then
Me.RecordSource = "tblForms"
End If
End If

 

[Daryl]

Thanks for the suggestion but I have trouble getting it to work some of the time. If I comment out the ElseIf code (including the EndIf) everthing works fine. But when I delete the ElseIf code (including the EndIf) I get an 'Argument Not Optional' error message. Any idea what may cause this?