Looping through Table records.

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I was wondering if there is a way to loop through a table,
check a field in each record, and then display a message
box.

For example, each record has an expiry date, and I want to
compare each expiry date (datExpires) to the current date
(Date()). eg: datExpires <= Date() then msgbox("The
product has expired)

I attempted doing this with a macro, but it did not have
the degree of control that I was looking for. So, I
decided I want to do this in Visual Basic code.

I know how to do loops, if statements, and all of that
basic stuff. My problem is looping through a table. Is a
table an array? Does it have a value that can return the
number of records?

For example, can I do something like Tables!
[tblName].numberOfRecords (or something similar) and then
look at each individual record using an index.

I know this may sound a little confusing, but any help
would be greatly appreciated.
 
Hi Mike,

Here is some very basic code to work with a recordset. A recordset is
actually an object defined by DAO or ADO which is used for data access. I
prefer DAO and in the following example you will see that the code opens a
recordset object using some SQL, then using the navigation methods of the
recordset, moves through the records one by one until the EOF (End of File)
conditon is true. DAO is fairly easy to master but you may want to spend
some time in help or with a good book on the subject while you get more
familiar with it.

dim rst as dao.recordset
dim db as dao.database
set db=currentdb()
'Change this to your SQL or to the name of a saved query
set rst=db.openrecordset("Select * from Mytable;")
with rst
' Very important to avoid errors if no records are returned
if not (.eof and .bof) then
'movefirst isn't strictly necessary - but just in case
.movefirst
do until .eof
'do something with record
'reference any fields as I've referenced PKid below
msgbox "PkId is: " & .fields("PKid").value
.movenext
loop
endif
.close
end with
set rst=nothing
set db=nothing
 
Thank you so much for your help.

While looking through your code, I have picked up on a lot
of things. I have programmed in C++ before, but Visual
Basic Access is quite new to me. But by looking through
your example, I have been able to make some nice
comparisons between the two and understand how VB works a
little better.

Thanks so much.

-----Original Message-----
Hi Mike,

Here is some very basic code to work with a recordset. A recordset is
actually an object defined by DAO or ADO which is used for data access. I
prefer DAO and in the following example you will see that the code opens a
recordset object using some SQL, then using the navigation methods of the
recordset, moves through the records one by one until the EOF (End of File)
conditon is true. DAO is fairly easy to master but you may want to spend
some time in help or with a good book on the subject while you get more
familiar with it.

dim rst as dao.recordset
dim db as dao.database
set db=currentdb()
'Change this to your SQL or to the name of a saved query
set rst=db.openrecordset("Select * from Mytable;")
with rst
' Very important to avoid errors if no records are returned
if not (.eof and .bof) then
'movefirst isn't strictly necessary - but just in case
.movefirst
do until .eof
'do something with record
'reference any fields as I've referenced PKid below
msgbox "PkId is: " & .fields("PKid").value
.movenext
loop
endif
.close
end with
set rst=nothing
set db=nothing


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

I was wondering if there is a way to loop through a table,
check a field in each record, and then display a message
box.

For example, each record has an expiry date, and I want to
compare each expiry date (datExpires) to the current date
(Date()). eg: datExpires <= Date() then msgbox("The
product has expired)

I attempted doing this with a macro, but it did not have
the degree of control that I was looking for. So, I
decided I want to do this in Visual Basic code.

I know how to do loops, if statements, and all of that
basic stuff. My problem is looping through a table. Is a
table an array? Does it have a value that can return the
number of records?

For example, can I do something like Tables!
[tblName].numberOfRecords (or something similar) and then
look at each individual record using an index.

I know this may sound a little confusing, but any help
would be greatly appreciated.
Click to expand...


.
Click to expand...
 
You need to either use ADO or DAO to loop through the
records. Since Access 2000+ uses ADO by default, the code
is:


Sub test()
Dim rst As New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open "Select * from YourTable"
Do Until rst.EOF
If rst("Field1") < rst("Field2") Then
MsgBox "The Product has expired"
End If
'Very important. You have to issue the .MoveNext
method or you will
'be stuck in an infinite loop
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub


Chris Nebinger
 
Sorry to post again, but just out of curiosity, what does
BOF mean? I know EOF is End Of File, but don't know what
BOF means.

-----Original Message-----
Hi Mike,

Here is some very basic code to work with a recordset. A recordset is
actually an object defined by DAO or ADO which is used for data access. I
prefer DAO and in the following example you will see that the code opens a
recordset object using some SQL, then using the navigation methods of the
recordset, moves through the records one by one until the EOF (End of File)
conditon is true. DAO is fairly easy to master but you may want to spend
some time in help or with a good book on the subject while you get more
familiar with it.

dim rst as dao.recordset
dim db as dao.database
set db=currentdb()
'Change this to your SQL or to the name of a saved query
set rst=db.openrecordset("Select * from Mytable;")
with rst
' Very important to avoid errors if no records are returned
if not (.eof and .bof) then
'movefirst isn't strictly necessary - but just in case
.movefirst
do until .eof
'do something with record
'reference any fields as I've referenced PKid below
msgbox "PkId is: " & .fields("PKid").value
.movenext
loop
endif
.close
end with
set rst=nothing
set db=nothing


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

I was wondering if there is a way to loop through a table,
check a field in each record, and then display a message
box.

For example, each record has an expiry date, and I want to
compare each expiry date (datExpires) to the current date
(Date()). eg: datExpires <= Date() then msgbox("The
product has expired)

I attempted doing this with a macro, but it did not have
the degree of control that I was looking for. So, I
decided I want to do this in Visual Basic code.

I know how to do loops, if statements, and all of that
basic stuff. My problem is looping through a table. Is a
table an array? Does it have a value that can return the
number of records?

For example, can I do something like Tables!
[tblName].numberOfRecords (or something similar) and then
look at each individual record using an index.

I know this may sound a little confusing, but any help
would be greatly appreciated.
Click to expand...


.
Click to expand...
 
Glad to help - feel free to post back when/if you need help with other
issues. It definately helps that you already have the programming
background.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Thank you so much for your help.

While looking through your code, I have picked up on a lot
of things. I have programmed in C++ before, but Visual
Basic Access is quite new to me. But by looking through
your example, I have been able to make some nice
comparisons between the two and understand how VB works a
little better.

Thanks so much.

-----Original Message-----
Hi Mike,

Here is some very basic code to work with a recordset. A recordset is
actually an object defined by DAO or ADO which is used for data
access. I prefer DAO and in the following example you will see that
the code opens a recordset object using some SQL, then using the
navigation methods of the recordset, moves through the records one
by one until the EOF (End of File) conditon is true. DAO is fairly
easy to master but you may want to spend some time in help or with a
good book on the subject while you get more familiar with it.

dim rst as dao.recordset
dim db as dao.database
set db=currentdb()
'Change this to your SQL or to the name of a saved query
set rst=db.openrecordset("Select * from Mytable;")
with rst
' Very important to avoid errors if no records are returned
if not (.eof and .bof) then
'movefirst isn't strictly necessary - but just in case
.movefirst
do until .eof
'do something with record
'reference any fields as I've referenced PKid below
msgbox "PkId is: " & .fields("PKid").value
.movenext
loop
endif
.close
end with
set rst=nothing
set db=nothing


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

I was wondering if there is a way to loop through a table,
check a field in each record, and then display a message
box.

For example, each record has an expiry date, and I want to
compare each expiry date (datExpires) to the current date
(Date()). eg: datExpires <= Date() then msgbox("The
product has expired)

I attempted doing this with a macro, but it did not have
the degree of control that I was looking for. So, I
decided I want to do this in Visual Basic code.

I know how to do loops, if statements, and all of that
basic stuff. My problem is looping through a table. Is a
table an array? Does it have a value that can return the
number of records?

For example, can I do something like Tables!
[tblName].numberOfRecords (or something similar) and then
look at each individual record using an index.

I know this may sound a little confusing, but any help
would be greatly appreciated.
Click to expand...


.
Click to expand...
Click to expand...
 
Beginning of File. This condition is true if the current record is before
the first record - meaning that you have navigated past the first record.
When BOF and EOF are both true the recordset is empty.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Sorry to post again, but just out of curiosity, what does
BOF mean? I know EOF is End Of File, but don't know what
BOF means.

-----Original Message-----
Hi Mike,

Here is some very basic code to work with a recordset. A recordset is
actually an object defined by DAO or ADO which is used for data
access. I prefer DAO and in the following example you will see that
the code opens a recordset object using some SQL, then using the
navigation methods of the recordset, moves through the records one
by one until the EOF (End of File) conditon is true. DAO is fairly
easy to master but you may want to spend some time in help or with a
good book on the subject while you get more familiar with it.

dim rst as dao.recordset
dim db as dao.database
set db=currentdb()
'Change this to your SQL or to the name of a saved query
set rst=db.openrecordset("Select * from Mytable;")
with rst
' Very important to avoid errors if no records are returned
if not (.eof and .bof) then
'movefirst isn't strictly necessary - but just in case
.movefirst
do until .eof
'do something with record
'reference any fields as I've referenced PKid below
msgbox "PkId is: " & .fields("PKid").value
.movenext
loop
endif
.close
end with
set rst=nothing
set db=nothing


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

I was wondering if there is a way to loop through a table,
check a field in each record, and then display a message
box.

For example, each record has an expiry date, and I want to
compare each expiry date (datExpires) to the current date
(Date()). eg: datExpires <= Date() then msgbox("The
product has expired)

I attempted doing this with a macro, but it did not have
the degree of control that I was looking for. So, I
decided I want to do this in Visual Basic code.

I know how to do loops, if statements, and all of that
basic stuff. My problem is looping through a table. Is a
table an array? Does it have a value that can return the
number of records?

For example, can I do something like Tables!
[tblName].numberOfRecords (or something similar) and then
look at each individual record using an index.

I know this may sound a little confusing, but any help
would be greatly appreciated.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top