N
nicolascaprais
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:
Option Compare DatabaseOption 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, ";") '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 IndexExit Function ParseField_error: MsgBox "Something went wrong" End FunctionSub Unmotparcase()End Sub
I dunno which part of the code is going wrong....
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:
Option Compare DatabaseOption 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, ";") '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 IndexExit Function ParseField_error: MsgBox "Something went wrong" End FunctionSub Unmotparcase()End Sub
I dunno which part of the code is going wrong....
if anyone could check I'd really appreciate it!
Cheers nico