• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

Easy Problem with my code URGENT

Hi all, I cant seem to get round this even although its a very simple idea, what I am trying to do is search through a list of names and if there is more than one name then i need to another page with a flexgrid with full details of each of the names if there are more than one, but for simple sake, I just need it to show a text box. If there is only one name found then all i need is to show another text box. here is the code I am using, I have changed the requirements so that a msg box is shown if there is one name and another message is shown if there are more than one.

Private Sub CSearch(SCriteria)
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strIniFile As String
Dim criteria
Dim counter
Dim found As Boolean

strIniFile = App.Path & "\dbconnectionstrings.ini"


conn.Provider = GetFromINI("ConnectionStrings", "sdmpr", strIniFile)
conn.Open GetFromINI("ConnectionStrings", "sdmdb", strIniFile)


sqlstr = "SELECT * FROM Clients ORDER BY Surname ASC"

rs.Open sqlstr, conn, adOpenKeyset, adLockOptimistic


If cmbsearch.Text = "" Then
MsgBox "Please select a field to search", vbintormation, "Define Search Field"
Else
criteria = cmbsearch.Text

With rs

    .MoveFirst
   
    Do Until .EOF Or found
 
    If .Fields(criteria) = SCriteria Then
   
        counter = counter + 1
        found = true                                      <<< I know this will stop the loop here, but just an idea of what im trying to get.
    Else
   
    .MoveNext
         
    End If
    End If
    Loop
   
If counter > 1 Then

    MsgBox "found more than one name"

 End If


If found = True Then

         MsgBox "Found one name"

Else

    MsgBox "Record not found", vbInformation, "Record Search"

    .MoveFirst

End If


End With


Set rs = Nothing
conn.Close
Set conn = Nothing
End If
End Sub
0
Stephen Manderson
Asked:
Stephen Manderson
1 Solution
 
fulscherCommented:
A suggestion:

Private Sub CSearch(SCriteria)
...
Dim resultcount as Long

...
rs.Open sqlstr, conn, adOpenKeyset, adLockOptimistic
rs.MoveLast
resultcount = rs.Count
rs.MoveFirst
...

if resultcount >= 2 then
   ...
else
   ...
end if

0
 
ShauliCommented:
Private Sub CSearch(SCriteria)
Dim conn As New ADODB.Connection, rs As New ADODB.Recordset
Dim strIniFile As String, criteria As String, counter As Integer

strIniFile = App.Path & "\dbconnectionstrings.ini"

conn.Provider = GetFromINI("ConnectionStrings", "sdmpr", strIniFile)
conn.Open GetFromINI("ConnectionStrings", "sdmdb", strIniFile)

'validate search field
If cmbsearch.Text = "" Then
    MsgBox "Please select a field to search", vbintormation, "Define Search Field"
    Exit Sub
End If

'set sql string
criteria = cmbsearch.Text
sqlstr = "SELECT * FROM Clients WHERE " & criteria & "='" & SCriteria & "' ORDER BY Surname ASC"

'open recordset for search
rs.Open sqlstr, conn, adOpenKeyset, adLockOptimistic
    With rs
        Do Until .EOF
            counter = counter + 1
            .MoveNext
        Loop
    End With
rs.Close

'display msg
Select Case counter
    Case 0
        MsgBox "Record not found", vbInformation, "Record Search"
    Case 1
         MsgBox "Found one name"
    Case Is > 1
        MsgBox "found more than one name"
End Select

Set rs = Nothing
conn.Close
Set conn = Nothing
End Sub

S
0
 
Stephen MandersonAuthor Commented:
Thanks S
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now