Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Location
    paris, FRANCE
    Posts
    88

    Unanswered: compiling error but working script

    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... The fields in both tables are memo type
    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 Sub
    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
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    that's a lot of nice code, nico, but there is at least one error that could cause the problem.

    you do .movenext until you hit .EOF and then you do another .movenext which triggers a system error which bounces you to your error handling and then you fall off the end of the function. everything completes OK because the error doesn't occur until you have processed the whole recordset.

    you need to add

    do while not rstOldKeywordTable.eof
    'all
    'the
    'stuff
    'in
    'the
    'loop
    rstOldKeywordTable.movenext
    loop

    ...to avoid that last .movenext

    izy

    and after looking at your code again, i see you try to handle this with
    For Index = 1 To rstOldKeywordTable.RecordCount
    ...but this is reinventing the wheel .EOF is there to do this for you.

    further (in old-A, don't know A2k2) .recordcount = 0 if there are zero records or = 1 if there are more than zero records until you do a .movelast to populate the whole recordset. rather than worrying about all this, use .EOF
    izy
    Last edited by izyrider; 09-02-03 at 13:55.

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    oh yes - to avoid the "dunno which part of the code is going wrong" just comment out the

    On Error GoTo

    line so the code crashes out to the debug window with the error line highlit.

    izy

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    final thought:

    change
    function parsefield()
    exit function
    end function

    to
    sub parsefield()
    exit sub
    end sub

    your function is improperly declared, and in any case you are not returning a value: a sub will do fine.

    izy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •