controls and case statement 1

[Lhuffst]

I have 9 text boxes on on search form. Depending on what is filled in, I build my sql statements.

 For Each ctl In Me.Controls
    Select Case ctl.Name
            Case "fldPermitNo"
                   If DCount("*", "tblParkingPermitAppl", fldPermitNo & "=" & fldPermitNo & "") = 0 Then
                        MsgBox "zero records.", vbInformation, "System Error"
                    Else
                        ssql = ssql + " AND a.fldPermitNo = '" & fldPermitNo & "'"
                        Debug.Print ssql
                     End If

            Case "fldLastName"
              If DCount("*", "tblParkingPermitAppl", fldLastName & "='" & fldLastName & "'") = 0 Then
                 MsgBox "zero records.", vbInformation, "System Error"
                    Else
                        ssql = ssql + " AND a.fldLastName = '" & fldLastName & "'"
                        Debug.Print ssql
                     End If

        End Select
        Debug.Print ctl.Name
    Next ctl

If I fill in the first fldpermitno box, that line works fine. If I leave it blank, I get a syntax error.

If I fill in the fldlastname box, then I get runtime 2001 error saying I have previously canceled the operation which I didn't intend to do.

What I need to do is check for each box, if there are not any corresponding database records, then I don't want to build the sql, instead tell them no records were found or at least move on to the next control. At the end, if the search criteria doesn't produce results, then I want to pop a message saying no records found for the criteria specified.

Not sure what I did wrong. I did look at ctl.tag but I don't really understand how I would build my sql using it. Any help would be appreciated.

 

[PHV]

What about this ?

For Each ctl In Me.Controls
    Select Case ctl.Name
    Case "fldPermitNo"
        If DCount("*", "tblParkingPermitAppl", [!]"fldPermitNo='" & fldPermitNo & "'"[/!]) = 0 Then
            MsgBox "zero records.", vbInformation, "System Error"
        Else
            ssql = ssql + " AND a.fldPermitNo = '" & fldPermitNo & "'"
            Debug.Print ssql
        End If
    Case "fldLastName"
        If DCount("*", "tblParkingPermitAppl", [!]"fldLastName='"[/!] & fldLastName & "'") = 0 Then
            MsgBox "zero records.", vbInformation, "System Error"
        Else
            ssql = ssql + " AND a.fldLastName = '" & fldLastName & "'"
            Debug.Print ssql
        End If
    End Select
    Debug.Print ctl.Name
Next ctl

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Lhuffst]

Worked like a charm. Thanks

One question though. Stepping through the code, I realized that the ctl.name is getting all of the controls on the page. I don't really need to cycle through the labels. Can I just check the textboxes and skip labels?

 

[PHV]

For Each ctl In Me.Controls
  [!]If ctl.ControlType = acTextBox Then[/!]
    Select Case ctl.Name
...
    End Select
    Debug.Print ctl.Name
  [!]End If[/!]
Next ctl

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Lhuffst]

thanks