Opening and using a RecordSet

[Patrick Maslanka]

Im new to VBA programming, and looking for some help on how to open a table
as a RecordSet, so that i can parse through it and perform tests and
modifications on the data in the table.

What i need to do, is move thru the table from begining to end, in the order
that it is sorted, and check a field in the record. If the field is blank (
NULL ) then i need to set the field equal to the value of the previous
records field, if a test condition is true.

I have written the following function code, and would appreciate someone
looking at it, and tell me if im doing it right, or tell me what im doing
wrong.

I believe i have coded it correctly ( as far as the logic), but the syntax
may be wrong. Specifically, im not sure how to set which table im using ( the
set db and set rst lines )

Thanks in advance for your assistance.

-------------------------------------------
Public Function PopulateEmptySpec()


Dim PreviousSpec As String
Dim db As Database
Dim rst As Recordset

Set db = DBEngine.Workspaces(0).Databases(0) ' ??? Need Help Here ???
Set rst = db.OpenRecordset("OID") ' ??? Open Table "OID" ???

rst.MoveFirst ' Goto 1st Record
PreviousSpec = rst!Spec ' Set String to Record's
"Spec" field

rst.MoveNext

Do Until rst.EOF
If rst!Spec = "" Then ' If current Record's Spec
field is empty

If PreviousSpec = "EFI" Then ' copy previous Record's
Spec field if
Set rst!Spec = "EFI" ' that field contained
"EFI" or "IOT"

If PreviousSpec = "IOT" Then
Set rst!Spec = "IOT"

rst.Update ' Write to the table
before moving

PreviousSpec = rst!Spec ' Save Record's "Spec"
field for compare

rst.MoveNext
Loop


End Function

------------------------------------

 

[Daniel Pineault]

If you are working on the current db change the following line

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDB()
Set rst = db.OpenRecordset("YourTableName")

There is then an issue with your processing your are simply looping through
the records however they are returned. You have no guarantee that they are
in the given order you need to perform you task. As such you'd be better to

Set rst = db.OpenRecordset("YourQueryName OR SQLStatment")

This way you can ensure a specific order.

Also, I suggest you always test for records before doing any processing
whatsoever.

rst.MoveLast
If rst.RecordCount > 0 Then
'Your processing code goes here
End if

Start with the above and post back.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com
If this post was helpful, please rate it by using the vote buttons.

 

[Klatuu]

You also need to check the record count before you move

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDB()
Set rst = db.OpenRecordset("YourTableName")
If rst.RecordCount = 0 Then
MsgBox "No Record In the Table"
rst.Close
Set rst = Nothing
Set db = Nothing
Else
With rst
.MoveLast
.MoveFirst
Do While Not .EOF
yada yada
Loop
.Close
End With
End If

 

[Patrick Maslanka]

Thanks for your responses. I have a custom sort public function that i had
been using in the Order By field of the table/query properties in the design
view. Apparently this only sorts the view, and not the actual records.

Took some doing, but i figured out how to include the funtion in the SQL for
a query so that it actually orders the records, and it is now working
properly.

As far as checking for empty, etc .... this is strictly for a testing
utility that i will be using, and the database is not persistant ( i clear it
each time i use it ). Normally i would code for such contingencies, but since
im the only one using it, i dropped those tests to reduce code size, and
therefore speed processing.

Next step is to figure out how to programmically build a new table from a
merge of this query and 3 other tables in the DB......

Ill be back if/when i get stuck.....

Thnx again