Empty Recordset?

[BlackRiver]

Private Sub Text3_Change()
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & App.Path & "\Videoteka.mdb;Persist Security Info=False;"

cn.Open
Set rsNew = New ADODB.Recordset

StringQuery = "SELECT DISTINCT MovieName FROM Transactions WHERE MemberID = " & Text3.Text & " AND Returned = 'NO' "
On Error Resume Next
rsNew.Open StringQuery, cn, adOpenDynamic, adLockOptimistic, adCmdText

If rsNew.EOF = True Then
MsgBox " No such ID!"

Else

Do Until rsNew.EOF
List2.AddItem rsNew!MovieName
rsNew.MoveNext
Loop
cn.Close
End If


Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & App.Path & "\Videoteka.mdb;Persist Security Info=False;"

cn.Open

Set rsNew = New ADODB.Recordset

StringQuery = " SELECT FirstName,LastNamee,Debt,TMR FROM Members WHERE MemberID = " & Text3.Text & " "
On Error Resume Next
rsNew.Open StringQuery, cn, adOpenDynamic, adLockOptimistic, adCmdText

If rsNew.EOF = True Then
MsgBox "No such ID!"

Else


Label16.Caption = rsNew.Fields("FirstName")

Label18.Caption = rsNew.Fields("LastName")

Label20.Caption = rsNew.Fields("Debt")

Label22.Caption = rsNew.Fields("TMR")

cn.Close
End If
End Sub

Hello again!The above code shows my way of dealing with transactions.To be exact,I'm working on a VideoStore program.I use the above code to work with returned films.
I'll try to explain what the code does:

1.Enter MemberID in TextBox(Text3)
2.Use that ID and RETURNED value to select MovieName from table Transactions
3.If the Recordset is empty(RsNew.EOF=True) then notify the user that the ID is not valid.
4.Else,add the MovieName to List2.
5.Close the connection
6.Open it again,and select FirstName,LastName,Debt,TotalMoviesRented(TMR) from table Members where MemberID = Text3.Text
7.Place the values into corresponding labels.

Now,the reason I used On Error Resume Next:
If I enter a number in a textbox,and I clear it because I'd like to enter another one I get an error because in that moment Text3.Text is set to nothing.So,in my StringQuery MemberID=Nothing results in an error.
Is there a better way of dealing with this?

THE PROBLEM:
No matter which number I enter,it always executes the IF...THEN statement!Twice!
That means my Recordset is always empty.Why is that?How can it be empty if it's displaying MovieName is List2 and also displays data from Members table in labels?

I'll go rest now and come back later to think it through!

[ungas023]

you should put the cn.close on the end of your if statement. and also input rs.close before the cn.close, hope this helps...

[NeedSomeAnswers]

Can i ask why you are running your code on the Textbox_Change Event ??

You do realise that using this method that for each character you type or delete in your textbox, it will run your query !!!???

So if you type in an id of 1234 your code will run 4 times ?

[ungas023]

i did notice that just now, never seen his full code.. anyway, BlackRiver if i understand you correctly you only want to display a data from your database in your Object Labels whenever you type a number in the text box and display "no result found" if there is no data like that in the database. is that what you are trying to imply?

[BlackRiver]

ungas,yeah you're partly right!I'm actualy running two queries.One to display data in Labels and other to display data in ListBox.WHen I enter the MemberID I want to check if that number exists in table Members.If not,display "no results found".

And NeedSomeAnswers,looks like I didnt think that part through very well!I wanted to reduce the number of buttons on my form so I figured I should use OnChange Event to load data from DB.

Still,that doesnt explain why it executes the IF...THEN statemens when clearly the recordset is not empty??
Also,I noticed that my Program stops responding if I enter a number in a textbox and then clear it in order to enter another!?

[si_the_geek]

It could be better in the _Validate event, which only fires when the textbox loses focus.

Now,the reason I used On Error Resume Next:
If I enter a number in a textbox,and I clear it because I'd like to enter another one I get an error because in that moment Text3.Text is set to nothing.So,in my StringQuery MemberID=Nothing results in an error.
Is there a better way of dealing with this?

There is a better way... and not just because it makes that part easier/quicker, but also because it means you aren't ignoring any other errors that might occur in the rest of the code.

The way you should deal with it is to check Text3.Text before running any of the other code - because the rest of the code is useless when it is empty. eg:

Private Sub Text3_Change()
  If Trim(Text3.Text) = "" Then 
    'something else here if you want (maybe clearing the labels?)
    Exit Sub
  End If
  Set cn = New ADODB.Connection
  ...

Note that you need to be very careful when using On Error Resume Next, and usually it is a bad idea, see the link for explanations.

[ungas023]

ungas,yeah you're partly right!I'm actualy running two queries.One to display data in Labels and other to display data in ListBox.WHen I enter the MemberID I want to check if that number exists in table Members.If not,display "no results found".

And NeedSomeAnswers,looks like I didnt think that part through very well!I wanted to reduce the number of buttons on my form so I figured I should use OnChange Event to load data from DB.

Still,that doesnt explain why it executes the IF...THEN statemens when clearly the recordset is not empty??
Also,I noticed that my Program stops responding if I enter a number in a textbox and then clear it in order to enter another!?

i have made an example program which almost looks like that and working with a listbox a couple of weeks ago, and it will also highlight the name/number of data you want to search and will display the result in label box, let me know if you are interested and btw, you can read it and see all your answers in si_the_geek's tutorial for ADODB.

[NeedSomeAnswers]

Hi Again,

Si, wont your solution for the change event still run multiple times if the id is over 1 character long ?

I was thinking along the lines of the Validate event too, at least you then know the user has finished typing.

And NeedSomeAnswers,looks like I didnt think that part through very well!I wanted to reduce the number of buttons on my form so I figured I should use OnChange Event to load data from DB.

I would first sort out which event you are going to run your code from before worrying about you if statement. It may be related, for instance partial id's will probably return no records.

Try using the texbox_validate event as suggested by Si, this will mean as you tab away from the textbox your code will run.

Also,I noticed that my Program stops responding if I enter a number in a textbox and then clear it in order to enter another!?

Again changing the event you run your code from should help this, but also you should include Si's snippet to stop your code running if the textbox is empty. You should never wont your SQL to run if the textbox is empty.

[si_the_geek]

Si, wont your solution for the change event still run multiple times if the id is over 1 character long ?

Indeed it will, that snippet was just dealing with one of the issues - avoiding the dodgy usage of OERN.

[BlackRiver]

Using _Validate did solve my problem with "empty recordset"!

But when using

Private Sub Text1_Validate(Cancel as Boolean)
If  Len(Trim(Text1.Text))= 0 Then 
MsgBox "Cannot be empty!"
Text1.SetFocus
Exit Sub
End if

it doesnt work very well,because if I leave the textbox empty and "tab away" the messagebox is displayed but the SetFocus doesnt work!The focus is set on Text2.
How to avoid this?

[NeedSomeAnswers]

There is your problem. By trying to get rid of buttons on your form you are encountering other problems. Your SetFocus code does run, but you haven't yet left the _validate sub and when you do leave it, only then does the tab move to the next control.

You will need to find another method to set Focus in or just use a command button.

[si_the_geek]

Don't try to set the focus, instead just set the parameter Cancel to True, as that stops the focus from moving on, eg:

Private Sub Text1_Validate(Cancel as Boolean)
If  Len(Trim(Text1.Text))= 0 Then 
  MsgBox "Cannot be empty!"
  Cancel = True
  Exit Sub
End if
...
End Sub

However, having it like that means that you can't leave the textbox at all until it has text in - so you can't even press a button for "cancel" (if there is one).

[BlackRiver]

Hmmm,guess I'll have to use some more command buttons.My idea was to display the data that the user requested without having him/her to press so many buttons.Maybe I could use a combobox with MemberID and FirstName,LastName.Or maybe even a ListView,or something that can contain the data I need to display.

Anyways,thank you everyone...SEMI-RESOLVED ???

[NeedSomeAnswers]

SEMI-RESOLVED ???

Sound like we need a new thread resolution option :0)

[BlackRiver]

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & App.Path & "\Videoteka.mdb;Persist Security Info=False;"

cn.Open


Set objKomanda = New ADODB.Command
With objKomanda
.ActiveConnection = cn
.CommandType = adCmdText
.CommandText = " SELECT Ime,Prezime FROM RentLog WHERE Datum = ? "
.Prepared = True

.Parameters.Append .CreateParameter("dfd", adDate, adParamInput, , Date)
Set rsNew = objKomanda.Execute
MsgBox rsNew!Ime & rsNew!Prezime

End With

cn.Close

End Sub

So I'm using parametrized query to select firstname,lastname from table RentLog where DateRented = Date

In the table,there are several enteries that match the criteria,but the query only returns the first one?Why doesnt it select all the enteries that match up?

[BlackRiver]

Hmm,I've done some reading on the subject,so here's my code again:

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & App.Path & "\Videoteka.mdb;Persist Security Info=False;"

cn.Open

rsNew.CursorLocation = adUseClient
set rsNew = NEW ADODB.Recordset
Set objKomanda = New ADODB.Command

With objKomanda
.ActiveConnection = cn
.CommandType = adCmdText
.CommandText = " SELECT Ime,Prezime FROM RentLog WHERE Datum = ? "
.Prepared = True
.Parameters.Append .CreateParameter("dfd", adDate, adParamInput, , Date)
 End With

Set rsNew = objKomanda.Execute
MsgBox rsNew!Ime & rsNew!Prezime
Set rsNew.ActiveConnection = Nothing
cn.Close
set cn = Nothing


End Sub

I read that setting the CursorLocation to Client "will force the entire recordset to be fed to the client."
Then I disconnect the recordset from the connection,so I set .ActiveConnection Property to Nothing.I close the connection and now I have a Disconnected RecordSet.Now I can manipulate with its data.
Hope this works!

[NeedSomeAnswers]

You have the whole recordset already !!

You need to loop through it to get to your other records.

do something like;

Do While Not rsNew.EOF
 
MsgBox rsNew!Ime & rsNew!Prezime
 
rsNew.MoveNext
Loop