vb hangs help needed

[venkatnams]

guys,,i use vb6 and access 2007,,i hav a problem with my project,,,,my project is about college admin s/m,,,on runtime if i give certain id's for input it hangs and just crashes,,,i dont know why this happens,,,,,i hav applied the same code in many other places of my project but they dont seem to hang though i've faced the hanging trouble previously but which doesnt happen now,,,,,pls i need ur help desperately,,,,

[vb5prgrmr]

And how can we help you without seeing the code???

[venkatnams]

dim s as string
Private Sub Form_Load()
s = InputBox("Enter the student's id")
Set db = OpenDatabase("C:\Program Files\Microsoft Visual Studio\VB98\my new prj\prj1.mdb")
Set rt = db.OpenRecordset("studper", dbOpenDynaset)
rt.MoveLast
lab: If (rt.Fields(12) <> s) Then
rt.MovePrevious
GoTo lab
End If
Text1.Text = rt.Fields(0)
Text2.Text = rt.Fields(1)
Text3.Text = rt.Fields(2)
Text4.Text = rt.Fields(3)
Text5.Text = rt.Fields(4)
Text6.Text = rt.Fields(5)
Text7.Text = rt.Fields(6)
Text8.Text = rt.Fields(7)
Text9.Text = rt.Fields(8)
Text10.Text = rt.Fields(9)
Text11.Text = rt.Fields(11)
Label9.Caption = rt.Fields(12)
If Option1.Caption = rt.Fields(10) Then
Option1.Value = True
End If
If Option2.Caption = rt.Fields(10) Then
Option2.Value = True
End If
If Option3.Caption = rt.Fields(10) Then
Option3.Value = True
End If
If Option4.Caption = rt.Fields(10) Then
Option4.Value = True
End If
If Option5.Caption = rt.Fields(10) Then
Option5.Value = True
End If
If Option6.Caption = rt.Fields(13) Then
Option6.Value = True
End If
If Option7.Caption = rt.Fields(13) Then
Option7.Value = True
End If
End Sub

I've used the same kind of code for searching an id in my project,,,in some places it works fine,,but at some places it hangs,,,after entering the id in the box,,,pls sort my prob out,,

[vb5prgrmr]

Okay,... Your query should be more like this...

strSQL = "SELECT * FROM tablename WHERE fieldname = " & inputednumbervalue

or if string

strSQL = "SELECT * FROM tablename WHERE fieldname = '" & inputedstringvalue & "'"

and then to check to see if any records were returned you could check it like this...

If Rs.RecordCount <> 0 AND Rs.BOF = False AND Rs.EOF = False Then
  'have records
Else
  'no records
End If

NOTE: I check for all three conditions as you can see...

and this...

lab: If (rt.Fields(12) <> s) Then
rt.MovePrevious
GoTo lab

is not good programming practice. Not only because of using a GoTo to loop, but because you have no check for BOF, which means if user inputted a value that did not exist in the table, the recordset would error out as you tried to move prior to the first record and read a value. Now if you just went Huh? Well think of it this way, if you were to open a text file and try to read before the first line in that text file, what would you be reading? Nothing in that text file that is for sure.

Second, this...

rt.Fields(12)

While very easy to code, this does not lend itself to easy readability or more important, program maintence. While most programmers would know that you are referencing the 13th field of the table, the question is, what is that fields name? You should reference fields like so...

Text1.Text = Rs.Fields("FieldName").Value

Also, by using field numbers as you have, what happens if the database changes structure? Two fields switched with each other, or a field added say at postion 4? Then you would have to go back through every place in your program where that table is referenced and reorder your numbers from 4 on up.

Hope this helps...

Good Luck

[jonc]

I'd try setting a breakpoint and stepping through the code to see where it is hanging. I'm sure you would be able to identify the line which causes the hanging quite easily that way.

If it only hangs after it's been ran a few times, try cleaning up the code (i.e. define the objects correctly and destroy them when you've finished with them).

At this point, if you're still having problems, you should have an idea at what point in your code the problem is occurring and we should be able to help further.

Edit: after spending 5mins trying to type my post with cold hands, it appears someone has beaten me to it :)

[venkatnams]

thanx for that,,,i've learnt that the problem is with the searching part only...cos the progrm hangs only after getting the id through the input box,,,if there is any other way u can suggest me to search for id effeciently pls suggest me,,,thanx,,i'l try ur technique and tell my response,,,waitin for ur response,,

[venkatnams]

i need to add that if i've not completed my coding in it about wat the program should do if it has not found the users record,,,,im just planning to add a message box in the else part of the searching place,,which display "no record found",,,by the way pls tell me how i should use the sql querry tat you have posted here,,,i'm totally new to vb...

[vb5prgrmr]

somestringvariable = inputbox(...
strSQL = "SELECT * FROM tablename WHERE fieldname = '" & somestringvariable & "'" 'or no single quotes if field is numeric
Set Rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Good Luck