Hi,First post. I have many small problems with some code I have to add a new record back into an existing table by copying an old record and inserting it. I figure I have the following to resolve.
Do loop in the wrong place.
Wrong quotes in statements
Getting "[CustomerID]= 'ZZZZZA'" in the table and not just ZZZZZA on its own in the field, and 3000 records for each new record copied and 0 in the contactID not 3854 for example
and error 3021 – no record.
3 tables of a new customer database.
Table 2 links to table 1 via new customerID
Table 3 links to table 2 via ContactID.
Idea is to Copy only old records in the table 3 for each new Contact in Table 2 where the LastName matches back into Table 3 and then step to the next record etc. stopping when the new CustomerID ends and there is no match in the last Name. So for example three old records matching the last name become 3 new records with 3 new ContactIDs under the new CustomerID.
If for example
strsql_CustID_New = "[CustomerID]='" & Me![CustomerID] & "'" is alpha like zzzzza 6 letters
strsql_Cust_Old = "Cust_ID= '" & Me!Cust_Alias_ID & "'" is alpha also like abasil 6 letters.
strContactID = "[ContactID] = '" & rst1!LineID & "'" is numeric like 3854
LastName = like Alan for example
So a new record would show somewhere in the fields
Zzzzza, 3854 , alan - plus all the other fields copied.
Old record could have. The ContactID and old CustomerID will not be used
Abasil, 123, alan - plus all the other fields.
And so I have :
Set db = CurrentDb
Set rst1 = db.OpenRecordset("tblCustomerContacts", dbOpenDynaset)
MsgBox "Entering rst1"
'need to use the new ID
strsql_CustID_New = "[CustomerID]='" & Me![CustomerID] & "'"
rst1.FindFirst strsql_CustID_New 'we are now at the first of the new records
'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Do While Not rst1.NoMatch 'we want to loop thru here checking each record
‘always check the value of the NoMatch property to determine
'whether the FIND operation has suceeded.
If rst1.NoMatch Then 'then the name doesn't exist
'do nothing
Else
'the name does exist, so what do we do?
'check the old records
Set rst2 = db.OpenRecordset("tblCustomer_Contact_Nucon_Rep", dbOpenDynaset)
strsql_Cust_Old = "Cust_ID= '" & Me!Cust_Alias_ID & "'"
'this is the old ID which is text which we will need
‘when we copy the old records to the new ID
'when the last name matches
rst2.FindFirst strsql_Cust_Old
If rst2.NoMatch Then 'The name does not exist
'do nothing
Else 'The number is already used
'we have the first record with the old ID
'so we have now both recordsets
'at the first record of each CustomerID.
'final check - locate the last name
If rst1!LastName = rst2!LastName Then
‘insert as new record the copied
'records matching the last name
MsgBox "Names Match"
strsql_RepFields = "LastName,FirstName,ContactName,[Sales / Contact Region or Territory]" _
& ",MA,TF,DG,SA,SC,TC,MM,SS,GS,RV,JS,LL,ML,HK,RP,WW,PF"
strContactID = "[ContactID] = " & rst1!LineID
MsgBox "Entering Insert SQL"
strsql_Rep = "INSERT INTO [tblCustomer_Contact_Nucon_Rep] " _
& "(Cust_ID, ContactID, " & strsql_RepFields & ") " _
& " SELECT """ & strsql_CustID_New & """ As NewCust_ID, ContactID=" & strContactID & " , " & strsql_RepFields & "" _
& " FROM [tblCustomer_Contact_Nucon_Rep] "
'remember Cust_ID is the new record
'ContactID is the LineID of The Contact
Debug.Print strsql_Rep
DBEngine(0)(0).Execute strsql_Rep, dbFailOnError
‘problem no current record so not inserting as new record
Else
'do Nothing
End If
End If
rst1.MoveNext
rst2.MoveNext
End If
Loop
'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Debug print out gives me
INSERT INTO [tblCustomer_Contact_Nucon_Rep] (Cust_ID, ContactID, LastName,FirstName,ContactName,[Sales / Contact Region or Territory],MA,TF,DG,SA,SC,TC,MM,SS,GS,RV,JS,LL,ML,HK,RP,WW,PF) SELECT "[CustomerID]='ZZZZZA'" As NewCust_ID, ContactID=[ContactID] = '3854' , LastName,FirstName,ContactName,[Sales / Contact Region or Territory],MA,TF,DG,SA,SC,TC,MM,SS,GS,RV,JS,LL,ML,HK,RP,WW,PF FROM [tblCustomer_Contact_Nucon_Rep]
Any help or guidance really appreciated as I'm going around in circles.
Thankz
Bill
