David Mack
Registered User.
- Local time
- Today, 13:18
- Joined
- Jan 4, 2000
- Messages
- 53
Happy New Years everyone!!!
I wrote following procedure (with the help of The Access '97 developer handbook) in Access 97 and is only a small part of the app. I converted the db to Access 2000 and it runs fine. Then I wanted to make things work a little different so I figured I would rewrite the app from scratch. Now I have problems when I copy and paste the following code form the converted 2000 app into the VBA of a form in the new db.
Background: I use import spec files along with TranferText commands to import three text files into one table in the db. The table name is RH400281. This is accomplished with a command button on the form. The next step is to add a new column called "intRandomNumber" to the imported table and subsequently add a random number t this new field in each record (this is where the code blows up and it used to work fine). The last thing in this code calls another procedure called "Top" which takes the top 500 random records from each unit group in the unit column of the RH400281 table where another procedure scrubs the text data so it is presentable in a Word mail merge.
I had commented out some of the problem areas. The ".Bookmarkable property" upon compile gives the following error:
"Compile error Method or data member not found"
After commenting out the ".Bookmarkable" If statement, I get the same error on the .Edit
There must be something silly I am missing. Why wouldn't the same code in a converted '97 db not work in a new 2000 db?
Thanks for taking the time to look over this New Years mess for me
Dave Mack
Private Sub cmdAddRandomNumber_Click()
'This Routine adds the random number to every claim in the RH400281 table
'The drop statement is only for testing and retesting the procedure and should be commented in production
'DoCmd.RunSQL "ALTER TABLE RH400281 DROP COLUMN intRandomNumber;"
DoCmd.RunSQL "ALTER TABLE RH400281 ADD COLUMN intRandomNumber Double;"
Dim dbs As Database
Dim rstRH400281 As Recordset
Dim rstCloneRH400281 As Recordset
Dim fldName As Field
Dim varBook As Variant
Dim intRandomNumber As Double
' Return reference to current database.
Set dbs = CurrentDb
' Open dynaset-type Recordset object.
Set rstRH400281 = dbs.OpenRecordset("SELECT * FROM RH400281")
' Clone Recordset object.
Set rstCloneRH400281 = rstRH400281.Clone
' Set the field definition in the table needing updates
Set fldName = rstRH400281.Fields!intRandomNumber
' Set cloned recordset current record.
rstCloneRH400281.MoveLast
rstCloneRH400281.MoveFirst
Do While Not rstCloneRH400281.EOF
' Get Bookmark property value
'If rstCloneRH400281.Bookmarkable Then
varBook = rstCloneRH400281.Bookmark
'Else
' If Recordset object doesn't support bookmarks, exit procedure.
' Exit Sub
'End If
'The cloned recordset bookmark is passed to the rstRH400281 recordset and random number inserted
With rstRH400281
Randomize
intRandomNumber = ((9132000 - 2 + 1) * Rnd + 2) ' Returns an integer in the 2 to 9132000 range
'go to the correct record to update
rstRH400281.Bookmark = varBook
.Edit 'go into edit mode on the selected record
![intRandomNumber] = intRandomNumber
.Update 'update the record
End With
rstCloneRH400281.MoveNext 'go to the next record in the clone
Loop
ExitCreateClone:
rstCloneRH400281.Close
rstRH400281.Close
Set dbs = Nothing
'Call Top 'calls the subroutine which creates a table for every unit in the the "tblACC" Then creates
'a table for each unit with only the top 500 random numbers selected--then to scrub the data
End Sub
[This message has been edited by David Mack (edited 01-01-2001).]
I wrote following procedure (with the help of The Access '97 developer handbook) in Access 97 and is only a small part of the app. I converted the db to Access 2000 and it runs fine. Then I wanted to make things work a little different so I figured I would rewrite the app from scratch. Now I have problems when I copy and paste the following code form the converted 2000 app into the VBA of a form in the new db.
Background: I use import spec files along with TranferText commands to import three text files into one table in the db. The table name is RH400281. This is accomplished with a command button on the form. The next step is to add a new column called "intRandomNumber" to the imported table and subsequently add a random number t this new field in each record (this is where the code blows up and it used to work fine). The last thing in this code calls another procedure called "Top" which takes the top 500 random records from each unit group in the unit column of the RH400281 table where another procedure scrubs the text data so it is presentable in a Word mail merge.
I had commented out some of the problem areas. The ".Bookmarkable property" upon compile gives the following error:
"Compile error Method or data member not found"
After commenting out the ".Bookmarkable" If statement, I get the same error on the .Edit
There must be something silly I am missing. Why wouldn't the same code in a converted '97 db not work in a new 2000 db?
Thanks for taking the time to look over this New Years mess for me

Dave Mack
Private Sub cmdAddRandomNumber_Click()
'This Routine adds the random number to every claim in the RH400281 table
'The drop statement is only for testing and retesting the procedure and should be commented in production
'DoCmd.RunSQL "ALTER TABLE RH400281 DROP COLUMN intRandomNumber;"
DoCmd.RunSQL "ALTER TABLE RH400281 ADD COLUMN intRandomNumber Double;"
Dim dbs As Database
Dim rstRH400281 As Recordset
Dim rstCloneRH400281 As Recordset
Dim fldName As Field
Dim varBook As Variant
Dim intRandomNumber As Double
' Return reference to current database.
Set dbs = CurrentDb
' Open dynaset-type Recordset object.
Set rstRH400281 = dbs.OpenRecordset("SELECT * FROM RH400281")
' Clone Recordset object.
Set rstCloneRH400281 = rstRH400281.Clone
' Set the field definition in the table needing updates
Set fldName = rstRH400281.Fields!intRandomNumber
' Set cloned recordset current record.
rstCloneRH400281.MoveLast
rstCloneRH400281.MoveFirst
Do While Not rstCloneRH400281.EOF
' Get Bookmark property value
'If rstCloneRH400281.Bookmarkable Then
varBook = rstCloneRH400281.Bookmark
'Else
' If Recordset object doesn't support bookmarks, exit procedure.
' Exit Sub
'End If
'The cloned recordset bookmark is passed to the rstRH400281 recordset and random number inserted
With rstRH400281
Randomize
intRandomNumber = ((9132000 - 2 + 1) * Rnd + 2) ' Returns an integer in the 2 to 9132000 range
'go to the correct record to update
rstRH400281.Bookmark = varBook
.Edit 'go into edit mode on the selected record
![intRandomNumber] = intRandomNumber
.Update 'update the record
End With
rstCloneRH400281.MoveNext 'go to the next record in the clone
Loop
ExitCreateClone:
rstCloneRH400281.Close
rstRH400281.Close
Set dbs = Nothing
'Call Top 'calls the subroutine which creates a table for every unit in the the "tblACC" Then creates
'a table for each unit with only the top 500 random numbers selected--then to scrub the data
End Sub
[This message has been edited by David Mack (edited 01-01-2001).]