Results 1 to 5 of 5
  1. #1
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402

    split funtion "Run Time error 9, Subscript out of range" Error

    Hi Guys



    I have been struggling with a problem

    I have two forms

    frmAddNotes
    frmAddContacts

    On the form frmAddNotes i have a "ComboBox" with the names of contacts associated with a customer called "ContactNameCBOBox"

    on the Notinlist event of the combobox i have this code
    Code:
    Dim AddContact
       AddContact = MsgBox("Would You Like To Add " & _
          NewData & " As A New Contact? ", vbYesNo)
    
    
      
       If AddContact = vbNo Then
    
          ' cancel the edit.
          ContactNameCBOBox.Undo
    
          ' tell access not to show the error message you have already dealt with it
          Response = acDataErrContinue
    
       Else
         
          'tell access not to worry as you are adding the missing contact
          Response = acDataErrAdded
    
          ' open the add contact form with these details
          DoCmd.OpenForm "frmAddContact", , , , , acDialog, NewData
    
          'cancel the edit. thats because you need to refresh the list before you can select the new Product
          ContactNameCBOBox.Undo
    
          'refresh the list
          ContactNameCBOBox.Requery
          
          'the contact name is being pulled in from frmAddContact close event to allow for changes
              
       End If
    this opens up the form "frmAddContact", if the combobox on the form "frmAddNotes" form contained a space for example "Steve Smith" then these are broken out correctly into the firstnametxtbox and lastnametxtbox when the form "frmAddContact" form opens

    if how ever only a single name has been entered for example "Steve" i.e the combo box does not contain any spaces, i am getting a "Run Time error 9, Subscript out of range" Error

    The code i have on the form "frmAddContact" is below

    Code:
    Select Case CurrentProject.AllForms("frmCustomersAddNote").IsLoaded
    
          Case Is = True
             Me.CustomerID = [Forms]![frmCustomersDetails]![CustomerID]
             [ContactFullName] = OpenArgs
             'now we split contact ful name string into first name last name
             Dim LString As String
             Dim LArray() As String
             LString = [ContactFullName]
             LArray = Split(LString)
    
             'if the contactfullname textbox has a a string like "Steven Hawkins", put the firstname into the firstnametxtbox and the last name into the lastnametxtbox
             If Not (IsEmpty(LArray(0))) And Not (IsEmpty(LArray(1))) Then
                Me.FirstNameTXTBox = LArray(0)
                Me.LastNAmeTXTBox = LArray(1)
                Me.FirstNameTXTBox = StrConv(FirstName, vbProperCase)
                Me.LastNAmeTXTBox = StrConv(LastName, vbProperCase)
                Exit Sub
             ElseIf Not (IsEmpty(LArray(0))) And (IsNull(LArray(1))) Then
                Me.FirstNameTXTBox = LArray(0)
                Me.FirstNameTXTBox = StrConv(FirstName, vbProperCase)
             End If
          Case Is = False
             Me.CustomerID = [Forms]![frmCustomersDetails]![CustomerID]
    
       End Select
    The red line is the point at which the code fails if only "steve" is entered into the combobox on the form "frmAddNote"

    How can i change the code so that if the string being entered into the combobox does not contain a space, then put this value in the firstname field
    but if it does have a space such as "Steven Hawkins" the put "Steven" in the firstname and "Hakings" into the lastname textbox

    any help would be wonderful

    many thanks

    Steve

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you are trying to reference an array element that does not exist (so cannot be empty)

    use the Ubound control

    myarray=split("SteveHawkisn"," ")
    debug.print ubound(myarray) '=1

    to loop through an array

    for I=0 to ubound(myarray)-1
    ....

  3. #3
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi Ajax

    thats great will give that a shot

    many thanks

    steve

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe try (I added anything in BLUE)
    Code:
        Select Case CurrentProject.AllForms("frmCustomersAddNote").IsLoaded
    
            Case Is = True
                Me.CustomerID = [Forms]![frmCustomersDetails]![CustomerID]
                [ContactFullName] = OpenArgs
                'now we split contact ful name string into first name last name
                Dim ULimit As Integer
                Dim LString As String
                Dim LArray() As String
                LString = [ContactFullName]
                LArray = Split(LString)
    
                'comment out next two lines after debugging is completed
                MsgBox "Lower bound of array = " & LBound(LArray)
                MsgBox "Upper bound of array = " & UBound(LArray)
    
                'upper bound of array (largest available subscript for the indicated dimension of an array)
                ULimit = UBound(LArray)
    
                'if the contactfullname textbox has a a string like "Steven Hawkins", put the firstname into the firstnametxtbox and the last name into the lastnametxtbox
                Select Case ULimit
                    Case -1  ' empty string
                        MsgBox "No name"
                    Case 0  '1 name
                        Me.FirstNameTXTBox = LArray(0)
                        Me.FirstNameTXTBox = StrConv(Me.FirstName, vbProperCase)
                    Case 1  '2 names
                        Me.FirstNameTXTBox = LArray(0)
                        Me.LastNAmeTXTBox = LArray(1)
                        Me.FirstNameTXTBox = StrConv(Me.FirstName, vbProperCase)
                        Me.LastNAmeTXTBox = StrConv(Me.LastName, vbProperCase)
                    Case Else
                        MsgBox "More than 3 names"
                End Select
            Case Is = False
                Me.CustomerID = [Forms]![frmCustomersDetails]![CustomerID]
        End Select
    End Sub

  5. #5
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi Ssanfu

    Oh My god that's great and works brilliantly

    that is so cool, many many thanks

    steve

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 01-13-2015, 05:21 PM
  2. Replies: 4
    Last Post: 02-13-2013, 02:35 PM
  3. Replies: 3
    Last Post: 02-01-2013, 12:26 PM
  4. "Subscript out of range" Error
    By yes sir in forum Access
    Replies: 21
    Last Post: 08-16-2012, 08:02 PM
  5. Database Split - Error Subscript out of range
    By Huddle in forum Database Design
    Replies: 7
    Last Post: 07-16-2010, 01:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums