K
Kim
Hello,
I am trying to loop through a recordset and duplicate every record
found for a particular client project to a new client project. Here is
a little detail:
In this Access 2003 db, a client can have multiple projects of which
most of the information that was entered for the first project can be
copied to a new project to save time. I am trying to duplicate the
original client's information using a form to a new project (same
client) but it gets to EOF after the first record update instead of
looping through every record to find all matches and duplicating them
individually (Example: a client (Project A) has 4 records of auth
methods and all 4 records need to be duplicated to the new client
project (Project B) in the AuthMethod table using the ClientID).
The AuthMethod table has an autonumber field that is the Unique ID and
consists of multiple records for each client based on their connections
to our system like TCP/IP, Dial, etc.. I am assuming when it adds the
new duplicated record it is at the EOF because of the autonumber field
and this is why it exits the loop without continuing through the
recordset.
Sorry, I know this sounds confusing. Below is the Form Update
Procedure. I know I am missing something or the loop needs to end
using something other then Do Until .EOF but I can't figure it out.
Can you give some guidance or suggestion? Thanks so much!
Kim
Dim db As database
Dim rs As Recordset
Dim strSQL As String
Dim intI As Integer
'Open a connection to this database file
Set db = CurrentDb
strSQL = "SELECT * FROM qryClientDupAuthTo " & _
"WHERE ClientID ='" & Me.ClientIDDupAuthTo & "'Order By ClientID"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbConsistent,
dbOptimistic)
'If recordset is empty, exit.
If rs.EOF Then Exit Sub
'Me.ClientIDDupAuthTo is a combo box that I use to hold the new
project ClientID
'the form is filled with the client's records to duplicate from
intI = 1
With rs
.movefirst
Do Until .EOF
If !ClientID = Me.ClientIDDupAuthTo Then
.Edit
!AuthClientID = Me.ClientIDDupAuthTo
!AuthClientCode = Me.txtCC
!AuthMethod = Me.AuthMethod
!ZTCProcess = Me.ZTCProcess
!RoutingInd = Me.RoutingInd
!IPAddr = Me.IPAddr
!IPPort = Me.IPPort
!IPNumConn = Me.IPNumConn
!TNSPrimary = Me.TNSPrimary
!TNSSecondary = Me.TNSSecondary
!NetworkConn = Me.NetworkConn
.Update
.Bookmark = .LastModified
.MoveNext
intI = intI + 1
End If
Loop
End With
'Close recordset and db objects
rs.Close
db.Close
'Clear memory
Set rs = Nothing
Set db = Nothing
I am trying to loop through a recordset and duplicate every record
found for a particular client project to a new client project. Here is
a little detail:
In this Access 2003 db, a client can have multiple projects of which
most of the information that was entered for the first project can be
copied to a new project to save time. I am trying to duplicate the
original client's information using a form to a new project (same
client) but it gets to EOF after the first record update instead of
looping through every record to find all matches and duplicating them
individually (Example: a client (Project A) has 4 records of auth
methods and all 4 records need to be duplicated to the new client
project (Project B) in the AuthMethod table using the ClientID).
The AuthMethod table has an autonumber field that is the Unique ID and
consists of multiple records for each client based on their connections
to our system like TCP/IP, Dial, etc.. I am assuming when it adds the
new duplicated record it is at the EOF because of the autonumber field
and this is why it exits the loop without continuing through the
recordset.
Sorry, I know this sounds confusing. Below is the Form Update
Procedure. I know I am missing something or the loop needs to end
using something other then Do Until .EOF but I can't figure it out.
Can you give some guidance or suggestion? Thanks so much!
Kim
Dim db As database
Dim rs As Recordset
Dim strSQL As String
Dim intI As Integer
'Open a connection to this database file
Set db = CurrentDb
strSQL = "SELECT * FROM qryClientDupAuthTo " & _
"WHERE ClientID ='" & Me.ClientIDDupAuthTo & "'Order By ClientID"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbConsistent,
dbOptimistic)
'If recordset is empty, exit.
If rs.EOF Then Exit Sub
'Me.ClientIDDupAuthTo is a combo box that I use to hold the new
project ClientID
'the form is filled with the client's records to duplicate from
intI = 1
With rs
.movefirst
Do Until .EOF
If !ClientID = Me.ClientIDDupAuthTo Then
.Edit
!AuthClientID = Me.ClientIDDupAuthTo
!AuthClientCode = Me.txtCC
!AuthMethod = Me.AuthMethod
!ZTCProcess = Me.ZTCProcess
!RoutingInd = Me.RoutingInd
!IPAddr = Me.IPAddr
!IPPort = Me.IPPort
!IPNumConn = Me.IPNumConn
!TNSPrimary = Me.TNSPrimary
!TNSSecondary = Me.TNSSecondary
!NetworkConn = Me.NetworkConn
.Update
.Bookmark = .LastModified
.MoveNext
intI = intI + 1
End If
Loop
End With
'Close recordset and db objects
rs.Close
db.Close
'Clear memory
Set rs = Nothing
Set db = Nothing