Solved Fill List with an other List

[Gizem]

Hello together,

i need help again A couple of weeks ago, i got so many advices.
I have two listboxes. The first one is filled with data and the other one is empty. Now the User wants to fill the second Listbox by using the first Listbox and a button. The User has to choose one element in the first Listbox with a click on it. The data origin is a table. The table has three colomns(ID, POSITION, NAME).

Private Sub HinHer(Ctl0 As Control, Ctl1 As Control, OnlySelected As Boolean)
Dim Itm, Wert, Tbl1, Tbl0, DB As Database
  Set DB = CurrentDb
  Tbl0 = Ctl0.RowSource
  Tbl1 = Ctl1.RowSource
 
  If Ctl1.ListCount = 0 Then
    For Itm = 0 To Ctl0.ListCount - 1
        If Not OnlySelected Or Ctl0.Selected(Itm) Then
        Wert = Ctl0.ItemData(Itm)
        DB.Execute "INSERT INTO " & Tbl1 & "(NAME) VALUES ('" & Wert & "')"
        End If
    Next Itm
  End If
 
  If Ctl1.ListCount > 0 Then
  Dim var0 As Integer
  Dim var1 As Integer
  Dim var2 As Integer
  var0 = Ctl1.ListCount
 


      For Itm = 0 To Ctl0.ListCount - 1


 
        If Not OnlySelected Or Ctl0.Selected(Itm) Then
  For var1 = 0 To var0 - 1


        
        
        Wert = Ctl0.ItemData(Itm)
            
        If Me.Liste37.ItemData(var1) = Wert Then
        MsgBox "Sie haben das schon eingefügt ?!"
        Exit Sub
        End If
        Next var1
        
        DB.Execute "INSERT INTO " & Tbl1 & "(PS_P_NAME) VALUES ('" & Wert & "')"
        End If
    Next Itm
 
  End If
 


 
  Ctl0.Requery
  Ctl1.Requery
  Set DB = Nothing
 
 
End Sub

I get a runtime error 3134: Syntax Error in INSERT-INTO Statement.

I hope you can see my failure

 

[Gasman]

Put the sql into a string varaible and debug.print it. Should be obvious then?

 

[Pat Hartman]

What are you going to do with the other list?

When I do this, it is usually to create something like a transmittal or possibly an order. As each item is double clicked in list A an append query runs and adds it to the Transmittal or Order or whatever. Doing it this way generally saves you the step of doing something with the right side list at the end.

The form will have "left to right" and "right to left" buttons. So, "left to right" runs an append query and "right to left" runs a delete query.

 

[bastanu]

Looks like you are missing spaces in all your Insert lines immediately after the Tbl1 variable, debugging that statement as suggested by Gasman should show you the issue. Usually what you are describing is used in the context of Available on the left and Selected on the right, so again, the normal practice is to remove from the Available list the item just added to Selected and to add it back if it is "de-selected" on the right. So you need an append and a delete query for both directions (left to right and right to left).

Cheers,

 

[Gizem]

Put the sql into a string varaible and debug.print it. Should be obvious then?

Hey i tried it, but there is not any difference.

 

[Gizem]

I think it does not work because of data origin:

SELECT [ProjekteName].[POSITION], [ProjekteName].[PS_P_NAME] FROM ProjekteName ORDER BY [POSITION];

 

[Gizem]

I proved Wert with a MsgBox. Wert was a number( I think Index), but it should be a String(Name like Mark ).

 

[Gasman]

Hey i tried it, but there is not any difference.

Post the result of the debug.print back here. Copy and paste, DO NOT try and rewrite it.

 

[bastanu]

Can you please show us the row source for the first listbox (Ctl0)?

It looks like the problem might be with the way you are trying the get the value for the wert variable:
Wert = Ctl0.ItemData(Itm) ' this will return the value in the bound column of the listbox

If the listbox has two columns (the first one being the bound column) and the one you want to extract is the second one you would need something like this:

Wert = Ctl0.Column(1,Itm) 'column indexes are 0 based so 1 will return the second one

Cheers,

 

[Gizem]

Can you please show us the row source for the first listbox (Ctl0)?

It looks like the problem might be with the way you are trying the get the value for the wert variable:
Wert = Ctl0.ItemData(Itm) ' this will return the value in the bound column of the listbox

If the listbox has two columns (the first one being the bound column) and the one you want to extract is the second one you would need something like this:

Wert = Ctl0.Column(1,Itm) 'column indexes are 0 based so 1 will return the second one

Cheers,

Yes exactly wert was my problem. I solved the problem like this :

wert = Ctl0.Column(1)

But here i can just choose one element. Now i will try it like your suggestion.