?
Solved

access vba office 365 undefined function error.

Posted on 2013-11-23
8
Medium Priority
?
961 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 85
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 93

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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

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.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

800 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