Checking for Null & Select Case

[lmcc007]

I have a form called fSearchforCompany with two unbound fields. After
selecting a field from lstQuickSearch (list box) and tabbing to txtSearchBox
to enter data, the correct form should open with the text from txtSearchBox
entered into the designated field. But, first the code should check for null,
and if Null, open the correct form.

The problems are the code is not doing anything on Null and when there is
data it is not selecting the data.

The two fields are:
.....lstQuickSearch
.....ControlSource is empty
.....Row Source = 1;"Company ID";2;"Company Name";3;"Date of Event";4;"Event Type"
.....Row Type = Value List[/INDENT]


.....txtSearchBox
.....Control Source is empty

After Update Event code is listed below:

Private Sub txtSearchBox_AfterUpdate()

On Error GoTo ErrorHandler

Select Case Me.lstQuickSearch.Value

Case 1:

If IsNull(txtSearchBox) Then

DoCmd.OpenForm ("[frmListforCompanyID")
DoCmd.GoToControl ("cboSearchBox")​

Else

DoCmd.OpenForm ("frmListforCompanyID")
DoCmd.GoToControl ("cboSearchBox")
Forms!frmListforCompanyID!cboSearchBox.ControlSource =
txtSearchBox​

End If​

Case 2:

If IsNull(txtSearchBox) Then

DoCmd.OpenForm ("frmListforCompanyName")
DoCmd.GoToControl ("cboSearchBox")​

Else

DoCmd.OpenForm ("frmListforCompanyName")
DoCmd.GoToControl ("cboSearchBox")
Forms!frmListforCompanyName!cboSearchBox.ControlSource =
txtSearchBox​

End If​

Case 2:

If IsNull(txtSearchBox) Then

DoCmd.OpenForm ("fdlgEventDetail")
DoCmd.GoToControl ("txtEventDate")​

Else

DoCmd.OpenForm ("fdlgEventDetail")
DoCmd.GoToControl ("txtEventDate")
Forms!fdlgEventDetail!txtEventDate.ControlSource =
txtSearchBox​

End If​

Case Else

MsgBox "Invalid selection", vbExclamation​

Exit Sub
End Select​

CleanUpAndExit:
Exit Sub

ErrorHandler:
Call MsgBox("An error was encountered" & vbCrLf & vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Error Number: " & Err.Number, , "Error")
Resume CleanUpAndExit

End Sub
​

Thanks for any help!

 

[stopher]

If the user does not enter anything into txtSearchBox then the AfterUpdate won't fire hence no code is running when txtSearchBox is null. You should consider using a different event e.g. the On Exit event for txtSearchBox. Or maybe you could have a button.

The second problem is that you are setting cboSearchBox.ControlSource. You should be setting cboSearchBox.value. But this will only make the combo box on your 2nd form be set to the value. It won't apply any filtering or go to a given record. Is that what you want to do? If so then use the filter arguement of the OpenForm function to open the form and go to a specific record (or filter)

If your only ambition is to set the value of the combo in the 2nd for then personally I would not rely on the code from another form that no longer has the focus updating fields on a form that does have the focus. Instead I would use OpenArgs to pass values to the form when I open it:

DoCmd.OpenForm "frmListforCompanyID", , , , , , Me.txtSearchBox

Then in the On Open event for the form I'm opening, I would place:

me.cboSearchBox=me.OpenArgs

In full I would expect your code to be as follows:
In the On Exit event of txtSearchBox:

If Me.lstQuickSearch >= 1 And Me.lstQuickSearch <= 3 Then
    DoCmd.OpenForm "frmListforCompanyID", , , , , , Nz(Me.txtSearchBox, "")
Else
    MsgBox "Invalid selection", vbExclamation
End If

And in the On Open events for each of the 2nd forms (the fdlgEventDetail will be slightly different of course):

Me.cboSearchBox.SetFocus
Me.cboSearchBox = Me.OpenArgs

hth
Chris

 

[boblarson]

Instead of IsNull, you also want to check for empty strings.

If Len(Me.YourTextBox & "") = 0 Then

That will take care of both Nulls AND Empty Strings.

 

[lmcc007]

Thanks Stopher,

I had changed the code to:

Select Case Me.lstQuickSearch.Value
Case 1:
If Trim([txtSearchBox] & " ") = "" Then
DoCmd.OpenForm ("[frmListforCompanyID")
DoCmd.GoToControl ("cboSearchBox")
Else
DoCmd.OpenForm ("frmListforCompanyID")
DoCmd.GoToControl ("cboSearchBox")
Forms!frmListforCompanyID!cboSearchBox = txtSearchBox
End If
Case 2:
If Trim([txtSearchBox] & " ") = "" Then
DoCmd.OpenForm ("frmListforCompanyName")
DoCmd.GoToControl ("cboSearchBox")
Else
DoCmd.OpenForm ("frmListforCompanyName")
DoCmd.GoToControl ("cboSearchBox")
Forms!frmListforCompanyName!cboSearchBox = txtSearchBox
End If
Case 3:
If Trim([txtSearchBox] & " ") = "" Then
DoCmd.OpenForm ("fdlgEventDetail")
DoCmd.GoToControl ("txtEventDate")
Else
DoCmd.OpenForm ("fdlgEventDetail")
DoCmd.GoToControl ("txtEventDate")
Forms!fdlgEventDetail!txtEventDate = txtSearchBox
End If
Case Else
MsgBox "Invalid selection", vbExclamation
Exit Sub
End Select
​

Now the Null situation is taken care of.

The only problem is the selected text in txtSearchBox when put into
cboSearchBox won't search on the data; I have to delete it and type it back
in and then the search works. That's what I was trying to use the Else
statement for, so I would not have to retype the entry.

 

[lmcc007]

The second problem is that you are setting cboSearchBox.ControlSource. You should be setting cboSearchBox.value. But this will only make the combo box on your 2nd form be set to the value. It won't apply any filtering or go to a given record. Is that what you want to do? If so then use the filter arguement of the OpenForm function to open the form and go to a specific record (or filter)

The above is correct Stopher, it is exactly what I am trying to do.

I am printing out the instructions now and will let you know how it went.

Thanks!

 

[lmcc007]

Instead of IsNull, you also want to check for empty strings.

If Len(Me.YourTextBox & "") = 0 Then

That will take care of both Nulls AND Empty Strings.

Okay, it good to know of other ways to check for Null.

I am using: If Trim([txtSearchBox] & " ") = ""

Thanks!

 

[lmcc007]

Hi Stopher,

I am having problems with the filter part. That is:

"But this will only make the combo box on your 2nd form be set to the value. It won't apply any filtering or go to a given record. Is that what you want to do? If so then use the filter arguement of the OpenForm function to open the form and go to a specific record (or filter)"
​

How shall I handle that?