P
PatK
Hi:
I have this code, below wherein I am attempting to delete, then re-add a
series of records in one table, based upon an "input" table record set. At
the line noted, below, I get an error: Item cannot be found in the
collection corresopnding to the requested name or ordinal. Is this because
of a "quotes" thing, or possibly brackets needing to be concatenated, fore
and aft, of the map(i).strOutField parameter in the .Fields code? I do no
that the field name I am outputing to has a space in the middle of it
("Created YYYYMM" is the field name in the output table).
The debug line immediately preceding the offending code displays exactly
what I would expect namely:
Created YYYYMM (without any quotes) and the value 2008-01 for the source
field.
I have tried adding quotes like:
ro.fields(""" & map(i).strOutField & """) to the parameter, thinking that is
what is needed, but it either is not needed (since the value is already a
string), OR, I am not setting up the quotes correctly (or I need brackets, or
somesuch).
Hope this rambling makes some sense?
PatK
Sub TransformData(map() As dtype, InTable As String, OutTable As String, _
DebugFlag As Boolean)
Dim rs As ADODB.Recordset
Dim ro As ADODB.Recordset
Set rs = New ADODB.Recordset
Set ro = New ADODB.Recordset
With rs
.Open "SELECT * FROM " & InTable, CurrentProject.Connection, _
adOpenStatic, adLockPessimistic, adCmdText
.MoveFirst
ro.Open "[" & OutTable & "]", CurrentProject.Connection, adOpenStatic,
_ adLockPessimistic
Do Until .EOF
DeleteExisting rs.Fields("Service Call ID"), "ID", OutTable 'first
delete existing
i = -1
ro.AddNew
Do Until i = UBound(map)
i = i + 1
MsgBox map(i).strOutField & " and " & .Fields(map(i).strInField)
' in this message box, I see "exactly" what I would expect to see in the two
_
string fields
' Yet the next row craps out with the error noted above
ro.Fields(map(i).strOutField) = .Fields(map(i).strInField)
Loop
ro.Update
.MoveNext
Loop
End With
rs.Close
ro.Close
Set rs = Nothing
Set ro = Nothing
End Sub
I have this code, below wherein I am attempting to delete, then re-add a
series of records in one table, based upon an "input" table record set. At
the line noted, below, I get an error: Item cannot be found in the
collection corresopnding to the requested name or ordinal. Is this because
of a "quotes" thing, or possibly brackets needing to be concatenated, fore
and aft, of the map(i).strOutField parameter in the .Fields code? I do no
that the field name I am outputing to has a space in the middle of it
("Created YYYYMM" is the field name in the output table).
The debug line immediately preceding the offending code displays exactly
what I would expect namely:
Created YYYYMM (without any quotes) and the value 2008-01 for the source
field.
I have tried adding quotes like:
ro.fields(""" & map(i).strOutField & """) to the parameter, thinking that is
what is needed, but it either is not needed (since the value is already a
string), OR, I am not setting up the quotes correctly (or I need brackets, or
somesuch).
Hope this rambling makes some sense?
PatK
Sub TransformData(map() As dtype, InTable As String, OutTable As String, _
DebugFlag As Boolean)
Dim rs As ADODB.Recordset
Dim ro As ADODB.Recordset
Set rs = New ADODB.Recordset
Set ro = New ADODB.Recordset
With rs
.Open "SELECT * FROM " & InTable, CurrentProject.Connection, _
adOpenStatic, adLockPessimistic, adCmdText
.MoveFirst
ro.Open "[" & OutTable & "]", CurrentProject.Connection, adOpenStatic,
_ adLockPessimistic
Do Until .EOF
DeleteExisting rs.Fields("Service Call ID"), "ID", OutTable 'first
delete existing
i = -1
ro.AddNew
Do Until i = UBound(map)
i = i + 1
MsgBox map(i).strOutField & " and " & .Fields(map(i).strInField)
' in this message box, I see "exactly" what I would expect to see in the two
_
string fields
' Yet the next row craps out with the error noted above
ro.Fields(map(i).strOutField) = .Fields(map(i).strInField)
Loop
ro.Update
.MoveNext
Loop
End With
rs.Close
ro.Close
Set rs = Nothing
Set ro = Nothing
End Sub