Checking for Null & Select Case issues

[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:

1. lstQuickSearch
ControlSource is empty
Row Source = 1;"Company ID";2;"Company Name";3;"Date
of Event";4;"Event Type"
Row Type = Value List

2. 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!

 

[MajP]

In access checking for null will not catch an empty string, space, spaces. The best check is

if trim([controlName] & " ") = ""

This will catch null, space, spaces, zero length string which all look to the user as null

second put a debug.print at the beginning so you can see what is happening

debug.print "combo search: " & Me.lstQuickSearch.Value
debug.print "txt box: " & textSearchBox
debug.print "Is Null? " & trim(testSearchBox & " ") = ""

also this is wrong:
you have two case 2s. The second one will never occur
Case 2:
If IsNull(txtSearchBox) Then
DoCmd.OpenForm ("fdlgEventDetail")
DoCmd.GoToControl ("txtEventDate")
Else
DoCmd.OpenForm ("fdlgEventDetail")
DoCmd.GoToControl ("txtEventDate")
Forms!fdlgEventDetail!txtEventDate.ControlSource =

Also the if and else are exactly the same.

 

[lmcc007]

Okay MajP,

On the Else statement:

Forms!frmListforCompanyID!cboSearchBox = txtSearchBox

I was trying to tell it to select the data in txtSearchBox and put it in the cobSearchBox, so I won't have to retype it.

I revised the code to the below:

Debug.Print "combo search: " & Me.lstQuickSearch.Value
Debug.Print "txt box: " & txtSearchBox
Debug.Print "Is Null? " & Trim(txtSearchBox & " ") = ""


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


The Immediate Window displays:

combo search: 2
txt box: zat
False


After opening the designated form, I have to delete and re-enter the text in order to get the cboSearch to work.

 

[MajP]

what is the query for combo, and what column is bound?

your code could be a little shorter (not that it matters much)

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

 

[lmcc007]

Okay MajP,

Answer to your questions are:

"what column is bound?"

Bound = 1
Column Count = 1


The query for combo is:

Private Sub cboSearchBox_AfterUpdate()

On Error GoTo ErrorHandler

Me.txtCompanyName.Enabled = True
Me.txtCompanyName.SetFocus

If Not IsNull(cboSearchBox) Then
DoCmd.FindRecord cboSearchBox
Me.txtBlank.SetFocus
Me.txtCompanyName.Enabled = False
Me.cboSearchBox.Value = Null
End If


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

 

[MajP]

A problem could be with the docmd.findrecord. Although this is viable, unless you look at the additional parameters and setting the field focus you may not get the desired results. A better search method is:

dim rs as dao.recordset
set rs = me.recordset
rs.findfirst "yourFieldName = '" & cboSearch & "'"
'remove the single quotes if numeric.

This way it knows exactly the field to search without setting the focus to the desired field.

 

[lmcc007]

Okay MajP,

I will work on it later--got an appointment in 30. Will let you know.

Thank you for your help!

 

[MajP]

Also form events do not happen due to code changes. If I change the value of a combo in code, the forms after update does no happen. So if you expect
Forms!fdlgEventDetail!txtEventDate = txtSearchBox
to cause a search it will not.


If you want those procedures to happen due to a code change then probably a better design would be.

Private Sub cboSearchBox_AfterUpdate()
  call searchForText
end sub
public sub searchForText()
     On Error GoTo ErrorHandler
         Me.txtCompanyName.Enabled = True
         Me.txtCompanyName.SetFocus
         If Not IsNull(cboSearchBox) Then
             DoCmd.FindRecord cboSearchBox
             Me.txtBlank.SetFocus
             Me.txtCompanyName.Enabled = False
             Me.cboSearchBox.Value = Null        
         End If
     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
[code]

so if you want a search following
Forms!fdlgEventDetail!txtEventDate = txtSearchBox
you can add
Form_fdlgEventDetail.searchForText

That will call the procedure on form fdlgEventDetail

 

[lmcc007]

Yeah MajP, this is what I was trying to do:

dim rs as dao.recordset
set rs = me.recordset
rs.findfirst "yourFieldName = '" & cboSearch & "'"
'remove the single quotes if numeric.

but I got lost and trying to do something in a hurry never works out.