how to move through records on a database.

hi,

sorry to be a pain, i just cant figure it out. i've put a combo box at the
top for easy navigation, but i still cant get the fields to display any
reocrds.

i've used the link u gave me, and i keep getting errors at the data source
bit, wven thought i put teh correct path in.

this is what i've got so far.

Dim Assignment2 As Connection
Dim rs As Recordset

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
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

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

Do While Not rs.EOF

cmbName.List(i) = rs!contact_author_first_name

'rs.MoveNext

rs!Title = cboTitle.Text
rs!contact_author_first_name = txtFirstName.Text
rs!contact_author_last_name = txtSecondName.Text
rs!contact_author_address_1 = txtInstitute.Text
'rs!contact_author_address_2 = txtNameOfInstitute.Text
'rs!contact_author_address_3 = txtAddress.Text
'rs!contact_author_address_4 = txtCity.Text
'rs!contact_author_address_5 = txtCountry.Text
'rs!contact_author_address_6 = txtZipCode.Text
'rs!paper_ID = txtPaperID.Text
'rs!paper_title = txtPaperTitle.Text
'rs.Update

i = i + 1
rs.MoveNext
Loop
rs.Close
Set Assignment2 = Nothing
Set rs = Nothing

End Sub

i'm totatly new to this, and am guessing most of it. when i run this code, i
get an error at 'rs!Title' bit.
my other form works, and that is why i have used the fields from that form,
to make this sort of datbase. all i want to do is view and modify the
datbase.

thanx
dev

"Randy Day" <ruthal DeleteThis @sasktel.nex> wrote in message
news:110prpcs7so8u86@corp.supernews.com...
 > Devin Panchal wrote:
  > > hi,
  > >
  > > i've finished my 1st form, and now i have to create another form, which
  > > enables me to move through records, and make modifications.
  > > i want to use a simialr sort of form, as my 1st one, but need a couple
of
  > > extra buttons to move forward 1 record and move back 1 record. I'm
guessing
  > > i'll need and update button as well, or will this be automatic?
  > >
  > > this is what i'v got so far;
  > >
  > > Dim Assignment2 As Connection
  > > Dim rs As Recordset
 >
 > Some useful examples:
<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>>
 >
 > Your buttons won't work because your 'ar'
 > variable is local to the Click() event;
 > it disappears when the procedure finishes.
 >
 > I think you want to load the 'rs' recordset
 > and use your other buttons to navigate though
 > *it*...
 >
  > >
  > > Private Sub cmdNextRecord_Click()
  > > Dim i As Integer
  > > Set Assignment2 = New ADODB.Connection
  > > Assignment2.Provider = "Microsoft.Jet.OLEDB.4.0;"
  > > Assignment2.ConnectionString = "C:\...\Assignment2.mdb"
  > > Assignment2.Open
  > > Dim ar As ADODB.Recordset
  > > Set ar = New ADODB.Recordset
  > >
  > > ar.Open "select * from submission_info", Assignment2, adOpenDynmic,
  > > adLockOptimistic
  > >
  > > "ar.MoveNext"
  > >
  > > ar!Title = cboTitle.Text
  > > ar!contact_author_first_name = txtFirstName.Text
  > > ar!contact_author_last_name = txtSecondName.Text
  > > ar!contact_author_address_1 = txtInstitute.Text
  > > ar!contact_author_address_2 = txtNameOfInstitute.Text
  > > ar!contact_author_address_3 = txtAddress.Text
  > > ar!contact_author_address_4 = txtCity.Text
  > > ar!contact_author_address_5 = txtCountry.Text
  > > ar!contact_author_address_6 = txtZipCode.Text
  > > ar!paper_ID = txtPaperID.Text
  > > ar!paper_title = txtPaperTitle.Text
  > > ar.Update
  > >
  > > End Sub
  > >
  > > i've put " marks where i think somthng needs to go. i'm trying to get
the
  > > fields above to change to a specific record when the suer clicks on the
  > > next/previous button.
  > >
  > > i know i have to use some kind of 'MoveNext' command.
  > >
  > > thanx
  > >
  > > devin
  > >
  > >
  > ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: how to move through records on a database. 

Devin Panchal wrote:

 > hi,
 >
 > sorry to be a pain, i just cant figure it out. i've put a combo box at the
 > top for easy navigation, but i still cant get the fields to display any
 > reocrds.
 >
 > i've used the link u gave me, and i keep getting errors at the data source
 > bit, wven thought i put teh correct path in.
 >
 > this is what i've got so far.
 >
 > Dim Assignment2 As Connection
 > Dim rs As Recordset

Dim Assignment2 As ADODB.Connection
Dim rs As ADODB.Recordset

 > 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
 > Dim rs As ADODB.Recordset

Get rid of the line above. You've already
created rs.

 > 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
 > Do While Not rs.EOF
 >
'assuming 'contact_author_first_name' is the database field name
cmbName.additem rs.fields.item("contact_author_first_name")
 > cmbName.List(i) = rs!contact_author_first_name
 >
 > 'rs.MoveNext
 >
 > rs!Title = cboTitle.Text
 > rs!contact_author_first_name = txtFirstName.Text
 > rs!contact_author_last_name = txtSecondName.Text
 > rs!contact_author_address_1 = txtInstitute.Text

I'm confused here. Are you trying to put the
textbox info into the recordset, or the recordset
info into the textboxes? See my 'additem' line
above for proper access to rs field data.

 > 'rs!contact_author_address_2 = txtNameOfInstitute.Text
 > 'rs!contact_author_address_3 = txtAddress.Text
 > 'rs!contact_author_address_4 = txtCity.Text
 > 'rs!contact_author_address_5 = txtCountry.Text
 > 'rs!contact_author_address_6 = txtZipCode.Text
 > 'rs!paper_ID = txtPaperID.Text
 > 'rs!paper_title = txtPaperTitle.Text
 > 'rs.Update
 >
 > i = i + 1
 > rs.MoveNext
 > Loop

End If

 > rs.Close

Don't run these next two lines unless
you're absolutely sure you're finished
doing any and all database work!

 > Set Assignment2 = Nothing
 > Set rs = Nothing
 >
 > End Sub
 >
 > i'm totatly new to this, and am guessing most of it. when i run this code, i
 > get an error at 'rs!Title' bit.
 > my other form works, and that is why i have used the fields from that form,
 > to make this sort of datbase. all i want to do is view and modify the
 > datbase.

I'm willing to bet you don't want a loop like this.
Assuming you get the recordset working and the field
values into your textboxes, when you run, your
textboxes will flicker and display the *last* record
in the recordset (but at least your combobox will be
filled)!

Then, when you try to use your navigation buttons,
you'll get an error because your recordset no longer
exists and you've set your database
connection = nothing.

Concentrate on getting the first record displayed.
For now, leave your database connection and recordset
open until the program ends; VB will clean them up
when it exits.

If you want to use rs for a new search while the
program is running, issue the rs.close command
before doing the new rs.open.


Have fun! <big evil grin><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: how to move through records on a database. 

hi,

i've read wat u wrote, and edited my code, but it still does not work.
i want to display the database data into my fields. (just like a database).

the code does not have any errors, but the combo is blank now. (it just says
combo1 in it.)
this is wat i've got;

Dim Assignment2 As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Form_Load()
Dim i As Integer
Set Assignment2 = New ADODB.Connection
Assignment2.Provider = "Microsoft.Jet.OLEDB.4.0;"
Assignment2.ConnectionString = "C:\Documents and Settings\Devin Panchal\My
Documents\My Documents\Devins work\Computer Science- Year 2\262cs-
Commercial Programming\Coursework 1\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

Do While Not rs.EOF
cmbName.AddItem rs.Fields.Item("contact_author_first_name")
cmbName.List(i) = rs!contact_author_first_name

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

rs.MoveNext

'rs!Title = cboTitle.Text
'rs!contact_author_first_name = txtFirstName.Text
'rs!contact_author_last_name = txtSecondName.Text
'rs!contact_author_address_1 = txtInstitute.Text
'rs!contact_author_address_2 = txtNameOfInstitute.Text
'rs!contact_author_address_3 = txtAddress.Text
'rs!contact_author_address_4 = txtCity.Text
'rs!contact_author_address_5 = txtCountry.Text
'rs!contact_author_address_6 = txtZipCode.Text
'rs!paper_ID = txtPaperID.Text
'rs!paper_title = txtPaperTitle.Text
'rs.Update

i = i + 1
rs.MoveNext
Loop

End If

rs.Close
'Set Assignment2 = Nothing
'Set rs = Nothing

End Sub

also is there any chance, to get the buttons to work? e.g. i've got a next
and previous button, how do i assign them to move forward and back in the
database. (cmdNextRecord).
Note. the above code is executed when loading the form.

also before i started editing the form, i used 'ar' and have now changed
them all to 'rs'. wat does this mean? are they special key words? which
should i use?


thanx for all the help. i've learnt a lot.

dev


"Randy Day" <ruthal.DeleteThis@sasktel.nex> wrote in message
news:110v7fhhcs2bkf4@corp.supernews.com...
 > Devin Panchal wrote:
 >
  > > hi,
  > >
  > > sorry to be a pain, i just cant figure it out. i've put a combo box at
the
  > > top for easy navigation, but i still cant get the fields to display any
  > > reocrds.
  > >
  > > i've used the link u gave me, and i keep getting errors at the data
source
  > > bit, wven thought i put teh correct path in.
  > >
  > > this is what i've got so far.
  > >
  > > Dim Assignment2 As Connection
  > > Dim rs As Recordset
 >
 > Dim Assignment2 As ADODB.Connection
 > Dim rs As ADODB.Recordset
 >
  > > 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
  > > Dim rs As ADODB.Recordset
 >
 > Get rid of the line above. You've already
 > created rs.
 >
  > > 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
  > > Do While Not rs.EOF
  > >
 > 'assuming 'contact_author_first_name' is the database field name
 > cmbName.additem rs.fields.item("contact_author_first_name")
  > > cmbName.List(i) = rs!contact_author_first_name
  > >
  > > 'rs.MoveNext
  > >
  > > rs!Title = cboTitle.Text
  > > rs!contact_author_first_name = txtFirstName.Text
  > > rs!contact_author_last_name = txtSecondName.Text
  > > rs!contact_author_address_1 = txtInstitute.Text
 >
 > I'm confused here. Are you trying to put the
 > textbox info into the recordset, or the recordset
 > info into the textboxes? See my 'additem' line
 > above for proper access to rs field data.
 >
  > > 'rs!contact_author_address_2 = txtNameOfInstitute.Text
  > > 'rs!contact_author_address_3 = txtAddress.Text
  > > 'rs!contact_author_address_4 = txtCity.Text
  > > 'rs!contact_author_address_5 = txtCountry.Text
  > > 'rs!contact_author_address_6 = txtZipCode.Text
  > > 'rs!paper_ID = txtPaperID.Text
  > > 'rs!paper_title = txtPaperTitle.Text
  > > 'rs.Update
  > >
  > > i = i + 1
  > > rs.MoveNext
  > > Loop
 >
 > End If
 >
  > > rs.Close
 >
 > Don't run these next two lines unless
 > you're absolutely sure you're finished
 > doing any and all database work!
 >
  > > Set Assignment2 = Nothing
  > > Set rs = Nothing
  > >
  > > End Sub
  > >
  > > i'm totatly new to this, and am guessing most of it. when i run this
code, i
  > > get an error at 'rs!Title' bit.
  > > my other form works, and that is why i have used the fields from that
form,
  > > to make this sort of datbase. all i want to do is view and modify the
  > > datbase.
 >
 > I'm willing to bet you don't want a loop like this.
 > Assuming you get the recordset working and the field
 > values into your textboxes, when you run, your
 > textboxes will flicker and display the *last* record
 > in the recordset (but at least your combobox will be
 > filled)!
 >
 > Then, when you try to use your navigation buttons,
 > you'll get an error because your recordset no longer
 > exists and you've set your database
 > connection = nothing.
 >
 > Concentrate on getting the first record displayed.
 > For now, leave your database connection and recordset
 > open until the program ends; VB will clean them up
 > when it exits.
 >
 > If you want to use rs for a new search while the
 > program is running, issue the rs.close command
 > before doing the new rs.open.
 >
 >
 > Have fun! <big evil grin><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: how to move through records on a database. 

On Mon, 14 Feb 2005 19:00:36 GMT, "Devin Panchal"
<d.panchal.DeleteThis@ntlworld.com> wrote:

 > i've read wat u wrote, and edited my code, but it still does not work.
 >i want to display the database data into my fields. (just like a database).
 >
 >the code does not have any errors, but the combo is blank now. (it just says
 >combo1 in it.)
 >this is wat i've got;

The combo may be blank, but have you tried clicking on the dark
triangle to see if there are any choices in it?

And does adding:

<code>
cmbName.Text = rs.Fields.Item("contact_author_first_name") & ""
</code>

do anything for you?
_______________________
Michael B. Johnson<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: how to move through records on a database. 

Devin Panchal wrote:
 > hi,
 >
 > i've read wat u wrote, and edited my code, but it still does not work.
 > i want to display the database data into my fields. (just like a database).
 >
 > the code does not have any errors, but the combo is blank now. (it just says
 > combo1 in it.)
 > this is wat i've got;
 >
 > Dim Assignment2 As ADODB.Connection
 > Dim rs As ADODB.Recordset
 >
 > Private Sub Form_Load()
 > Dim i As Integer
 > Set Assignment2 = New ADODB.Connection
 > Assignment2.Provider = "Microsoft.Jet.OLEDB.4.0;"
 > Assignment2.ConnectionString = "C:\Documents and Settings\Devin Panchal\My
 > Documents\My Documents\Devins work\Computer Science- Year 2\262cs-
 > Commercial Programming\Coursework 1\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
 >
 > Do While Not rs.EOF
 > cmbName.AddItem rs.Fields.Item("contact_author_first_name")
 > cmbName.List(i) = rs!contact_author_first_name

Delete the above line; it does nothing useful.

 >
 > txtFirstName.AddItem rs.Fields.Item("contact_author_first_name")

Replace '.additem' with '=', and this should
work correctly. Comboboxes have 'additem';
textboxes don't...

Oh, yeah; take this line out and put it in a
*separate sub*. Put all your other textbox-
filling statements in there, too.

Call the sub just before your 'Do while not .eof'
statement above. I'll explain below.

 >
 > rs.MoveNext

You've got two of these in your loop.

 > 'rs!Title = cboTitle.Text
 > 'rs!contact_author_first_name = txtFirstName.Text
 > 'rs!contact_author_last_name = txtSecondName.Text
 > 'rs!contact_author_address_1 = txtInstitute.Text
 > 'rs!contact_author_address_2 = txtNameOfInstitute.Text
 > 'rs!contact_author_address_3 = txtAddress.Text
 > 'rs!contact_author_address_4 = txtCity.Text
 > 'rs!contact_author_address_5 = txtCountry.Text
 > 'rs!contact_author_address_6 = txtZipCode.Text
 > 'rs!paper_ID = txtPaperID.Text
 > 'rs!paper_title = txtPaperTitle.Text
 > 'rs.Update

Cut all this, and put it somewhere out of sight
for reference only. It's backward, it clutters
up code you're trying to debug, and it can damage
your database if you happen to uncomment it by
mistake. 'Update' is how you write stuff back to
your database!

 >
 > i = i + 1

You don't need your counter any more, either.

 > rs.MoveNext

This is your second movenext in the loop.

 > Loop
 >
 > End If
 >
 > rs.Close

Don't close the recordset. After form_load
ends, you still want it open for your buttons
to navigate through.

 > 'Set Assignment2 = Nothing
 > 'Set rs = Nothing
 >
 > End Sub
 >
 > also is there any chance, to get the buttons to work? e.g. i've got a next
 > and previous button, how do i assign them to move forward and back in the
 > database. (cmdNextRecord).
 > Note. the above code is executed when loading the form.

You need to put your textbox-filling code into a sub,
and call it from form_load; that way your buttons can
call the same sub to refresh your textboxes when you
move to a new record.

Your button code is simple:
Next button:
rs.movenext
YourTextBoxRefreshSub 'or whatever you call it

Prev button:
rs.moveprevious
YourTextBoxRefreshSub 'or whatever you call it

You'll have to add code to test for EOF and BOF, but
I leave that up to you.

 >
 > also before i started editing the form, i used 'ar' and have now changed
 > them all to 'rs'. wat does this mean? are they special key words? which
 > should i use?

It could just as easily be 'rsMyData', the same way
'txtMyText' is a textbox or 'cboName' is a combobox.

The guy who wrote the example just got lazy and
called it 'rs'.<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: how to move through records on a database. 

the combo box is blank. it should display a list of first names, of people
already in the database.

adding that code does not do anything, it shows a list without adding any of
that code. .

dev

"Michael B. Johnson" <mjohnson DeleteThis @veribox.net> wrote in message
news:5n321114i5vcrpoko1bq0judap5qcv3rcp@4ax.com...
 > On Mon, 14 Feb 2005 19:00:36 GMT, "Devin Panchal"
 > <d.panchal DeleteThis @ntlworld.com> wrote:
 >
  > > i've read wat u wrote, and edited my code, but it still does not work.
  > >i want to display the database data into my fields. (just like a
database).
  > >
  > >the code does not have any errors, but the combo is blank now. (it just
says
  > >combo1 in it.)
  > >this is wat i've got;
 >
 > The combo may be blank, but have you tried clicking on the dark
 > triangle to see if there are any choices in it?
 >
 > And does adding:
 >
 > <code>
 > cmbName.Text = rs.Fields.Item("contact_author_first_name") & ""
 > </code>
 >
 > do anything for you?
 > _______________________
 > Michael B. Johnson<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: how to move through records on a database. 

hi,

i understand yor code, but how do i call another sub? E.g. dim
private_sub...

and how do i get that to work.

thanx
devin

"Randy Day" <ruthal.DeleteThis@sasktel.nex> wrote in message
news:1113b9dt85se507@corp.supernews.com...
 > Devin Panchal wrote:
  > > hi,
  > >
  > > i've read wat u wrote, and edited my code, but it still does not work.
  > > i want to display the database data into my fields. (just like a
database).
  > >
  > > the code does not have any errors, but the combo is blank now. (it just
says
  > > combo1 in it.)
  > > this is wat i've got;
  > >
  > > Dim Assignment2 As ADODB.Connection
  > > Dim rs As ADODB.Recordset
  > >
  > > Private Sub Form_Load()
  > > Dim i As Integer
  > > Set Assignment2 = New ADODB.Connection
  > > Assignment2.Provider = "Microsoft.Jet.OLEDB.4.0;"
  > > Assignment2.ConnectionString = "C:\Documents and Settings\Devin
Panchal\My
  > > Documents\My Documents\Devins work\Computer Science- Year 2\262cs-
  > > Commercial Programming\Coursework 1\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
  > >
  > > Do While Not rs.EOF
  > > cmbName.AddItem rs.Fields.Item("contact_author_first_name")
  > > cmbName.List(i) = rs!contact_author_first_name
 >
 > Delete the above line; it does nothing useful.
 >
  > >
  > > txtFirstName.AddItem rs.Fields.Item("contact_author_first_name")
 >
 > Replace '.additem' with '=', and this should
 > work correctly. Comboboxes have 'additem';
 > textboxes don't...
 >
 > Oh, yeah; take this line out and put it in a
 > *separate sub*. Put all your other textbox-
 > filling statements in there, too.
 >
 > Call the sub just before your 'Do while not .eof'
 > statement above. I'll explain below.
 >
  > >
  > > rs.MoveNext
 >
 > You've got two of these in your loop.
 >
  > > 'rs!Title = cboTitle.Text
  > > 'rs!contact_author_first_name = txtFirstName.Text
  > > 'rs!contact_author_last_name = txtSecondName.Text
  > > 'rs!contact_author_address_1 = txtInstitute.Text
  > > 'rs!contact_author_address_2 = txtNameOfInstitute.Text
  > > 'rs!contact_author_address_3 = txtAddress.Text
  > > 'rs!contact_author_address_4 = txtCity.Text
  > > 'rs!contact_author_address_5 = txtCountry.Text
  > > 'rs!contact_author_address_6 = txtZipCode.Text
  > > 'rs!paper_ID = txtPaperID.Text
  > > 'rs!paper_title = txtPaperTitle.Text
  > > 'rs.Update
 >
 > Cut all this, and put it somewhere out of sight
 > for reference only. It's backward, it clutters
 > up code you're trying to debug, and it can damage
 > your database if you happen to uncomment it by
 > mistake. 'Update' is how you write stuff back to
 > your database!
 >
  > >
  > > i = i + 1
 >
 > You don't need your counter any more, either.
 >
  > > rs.MoveNext
 >
 > This is your second movenext in the loop.
 >
  > > Loop
  > >
  > > End If
  > >
  > > rs.Close
 >
 > Don't close the recordset. After form_load
 > ends, you still want it open for your buttons
 > to navigate through.
 >
  > > 'Set Assignment2 = Nothing
  > > 'Set rs = Nothing
  > >
  > > End Sub
  > >
  > > also is there any chance, to get the buttons to work? e.g. i've got a
next
  > > and previous button, how do i assign them to move forward and back in
the
  > > database. (cmdNextRecord).
  > > Note. the above code is executed when loading the form.
 >
 > You need to put your textbox-filling code into a sub,
 > and call it from form_load; that way your buttons can
 > call the same sub to refresh your textboxes when you
 > move to a new record.
 >
 > Your button code is simple:
 > Next button:
 > rs.movenext
 > YourTextBoxRefreshSub 'or whatever you call it
 >
 > Prev button:
 > rs.moveprevious
 > YourTextBoxRefreshSub 'or whatever you call it
 >
 > You'll have to add code to test for EOF and BOF, but
 > I leave that up to you.
 >
  > >
  > > also before i started editing the form, i used 'ar' and have now changed
  > > them all to 'rs'. wat does this mean? are they special key words? which
  > > should i use?
 >
 > It could just as easily be 'rsMyData', the same way
 > 'txtMyText' is a textbox or 'cboName' is a combobox.
 >
 > The guy who wrote the example just got lazy and
 > called it 'rs'.<!-- ~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 done it!!

this is what i've got;

Dim Assignment2 As ADODB.Connection
Dim rs As ADODB.Recordset

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

Do While Not rs.EOF

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

txtFirstName.Text = rs.Fields.Item("contact_author_first_name")

i = i + 1
rs.MoveNext
Loop
rs.Close
Set Assignment2 = Nothing
Set rs = Nothing

End Sub

i had to take yor 2 lines out, but it displays the combo box correctly, and
the first name, of the last person in the database. (wierd)

now, i need to figure out, how to get the combo box to work, e.g. update the
fields when a name is clicked. and how to navigate through the database
using the 'next' button.

thanx

devin

"Devin Panchal" <d.panchal RemoveThis @ntlworld.com> wrote in message
news:3ipQd.357$ZZ6.162@newsfe3-gui.ntli.net...
 > this is what i've got;
 >
 > Dim Assignment2 As ADODB.Connection
 > Dim rs As ADODB.Recordset
 >
 > 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
 >
 > cmbName.AddItem rs.Fields.Item("contact_author_first_name")
 >
 > txtFirstName = rs.Fields.Item("contact_author_first_name")
 >
 > Do While Not rs.EOF
 >
 > rs.MoveNext
 > Loop
 >
 > End If
 >
 > 'rs.Close
 > 'Set Assignment2 = Nothing
 > 'Set rs = Nothing
 >
 > End Sub
 >
 > i deleted all the crap, my button is called 'cmdNext'. i know i'm being a
 > pain, but i just cant get this to work properly, and i wont give up, till
i
 > get it done. i've spent loads of time on this, and the lecture twat, has
got
 > loads of errors in his coding, which he gave to us as notes.
 >
 > thanx for all the help.
 > dev
 >
 >
 > "Devin Panchal" <d.panchal RemoveThis @ntlworld.com> wrote in message
 > news:fXoQd.320$ZZ6.79@newsfe3-gui.ntli.net...
  > > hi,
  > >
  > > i understand yor code, but how do i call another sub? E.g. dim
  > > private_sub...
  > >
  > > and how do i get that to work.
  > >
  > > thanx
  > > devin
  > >
  > > "Randy Day" <ruthal RemoveThis @sasktel.nex> wrote in message
  > > news:1113b9dt85se507@corp.supernews.com...
   > > > Devin Panchal wrote:
   > > > > hi,
   > > > >
   > > > > i've read wat u wrote, and edited my code, but it still does not
 > work.
   > > > > i want to display the database data into my fields. (just like a
  > > database).
   > > > >
   > > > > the code does not have any errors, but the combo is blank now. (it
 > just
  > > says
   > > > > combo1 in it.)
   > > > > this is wat i've got;
   > > > >
   > > > > Dim Assignment2 As ADODB.Connection
   > > > > Dim rs As ADODB.Recordset
   > > > >
   > > > > Private Sub Form_Load()
   > > > > Dim i As Integer
   > > > > Set Assignment2 = New ADODB.Connection
   > > > > Assignment2.Provider = "Microsoft.Jet.OLEDB.4.0;"
   > > > > Assignment2.ConnectionString = "C:\Documents and Settings\Devin
  > > Panchal\My
   > > > > Documents\My Documents\Devins work\Computer Science- Year 2\262cs-
   > > > > Commercial Programming\Coursework 1\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
   > > > >
   > > > > Do While Not rs.EOF
   > > > > cmbName.AddItem rs.Fields.Item("contact_author_first_name")
   > > > > cmbName.List(i) = rs!contact_author_first_name
   > > >
   > > > Delete the above line; it does nothing useful.
   > > >
   > > > >
   > > > > txtFirstName.AddItem rs.Fields.Item("contact_author_first_name")
   > > >
   > > > Replace '.additem' with '=', and this should
   > > > work correctly. Comboboxes have 'additem';
   > > > textboxes don't...
   > > >
   > > > Oh, yeah; take this line out and put it in a
   > > > *separate sub*. Put all your other textbox-
   > > > filling statements in there, too.
   > > >
   > > > Call the sub just before your 'Do while not .eof'
   > > > statement above. I'll explain below.
   > > >
   > > > >
   > > > > rs.MoveNext
   > > >
   > > > You've got two of these in your loop.
   > > >
   > > > > 'rs!Title = cboTitle.Text
   > > > > 'rs!contact_author_first_name = txtFirstName.Text
   > > > > 'rs!contact_author_last_name = txtSecondName.Text
   > > > > 'rs!contact_author_address_1 = txtInstitute.Text
   > > > > 'rs!contact_author_address_2 = txtNameOfInstitute.Text
   > > > > 'rs!contact_author_address_3 = txtAddress.Text
   > > > > 'rs!contact_author_address_4 = txtCity.Text
   > > > > 'rs!contact_author_address_5 = txtCountry.Text
   > > > > 'rs!contact_author_address_6 = txtZipCode.Text
   > > > > 'rs!paper_ID = txtPaperID.Text
   > > > > 'rs!paper_title = txtPaperTitle.Text
   > > > > 'rs.Update
   > > >
   > > > Cut all this, and put it somewhere out of sight
   > > > for reference only. It's backward, it clutters
   > > > up code you're trying to debug, and it can damage
   > > > your database if you happen to uncomment it by
   > > > mistake. 'Update' is how you write stuff back to
   > > > your database!
   > > >
   > > > >
   > > > > i = i + 1
   > > >
   > > > You don't need your counter any more, either.
   > > >
   > > > > rs.MoveNext
   > > >
   > > > This is your second movenext in the loop.
   > > >
   > > > > Loop
   > > > >
   > > > > End If
   > > > >
   > > > > rs.Close
   > > >
   > > > Don't close the recordset. After form_load
   > > > ends, you still want it open for your buttons
   > > > to navigate through.
   > > >
   > > > > 'Set Assignment2 = Nothing
   > > > > 'Set rs = Nothing
   > > > >
   > > > > End Sub
   > > > >
   > > > > also is there any chance, to get the buttons to work? e.g. i've got
a
  > > next
   > > > > and previous button, how do i assign them to move forward and back
in
  > > the
   > > > > database. (cmdNextRecord).
   > > > > Note. the above code is executed when loading the form.
   > > >
   > > > You need to put your textbox-filling code into a sub,
   > > > and call it from form_load; that way your buttons can
   > > > call the same sub to refresh your textboxes when you
   > > > move to a new record.
   > > >
   > > > Your button code is simple:
   > > > Next button:
   > > > rs.movenext
   > > > YourTextBoxRefreshSub 'or whatever you call it
   > > >
   > > > Prev button:
   > > > rs.moveprevious
   > > > YourTextBoxRefreshSub 'or whatever you call it
   > > >
   > > > You'll have to add code to test for EOF and BOF, but
   > > > I leave that up to you.
   > > >
   > > > >
   > > > > also before i started editing the form, i used 'ar' and have now
 > changed
   > > > > them all to 'rs'. wat does this mean? are they special key words?
 > which
   > > > > should i use?
   > > >
   > > > It could just as easily be 'rsMyData', the same way
   > > > 'txtMyText' is a textbox or 'cboName' is a combobox.
   > > >
   > > > The guy who wrote the example just got lazy and
   > > > called it 'rs'.
  > >
  > >
 >
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: how to move through records on a database. 

this is what i've got;

Dim Assignment2 As ADODB.Connection
Dim rs As ADODB.Recordset

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

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

txtFirstName = rs.Fields.Item("contact_author_first_name")

Do While Not rs.EOF

rs.MoveNext
Loop

End If

'rs.Close
'Set Assignment2 = Nothing
'Set rs = Nothing

End Sub

i deleted all the crap, my button is called 'cmdNext'. i know i'm being a
pain, but i just cant get this to work properly, and i wont give up, till i
get it done. i've spent loads of time on this, and the lecture twat, has got
loads of errors in his coding, which he gave to us as notes.

thanx for all the help.
dev


"Devin Panchal" <d.panchal DeleteThis @ntlworld.com> wrote in message
news:fXoQd.320$ZZ6.79@newsfe3-gui.ntli.net...
 > hi,
 >
 > i understand yor code, but how do i call another sub? E.g. dim
 > private_sub...
 >
 > and how do i get that to work.
 >
 > thanx
 > devin
 >
 > "Randy Day" <ruthal DeleteThis @sasktel.nex> wrote in message
 > news:1113b9dt85se507@corp.supernews.com...
  > > Devin Panchal wrote:
   > > > hi,
   > > >
   > > > i've read wat u wrote, and edited my code, but it still does not
work.
   > > > i want to display the database data into my fields. (just like a
 > database).
   > > >
   > > > the code does not have any errors, but the combo is blank now. (it
just
 > says
   > > > combo1 in it.)
   > > > this is wat i've got;
   > > >
   > > > Dim Assignment2 As ADODB.Connection
   > > > Dim rs As ADODB.Recordset
   > > >
   > > > Private Sub Form_Load()
   > > > Dim i As Integer
   > > > Set Assignment2 = New ADODB.Connection
   > > > Assignment2.Provider = "Microsoft.Jet.OLEDB.4.0;"
   > > > Assignment2.ConnectionString = "C:\Documents and Settings\Devin
 > Panchal\My
   > > > Documents\My Documents\Devins work\Computer Science- Year 2\262cs-
   > > > Commercial Programming\Coursework 1\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
   > > >
   > > > Do While Not rs.EOF
   > > > cmbName.AddItem rs.Fields.Item("contact_author_first_name")
   > > > cmbName.List(i) = rs!contact_author_first_name
  > >
  > > Delete the above line; it does nothing useful.
  > >
   > > >
   > > > txtFirstName.AddItem rs.Fields.Item("contact_author_first_name")
  > >
  > > Replace '.additem' with '=', and this should
  > > work correctly. Comboboxes have 'additem';
  > > textboxes don't...
  > >
  > > Oh, yeah; take this line out and put it in a
  > > *separate sub*. Put all your other textbox-
  > > filling statements in there, too.
  > >
  > > Call the sub just before your 'Do while not .eof'
  > > statement above. I'll explain below.
  > >
   > > >
   > > > rs.MoveNext
  > >
  > > You've got two of these in your loop.
  > >
   > > > 'rs!Title = cboTitle.Text
   > > > 'rs!contact_author_first_name = txtFirstName.Text
   > > > 'rs!contact_author_last_name = txtSecondName.Text
   > > > 'rs!contact_author_address_1 = txtInstitute.Text
   > > > 'rs!contact_author_address_2 = txtNameOfInstitute.Text
   > > > 'rs!contact_author_address_3 = txtAddress.Text
   > > > 'rs!contact_author_address_4 = txtCity.Text
   > > > 'rs!contact_author_address_5 = txtCountry.Text
   > > > 'rs!contact_author_address_6 = txtZipCode.Text
   > > > 'rs!paper_ID = txtPaperID.Text
   > > > 'rs!paper_title = txtPaperTitle.Text
   > > > 'rs.Update
  > >
  > > Cut all this, and put it somewhere out of sight
  > > for reference only. It's backward, it clutters
  > > up code you're trying to debug, and it can damage
  > > your database if you happen to uncomment it by
  > > mistake. 'Update' is how you write stuff back to
  > > your database!
  > >
   > > >
   > > > i = i + 1
  > >
  > > You don't need your counter any more, either.
  > >
   > > > rs.MoveNext
  > >
  > > This is your second movenext in the loop.
  > >
   > > > Loop
   > > >
   > > > End If
   > > >
   > > > rs.Close
  > >
  > > Don't close the recordset. After form_load
  > > ends, you still want it open for your buttons
  > > to navigate through.
  > >
   > > > 'Set Assignment2 = Nothing
   > > > 'Set rs = Nothing
   > > >
   > > > End Sub
   > > >
   > > > also is there any chance, to get the buttons to work? e.g. i've got a
 > next
   > > > and previous button, how do i assign them to move forward and back in
 > the
   > > > database. (cmdNextRecord).
   > > > Note. the above code is executed when loading the form.
  > >
  > > You need to put your textbox-filling code into a sub,
  > > and call it from form_load; that way your buttons can
  > > call the same sub to refresh your textboxes when you
  > > move to a new record.
  > >
  > > Your button code is simple:
  > > Next button:
  > > rs.movenext
  > > YourTextBoxRefreshSub 'or whatever you call it
  > >
  > > Prev button:
  > > rs.moveprevious
  > > YourTextBoxRefreshSub 'or whatever you call it
  > >
  > > You'll have to add code to test for EOF and BOF, but
  > > I leave that up to you.
  > >
   > > >
   > > > also b