how to move through records on a database.

Devin Panchal wrote:
 > wicked. i've done that, and it works.
 >
 > now i cant figure out this button thing. i get this bof, eof error?? and
 > then i get this do while loop error. do i need to put a 'do while' , 'loop'
 > in???

I thought you had your form_load loop
working properly. Where's this mysterious
'other' loop?

Sounds like you're trying to access records
after you've hit .eof. You'll have to step
through your code to see what's going on.


.... BTW, did you remember to put an
'rs.movefirst' just after your form_load
combobox loop? Otherwise, you're displaying
the first record but pointing to the last!

 >
 > i get a run-time error 2031- saying 'either BOF or EOF is true, or the
 > current record has been deleted. Requested operation requires a current
 > record'

It means you've moved past the end of physical
records. VB doesn't issue an .eof and then
bounce back to the last valid record.

Your record pointer now points to a 'fake'
EOF record. You have to check for eof then
move back to the last record. A simple
IF-THEN-ELSE in your button code is all it
takes.

|bof|rec|rec|...|rec|rec|eof|
^ ^
^--you are here or here-^

 >
 > i know i have to use the 'rs.MoveNext' method. this si what i've got so far;
 >
 > Private Sub cmdNextRecord_Click()
 >
 > rs.MoveNext
 >
 > RefreshForm
 >
 > End Sub

Looks good; all you need is the if-then-else
I mentioned above.

Once you get it working, your 'prev' button
code will be almost identical to your 'next'
button; just switch around the eof's to
bof's and the next's to prev's and the
prev's to nexts.<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: how to move through records on a database. 

ive got the buttons working.
but when i get to the last record, and then i press it, it comes with an eof
bof error. how can i stop it just before, and display a message??

thanx
devin

"Devin Panchal" <d.panchal.TakeThisOut@ntlworld.com> wrote in message
news:fMIQd.442$fh2.225@newsfe2-win.ntli.net...
 > wicked. i've done that, and it works.
 >
 > now i cant figure out this button thing. i get this bof, eof error?? and
 > then i get this do while loop error. do i need to put a 'do while' ,
'loop'
 > in???
 >
 > i get a run-time error 2031- saying 'either BOF or EOF is true, or the
 > current record has been deleted. Requested operation requires a current
 > record'
 >
 > i know i have to use the 'rs.MoveNext' method. this si what i've got so
far;
 >
 > Private Sub cmdNextRecord_Click()
 >
 > rs.MoveNext
 >
 > RefreshForm
 >
 > End Sub
 >
 >
 > thanx
 > devin
 >
 >
 >
 > "Randy Day" <ruthal.TakeThisOut@sasktel.nex> wrote in message
 > news:1114jqtab67uk52@corp.supernews.com...
  > > Devin Panchal wrote:
  > >
   > > > like this;
   > > > ...(from above)
   > > > i = i + 1
   > > > rs.MoveNext
   > > > Loop
   > > >
  > >
  > > rs.movefirst 'now you're back at the first record
  > >
   > > > End Sub
   > > >
   > > > Private Sub Form_Unload(Cancel As Integer)
   > > >
   > > > rs.Close
   > > > Set Assignment2 = Nothing
   > > > Set rs = Nothing
   > > >
   > > > End Sub
   > > >
   > > > is that correct? sorted! now i read the bit about navigating through
  > >
  > > Looks good. That 'i=i+1' statement isn't
  > > doing anything for you anymore, so you
  > > can get rid of it, too...
  > >
   > > > records, using buttons. wat do u mean, (RefreshSub)??
  > >
  > > I picked a name out of the air to show you
  > > how to create your own subroutine.
  > >
  > > When you move to a new record, you have to
  > > re-fill your textboxes, right? You do the
  > > same 'txtFirstName=...' assignments in at
  > > least three places: form_load at the start,
  > > and when you click a next/prev nav button.
  > >
  > > Just put all your 'txtFirstName=...' lines
  > > into a subroutine, then call the subroutine
  > > in each of the three places instead.
  > >
  > > Saves time, space and headaches.
  > >
   > > >
   > > > this is wat i've got for my button;
   > > >
   > > > Private Sub cmdNextRecord_Click()
   > > > rs.MoveNext
   > > > txtFirstName.Text...??
   > > > End Sub
 >
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: how to move through records on a database. 

i've had to modify some of the code u gave me, cos it aint wokrkin.

this is what i've got. and it works so far;

Private Sub Form_Load()
Dim i As Integer
Set Assignment2 = New ADODB.Connection
Assignment2.Provider = "Microsoft.Jet.OLEDB.4.0;"
Assignment2.ConnectionString = "C:\...\Assignment2.mdb"
Assignment2.Open
Set rs = New ADODB.Recordset

rs.Open "select * from submission_info", Assignment2, adOpenDynmic,
adLockOptimistic

'If rs.RecordCount > 0 Then 'do the loop only if records exist
' rs.MoveFirst
RefreshForm

cmbName.AddItem rs.Fields.Item("contact_author_first_name")

rs.MoveFirst

End Sub

the if statement wat u told me about, dont work. if i leave it in, all the
fields are blank.
when its like this, the first record shows, and the next button works fine.
but when i get to the last record i get the error.

for the next button, this is what i've got;

Private Sub cmdNextRecord_Click()
rs.MoveNext
RefreshForm
If rs.EOF = True Then
rs.MoveNext
Else
rs.MoveNext
RefreshForm
End If
End Sub

i know its wrong, i know somthing is in the wrong order. i think the 1st 2
lines should go after the 'if'.

thanx
dev


"Randy Day" <ruthal.TakeThisOut@sasktel.nex> wrote in message
news:111713etl8610b8@corp.supernews.com...
 > Devin Panchal wrote:
  > > wicked. i've done that, and it works.
  > >
  > > now i cant figure out this button thing. i get this bof, eof error?? and
  > > then i get this do while loop error. do i need to put a 'do while' ,
'loop'
  > > in???
 >
 > I thought you had your form_load loop
 > working properly. Where's this mysterious
 > 'other' loop?
 >
 > Sounds like you're trying to access records
 > after you've hit .eof. You'll have to step
 > through your code to see what's going on.
 >
 >
 > ... BTW, did you remember to put an
 > 'rs.movefirst' just after your form_load
 > combobox loop? Otherwise, you're displaying
 > the first record but pointing to the last!
 >
  > >
  > > i get a run-time error 2031- saying 'either BOF or EOF is true, or the
  > > current record has been deleted. Requested operation requires a current
  > > record'
 >
 > It means you've moved past the end of physical
 > records. VB doesn't issue an .eof and then
 > bounce back to the last valid record.
 >
 > Your record pointer now points to a 'fake'
 > EOF record. You have to check for eof then
 > move back to the last record. A simple
 > IF-THEN-ELSE in your button code is all it
 > takes.
 >
 > |bof|rec|rec|...|rec|rec|eof|
 > ^ ^
 > ^--you are here or here-^
 >
  > >
  > > i know i have to use the 'rs.MoveNext' method. this si what i've got so
far;
  > >
  > > Private Sub cmdNextRecord_Click()
  > >
  > > rs.MoveNext
  > >
  > > RefreshForm
  > >
  > > End Sub
 >
 > Looks good; all you need is the if-then-else
 > I mentioned above.
 >
 > Once you get it working, your 'prev' button
 > code will be almost identical to your 'next'
 > button; just switch around the eof's to
 > bof's and the next's to prev's and the
 > prev's to nexts.<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: how to move through records on a database. 

this post is not archived.
 >> Stay informed about: how to move through records on a database. 

Devin Panchal wrote:
 > i've had to modify some of the code u gave me, cos it aint wokrkin.
 >
 > this is what i've got. and it works so far;
 >
 > Private Sub Form_Load()
 > Dim i As Integer
 > Set Assignment2 = New ADODB.Connection
 > Assignment2.Provider = "Microsoft.Jet.OLEDB.4.0;"
 > Assignment2.ConnectionString = "C:\...\Assignment2.mdb"
 > Assignment2.Open
 > Set rs = New ADODB.Recordset
 >
 > rs.Open "select * from submission_info", Assignment2, adOpenDynmic,
 > adLockOptimistic
 >

The following should work as long as NOTHING
in your RefreshForm sub moves the record pointer.
It IS just a bunch of 'txtBox.Text= ...' statements,
right?

If rs.RecordCount = 0 Then
'you have no records to process
else
rs.MoveFirst
RefreshForm
Do while not rs.eof
cmbName.AddItem rs.Fields.Item("contact_author_first_name")
rs.movenext
loop
rs.MoveFirst
end if

 >
 > End Sub
 >
 > the if statement wat u told me about, dont work. if i leave it in, all the
 > fields are blank.
 > when its like this, the first record shows, and the next button works fine.
 > but when i get to the last record i get the error.
 >
 > for the next button, this is what i've got;
 >
 > Private Sub cmdNextRecord_Click()
 > rs.MoveNext
 > RefreshForm
^
Get rid of this line.

 > If rs.EOF = True Then
 > rs.MoveNext

Wrong direction. Move the other way.

 > Else
 > rs.MoveNext
^
Get rid of this line.

 > RefreshForm
 > End If
 > End Sub<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: how to move through records on a database. 

i've got a sub routine called refreshform, that refreshes the form.
and i have to put that in for it to work.

but the problem is, when i 1st click the next button, nothing happens, its
only moves after the 2nd click.

dev

"mickey" <mickeyATgruizelgruis.nl> wrote in message
news:5sl711h0925mk67e9vodrask848stf7i11@4ax.com...
 > On Wed, 16 Feb 2005 18:07:52 GMT, "Devin Panchal"
 > <d.panchal DeleteThis @ntlworld.com> you typed some letters in random order:
 >
  > >Private Sub cmdNextRecord_Click()
  > >rs.MoveNext
  > >RefreshForm
  > >If rs.EOF = True Then
  > >rs.MoveNext
  > >Else
  > >rs.MoveNext
  > >RefreshForm
  > >End If
  > >End Sub
 >
 >
 > You could try:
 > Private Sub cmdNextRecord_Click()
 > If not rs.eof then
 > rs.movenext
 > else
 > cmdNextRecord.enabled=false
 > end if
 > end sub
 > Groetjenz,
 >
 > Mickey
 > --
 > #### gewoan skrieve su ast ut seist ####<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: how to move through records on a database. 

wicked.
its works. U DA MAN!!
i added some stuff in, and works perfectly.

is it possible to update (as in modify), and delete records from this form i
have made??
and would i need SQL, to update and delete a record?

i've got somthing like this for delete.

rs.Open "select * from submission_info", Assignment2, adOpenDynmic,
adLockOptimistic
rs.delete
rs.update

i know i have to assign it to a delete button.
thanx
devin

"Randy Day" <ruthal DeleteThis @sasktel.nex> wrote in message
news:11183p9dkaoiq2a@corp.supernews.com...
 > Devin Panchal wrote:
  > > i've had to modify some of the code u gave me, cos it aint wokrkin.
  > >
  > > this is what i've got. and it works so far;
  > >
  > > Private Sub Form_Load()
  > > Dim i As Integer
  > > Set Assignment2 = New ADODB.Connection
  > > Assignment2.Provider = "Microsoft.Jet.OLEDB.4.0;"
  > > Assignment2.ConnectionString = "C:\...\Assignment2.mdb"
  > > Assignment2.Open
  > > Set rs = New ADODB.Recordset
  > >
  > > rs.Open "select * from submission_info", Assignment2, adOpenDynmic,
  > > adLockOptimistic
  > >
 >
 > The following should work as long as NOTHING
 > in your RefreshForm sub moves the record pointer.
 > It IS just a bunch of 'txtBox.Text= ...' statements,
 > right?
 >
 > If rs.RecordCount = 0 Then
 > 'you have no records to process
 > else
 > rs.MoveFirst
 > RefreshForm
 > Do while not rs.eof
 > cmbName.AddItem rs.Fields.Item("contact_author_first_name")
 > rs.movenext
 > loop
 > rs.MoveFirst
 > end if
 >
  > >
  > > End Sub
  > >
  > > the if statement wat u told me about, dont work. if i leave it in, all
the
  > > fields are blank.
  > > when its like this, the first record shows, and the next button works
fine.
  > > but when i get to the last record i get the error.
  > >
  > > for the next button, this is what i've got;
  > >
  > > Private Sub cmdNextRecord_Click()
  > > rs.MoveNext
  > > RefreshForm
 > ^
 > Get rid of this line.
 >
  > > If rs.EOF = True Then
  > > rs.MoveNext
 >
 > Wrong direction. Move the other way.
 >
  > > Else
  > > rs.MoveNext
 > ^
 > Get rid of this line.
 >
  > > RefreshForm
  > > End If
  > > End Sub<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: how to move through records on a database. 

this post is not archived.
 >> Stay informed about: how to move through records on a database. 

i clocked the delete button. its works well. this is waht i've got;
Private Sub cmdDeleteRecord_Click()
If Not rs.EOF Then
rs.Delete
MsgBox "You have deleted 1 record.", vbExclamation, "Database"
rs.MoveFirst
RefreshForm
rs.Update
End If
End Sub

is it possible to modify records?? (existing records) and then it updates
the database?

dev

"Devin Panchal" <d.panchal.DeleteThis@ntlworld.com> wrote in message
news:e01Rd.128$0x1.95@newsfe2-win.ntli.net...
 > wicked.
 > its works. U DA MAN!!
 > i added some stuff in, and works perfectly.
 >
 > is it possible to update (as in modify), and delete records from this form
i
 > have made??
 > and would i need SQL, to update and delete a record?
 >
 > i've got somthing like this for delete.
 >
 > rs.Open "select * from submission_info", Assignment2, adOpenDynmic,
 > adLockOptimistic
 > rs.delete
 > rs.update
 >
 > i know i have to assign it to a delete button.
 > thanx
 > devin
 >
 > "Randy Day" <ruthal.DeleteThis@sasktel.nex> wrote in message
 > news:11183p9dkaoiq2a@corp.supernews.com...
  > > Devin Panchal wrote:
   > > > i've had to modify some of the code u gave me, cos it aint wokrkin.
   > > >
   > > > this is what i've got. and it works so far;
   > > >
   > > > Private Sub Form_Load()
   > > > Dim i As Integer
   > > > Set Assignment2 = New ADODB.Connection
   > > > Assignment2.Provider = "Microsoft.Jet.OLEDB.4.0;"
   > > > Assignment2.ConnectionString = "C:\...\Assignment2.mdb"
   > > > Assignment2.Open
   > > > Set rs = New ADODB.Recordset
   > > >
   > > > rs.Open "select * from submission_info", Assignment2, adOpenDynmic,
   > > > adLockOptimistic
   > > >
  > >
  > > The following should work as long as NOTHING
  > > in your RefreshForm sub moves the record pointer.
  > > It IS just a bunch of 'txtBox.Text= ...' statements,
  > > right?
  > >
  > > If rs.RecordCount = 0 Then
  > > 'you have no records to process
  > > else
  > > rs.MoveFirst
  > > RefreshForm
  > > Do while not rs.eof
  > > cmbName.AddItem rs.Fields.Item("contact_author_first_name")
  > > rs.movenext
  > > loop
  > > rs.MoveFirst
  > > end if
  > >
   > > >
   > > > End Sub
   > > >
   > > > the if statement wat u told me about, dont work. if i leave it in, all
 > the
   > > > fields are blank.
   > > > when its like this, the first record shows, and the next button works
 > fine.
   > > > but when i get to the last record i get the error.
   > > >
   > > > for the next button, this is what i've got;
   > > >
   > > > Private Sub cmdNextRecord_Click()
   > > > rs.MoveNext
   > > > RefreshForm
  > > ^
  > > Get rid of this line.
  > >
   > > > If rs.EOF = True Then
   > > > rs.MoveNext
  > >
  > > Wrong direction. Move the other way.
  > >
   > > > Else
   > > > rs.MoveNext
  > > ^
  > > Get rid of this line.
  > >
   > > > RefreshForm
   > > > End If
   > > > End Sub
 >
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: how to move through records on a database. 

Devin Panchal wrote:
 > i clocked the delete button. its works well. this is waht i've got;
 > Private Sub cmdDeleteRecord_Click()
 > If Not rs.EOF Then

Don't forget BOF.

 > rs.Delete
 > MsgBox "You have deleted 1 record.", vbExclamation, "Database"
 > rs.MoveFirst
 > RefreshForm
 > rs.Update
 > End If
 > End Sub
 >
 > is it possible to modify records?? (existing records) and then it updates
 > the database?

Remember that chunk of code I told you to stick
away in a corner for reference because it was
'backwards'? That is how you update your DB.

Instead of assigning field values to textboxes,
assign textbox values to fields, then do the
'rs.update'.

There is also a way to do updates with actual
SQL statements, but that is more useful if you
want to update several records meeting a specified
condition.

Next you'll be asking about adding new records,
so I'll just suggest you do a search for 'addnew'
in the Juicy Studio tutorial.

<a style='text-decoration: underline;' href="http://juicystudio.com/tutorial/vb/database.asp" target="_blank">http://juicystudio.com/tutorial/vb/database.asp</a><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: how to move through records on a database. 

i can delete records properly. i dont think i need to mess araound with that
anymore.
i have not made the database, that is from the lecturer. it has been poorly
made. it had no primary keys or relationships, i just made them for this
assignment.

i want to know how to update a record, in VB. is it possible to use this
form i am currently working on, to modify data, and write it back into the
databse? e.g. like a real database, where u can edit the data, and it will
save.

thanx

devin

"mickey" <mickeyATgruizelgruis.nl> wrote in message
news:mac911tp5k5u1n2p36kkqjb952oi2nqpgg@4ax.com...
 > On Thu, 17 Feb 2005 14:46:03 GMT, "Devin Panchal"
 > <d.panchal.TakeThisOut@ntlworld.com> you typed some letters in random order:
 >
  > >i clocked the delete button. its works well. this is waht i've got;
  > >Private Sub cmdDeleteRecord_Click()
  > >If Not rs.EOF Then
  > >rs.Delete
  > >MsgBox "You have deleted 1 record.", vbExclamation, "Database"
  > >rs.MoveFirst
  > >RefreshForm
  > >rs.Update
  > >End If
  > >End Sub
  > >
  > >is it possible to modify records?? (existing records) and then it updates
  > >the database?
 >
 > Do you use an 'ID' with 'autoincrement' in your database? That makes
 > deleting much simpler.
 >
 > Perhaps it is possible to e-mail your code and DB.
 >
 > Groetjenz,
 >
 > Mickey
 > --
 > #### gewoan skrieve su ast ut seist ####<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: how to move through records on a database. 

Devin Panchal wrote:

 > i clocked the delete button. its works well. this is waht i've got;
 > Private Sub cmdDeleteRecord_Click()
 > If Not rs.EOF Then
 > rs.Delete
 > MsgBox "You have deleted 1 record.", vbExclamation, "Database"
 > rs.MoveFirst
 > RefreshForm
 > rs.Update
 > End If
 > End Sub

Devin, I put this in as separate reply to let you
get those other changes made and understood.

I wanted to make some suggestions about programming
style, especially indentation.

You may have noticed I've sent you a few code snippets
where some lines were indented. Indenting lines within
a loop or IF statement is a commonly accepted (some might
say 'expected') means of debugging code.

It's surprising how often a visual cue such as the
wrong indentation on a line will point out a coding
error. I've included two copies of your Delete code
below, both with errors introduced; see which one you
spot quicker!

Private Sub cmdDeleteRecord_Click()
If Not rs.EOF Then
rs.Delete
MsgBox "You have deleted 1 record.", vbExclamation, "Database"
rs.MoveFirst
RefreshForm
rs.Update
End Sub

Private Sub cmdDeleteRecord_Click()
If Not rs.EOF Then
rs.Delete
MsgBox "You have deleted 1 record.", vbExclamation, "Database"
rs.MoveFirst
RefreshForm
End If
rs.Update
End Sub

It's normal to have multiple levels of indentation;
notice below how the 'hierarchy' of the lines
appears visually. Lines in the same loop appear at
the same level of indentation:

if v = 7 then
for x = 1 to 9
w = w + 1
do while z < w
rs.addnew
rs.movelast
rs.fields.item("Total") = x
loop
w = w + x
next x
rs.update
end if

In VB (and other languages) the TAB key is your
friend!

**************************************************

The other thing you might find useful is the
'with' keyword. Here's your delete code again:

Private Sub cmdDeleteRecord_Click()
With rs
If Not .EOF Then
.Delete
MsgBox "You have deleted 1 record.", vbExclamation, "Database"
.MoveFirst
RefreshForm
.Update
End If
End with
End Sub

With a nice short recordset name like 'rs' it isn't
so obvious, but imagine the savings in keystrokes
if 'rs' was called 'rsMyRecordSetNumber1'.

'With' can be used with any object using the '.'
separator: textboxes, comboboxes, recordsets, etc.<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: how to move through records on a database. 

lol, i done that already.
thats the 1st form, i done. it works well.

thanx man, that code came in use. thanx. now i got this form fully working
as well.

thanx again.

dev

"Randy Day" <ruthal.TakeThisOut@sasktel.nex> wrote in message
news:1119f1lruh986fb@corp.supernews.com...
 > Devin Panchal wrote:
  > > i clocked the delete button. its works well. this is waht i've got;
  > > Private Sub cmdDeleteRecord_Click()
  > > If Not rs.EOF Then
 >
 > Don't forget BOF.
 >
  > > rs.Delete
  > > MsgBox "You have deleted 1 record.", vbExclamation, "Database"
  > > rs.MoveFirst
  > > RefreshForm
  > > rs.Update
  > > End If
  > > End Sub
  > >
  > > is it possible to modify records?? (existing records) and then it
updates
  > > the database?
 >
 > Remember that chunk of code I told you to stick
 > away in a corner for reference because it was
 > 'backwards'? That is how you update your DB.
 >
 > Instead of assigning field values to textboxes,
 > assign textbox values to fields, then do the
 > 'rs.update'.
 >
 > There is also a way to do updates with actual
 > SQL statements, but that is more useful if you
 > want to update several records meeting a specified
 > condition.
 >
 > Next you'll be asking about adding new records,
 > so I'll just suggest you do a search for 'addnew'
 > in the Juicy Studio tutorial.
 >
<font color=purple> > <a style='text-decoration: underline;' href="http://juicystudio.com/tutorial/vb/database.asp</font" target="_blank">http://juicystudio.com/tutorial/vb/database.asp</font</a>><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: how to move through records on a database.