handling multi select listbox

[Andromeda31]

I am trying to enter a comma separated string from a textbox into a series of (up to 4) database fields in a table, I've called the field names [animal0] [animal1] [animal2] [animal3]

am using the following code which I am adapting to my need:

Sub cmdAddstr_Click()
      Dim DataString As String
      ReDim Substr(0) As String
      Dim SubStrCount As Integer
      Dim i As Integer
      Dim strSQLinsert As String
      Dim FieldName As String
      
      
      ' Create a comma-delimited string:
      DataString = Me!MySelections.Value
     Debug.Print "Data String: " & DataString
      ' Parse the string into sub-strings:
      SubStrCount = ParseString(Substr(), DataString, ",")
     ' Display the sub-strings:
      For i = 1 To SubStrCount
      'MsgBox "Sub-String " & i & ": " & Substr(i)
      FieldName = "animal" & i
      DoCmd.RunSQL ("INSERT INTO animals ('" & FieldName & "') values ('" & _
            Substr(i) & "')")
      
    Debug.Print "this is it: " & Substr(i) & i
    Debug.Print "this is the fieldname: " & FieldName
      Next
Exit Sub


   End Sub

I think where it is going wrong is the ('" & FieldName & "') bit. I got it to successfully enter the names in a simple table where just one field is used
e.g (animal) - how would I deal with the fact that the string may be 1,2,3 or 4 values?

Any pointers to what is missing gratefully received
Obviously I will remove the msgbox and Debug.Print lines later, and put the sql into the string strSQLinsert once it works using CurrentDb.execute

cheers

 

[vbaInet]

* A field in an INSERT INTO statement is normally enclosed in square brackets (if it contains spaces or special characters) or nothing if it doesn't.
* When you have one working, you should concatenate all the field names and values before you run the sql only once.

By the way, are you using the Split() function in your ParseString function?

 

[Andromeda31]

Thought you only needed square brackets for [field names] and not for fieldnames?

Split() function?

dunno, I didn't write it, I only know how to plagiarize!

Thanks

 

[Andromeda31]

Not using the split() function, but it does use a delimiter string:

All this code came from an office help page somewhere.

Option Compare Database
Option Explicit
' Enter the following Function statement as one, single line:
   Function ParseString(SubStrs() As String, ByVal SrcStr As String, ByVal Delimiter As String) As Integer

      ' Dimension variables:
      ReDim SubStrs(0) As String
      Dim CurPos As Long
      Dim NextPos As Long
      Dim DelLen As Integer
      Dim nCount As Integer
      Dim TStr As String

      ' Add delimiters to start and end of string to make loop simpler:
      SrcStr = Delimiter & SrcStr & Delimiter
      ' Calculate the delimiter length only once:
      DelLen = Len(Delimiter)
      ' Initialize the count and position:
      nCount = 0
      CurPos = 1
      NextPos = InStr(CurPos + DelLen, SrcStr, Delimiter)

      ' Loop searching for delimiters:
      Do Until NextPos = 0
         ' Extract a sub-string:
         TStr = Mid$(SrcStr, CurPos + DelLen, NextPos - CurPos - DelLen)
         ' Increment the sub string counter:
         nCount = nCount + 1
         ' Add room for the new sub-string in the array:
         ReDim Preserve SubStrs(nCount) As String
         ' Put the sub-string in the array:
         SubStrs(nCount) = TStr
         ' Position to the last found delimiter:
         CurPos = NextPos
         ' Find the next delimiter:
         NextPos = InStr(CurPos + DelLen, SrcStr, Delimiter)
      Loop

      ' Return the number of sub-strings found:
      ParseString = nCount

   End Function

 

[vbaInet]

Thought you only needed square brackets for [field names] and not for fieldnames?

That was what I said -->

* A field in an INSERT INTO statement is normally enclosed in square brackets (if it contains spaces or special characters) or nothing if it doesn't.

You are enclosing it in single quotes.

What does Debug.Print DataString return when you run the code?

 

[Andromeda31]

I select three animals in the list box to the text box.

In my immediate window I get

Data String: elephant,chipmunk,gecko

When I comment out my sql string which is failing, I get:


Debug.Print "this is the fieldname: " & FieldName
and
Debug.Print "this is it: " & Substr(i)

Immediate window:
this is the fieldname: animal1
this is it: elephant
this is the fieldname: animal2
this is it: chipmunk
this is the fieldname: animal3
this is it: gecko

So near, yet so far.

 

[vbaInet]

I don't see why you're needing to do this whole exercise, but here's your code revised:

Sub cmdAddstr_Click()
    
    Dim strSQLinsert As String, FieldName As String
    Dim Substr As Variant, i As Integer
    
    ' split into sub strings
    Substr = Split(Nz(Me!MySelections.Value, vbNullString), ",")
    
    ' build field names and values
    For i = LBound(Substr) To UBound(Substr)
        FieldName = FieldName & "animal" & i & ", "
        strSQLinsert = strSQLinsert & "'" & Substr(i) & "', "
    Next
    
    ' get rid of the last two chars, i.e. comma and space
    FieldName = Left(FieldName, Len(FieldName) - 2)
    strSQLinsert = Left(strSQLinsert, Len(strSQLinsert) - 2)
    
    ' insert into db ONCE
    DoCmd.RunSQL ("INSERT INTO animals (" & FieldName & ") " & _
                  "VALUES ('" & Substr(i) & "');")

Exit Sub

 

[Andromeda31]

the sql is:

DoCmd.RunSQL ("INSERT INTO animals ('" & FieldName & "') values ('" & _
            Substr(i) & "')")

the error is:
Run-time error '3127':

The INSERT INTO statement contains the following field name: "animal1". Make
sure you have typed the name correctly, and try the operation again.

The table animals does have fields animal1, animal2, animal3

When the sql is commented out: the following three debug.print lines:
Debug.Print DataString
Debug.Print FieldName
Debug.Print Substr(i)

give:

elephant,chipmunk,gecko
animal1
elephant
animal2
chipmunk
animal3
gecko

 

[Andromeda31]

...all because the lady loves... multiselect boxes

Thanks for that will give it a go.

 

[vbaInet]

I gave you the exact code you should have copied the entire code and used it.

 

[Andromeda31]

Unfortunately it is throwing:
Run-time error '9':
Subscript out of range

 

[vbaInet]

It can't throw subscript out of range if it can't get into the loop. I'm using LBound and UBound functions.

Did you copy and paste the code from my post as it is?

 

[Andromeda31]

I am attaching the file here in case it is of use, presumptious though it feels.

 

[vbaInet]

Amended:

Sub cmdAddstr_Click()
    
    Dim strSQLinsert As String, FieldName As String
    Dim Substr As Variant, i As Integer
    
    ' split into sub strings
    Substr = Split(Nz(Me.MySelections.Value, vbNullString), ",")
    
    ' build field names and values
    For i = LBound(Substr) To UBound(Substr)
        FieldName = FieldName & "animal" & (i + 1) & ", "
        strSQLinsert = strSQLinsert & "'" & Substr(i) & "', "
    Next
    
    ' get rid of the last two chars, i.e. comma and space
    FieldName = Left(FieldName, Len(FieldName) - 2)
    strSQLinsert = Left(strSQLinsert, Len(strSQLinsert) - 2)
    
    ' insert into db once
    CurrentDb.Execute "INSERT INTO animals (" & FieldName & ") " & _
                      "VALUES (" & strSQLinsert & ");", dbOpenDynaset

End Sub

 

[Andromeda31]

Will try that, I got it to work with this:

Sub cmdAddstr_Click()
    
    Dim strSQLinsert As String, FieldName As String
    Dim Substr As Variant, i As Integer
    
    ' split into sub strings
    Substr = Split(Nz(Me!MySelections.Value, vbNullString), ",")
    
    ' build field names and values
    For i = LBound(Substr) To UBound(Substr)
        FieldName = FieldName & "animal" & i & ", "
        strSQLinsert = strSQLinsert & "'" & Substr(i) & "', "
    Next
    
    ' get rid of the last two chars, i.e. comma and space
    FieldName = Left(FieldName, Len(FieldName) - 2)
    strSQLinsert = Left(strSQLinsert, Len(strSQLinsert) - 2)
    
    ' insert into db ONCE
    'DoCmd.RunSQL ("INSERT INTO animals (" & FieldName & ") " & _
                  "VALUES ('" & Substr(i) & "');")
    DoCmd.RunSQL ("INSERT INTO animals (" & FieldName & ") " & _
                  "VALUES (" & strSQLinsert & ");")
                  
                  
Debug.Print strSQLinsert

Exit Sub
End Sub

 

[Andromeda31]

FieldName = FieldName & "animal" & (i + 1) & ", "

starts at Animal1, whereas your earlier code:

FieldName = FieldName & "animal" & i & ", "

starts at Animal0 as it should...
but hey....
Thanks a million

 

[vbaInet]

My first code wasn't tested. It was written in Notepad and as a result it's bound to have some errors