PLs pls help me!!!!!!!!!

I am making a project for a college, to store details of students exams and professors.
Im using adodb connection.
im having problem with search form where i have a MSHFlexGrid and im using adodb to search.
The problem is that
i have a textbox, and on entering text the grid should show the record similar to the entry.
This is not happening.
The code used is this

Code:

---

"to load the MSHFlexgrid"
Private Sub Form_Load()
  Dim conn1 As ADODB.Connection
    Dim rs1 As ADODB.Recordset
    Set conn1 = New ADODB.Connection
    conn1.ConnectionString = "Provider=MSDataShape.1;data provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\studentdetails.mdb;" & " Persist Security Info=False"
    conn1.Open
    Set rs1 = New ADODB.Recordset
    rs1.Open "shape {SELECT * from personaldetails order by ID} ", conn1, adOpenDynamic, adLockOptimistic
    Set MSHFlexGrid1.DataSource = rs1
    rs1.Close
    Set rs1 = Nothing
    conn1.Close
    Set conn1 = Nothing
    With MSHFlexGrid1
        .CollapseAll
        .BandDisplay = flexBandDisplayVertical
        .ColHeader(1) = flexColHeaderOn
        .BandIndent(1) = 2
    End With

"The textbox change code"
Private Sub Text1_Change()
conndb
Set rs2 = New ADODB.Recordset
Dim strSQL As String, sqlquery As String
Select Case Combo1.Text
Case Is = "Name"
sele = 1
MSHFlexGrid1.LeftCol = 1
sqlquery = "Select * from personaldetails"
strSQL = sqlquery & " where Student_name like '*" & Trim(Text1.Text) & "*'"
With rs2
.Open strSQL, conn1, adOpenStatic, adLockOptimistic
If .EOF Then
MsgBox "The Search Criteria Have Been Complete." & vbCrLf & "Or Result Not Found In Database!!", vbExclamation, "Search Error Failed."
SendKeys "{Home}+{End}"
On Error Resume Next
Else
If Text1.Text = "" Then
.Source = "Select * from personaldetails"
Else
.Source = "Select * from personaldetails where Student_name like '*" & Trim(Text1.Text) & "*'"
.MoveNext
m2 = Text1.Text
End If
End If
End With

---

pls guide me
whenevr i enter i get the msgbox error msg.
pls help!!!!!!!!!!!!!!!!!!!!!
Any suggestions will be appreciated

Please use Code Tags when you post code, and descriptive thread titles in the future

Please use code tags around your code sections so as the formatting is kept and readability is maintained.
Just write [ code ] in front and [ /code ] at the end of the code block, only without the blanks shown here.

First thing comes to mind, in Text1_Change() the rs2 variable is not declared.
Please turn on OPTION EXPLICIT to help you finding undeclared variables.
Then, we need to know which line produces the error, and what exactly the error message is.

ok sorry for the inconvenience.ill surly keep in mind.
i tried declaring rs2 in text1_Change() and option explicit.
but no results.
do you want me to add more code from my project for better idea or any kind of explanantion?
pls tell me ill be happy to do so.thnks

To find out where the error comes in you first have to comment out the line
On Error Resume Next.
Just put a quote in front to make a comment out of it for temporarily disabling it:
'On Error Resume Next.

Then a much more detailed description of the error should come up, plus the code line where the error occured.

Otherwise, for any small project it is best you zip it up and attach it, so as we can run a so far running program to examine closer where the problem is. It should be most complete then, including at least a sample database.

Take a look at this. I've included a project as well.

Code:

---

Option Explicit


Private Sub Command1_Click()
    Dim cnLvConnection As ADODB.Connection
    Set cnLvConnection = New ADODB.Connection
    Dim rsLvRecordset As ADODB.Recordset
    Set rsLvRecordset = New ADODB.Recordset
    With cnLvConnection
        .Provider = "MSDataShape.1"
        .ConnectionString = "Data Source=" & App.Path & "\db1.mdb;" _
                          & "Data Provider=Microsoft.Jet.OLEDB.4.0;"
        .Open
        With rsLvRecordset
            .CursorLocation = adUseClient
            .CursorType = adOpenStatic
            .LockType = adLockReadOnly
        End With
        Set rsLvRecordset = .Execute("SHAPE {SELECT c.CustomerName As Customer, c.CustomerID FROM Customers c ORDER BY c.CustomerName} As Customers" _
                                  & " APPEND ((SHAPE {SELECT oh.OrderNumber As [Order No], oh.CustomerID, oh.OrderHeaderID FROM OrderHeaders oh ORDER BY oh.OrderNumber} As OrderHeaders" _
                                  & "          APPEND ({SELECT od.OrderLine As [Line], od.OrderLineDescription As [Description], od.OrderLineQuantity As Quantity, od.OrderHeaderID FROM OrderDetails od ORDER BY od.OrderLine} As OrderDetails" _
                                  & "          RELATE OrderHeaderID TO OrderHeaderID))" _
                                  & " RELATE CustomerID TO CustomerID)")
    End With
    '  Setup Grid
    Set Me.MSHFlexGrid1.Recordset = rsLvRecordset
    Me.MSHFlexGrid1.ColWidth(1, 0) = 0 ' c.CustomerID
    Me.MSHFlexGrid1.ColWidth(1, 1) = 0 ' oh.CustomerID
    Me.MSHFlexGrid1.ColWidth(2, 1) = 0 ' oh.OrderHeaderID
    Me.MSHFlexGrid1.ColWidth(3, 2) = 0 ' od.OrderHeaderID
    '  Tidy up
    If Not rsLvRecordset Is Nothing Then
        If rsLvRecordset.State <> adStateClosed Then
            rsLvRecordset.Close
        End If
        Set rsLvRecordset = Nothing
    End If
    If Not cnLvConnection Is Nothing Then
        If cnLvConnection.State <> adStateClosed Then
            cnLvConnection.Close
        End If
        Set cnLvConnection = Nothing
    End If
End Sub

Private Sub Form_Load()
    Me.MSHFlexGrid1.FixedCols = 0
End Sub

'
'  Edit the cell, without the update

Private Sub MSHFlexGrid1_KeyPress(KeyAscii As Integer)
  If KeyAscii = vbKeyReturn Then  'enter key
    'move to next cell.
        With MSHFlexGrid1
            If .Col + 1 <= .Cols - 1 Then
                .Col = .Col + 1
            Else
                If .Row + 1 <= .Rows - 1 Then
                    .Row = .Row + 1
                    .Col = 0
                Else
                    .Row = 1
                    .Col = 0
                End If
            End If
        End With
    ElseIf KeyAscii = vbKeyBack Then  'back space key
        With MSHFlexGrid1
            'back space out the entered characters
            If Len(.Text) Then
                .Text = Left(.Text, Len(.Text) - 1)
            End If
        End With
    Else
        With MSHFlexGrid1
            .Text = .Text & Chr(KeyAscii)
        End With
    End If
End Sub

---

and a link: http://www.4guysfromrolla.com/webtech/092599-1.shtml

awsome code bro!!!
ill surly try it.....
ok ill also attach my pro with the database.
im attaching the search form and the next form to be loaded.

hey
i too im trying but still no break through.
any progress??or is anythn missing??
pls do tell me..

You have to know how your db and program works. Nobody wants to figure out WHERE the problem is, and then try to debug it for you.

Post a description of the problem that you're having, then post some code that you have tried (using CODE TAGS).

Make sure we know which LINE has the problem, as well as any error messages that you're getting.

You forgot to supply the file stu.mdb with your zip. The project wouldn't run without it, so as we cannot spot the error.
Please supply a version of stu.mdb.

ok sorry for that.
here is stu.mdb.

i understand what ur tryin to say.
the fact is tht there are no errors in the code.and thts why i feel there is a problem in the logic of Text1_Change().
The problem that im facing on debuggin is "If .EOF Then" statement where .EOF is comin true.
so its not able to find the record.

Code:

---

"The textbox change code"
Private Sub Text1_Change()
conndb
Set rs2 = New ADODB.Recordset
Dim strSQL As String, sqlquery As String
Select Case Combo1.Text
Case Is = "Name"
sele = 1
MSHFlexGrid1.LeftCol = 1
sqlquery = "Select * from personaldetails"
strSQL = sqlquery & " where Student_name like '*" & Trim(Text1.Text) & "*'"
With rs2
.Open strSQL, conn1, adOpenStatic, adLockOptimistic
If .EOF Then
MsgBox "The Search Criteria Have Been Complete." & vbCrLf & "Or Result Not Found In Database!!", vbExclamation, "Search Error Failed."
SendKeys "{Home}+{End}"
On Error Resume Next
Else
If Text1.Text = "" Then
.Source = "Select * from personaldetails"
Else
.Source = "Select * from personaldetails where Student_name like '*" & Trim(Text1.Text) & "*'"
.MoveNext
m2 = Text1.Text
End If
End If
End With

---

pls help me here.
thanks

I'm not sure about the code yet. The reoson for no error coming up is, there are plenty 'On Error Resume Next' statements which suppress the errors.
During developement you should comment out these statements, allowing the actual error messages to pop up.

I can look at the code later that eveneing. Now that you sent stu.mdb, I should be able to run it at least.

Ok. I'm now aware of what you want to achieve.
I must say it would have been easier if you'd have cultivated a better style of indentation.
Now this is the relevant piece of code, which I have indented to clarify the program flow.

Code:

---

  Case Is = "Name"
      sele = 1
      MSHFlexGrid1.LeftCol = 1
      sqlquery = "Select * from personaldetails"
      strSQL = sqlquery & " where Student_name like """ & Trim(Text1.Text) & """;"
      Debug.Print strSQL
      With rs2
          .Open strSQL, conn1, adOpenStatic, adLockOptimistic
          Set MSHFlexGrid1.DataSource = rs2 '<--inserted this line
          If .EOF Then
              MsgBox "The Search Criteria Have Been Complete." & vbCrLf & "Or Result Not Found In Database!!", vbExclamation, "Search Error Failed."
              SendKeys "{Home}+{End}"
              'On Error Resume Next
          Else
              If Text1.Text = "" Then
                .Source = "Select * from personaldetails"
              Else
                .Source = "Select * from personaldetails where Student_name like '*" & Trim(Text1.Text) & "*'"
              End If
              .MoveNext
              m2 = Text1.Text
          End If
      End With

---

1. It is obvious that the sql query string is not working.
2. I have changed the query to "Select * from personaldetails" without the selectivity to see if something comes back.
3. Something is coming back and without your On Error Resume Next it reveals that
4. (adorecordset).Source = "Select * from ..." is not allowed for an open object.
5. therefore I have inserted the line Set MSHFlexGrid1.DataSource = rs2 which propably does what you want.
The actual remaining code could look like this:

Code:

---

  Case Is = "Name"
      sele = 1
      MSHFlexGrid1.LeftCol = 1
      sqlquery = "Select * from personaldetails"
      strSQL = sqlquery & " where Student_name like """ & Trim(Text1.Text) & """;"
      Debug.Print strSQL
      With rs2
          .Open strSQL, conn1, adOpenStatic, adLockOptimistic
          Set MSHFlexGrid1.DataSource = rs2
          If .EOF Then
              MsgBox "The Search Criteria Have Been Complete." & vbCrLf & "Or Result Not Found In Database!!", vbExclamation, "Search Error Failed."
              SendKeys "{Home}+{End}"
              'On Error Resume Next
              End If
              .MoveNext
              m2 = Text1.Text
          End If
      End With

---

The only thing left - and which I have not yet figured out - is why the sql statement doesn't work as expected.
I have debug.printed it and it looks like this:
Select * from personaldetails where Student_name like "c*";
respectively before I changed the quotes into double quotes it was:
Select * from personaldetails where Student_name like '*c*'
Both strings do not return any record.
And I still don't know why.
To me they look like pretty good SQL statements.
Does anybody object?

If .EOF? Try it the other way...

Code:

---

Private Sub Form_Load()
Dim dteDate    As Date
Dim adoRec      As ADODB.Recordset
    dteDate = CDate("7 March 2005 10:04:00AM")
    Set adoRec = GetRecordset(dteDate)
    With adoRec
        Do While Not .EOF
            'your code goes here
            MsgBox .Fields(0)
            .MoveNext
        Loop
        .Close
    End With
    Set adoRec = Nothing
End Sub

---