Solved

access vba office 365 undefined function error.

Posted on 2013-11-23
8
954 Views
Last Modified: 2013-11-24
I have a new db in 2003 to 2007 format i am using access 365. I have a function that I hoped would split the string and return the rows of the following field:

SplitMultiDelims([VIN Number / Model Prefix],";")

AB1111; AB1112; AB1114

I would want returned three rows
AB1111
AB1112
AB1114

everytime i try to run it says undefined function but not sure whats missing.

references attached
The code I found via google.

I created a hello world function and put that in the module and that worked so not sure what item is missing.


Function SplitMultiDelims(Text As String, DelimChars As String) As String()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' SplitMutliChar
' This function splits Text into an array of substrings, each substring
' delimited by any character in DelimChars. Only a single character
' may be a delimiter between two substrings, but DelimChars may
' contain any number of delimiter characters. If you need multiple
' character delimiters, use the SplitMultiDelimsEX function. It returns
' an unallocated array it Text is empty, a single element array
' containing all of text if DelimChars is empty, or a 1 or greater
' element array if the Text is successfully split into substrings.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Pos1 As Long
Dim N As Long
Dim M As Long
Dim Arr() As String
Dim I As Long

''''''''''''''''''''''''''''''''
' if Text is empty, get out
''''''''''''''''''''''''''''''''
If Len(Text) = 0 Then
    Exit Function
End If
''''''''''''''''''''''''''''''''''''''''''''''
' if DelimChars is empty, return original text
'''''''''''''''''''''''''''''''''''''''''''''
If DelimChars = vbNullString Then
    SplitMultiDelims = Array(Text)
    Exit Function
End If

'''''''''''''''''''''''''''''''''''''''''''''''
' oversize the array, we'll shrink it later so
' we don't need to use Redim Preserve
'''''''''''''''''''''''''''''''''''''''''''''''
ReDim Arr(1 To Len(Text))

I = 0
N = 0
Pos1 = 1

For N = 1 To Len(Text)
    For M = 1 To Len(DelimChars)
        If StrComp(Mid(Text, N, 1), Mid(DelimChars, M, 1), vbTextCompare) = 0 Then
            I = I + 1
            Arr(I) = Mid(Text, Pos1, N - Pos1)
            Pos1 = N + 1
            N = N + 1
        End If
    Next M
Next N

If Pos1 <= Len(Text) Then
    I = I + 1
    Arr(I) = Mid(Text, Pos1)
End If

''''''''''''''''''''''''''''''''''''''
' chop off unused array elements
''''''''''''''''''''''''''''''''''''''
ReDim Preserve Arr(1 To I)
SplitMultiDelims = Arr
    
End Function

Open in new window

0
Comment
Question by:PeterBaileyUk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 

Author Comment

by:PeterBaileyUk
ID: 39671308
and reference attached
ee.PNG
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39671354
if you are just after splitting a simple string AB1111; AB1112; AB1114

I would want returned three rows
AB1111
AB1112
AB1114

you just need this simple code

Sub SplitInfo(vText As String, DelimChar As String)
Dim vArr() As String, j As Long
If vText & "" = "" Then Exit Sub

vArr = Split(vText, DelimChar)
For j = 0 To UBound(vArr)
    Debug.Print vArr(j)
Next
End Sub

Open in new window

0
 
LVL 84
ID: 39671382
Are those references as shown in Office 365? Or are those from your dev machine?

In most cases, you don't need the extensibility reference unless you have code that works with the VBA Editor environment (i.e. opening a Module and changing code there), so try removing that.

You should also determine if you need the other references. To do that, first, make a backup, then do this:

Open the project, open the VBA Editor. Click Debug - Compile and fix any errors you encounter. After doing that, remove a single reference, and then click Debug - Compile again. If you do NOT receive any errors or warnings, then you don't need that reference and can remove it.

Note that you'll need (at least) the top 3 items on your reference list.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39671924
You did not say *how* you are using that function.  Is it in a query?  As a data source in a form or report?  Are you using it in another VBA procedure or an Access macro?  Please fill us in on exactly when, where, and how you are using it.

Also, make sure that the function is in a "regular" VBA module--not a form, report, or class module--and that the module name is not the same as the function name.
0
 

Author Comment

by:PeterBaileyUk
ID: 39672298
I followed your tips but even with a simple sub it is just not working as its small I have zipped and attached it so maybe someone could shed light on this
Database1.zip
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39672338
You're trying to call a Sub from a query. Subs do not return anything, so it will fail. I did this:

Function SplitInfo(vText As String, DelimChar As String)
    Dim vArr() As String, j As Long
    If vText & "" = "" Then Exit Function

    vArr = Split(vText, DelimChar)
   
    SplitInfo = vArr(0)
   
End Function
And changed the query to this:

Expr1: SplitInfo([VIN Number / Model Prefix],";")

That parses the FIRST value (before the ;  delimiter) and returns it to the query.

If you have "AB1111; AB1112; AB1114" and pass that in, however, it would just return the first item in that string. I'm not sure how you'd return 3 distinct Rows in your query for a value like that - in fact, I don't think you can do this. I think you'd need a subquery that could be joined to the main query, which would show 3 exactly duplicated rows EXCEPT for that value.
0
 

Author Comment

by:PeterBaileyUk
ID: 39672508
ok I got it now, I created a recordset looped through and for each iteration created an array with the split function and added that to a duplicate table without data in it.

Public Sub Main()
Dim dbs As DAO.Database
Dim rstVins As DAO.Recordset
Dim rstNew As DAO.Recordset
Set dbs = CurrentDb
Set rstNew = dbs.OpenRecordset("NewTable")
Set rstVins = dbs.OpenRecordset("QryData")
Dim RecordCountIndex As Long
Dim TestArray() As String
Dim TestString As String
Dim x As Long
Dim LastNonEmpty As Integer
Dim i As Integer

With rstVins
.MoveLast
x = .RecordCount
Debug.Print "total records: " & x
    .MoveFirst
  'method one
    Do Until .EOF
 TestArray = Split(.Fields("VINstem").Value)

        For i = 0 To UBound(TestArray)
          TestArray(i) = Replace(TestArray(i), ";", "")
          rstNew.AddNew
          rstNew!Vinstem = TestArray(i)
          rstNew!SuzukiSparePartsCatalog = rstVins.Fields("SuzukiSparePartsCatalog").Value
          rstNew!Modelname = rstVins.Fields("Modelname").Value
          rstNew!YearCode = rstVins.Fields("YearCode").Value
          rstNew!Year = rstVins.Fields("Year").Value
          rstNew.Update
          
          
        Next i
 
    rstVins.MoveNext

    Loop
End With
MsgBox ("end")
End Sub

Open in new window

0
 

Author Closing Comment

by:PeterBaileyUk
ID: 39672510
thank you..steered me in the right direction.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question