niki
axes noob 'll b big 1 day
- Local time
- Today, 13:53
- Joined
- Apr 17, 2003
- Messages
- 66
Hello, I use Access 2k2 on windows XP,
when I run a module which parses the fields from table A and extracts the words contained between identified characters (",",";"," ","/","&") to table B, it does the job correctly, but it gives me an error 3021 at the end...
here's the code:
I dunno which part of the code is going wrong....
I am attaching the dbs there's a dim error but it appeared while I lighten the data in the tables I dunno where's the type of data not understood there...
if anyone could check I'd really appreciate it!
Cheers nico
when I run a module which parses the fields from table A and extracts the words contained between identified characters (",",";"," ","/","&") to table B, it does the job correctly, but it gives me an error 3021 at the end...
here's the code:
Code:
Option Compare Database
Option Explicit
'Here is a fail prrof way to do this
'I have included the Table "tblKeywords" so you can import it or just see its design.
'You need to have already created the table the keywords will go into. Name the table whatever you want.
'In your new Table the columns must be named "KeywordID" and "Keywords"
'Enter the table name the old keywords field is in and the table the new keywords are going to below. Where it says "ENTER TABLE NAMES HERE".
'Rename the field that is in the old table and has the old keywords in it "OldKeyword"
'Rename the field that is in the old table and has the old keyword ID in it "OldKeywordID"
'to run the funtion put your cursor in the funtion and from the menu bar select run and then select go
'The funtion will run and you will be left with a table with all the parsed keywords which you may use as you please
Function Parsefield()
'On Error GoTo ParseField_error
Dim bytWC As Long
Dim bytStringStart As Long
Dim bytStringEnd As Long
Dim dbCurrent As Database
Dim rstNewKeywordTable As Recordset
Dim rstOldKeywordTable As Recordset
Dim Keyword As String
Dim KeywordId As Long
Dim Index As Long
Dim tblOldKeywords As String
Dim tblNewKeywords As String
Dim tblOldKeyword As String
Dim tblNewKeyword As String
'---------- ENTER TABLE NAMES HERE -----------
tblOldKeywords = "Target Partner-GENERAL"
tblNewKeywords = "Target Partner_Keywords"
'----------------------------------------------
'Set a reference to the old and new keyword tables
Set dbCurrent = CurrentDb
Set rstNewKeywordTable = dbCurrent.OpenRecordset(tblNewKeywords)
Set rstOldKeywordTable = dbCurrent.OpenRecordset(tblOldKeywords)
'start with the first record for the old keywords
rstOldKeywordTable.MoveFirst
'set the function to run for each record in the old table
For Index = 1 To rstOldKeywordTable.RecordCount
'set variables to old keyword Id and old Keyword
KeywordId = rstOldKeywordTable![OldKeywordID]
Keyword = rstOldKeywordTable![OldKeyword]
'If the string is not zero length start parsing it
If Not (Len(Keyword) = 0) Then
'Set initial variable values for each record to be parsed
bytStringStart = 1
bytStringEnd = 1
'parse the field until there are no more words to be parsed
Do While bytStringEnd > 0
'Add a new record to teh new keyword table, then set the Id Number for the record
rstNewKeywordTable.AddNew
rstNewKeywordTable![KeywordId] = KeywordId
bytStringEnd = 0
'Find the end of the word you are parsing out
'Note this looks for different types of punctuation
'This looks for a comma
If bytStringEnd = 0 Then bytStringEnd = InStr(bytStringStart, Keyword, ",")
If bytStringEnd > InStr(bytStringStart, Keyword, ",") And Not (InStr(bytStringStart, Keyword, ",") = 0) _
Then bytStringEnd = InStr(bytStringStart, Keyword, ",")
'This looks for a semicolon
If bytStringEnd = 0 Then bytStringEnd = InStr(bytStringStart, Keyword, ";")
If bytStringEnd > InStr(bytStringStart, Keyword, ";") And Not (InStr(bytStringStart, Keyword, ";") = 0) _
Then bytStringEnd = InStr(bytStringStart, Keyword, ";")
'This looks for a Colon
If bytStringEnd = 0 Then bytStringEnd = InStr(bytStringStart, Keyword, ":")
If bytStringEnd > InStr(bytStringStart, Keyword, ":") And Not (InStr(bytStringStart, Keyword, ":") = 0) _
Then bytStringEnd = InStr(bytStringStart, Keyword, ":")
'This looks for a Space
If bytStringEnd = 0 Then bytStringEnd = InStr(bytStringStart, Keyword, " ")
If bytStringEnd > InStr(bytStringStart, Keyword, " ") And Not (InStr(bytStringStart, Keyword, " ") = 0) _
Then bytStringEnd = InStr(bytStringStart, Keyword, " ")
'This looks for a Slash
If bytStringEnd = 0 Then bytStringEnd = InStr(bytStringStart, Keyword, "/")
If bytStringEnd > InStr(bytStringStart, Keyword, "/") And Not (InStr(bytStringStart, Keyword, "/") = 0) _
Then bytStringEnd = InStr(bytStringStart, Keyword, "/")
'This looks for a Esperluette
If bytStringEnd = 0 Then bytStringEnd = InStr(bytStringStart, Keyword, "&")
If bytStringEnd > InStr(bytStringStart, Keyword, "&") And Not (InStr(bytStringStart, Keyword, "&") = 0) _
Then bytStringEnd = InStr(bytStringStart, Keyword, "&")
'parse the string and set the keyword in the table equal to the parsed string
If bytStringEnd = 0 Then
rstNewKeywordTable![Keywords] = Trim(Mid(Keyword, bytStringStart))
Else
rstNewKeywordTable![Keywords] = Trim(Mid(Keyword, bytStringStart, bytStringEnd - bytStringStart))
End If
'Update the New Table
rstNewKeywordTable.Update
'set the new start of the string right passed the last comma
bytStringStart = bytStringEnd + 1
Loop
End If
'move to the next record in the old table
rstOldKeywordTable.MoveNext
Next Index
Exit Function
ParseField_error:
MsgBox "Something went wrong"
End Function
Sub Unmotparcase()
End SubI dunno which part of the code is going wrong....
I am attaching the dbs there's a dim error but it appeared while I lighten the data in the tables I dunno where's the type of data not understood there...
if anyone could check I'd really appreciate it!
Cheers nico
