I have a table that shows a contract number in one field and it has a field called competing contracts. The competing
contracts field can be null or it can have one or many other contract numbers in it. If it has many contract numbers then
they are seperated by semicolons. if there is only on the semicolon is not there. I am attempting to create a new table with
the first contract number and then the next field would hold one competing contract number. So there would be multiple
instances of the original contract number and only one instance of each competing contract number. I am coding only in VB.
Below is the code I have used thusfar. The source file is from another company so I have no control over the column as it is
sent to me.
Public Sub Competing()
Dim strMaster As String
Dim strCompetitors As String
Dim varCompetitors As Variant
Dim i As Integer
With CurrentDb.OpenRecordset("File Import")
If Not .BOF Then
Do While Not .EOF 'For each record in File Import...
strMaster = ![Master Contract Number]
If Len(Nz(![Related Contracts], "")) 0 Then 'If there are related contracts...
varCompetitors = Split(![Related Contracts], ";") 'Split the related contracts into an array
For i = 0 To UBound(varCompetitors) 'Parse through the array adding each related contract to the
CurrentDb.Execute "INSERT INTO Competing_Contract_List ( [System Contract], [Competing Contract] ) "
"SELECT " & strMaster & ", " & varCompetitors(i) & ";"
This code was suggested from another forum and seems much better than what I was trying originally. However, I am getting
an error with this code as well.
Run Time Error '3075'
Syntax Error (missing operator) in query expression 'PP-OR-017A'
The error relates to the following line in the code.
CurrentDb.Execute "INSERT INTO Competing_Contract_List ( [System Contract], [Competing Contract] ) " & _
"SELECT " & strMaster & " , " & varCompetitors(i) & " ;"
If anyone can help me figure out what might be wrong in this code I would apprieciate it.