Member Avatar for mcdczzz

how can i write this in case select syntax? please help

If cboSearchType.Text = "Accredited Month" Then
    With grdSearchResults
        .Rows = .Rows - .Rows + 1
    End With
    If rs.State = 1 Then rs.Close
    SQL = "SELECT * FROM BusinessInfo WHERE " & " AccreditationDate " & " like '" & cboMonths.Text & "%'"
    rs.Open SQL, con
        While rs.EOF = False
                With grdSearchResults
                    .Redraw = False
                        .Rows = .Rows + 1
                        .Row = .Rows - 1
                        .Col = 1: .Text = grdSearchResults.Rows - 1
                        .Col = 2: .Text = rs!BusinessName
                        .Col = 3: .Text = rs!ApplicantType
                        .Col = 4: .Text = rs!ApplicantName
                        .Col = 5: .Text = rs!BusinessAddress
                        .Col = 6: .Text = rs!AccreditationDate
                    .Redraw = True
                 End With
            rs.MoveNext
        Wend
    Else
        MsgBox "Accredited Business Entry Does not Exist!", vbCritical, "No Records Found!"
        txtSEARCH.Text = ""
        txtSEARCH.SetFocus
End If
If cboSearchType.Text = "Accredited Year" Then
With grdSearchResults
    .Rows = .Rows - .Rows + 1
End With
    If rs.State = 1 Then rs.Close
    SQL = "SELECT * FROM BusinessInfo WHERE " & " AccreditationDate " & " like '" & txtSEARCH.Text & "%'"
    rs.Open SQL, con
        While rs.EOF = False
                With grdSearchResults
                    .Redraw = False
                        .Rows = .Rows + 1
                        .Row = .Rows - 1
                        .Col = 1: .Text = grdSearchResults.Rows - 1
                        .Col = 2: .Text = rs!BusinessName
                        .Col = 3: .Text = rs!ApplicantType
                        .Col = 4: .Text = rs!ApplicantName
                        .Col = 5: .Text = rs!BusinessAddress
                        .Col = 6: .Text = rs!AccreditationDate
                    .Redraw = True
                 End With
                rs.MoveNext
        Wend
    Else
        MsgBox "Accredited Business Entry Does not Exist!", vbCritical, "No Records Found!"
        txtSEARCH.Text = ""
        txtSEARCH.SetFocus
    End If
If cboSearchType.Text = "Applicant Name" Then
With grdSearchResults
    .Rows = .Rows - .Rows + 1
End With
    If rs.State = 1 Then rs.Close
    SQL = "SELECT * FROM BusinessInfo WHERE " & " ApplicantName " & " like '" & txtSEARCH.Text & "%'"
    rs.Open SQL, con
        While rs.EOF = False
                With grdSearchResults
                    .Redraw = False
                        .Rows = .Rows + 1
                        .Row = .Rows - 1
                        .Col = 1: .Text = grdSearchResults.Rows - 1
                        .Col = 2: .Text = rs!BusinessName
                        .Col = 3: .Text = rs!ApplicantType
                        .Col = 4: .Text = rs!ApplicantName
                        .Col = 5: .Text = rs!BusinessAddress
                        .Col = 6: .Text = rs!AccreditationDate
                    .Redraw = True
                 End With
                rs.MoveNext
        Wend
    Else
        MsgBox "Accredited Business Entry Does not Exist!", vbCritical, "No Records Found!"
        txtSEARCH.Text = ""
        txtSEARCH.SetFocus
    End If
If cboSearchType.Text = "Business Name" Then
With grdSearchResults
    .Rows = .Rows - .Rows + 1
End With
    If rs.State = 1 Then rs.Close
    SQL = "SELECT * FROM BusinessInfo WHERE " & " BusinessName " & " like '" & txtSEARCH.Text & "%'"
    rs.Open SQL, con
        While rs.EOF = False
                With grdSearchResults
                    .Redraw = False
                        .Rows = .Rows + 1
                        .Row = .Rows - 1
                        .Col = 1: .Text = grdSearchResults.Rows - 1
                        .Col = 2: .Text = rs!BusinessName
                        .Col = 3: .Text = rs!ApplicantType
                        .Col = 4: .Text = rs!ApplicantName
                        .Col = 5: .Text = rs!BusinessAddress
                        .Col = 6: .Text = rs!AccreditationDate
                    .Redraw = True
                 End With
            rs.MoveNext
        Wend
    Else
        MsgBox "Accredited Business Entry Does not Exist!", vbCritical, "No Records Found!"
        txtSEARCH.Text = ""
        txtSEARCH.SetFocus
    End If
End Sub

it is too many lines and i want to make it short because this lines is being repeated 4 times already

MsgBox "Accredited Business Entry Does not Exist!", vbCritical, "No Records Found!"
txtSEARCH.Text = ""
txtSEARCH.SetFocus

also this one, maybe i could use case select syntax to just type the sql statement as you see it is so dirty coding i am just a beginer so sorry for this..i hope you understand

While rs.EOF = False
    With grdSearchResults
        .Redraw = False
        .Rows = .Rows + 1
        .Row = .Rows - 1
            .Col = 1: .Text = grdSearchResults.Rows - 1
            .Col = 2: .Text = rs!BusinessName
            .Col = 3: .Text = rs!ApplicantType
            .Col = 4: .Text = rs!ApplicantName
            .Col = 5: .Text = rs!BusinessAddress
            .Col = 6: .Text = rs!AccreditationDate
        .Redraw = True
    End With
    rs.MoveNext
Wend
Edited by mcdczzz
  • 3 Contributors
  • 3 Replies
  • 185 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by mcdczzz
Member Avatar for Reverend Jim

Perhaps something like this

'This assumes that only valid selections appear in cboSearchType

Dim sql As String = "SELECT * FROM BusinessInfo WHERE "

Select Case cboSearchType.Text

    Case "Accredited Month"
        sql = sql & AccreditationDate " & " like '" & cboMonths.Text & "%'" 
    Case "Accredited Year"
        sql = sql & AccreditationDate " & " like '" & txtSEARCH.Text & "%'" 
    Case "Applicant Name"
        sql = sql & ApplicantName " & " like '" & txtSEARCH.Text & "%'" 
    Case "Business Name"
        sql = sql & BusinessName " & " like '" & txtSEARCH.Text & "%'"  

End Select

With grdSearchResults

    .Rows = .Rows - .Rows + 1

    If rs.State = 1 Then rs.Close()
    rs.Open sql, con

    While rs.EOF = False
        .Redraw = False
        .Rows = .Rows + 1
        .Row = .Rows - 1
        .Col = 1: .Text = grdSearchResults.Rows - 1
        .Col = 2: .Text = rs!BusinessName
        .Col = 3: .Text = rs!ApplicantType
        .Col = 4: .Text = rs!ApplicantName
        .Col = 5: .Text = rs!BusinessAddress
        .Col = 6: .Text = rs!AccreditationDate
        .Redraw = True
        rs.MoveNext
    Wend

End With

Although I should point out that you might not want to keep the connection open. The preferred method is to keep connections open only as long as they are immediately needed. In that case you should do something like

If con.State <> 1 Then con.Open()

rs.Open sql, con

'the loop goes here

rs.Close()
con.Close()
commented: Just because the OP should have, nice work. +5
Member Avatar for mcdczzz

thanks for the reply but i solved it before i see this post but i still appreciate your idea..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.