**RESOLVED**SQL Statement - Need a little HELP!

Ok, I thought that I was done, then the Management team pull me back!

On one of my forms I needed to view only records that follow a certain criteria....within the OnLoad event I have the following code:

VB Code:

Dim rs as Recordset
Set rs = New Recordset
 
rs.Open "SELECT AgentCompleted FROM Main WHERE AgentCompleted <> ' " & Text1.Text & "'", MyDataEnvironment, MyConnection
rs.MoveFirst
rs.Close
' so on 
' and so on

Now this workd great, it opens to the first record that fits the SQL criteria..but now on the form I have a command button for "Next Record"...I wrote the following code inside this command:

VB Code:

Dim rs as Recordset
Set rs = New Recordset
rs.Open "SELECT AgentCompleted FROM Main WHERE AgentCompleted <> ' " & Text1.Text & "'", MyDataEnvironment, MyConnection
rs.MoveNextrs.Close
'so on

but this SQL will not work....it does not give me an error message, but it will not move to the next available record???

Any suggestions??

Thanks

Don't believe

Code:

---

rs.MoveNextrs.Close

---

is a valid ado method.

Sorry, I wrote it wrong in here...

it should be :

VB Code:

Dim rs as Recordset
Set rs = New Recordset
 
rs.Open "SELECT AgentCompleted FROM Main WHERE AgentCompleted <> ' " & Text1.Text & "'", MyDataEnvironment, MyConnection
rs.MoveNext
rs.Close

Why not set rs as a form level variable (b/c you certainly don't need the traffic of going back and forth to the server for the *exact same data*) and then call the .MoveNext method from the Next Record cmd button?

I'm sorry if I sound stupid...but would the form level variable be set within the Load event or right within the cmd button?

And is this done to avoid creating a new recordset?

Thanks for the lesson!

You need to do some reading on variable scope. http://msdn.microsoft.com/library/de...fvariables.asp

Code:

---

Option Explicit
Dim rs As Recordset

Private Sub Form_Load()
    Set rs = New Recordset
    rs.Open "SELECT AgentCompleted FROM Main WHERE AgentCompleted <> ' " & Text1.Text & "'", MyDataEnvironment, MyConnection
    rs.MoveFirst
End Sub

Private Sub cmdNextRecord_Click()
    rs.MoveNext
End Sub

Private Sub Form_Terminate()
    rs.Close
    Set rs = Nothing
End Sub

---

Briantcva,

I appreciate the link, and yes I definitely need to do some more reading on these subjects...

I took your suggestion, and at first I received the Object not set errors, so I adjusted the code so that the Dim statements that were originally in each sub command were removed, and made sure that the proper set statments were placed....

But it still is not moving...I can tell this, because also within the code I am using the same SQL query as I gave earlier to count the records that fit that criteria...and it chows that there are still some records...

How can you tell it's not moving? Do you have bound (or manually filled for that matter) text boxes and the like that aren't updating?

Yes that's right...I can tell if it is not moving because within the Next Record cmd button I have a text box showing the total number of records that fit the criteria outlined in the SQl query...right now the count shows 2.

So when I open the form and place a name in let's say "Agent" text box and hit the Next cmd button it should send that name to the table and adjust the record count..

Right now all it does is stay on the same record, the number is not changing...

General declerations, Form_Load, cmdNext, common subs that might be called.

VB Code:

Option Explicit
Dim rs As Recordset
 
 
Private Sub cmdNextRec_Click()
Const Formclose As Long = 3021
On Error GoTo ErrorMsg
 
 
Dim i As Integer, j As Integer
Set rs = New Recordset
 
rs.MoveNext
 
rs.Open "SELECT AgentCmpltd FROM Main WHERE AgentCmpltd <> '" & Text1.Text & "'", MyDataEnvironment.MyConnection
i = rs.RecordCount
Text2.Text = i
rs.Close
 
rs.Open "SELECT RepName FROM Main WHERE RepName <> '" & Text1.Text & "'", MyDataEnvironment.MyConnection
j = rs.RecordCount
Text3.Text = j
rs.Close
 
txtSlsRefNot.Text = Text3.Text - Text2.Text
 
DisplayEnd:
    Exit Sub
 
ErrorMsg:
    If Err = Formclose Then
    frmMsgboxLstRec.Show
    End If
Resume DisplayEnd
End Sub
__________________________________________________
Private Sub cmdPrevRec_Click()
Const Formclose As Long = 3021
On Error GoTo ErrorMsg
 
MyDataEnvironment.rsCommand1.MovePrevious
 
DisplayEnd:
    Exit Sub
 
ErrorMsg:
    If Err = Formclose Then
    frmMsgbx1stRec.Show
    End If
Resume DisplayEnd
 
End Sub
____________________________________________________
Private Sub Form_Load()
Dim i As Integer, j As Integer
Set rs = New Recordset
 
rs.Open "SELECT AgentCmpltd FROM Main WHERE AgentCmpltd <> '" & Text1.Text & "'", MyDataEnvironment.MyConnection
If rs.EOF = True And rs.BOF = True Then
'do nothing
Else
rs.MoveFirst
 
If rs.RecordCount <> 0 Then
cmdPrevRec.Visible = True
Else
cmdPrevRec.Visible = False
 
End If
txtDateCmpltd.Text = Date
rs.Close
 
rs.Open "SELECT AgentCmpltd FROM Main WHERE AgentCmpltd <> '" & Text1.Text & "'", MyDataEnvironment.MyConnection
i = rs.RecordCount
Text2.Text = i
rs.Close
 
rs.Open "SELECT RepName FROM Main WHERE RepName <> '" & Text1.Text & "'", MyDataEnvironment.MyConnection
j = rs.RecordCount
Text3.Text = j
rs.Close
 
txtSlsRefNot.Text = Text3.Text - Text2.Text
End If
 
 
End Sub

Please tell me you're indenting your code in your app and that it's just a formatting issue here? Makes things ever so much easier to read. Anyway...

Why are you opening the exact same rs twice in your form load?

Your problem is that you're closing the rs each time you use it. Open the rs when you load the form and then use the .MoveNext/.MovePrevious to navigate. Close and dum the rs when you do the same to the form.

The reason why I have the recordsets opening and closing, was that I needed to use the various queries in order to pull the data...

All I simply want to do is use this form to view entries made by the Main form which are fed to the Access table...when the user view a record they will need to enter their name into a textbox...this will serve as a signature...I had the form calculate how many records were in the table that did not have a signature...that is where the queries came into play.

Once all records had an agent;s signature then I would alert them that there are no more records to view...this is where I am...I can move through the recordset, but I am having trouble with limiting the recordset to just records that have no agent name?? Also I noticed that when I went to the Access table to view th records from there..that each time I go to this form it is creating a new record :confused: :confused:

So I am back to square 1...

Any ideas will be appreciated...

Thank you.

Quote:

---

Once all records had an agent;s signature then I would alert them that there are no more records to view...this is where I am...I can move through the recordset, but I am having trouble with limiting the recordset to just records that have no agent name??

---

Create a new query in Access w/ the "Find Unmatched Query Wizard."

That was an excellent suggestion!!! I should have thought of that...I guess sometimes thinking a little simpler, is really the best way to go!

Thank you once again..

I hope that you don't come looking for payment on my tab!!!

:D