runtime error record is deleted

[trevi]

I have to convert some data from an xls spreadsheet. Im calling a function I
created that takes in a field that contains city,state zip. In the function I
split them out into separate fields. I ran this and it tells me that the
"record is deleted". When I pull up the table, the first record has
"#deleted" in every field. I even went back into my function and tried to
only display a couple of fields, and I still get the same error.ll show the
query and the function ( how the recordset is opened). Maybe somebody can see
what I am doing wrong. Thanks in advance.

Query: SELECT splitname(flname,citystzip), first, last,
FROM ActiveApproach
Function:
Dim rs As ADODB.Recordset
Dim stzp() As String
arname = Split(name, ",")
lastname = arname(0)
firstname = arname(1)
Set rs = New ADODB.Recordset
rs.Open "activeapproach query", CurrentProject.connection, adOpenDynamic,
adLockOptimistic
rs.MoveFirst
MsgBox (rs.Fields("flname") & "name")

 

[Duane Hookom]

Why don't you give us some sample values (find the difficult, weird ones) and
tell us what you want to do with it? Do you have a field you want to store
this information in or do you want to split to display only?

 

[trevi]

I want to split the fields to store in a table.

They dont seem that difficult. But maybe I'm doing it wrong. One of the
fields I have contains city , state and zip together in one cell (Excel).
I have corresponding fields, city,state and zip in my table. I am passing
the field to a function that splits the fields into an array :
dim array() as variant
array = split(inputfield,",")
rs.fields("city") = array(0)
rs.fields("state") = array(1)
rs.fields("zip") = array(2)
what do you think?

 

[Duane Hookom]

You should be able to do this with an update query. Creating a record set in
code isn't necessary.

Create a public function that you can use in an update query:

Update ActiveApproach
SET City = ParseText([citystzip],1,","),
State = ParseText([citystzip],2,","),
Zip = ParseText([citystzip],3,",");



Public Function ParseText(pstrText As String, intElement As Integer, _
pstrDelimiter As String) As String
Dim arText() As String
On Error GoTo ParseText_Error

arText() = Split(pstrText, pstrDelimiter)
ParseText = arText(intElement - 1)

ExitParseText:
On Error GoTo 0
Exit Function

ParseText_Error:
Select Case Err
Case 9 'subscript out of range
'don't do anything
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure ParseText of Module basParseText"
End Select
Resume ExitParseText
End Function

 

[trevi]

Thank you , Duane. I will try it.

You should be able to do this with an update query. Creating a record set in
code isn't necessary.

Create a public function that you can use in an update query:

Update ActiveApproach
SET City = ParseText([citystzip],1,","),
State = ParseText([citystzip],2,","),
Zip = ParseText([citystzip],3,",");



Public Function ParseText(pstrText As String, intElement As Integer, _
pstrDelimiter As String) As String
Dim arText() As String
On Error GoTo ParseText_Error

arText() = Split(pstrText, pstrDelimiter)
ParseText = arText(intElement - 1)

ExitParseText:
On Error GoTo 0
Exit Function

ParseText_Error:
Select Case Err
Case 9 'subscript out of range
'don't do anything
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure ParseText of Module basParseText"
End Select
Resume ExitParseText
End Function

--
Duane Hookom
Microsoft Access MVP

I want to split the fields to store in a table.

They dont seem that difficult. But maybe I'm doing it wrong. One of the
fields I have contains city , state and zip together in one cell (Excel).
I have corresponding fields, city,state and zip in my table. I am passing
the field to a function that splits the fields into an array :
dim array() as variant
array = split(inputfield,",")
rs.fields("city") = array(0)
rs.fields("state") = array(1)
rs.fields("zip") = array(2)
what do you think?

 

[trevi]

Duane,

I have another question to ask. Each row in the Excel spreadsheet I am
working with contains data that pertains to one individual and also is
related to more than one table. (ie, the teacher table and the classes he/she
teaches). What's the best way I should load the new data into Access ?
Should I run an update query to update all of the tables or should I load
the main table first, then create some procedures that update the other
related tables?

You should be able to do this with an update query. Creating a record set in
code isn't necessary.

Create a public function that you can use in an update query:

Update ActiveApproach
SET City = ParseText([citystzip],1,","),
State = ParseText([citystzip],2,","),
Zip = ParseText([citystzip],3,",");



Public Function ParseText(pstrText As String, intElement As Integer, _
pstrDelimiter As String) As String
Dim arText() As String
On Error GoTo ParseText_Error

arText() = Split(pstrText, pstrDelimiter)
ParseText = arText(intElement - 1)

ExitParseText:
On Error GoTo 0
Exit Function

ParseText_Error:
Select Case Err
Case 9 'subscript out of range
'don't do anything
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure ParseText of Module basParseText"
End Select
Resume ExitParseText
End Function

--
Duane Hookom
Microsoft Access MVP

I want to split the fields to store in a table.

They dont seem that difficult. But maybe I'm doing it wrong. One of the
fields I have contains city , state and zip together in one cell (Excel).
I have corresponding fields, city,state and zip in my table. I am passing
the field to a function that splits the fields into an array :
dim array() as variant
array = split(inputfield,",")
rs.fields("city") = array(0)
rs.fields("state") = array(1)
rs.fields("zip") = array(2)
what do you think?

 

[Duane Hookom]

I generally import the Excel ranges into temporary tables in Access. I then
create append and update tables to update my "production" tables.
--
Duane Hookom
Microsoft Access MVP

Duane,

I have another question to ask. Each row in the Excel spreadsheet I am
working with contains data that pertains to one individual and also is
related to more than one table. (ie, the teacher table and the classes he/she
teaches). What's the best way I should load the new data into Access ?
Should I run an update query to update all of the tables or should I load
the main table first, then create some procedures that update the other
related tables?

You should be able to do this with an update query. Creating a record set in
code isn't necessary.

Create a public function that you can use in an update query:

Update ActiveApproach
SET City = ParseText([citystzip],1,","),
State = ParseText([citystzip],2,","),
Zip = ParseText([citystzip],3,",");



Public Function ParseText(pstrText As String, intElement As Integer, _
pstrDelimiter As String) As String
Dim arText() As String
On Error GoTo ParseText_Error

arText() = Split(pstrText, pstrDelimiter)
ParseText = arText(intElement - 1)

ExitParseText:
On Error GoTo 0
Exit Function

ParseText_Error:
Select Case Err
Case 9 'subscript out of range
'don't do anything
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure ParseText of Module basParseText"
End Select
Resume ExitParseText
End Function

--
Duane Hookom
Microsoft Access MVP

I want to split the fields to store in a table.

They dont seem that difficult. But maybe I'm doing it wrong. One of the
fields I have contains city , state and zip together in one cell (Excel).
I have corresponding fields, city,state and zip in my table. I am passing
the field to a function that splits the fields into an array :
dim array() as variant
array = split(inputfield,",")
rs.fields("city") = array(0)
rs.fields("state") = array(1)
rs.fields("zip") = array(2)
what do you think?
:

Why don't you give us some sample values (find the difficult, weird ones) and
tell us what you want to do with it? Do you have a field you want to store
this information in or do you want to split to display only?
--
Duane Hookom
Microsoft Access MVP


:

I have to convert some data from an xls spreadsheet. Im calling a function I
created that takes in a field that contains city,state zip. In the function I
split them out into separate fields. I ran this and it tells me that the
"record is deleted". When I pull up the table, the first record has
"#deleted" in every field. I even went back into my function and tried to
only display a couple of fields, and I still get the same error.ll show the
query and the function ( how the recordset is opened). Maybe somebody can see
what I am doing wrong. Thanks in advance.

Query: SELECT splitname(flname,citystzip), first, last,
FROM ActiveApproach
Function:
Dim rs As ADODB.Recordset
Dim stzp() As String
arname = Split(name, ",")
lastname = arname(0)
firstname = arname(1)
Set rs = New ADODB.Recordset
rs.Open "activeapproach query", CurrentProject.connection, adOpenDynamic,
adLockOptimistic
rs.MoveFirst
MsgBox (rs.Fields("flname") & "name")