Scotty Boy

Hi Si

i would say that you are at the beginning of the recordset.
add this code;

The if statement is checking that the recordset is NOT at the beginning. If not at the beginning then move previous is acceptable.

cmbPrevious_Click()

If (NOT rsMatch.BOF) then
rsMatch.MovePrevious
printstatement
End If

End Sub

You should add the above code to movenext routine and check that the rsMatch is not at the end (EOF)

Hope this helps

Scott

[Edited by Scotty Boy - 5/4/2002 4:32:58 PM]

super_si

Im doing this course work

And in
cmbPrevious Click()
ifve got

rsMatch.MovePrevious
printstatement

end sub

BUT it says gonig back isnt valid! why is this? says that i havent said my Recordset can go back?

Si

ChrisB

What are you trying to do? Move back a record on the form you are looking at?

Dracoro

Possibly it's on the 1st record & therefore there aren't any b4 that, thus invalid. Been a while since I've done any vba stuff so take with pinch of salt!.

nigelward

What CursorType did you specify in the call to the Open method?

The default value is adOpenForwardOnly so moving backwards in the recordset will not be a valid operation.

If you used adStatic or adDynamic then call MoveFirst() to explicitly move the recordset to the first record returned by the query.

Nigel

super_si

I dunno the code!! ill paste it later im really sketchy using VB not my best attribute

cheers lads

Si

super_si

Dim dbConnection As New Connection 'Used to establish a link with the database

Dim rsMatch As Recordset
Dim rsSquads As Recordset

Dim intMatchID As Integer

Private Sub cmdAddRecord_Click()

frmSeasonResults.Hide
frmAddMatch.Show

End Sub

Private Sub cmdExit_Click()

Quit

End Sub

Private Sub cmdNext_Click()

rsMatch.MoveNext
If rsMatch.EOF Then
MsgBox "End of the recordset!"
'rsMatch.MoveLast
cmdNext.Enabled = False

Else

printFields
End If
End Sub

Private Sub findSquad()

rsSquads.Open "QuerySquads", dbConnection 'Open the QuerySquads recordset
rsSquads.Filter = "[matchID] = " + CStr(intMatchID) 'Filter out those players that played in the match
'indicated in variable intMatchID

rsSquads.Sort = "[playerSurname] Asc" 'Sort by surname
rsSquads.MoveFirst 'Back to the first record

End Sub


Private Sub printFields()

If rsMatch.EOF Then 'If at the end of recordset , terminate program
' Unload frmSeasonResults
' Unload frmAddMatch
' Unload frmTeamStats
rsMatch.Close ' Close the recordsets
dbConnection.Close ' and the connection
Exit Sub
End If

intMatchID = rsMatch("matchID") ' Get the ID for the this match
findSquad ' Find the squad who played in this match

txtDate = rsMatch!matchDate
txtOpposition = rsMatch!teamName

txtVenue = rsMatch!venu
txtRef = rsMatch!refereeForename + " " + rsMatch!refereeSurname
txtUs = rsMatch!us
txtThem = rsMatch!them


'Loop through the Squads recordset, printing filtered players
Do Until rsSquads.EOF
'Debug.Print " "; rsSquads("playerForename"); " "; rsSquads("playerSurname"); " "; rsSquads("goals")
rsSquads.MoveNext
Loop
rsSquads.Close 'Close the Squads recordset again
End Sub

Private Sub cmdPrevious_Click()


rsMatch.MovePrevious
If rsMatch.BOF Then
MsgBox "Already at beginning of recordset!"
rsMatch.MoveFirst
Else
printFields
End If

End Sub

Private Sub cmdStats_Click()

frmSeasonResults.Hide
frmTeamStats.Show

End Sub
Private Sub Form_Load()
' Open the football database and extract recordsets associated with
' the QueryMatches and QuerySquads queries in that database.

' Create the connection to the football db
Dim strConn As String

'************************************************* ****************************************
'CHANGE THE DATA SOURCE PATH below to point to the football02 database file on your system
'************************************************* ****************************************

strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\football02.mdb;"
dbConnection.Open strConn

' To be able to read database data, the data must first be loaded into a recordset.
' After an ADO Database Connection has been created
' it is possible to create an ADO Recordset
' from the predefined QueryMatches query in the Access db

Set rsMatch = New ADODB.Recordset
rsMatch.Open "QueryMatches", dbConnection
' After a recordset is opened, we can extract data from it.

' Create recordset reference
' to the predefined QuerySquads query in the Access db
' This one holds the records from the QuerySquads Query

Set rsSquads = New ADODB.Recordset
rsSquads.CursorLocation = adUseClient
rsMatch.MoveFirst 'Move to the first record in the recordset

'printFields 'Call the subroutine that prints the fields to the immediate window
End Sub



Private Sub mnuAddMatch_Click()

frmSeasonResults.Hide
frmAddMatch.Show

End Sub

Private Sub mnuExit_Click()

Quit

End Sub


Private Sub mnuResultsPage_Click()

frmTeamStats.Hide
rsSquads.Close 'Close the Squads recordset again


End Sub

Private Sub mnuViewStatistics_Click()

frmSeasonResults.Hide
frmTeamStats.Show
rsSquads.Close 'Close the Squads recordset again
End Sub

SO FAR!
at mo itll read all the Info from D.base then stop at end of recordset i cant go back !Si

super_si

Run time error 3219!

Operation is not allowed in the context

thats off yher previous code surgest above
Si

nigelward

OK, change the following and see if this fixes the problem:

rsSquads.Open "QuerySquads", dbConnection
to:
rsSquads.Open "QuerySquads", dbConnection, dbOpenStatic

and

rsMatch.Open "QueryMatches", dbConnection
to:
rsMatch.Open "QueryMatches", dbConnection, dbOpenStatic

Also replace the call to rsMatch.MoveFirst with rsMatch.MovePrevious in cmdPrevious_Click(), otherwise each time the previous command button is clicked the recordset will move to the first record rather than the previous record.

Nigel


[Edited by nigelward - 5/4/2002 11:13:30 PM]

[Edited by nigelward - 5/4/2002 11:13:56 PM]

super_si

NO!!

In the previous procedure its halting me @
Private Sub cmdPrevious_Click()


----> rsMatch.MovePrevious
If rsMatch.BOF Then
MsgBox "Already at beginning of recordset!"
rrsMatch.MovePrevious
Else
printFields
End If

saying Operation is not allowed in this context

Si

nigelward

Not sure why you're still getting this error, try this code in your form (it works for me):

[code begins]

Option Explicit

Private m_oDBConn As New Connection
Private m_oRS As New Recordset

Private Sub cmdNext_Click()

Call m_oRS.MoveNext

If (m_oRS.EOF = False) Then
Call MsgBox("Them: " + CStr(m_oRS!them) + ", Use: " + CStr(m_oRS!us))

Else
Call MsgBox("EOF")

End If

cmdPrevious.Enabled = True
cmdNext.Enabled = m_oRS.EOF

End Sub

Private Sub cmdPrevious_Click()

Call m_oRS.MovePrevious

If (m_oRS.BOF = False) Then
Call MsgBox("Them: " + CStr(m_oRS!them) + ", Use: " + CStr(m_oRS!us))

Else
Call MsgBox("BOF")

End If

cmdPrevious.Enabled = m_oRS.BOF
cmdNext.Enabled = True

End Sub


Private Sub Form_Load()

Call m_oDBConn.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\football02.mdb;")
Call m_oRS.Open("QueryMatches", m_oDBConn, adOpenStatic)

End Sub

{code ends]

The use of Call and parenthesis on method calls shouldn't affect the way the code works (just the way I code )

Hope this one works, if not

Nigel

PS: 9:50 on a Sunday, bit early for a student?

super_si

wheres this code ment for??

nar its late for me im used to 4am starts

nigelward

YHM

Should find a zip file with the above sample in it, save you from messing up the rest of your souce code.

Nigel

super_si

didnt work !! ive emailed u my code n forms

Si

nigelward

Yes, I discovered this, the button states are not handled correctly but now I have fixed this I can iterate through the QueryMatches query from the Access database you provided.

However I can't get it working with the VB code you provided but I cannot see why it should not work because it is more or less doing the same as my demo .

But still looking at it while watching the MotoGP.

Nigel

super_si

Its vb6 n acess Xp version!

theres some reference you need to instal.........
--> Microsoft ActiveX Data Objects 2.5 Library<--

' --> Microsoft ADO Ext. 2.5 for DDL and Security<--

' --> Microsoft Jet and Replication Objects 2.5 Library<--

' from Project/References.. menu.If uve got Hotmail messenger add pug_106_gti@hotmail.com

Si

nigelward

Nope, don't have messenger.

The code that I have written and the code that you have written doesn't need any of the additional references that have been specified.

The project only uses standard ADO and only needs a reference to the:

Microsoft ActiveX Data Objects 2.5 library

Nigel

super_si

How come you cant run it then?

nigelward

Ooops maybe I should have written that if I try and go backwards using the VB that you have provided I get the same run-time error that you have been experiencing. But if I run my own project I don't get this error.

Both projects are using the access database that you have provided.

Nigel

super_si

it propper annoying ive followed the book aswell!

Next ive got to tally up all the goals from each player on the next form!

nigelward

B0llocks

You know that feeling where you look at something for hours and can't see the wood for the trees, well I think I have spotted my deliberate (stupid) mistake.

The cursor type that I recomended doesn't actually exist (even though I used the correct one in my test program). Anyway change the calls to the open method from:

rsMatch.Open "QueryMatches", dbConnection, dbOpenStatic

to:
rsMatch.Open "QueryMatches", dbConnection, adOpenStatic

Repeat for rsSquad, although a forward only cursor could be used here as you never attempt to move backwards in the recordset.

Hopefully that should sort it.

One other thing, remove the call to MoveFirst (yep my initial suggestion) in Form_Load, if no records are returned by the query it will cause a run-time error.

Nigel

PS: Use Unload to exit from the application, End will not necessarily clear up the resources used by your program.

PPS: Put the line 'Option Explicit' at the top of your source files, it forces all variables to be explicitly defined and would have picked up my mistake when 'Start with Full Compile' is used in VB.

super_si

I changed them to ad but killed its self saying run time error!

Si

did u get it to work on the one i sent you?

Si

nigelward

YHM

super_si

so do you! its still not working lol

nigelward

I've tried the code again this morning, so at least one reboot since yesterday, and I am unable to get the source code I sent you to fail.

super_si

Weird! cannot understand why!

super_si

Dynamic This type of cursor lets users view changes to a data source made by other users. It enables recordset maintenance functions such as adding, changing, and deleting records, and it permits bidirectional navigation around a database. Users can see all changes to a database made by other users. Assign an intrinsic constant of adOpenDynamic to the CursorType property to specify this type of cursor.

Keyset This cursor has many of the properties of a dynamic cursor, except it does not offer immediate access to records added by other users. Records deleted by other users are inaccessible, but they appear in the recordset with a marker. Invoke a recordset’s Requery method to view records added by other users and to clear the deleted markers for records removed by other users. Assign an intrinsic constant of adOpenKeyset to the CursorType property to designate this type of cursor.

Static This cursor is a snapshot of a recordset at a particular point in time. It allows bidirectional navigation. Changes to the database by other users are not visible. This type of cursor is suitable when you do not need information about updates by other users, such as reports from a specific moment in time. Use an intrinsic constant setting of adOpenStatic to create this type of cursor.

Forward-only Sometimes called the fire-hydrant cursor, this type moves in one direction only and can speed up cursor performance. This is the default ADO cursor type. If you need another type of cursor, you must set the CursorType property before opening the recordset. If you are changing a recordset’s cursor type back to the default setting, assign adOpenForwardOnly to its CursorType property