What's wrong with my code

[jason12776]

I am trying to post the results of a search in both some text boxes as well as a datagrid...

Private Sub cmdLookUp_Click()
Dim Lsql As String, Dsql As String, Msql As String, conn As String
Dim rs As New ADODB.Recordset
Dim dpath As String

dpath = "C:\program files\DVD\dvd.mdb"
conn = "provider=microsoft.jet.oledb.4.0;data source = " & dpath
Lsql = &quot;SELECT * from movie where LentOut <> 'N/A'&quot;
Dsql = &quot;SELECT * from movie where director like '&quot; & txtSearch.Text & &quot;'&quot;
Msql = &quot;SELECT * from movie where movie like '&quot; & txtSearch.Text & &quot;'&quot;

If optLentOut Then
Set rs = New ADODB.Recordset
rs.Open Lsql, conn

With rs
Do Until .EOF
If .Fields(&quot;lentout&quot <> &quot;N/A&quot; Then
txtMovie.Text = .Fields(&quot;movie&quot
txtDirector.Text = .Fields(&quot;director&quot
txtCatagory.Text = .Fields(&quot;category&quot
txtRating.Text = .Fields(&quot;rating&quot
txtDate.Text = .Fields(&quot;date&quot
txtID.Text = .Fields(&quot;ID&quot
txtTime.Text = .Fields(&quot;time&quot
txtLentOut.Text = .Fields(&quot;lentout&quot
End If
Loop
End With
rs.Close
ElseIf optDirector Then
Set rs = New ADODB.Recordset
rs.Open Dsql, conn

With rs
Do Until .EOF
txtMovie.Text = .Fields(&quot;movie&quot
txtDirector.Text = .Fields(&quot;director&quot
txtCatagory.Text = .Fields(&quot;category&quot
txtRating.Text = .Fields(&quot;rating&quot
txtDate.Text = .Fields(&quot;date&quot
txtID.Text = .Fields(&quot;ID&quot
txtTime.Text = .Fields(&quot;time&quot
txtLentOut.Text = .Fields(&quot;lentout&quot
Loop
End With
rs.Close
ElseIf optMovie Then
Set rs = New ADODB.Recordset
rs.Open Msql, conn

With rs
Do Until .EOF
txtMovie.Text = .Fields(&quot;movie&quot
txtDirector.Text = .Fields(&quot;director&quot
txtCatagory.Text = .Fields(&quot;category&quot
txtRating.Text = .Fields(&quot;rating&quot
txtDate.Text = .Fields(&quot;date&quot
txtID.Text = .Fields(&quot;ID&quot
txtTime.Text = .Fields(&quot;time&quot
txtLentOut.Text = .Fields(&quot;lentout&quot
Loop
End With
rs.Close
Else
Call MsgBox(&quot;You must choose an option.&quot;, vbCritical, &quot;Error&quot
End If

end sub

Does all this make sense? Any suggestions will be appreciated.

Cheers.

 

[jason12776]

I forgot. I don't get any error messages. The processor works for a moment, like it's searching, but then nothing is displayed. If more code is needed, please let me know.

 

[petermeachem]

You've got the code looping round, but only writing to a single text box. So either there aren't any records, or the last one is blank. Have you tried putting a break point in and single stepping? Peter Meachem
peter @ accuflight.com

 

[jason12776]

No I haven't. How could I single step? Is that just the debugging mode? Also, how would I assign all this &quot;mess&quot; to the datagrid?

 

[vb5prgrmr]

I also do not see a movenext. Since you have a loop without a doevents in it, the first loop would make your program look like it was locked up because it would be constantly returning the first record and never reaching the end of the recordset. Try changing your code to something like...

[tt]
Private Sub cmdLookUp_Click()
Dim Lsql As String, conn As String
Dim rs As New ADODB.Recordset
Dim dpath As String

dpath = &quot;C:\program files\DVD\dvd.mdb&quot;
conn = &quot;provider=microsoft.jet.oledb.4.0;data source = &quot; & dpath

If optLentOut Then
Lsql = &quot;SELECT * from movie where LentOut <> 'N/A'&quot;
ElseIf optDirector Then
Lsql = &quot;SELECT * from movie where director like '&quot; & txtSearch.Text & &quot;'&quot;
ElseIf optMovie Then
Lsql = &quot;SELECT * from movie where movie like '&quot; & txtSearch.Text & &quot;'&quot;
Else
Call MsgBox(&quot;You must choose an option.&quot;, vbCritical, &quot;Error&quot
Exit Sub
End If

Set rs = New ADODB.Recordset
rs.Open Lsql, conn

if rs.recordcount <> 0 and rs.eof = false and rs.bof = false then
txtMovie.Text = .Fields(&quot;movie&quot
txtDirector.Text = .Fields(&quot;director&quot
txtCatagory.Text = .Fields(&quot;category&quot
txtRating.Text = .Fields(&quot;rating&quot
txtDate.Text = .Fields(&quot;date&quot
txtID.Text = .Fields(&quot;ID&quot
txtTime.Text = .Fields(&quot;time&quot
txtLentOut.Text = .Fields(&quot;lentout&quot
else
msgbox &quot;No Data Returned By Query&quot;,vbOkOnly,&quot;No Data&quot;
end if

end sub
[/tt]

Good Luck

 

[jason12776]

Thanks vb5programmer, but it doesn't work. I get the error, invalid or unqualified reference. I did copy your code and put it in my program, but I also checked all code, and it seems fine. It does stop me on the part:

txtMovie.Text = .Fields(&quot;movie&quot

with .Fields being highlighted.

Any thoughts?

 

[petermeachem]

Well spotted.

Debug mode is brilliant. Click on a line and press F9. When the programme execution reaches that line, it stops. Hover the mouse over variables to see the value. Press F8 to step to the next executable line. Click on the border to the left of the yellow line and drag up and down to execute from a different place (Use this if you alter code and want to reexecute it). Peter Meachem
peter @ accuflight.com

 

[vb5prgrmr]

It sounds to me that you do not have a field called movie. Put a break point on the rsopen line and when your program stops hit F8 and check the recordcount,bof,eof properties like petermeachem suggests then hover each of the....

Never mind....!

In my code I did not use the with rs add that and it should work (duh!)

 

[jason12776]

vb5progrmr, I put the code in and it still acts like it is doing nothing, although it actually is.

With rs
Do Until .EOF
If rs.RecordCount <> 0 And rs.EOF = False And
rs.BOF = False Then
txtMovie.Text = .Fields(&quot;Movie&quot
txtDirector.Text = .Fields(&quot;Director&quot
txtCatagory.Text = .Fields(&quot;Category&quot
txtRating.Text = .Fields(&quot;Rating&quot
txtDate.Text = .Fields(&quot;Date&quot
txtID.Text = .Fields(&quot;ID&quot
txtTime.Text = .Fields(&quot;Time&quot
txtLentOut.Text = .Fields(&quot;LentOut&quot
Else
MsgBox &quot;No Data Returned By Query&quot;,
vbOKOnly, &quot;No Data&quot;
End If
Loop
End With
rs.Close

I tried the debug mode as suggested, and it steps through each statement, excuse me if I'm not saying that correctly, and when it gets to the do statement, it jumps to the end with statement. Unfortunately, I don't know enough about debug mode to say if this is a good or bad thing. Anyway, it acts like it is not doing anything. Any suggestions?

Cheers.

 

[johnwm]

If you jump from
Do until .eof

to
End With
Then it looks as if your recordset is empty!

Put a msgbox rs.Recordcount just before you Do until... line and see what it says.

If it comes up with zero then check your SQL Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[jason12776]

Awsome catch johnwm. I never would have thought to do that. Anyway, I got a -1 for all 3 query's. lol, how do you get a -1? The weird part about it is, that I know for a fact that there are 6 records with the criteria that I am searching for. Any ideas?

 

[johnwm]

Usual problem I find is some typo in the compiled SQL statement.

Just before you do your rs.Open

stick in a
MsgBox lsql

or a Debug.Print lsql

so that you can examine it carefully for right number of quote marks etc. Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[vb5prgrmr]

The reason -1 is returned is because the recordset knows it has at least one record (or some data) but does not have the total count of records returned by the query.

As for it jumping from the do to the end I would add...
[tt]

'Add this before you go into your loop
if rs.recordcount <> 0 and rs.eof = false and rs.bof = false then
rs.MoveLast
rs.MoveFirst
End If
'this will fetch/pull the data from the database and you should be able to get an accutate re.recordcount
[/tt]

I Still do not see a [tt]rs.MoveNext[/tt] in your code. You will never reach the end of the recordset without it.

 

[jason12776]

You guys have been an awsome help, and I hate to bug you about this new issue for me, but...when I put in the code,

if rs.recordcount <> 0 and rs.eof = false and rs.bof = false then
rs.MoveLast
rs.MoveFirst
End If

It will tell me that it cannot fetch backwards, so I have it listed as below.


rs.Open Lsql, conn

If rs.RecordCount <> 0 And rs.EOF = False And rs.BOF =
False Then
rs.MoveFirst
End If

With rs
.MoveFirst
Do Until .EOF
Call MsgBox(rs.RecordCount)
If rs.RecordCount <> 0 And rs.EOF = False And
rs.BOF = False Then
txtMovie.Text = .Fields(&quot;Movie&quot
txtDirector.Text = .Fields(&quot;Director&quot
txtCatagory.Text = .Fields(&quot;Category&quot
txtRating.Text = .Fields(&quot;Rating&quot
txtDate.Text = .Fields(&quot;Date&quot
txtID.Text = .Fields(&quot;ID&quot
txtTime.Text = .Fields(&quot;Time&quot
txtLentOut.Text = .Fields(&quot;LentOut&quot
Else
MsgBox &quot;No Data Returned By Query&quot;,
vbOKOnly, &quot;No Data&quot;
End If
.MoveNext
Loop
End With
lblCount.Caption = &quot;There are a total of &quot; &
rs.RecordCount & &quot; records.&quot;
rs.Close

I still get a -1, but the text boxes do populate with the necessary record. But when there are 2 records with the same search criteria, it will prompt me with the -1, 2 times, which I understand, sorta, then if I try to click on my next button to go to the next record, I will get an error:
The changes you requested to the table were not successful because they would created duplicate values in the index, primary key, or relationship...

I feel like there is something minor I am missing, but for some reason, cannot find it.

 

[vb5prgrmr]

do you have a forward only recordset??? you may be defaulting to a forward only recordset if you have not declared the locking and cursor types you want.

 

[jason12776]

vb5prgrmmr, could you explain in a little more detail what you just said? I'm still new to the game and don't quite understand what you are suggesting.

 

[vb5prgrmr]

You guys have been an awsome help, and I hate to bug you about this new issue for me, but...when I put in the code,
[tt]
if rs.recordcount <> 0 and rs.eof = false and rs.bof = false then
rs.MoveLast
rs.MoveFirst
End If
[/tt]
It will tell me that it cannot fetch backwards, so I have it listed as below.



Change the line of code from
[tt]
rs.Open Lsql, conn

to

Rs.Open Lsql, conn, adOpenDynamic, adLockOptimistic
[/tt]

then check the recordcount and see if it is still -1 or you can try the movelast/movefirst and see if it works

Good Luck