new to access... how do I loop through a table in code?

[Guest]

can someone give me a brief idea of how to open tables in code and then loop
through them. I'm an FP programmer and access is just a little foreign to me.

I want to open a table, seek a key, then loop matching records to calculate
from data in a specific field.

 

[Larry Daugherty]

You can do it as you are thinking now and sometimes that's required.
However, it is more common to use a query (in FP parlance, a View) to return
just those records with a match on the key. There is a graphic tool, QBE
that will help you set up your query and you can flip to SQL view to see the
generated SQL. You can choose various settings in the QBE view to change
the query type, allow aggregate functions, etc.

HTH

 

[Guest]

thanks but I'm looking for the syntax to opening a table...
in fp it was

use tablename order indexname in 0 && This opens the table and sets the index
selet tablename && this selects the opened
table
seek keydata && This seek for the keydata
on the index
scan while keyfield = keydata && this loops only records that
match
calcunits = calcunits + tablename.fieldname && this is the calculation I
want to
endscan && this is the end of the
looping process
use in tablename && this closes the table

But in access I'm totally lost in code, I can manipulate queries using the
wizard, but that's not want I want.

I think I need to set up a recordset but I don't know how. Then after I do
set it up, how do I acces an individual field's data?

 

[Rick Brandt]

thanks but I'm looking for the syntax to opening a table...
in fp it was[snip]
But in access I'm totally lost in code, I can manipulate queries
using the wizard, but that's not want I want.

I think I need to set up a recordset but I don't know how. Then
after I do set it up, how do I acces an individual field's data?

Sample code using DAO...

Dim db As Database
Dim rs as Recordset
Dim var as Variant

Set db = CurrentDB
Set rs = db.OpenRecordSet("TableName")

If rs.EOF = False Then
var = rs!FieldName
rs.Edit
rs!FieldName = var + 1
rs.Update
rs.FindFirst "SomeField = SomeValue"
If rs.NoMatch Then 'not found

etc...

Check Help file for some of those terms for further details.

I agree though that this is rarely necessary to do in code. Updates are much
more efficient when done with queries.

 

[Dan Artuso]

Hi,
If I'm understanding correctly, you would do this in Access by opening a recordset based on
a query that returns only the records that match keydata (whatever that may be)

Here is how to open a recordset based on a simple Select statement:

Dim rs As DAO.Recordset
Dim strSql as String

strSql = "Select * From yourTable Where someField = " & keydata
Set rs = CurrentDb.OpenRecordset strSql

Do While Not rs.EOF
calcunits = calcunits + rs!fieldname
rs.MoveNext
Loop

Set rs = Nothing

The above assumes your criteris is numerical, if it's a string, you have to delimit with quotes:
strSql = "Select * From yourTable Where someField = '" & keydata & "'"

 

[John Vinson]

thanks but I'm looking for the syntax to opening a table...
in fp it was

Rick and Dan's advice is (as usual) spot on - but I just feel obliged
to say:

Access is not a flawed implementation of FP.

It's different.

Sometimes it's worth a little extra effort to find out how operations
are best carried out in a new software environment, rather than
insisting on doing it The Way It Has Always Been Done.


John W. Vinson[MVP]

 

[Guest]

Thanks everyone. I think I know what to do now.

For the record, I think I'm going to like access as much or more than fp.
It's just right now I'm having a little trouble getting used to it.

 

[Marsela]

Dan:
Follow Ricl's code, and don't forget to edit the recordset
before you do any changes or calculation, also if
calcualtion refers to a field in that table use rs.update
also, and before set rs=nothing use rs.close otherwise
your table may be open.

Thanks,
Marsela

-----Original Message-----
Hi,
If I'm understanding correctly, you would do this in

Access by opening a recordset based on

a query that returns only the records that match keydata (whatever that may be)

Here is how to open a recordset based on a simple Select statement:

Dim rs As DAO.Recordset
Dim strSql as String

strSql = "Select * From yourTable Where someField = " & keydata
Set rs = CurrentDb.OpenRecordset strSql

Do While Not rs.EOF
calcunits = calcunits + rs!fieldname
rs.MoveNext
Loop

Set rs = Nothing

The above assumes your criteris is numerical, if it's a

string, you have to delimit with quotes:

strSql = "Select * From yourTable Where someField = '" & keydata & "'"

--
HTH
Dan Artuso, Access MVP


"terry" <[email protected]> wrote in

message (e-mail address removed)...

 

[Marsela]

Dan:

Follow Rick's code. You have not to forget to edit the
recordset before calcualaiton or changes, also you need a
recordset update as rs.update after calculation, in mean
time before set rs=nothing use rs.close, because the
recordset (table or so)may be open.

Thanks,
Marsela

-----Original Message-----
Hi,
If I'm understanding correctly, you would do this in

Access by opening a recordset based on

a query that returns only the records that match keydata (whatever that may be)

Here is how to open a recordset based on a simple Select statement:

Dim rs As DAO.Recordset
Dim strSql as String

strSql = "Select * From yourTable Where someField = " & keydata
Set rs = CurrentDb.OpenRecordset strSql

Do While Not rs.EOF
calcunits = calcunits + rs!fieldname
rs.MoveNext
Loop

Set rs = Nothing

The above assumes your criteris is numerical, if it's a

string, you have to delimit with quotes:

strSql = "Select * From yourTable Where someField = '" & keydata & "'"

--
HTH
Dan Artuso, Access MVP


"terry" <[email protected]> wrote in

message (e-mail address removed)...

 

[sebrina-hopperstad]

Thanks everyone. I think I know what to do now.
For the record, I think I'm going to like access as much or more than fp.
It's just right now I'm having a little trouble getting used to it.
"John Vinson" wrote:

Great

(e-mail address removed)