Multiple Selections from Listbox 2

[zambrtp]

I want to be able to use a listbox with multiple selections turned on BUT I want that data ( whether its 1 or greater) to fill ONE Column field......is this possible?

So the user selects coffee, tea, milk they fill one beverage column.

 

[PaulBricker]

If I understand you, you want the answer to populate one field in one record. You can try this in the click event for a button.

Dim varItm as Variant
Dim ctl as Control
Dim mystr as String
Set ctl = Me.ListBoxName
For Each varItm in ctl.ItemsSelected
mystr = mystr & ctl.ItemData(varItm) & ", "
Next varItm
Me.TextboxName = Left(mystr,Len(mystr)-2)
Set ctl = Nothing

So if the user selects
Coffee
Tea
Milk
the Me.TextboxName will be assigned the value
Coffee, Tea, Milk

Paul

 

[zambrtp]

Paul

Can I add this code to the DoCmd.GoToRecord , , acNewRec

And I want it to populate a column in a table....I tried to change the code to an INSERT INTO but it wouldnt work....
Any ideas?

 

[PaulBricker]

So to get it to populate multiple records in a single column you would do something like this.

Dim varItm as Variant
Dim ctl as Control
Dim rst as DAO.Recordset
Set rst = CurrentDb.OpenRecordset("TableNameHere",dbOpenDynaset)
Set ctl = Me.ListBoxName
For Each varItm in ctl.ItemsSelected
rst.AddNew
rst!TableFieldName = ctl.ItemData(varItm)
rst.Update
Next varItm
Set ctl = Nothing

You have to have some event outside the Listbox to run this. Usually I do it with a button but almost any other event will work.
Hope this helps.

Paul

 

[zambrtp]

Paul:

I am lost...I dont know what event to place it at....When I put in on the OK ( ADDRECORD) button it blows uo on the Set rst = CurrentDb line...

The first code you suggested gives the right data but like this name, name so it wont allow it to enter one data field...can I make it see the comma as data not a delimiter?
then do an Insert into the table?

This form is a series of subforms can I put the code on the next fields on focus ...

 

[PaulBricker]

Sorry because now I'm not sure which what you want to add the data to the table. You need to try and clarify this a little for me. Do you want all the items selected in the Listbox in ONE RECORD or do you want each one in a DIFFERENT RECORD? It sounds like you want all the values in ONE RECORD. Try and clear that up for me and I'll see about the Insert Into statement. When it blows up on Set rst = CurrentDB line, what is the error message that you get. It may be a simple fix. Hang in there, we'll get it.

Paul

 

[zambrtp]

Paul:
Im sorry...People skills need some work...Im typing like Im talking to myself...OK....Ill try again...
Yes..I want the multiple items selected to go into ONE record...so if they were to see a report it would look like this...
name beverages location
Tereza milk,coke,wine Texas
I tried ..
Insert into Details (Levels) Values ("& mystr &&quot but that errored because it said two many values for Insert, I think because the items are separated by commas...

The other error is invalid argument...when I open up the debug it points to that Set rst line of code...

It maybe where I am placing it...

 

[PaulBricker]

OK, I ran this from a click event for a button and it did the trick just fine. No issues with the commas in the string.

Private Sub Command19_Click()
Dim db As DAO.Database
Dim ctl As Control
Dim varItm As Variant
Dim myStr As String
Dim strSQL As String
Set ctl = lstEmployeeNames
For Each varItm In ctl.ItemsSelected
myStr = myStr & ctl.ItemData(varItm) & " ,"
Next varItm
myStr = Left(myStr, Len(myStr) - 2)
strSQL = "Insert Into Table1(MyField) Values ('" & myStr & "')"
DoCmd.RunSQL strSQL
End Sub

Give it a try and see how it goes. Make sure your syntax around the variable myStr is correct for text. In the Insert Into string you posted, you only had one " on each side of the variable. That is the syntax for numbers. That may be why it bombed.

Paul

 

[zambrtp]

Paul:
YOU'VE BEEN SUCH A HELP....I DONT WANT TO BURN YOU OUT ON MY Question...BUT...
You were right its the extra tick....BUT my form is also giving me problems...it asks me if I really want to append the record I say yes then there are conflicts with other records on the form......I wish I could send the whole thing to you...OR maybe my form construction is wrong....
Here is some of the code...you'll see I made a button for the LISTBOX just to try it...where do you recommend I put the code?

VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "Form_MainForm"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Compare Database


Public Sub CmdFind_Click()
On Error GoTo Err_CmdFind_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_CmdFind_Click:
Exit Sub

Err_CmdFind_Click:
MsgBox Err.Description
Resume Exit_CmdFind_Click

End Sub
Public Sub CmdAdd_Click()
On Error GoTo Err_CmdAdd_Click


DoCmd.GoToRecord , , acNewRec



Exit_CmdAdd_Click:
Exit Sub

Err_CmdAdd_Click:
MsgBox Err.Description
Resume Exit_CmdAdd_Click

End Sub
Public Sub CmdExit_Click()
On Error GoTo Err_CmdExit_Click


DoCmd.Close

Exit_CmdExit_Click:
Exit Sub

Err_CmdExit_Click:
MsgBox Err.Description
Resume Exit_CmdExit_Click

End Sub
Public Sub CmdDel_Click()
On Error GoTo Err_CmdDel_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_CmdDel_Click:
Exit Sub

Err_CmdDel_Click:
MsgBox Err.Description
Resume Exit_CmdDel_Click

End Sub


Private Sub cmdtest_Click()


Dim varItm As Variant
Dim ctl As Control
Dim mystr As String
Dim strSQL As String
Set ctl = Me.levels
For Each varItm In ctl.ItemsSelected
mystr = mystr & ctl.ItemData(varItm) & ","
Next varItm
mystr = Left(mystr, Len(mystr) - 2)
strSQL = "Insert into Details (Levels) VALUES ('" & mystr & "')"
DoCmd.RunSQL strSQL

End Sub

 

[PaulBricker]

We'll try and work it out here first. To stop the Access messages about appending the records do this
Around this line in the code
DoCmd.RunSQL strSQL

add
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL 'this code sandwiched here
DoCmd.SetWarnings True

Now concerning the other problems, this change may cover the error messages up so you need to run it before changing the code and then post what the messages say. Without them, I can't tell you much. It may have to do with duplicates or incorrect field sizes but I won't know until you post the error descriptions.

Paul

 

[zambrtp]

The error I get after I say yes to append is

Microsoft Access can't append all the records in the append query.

MSACCESS set 0 fields to Null due to a type conversion failure and it didnt and add 0 records to the table to key violations, 0 records due to lock violations and 1 record du to validation rule violation
to ginore and run anyway Click yes

When I add the code you mentioned I dont get any errors but the record is not inserted into the table Details column Levels...

I looked over the table it has no required fields except the PK which I set to Autonumber...

 

[PaulBricker]

Sounds by the message that the field you are trying to insert the information in isn't set up as a text field. You might want to open your table in design view to check the field Data Type and also check the Field Size at the bottom of the page and make sure it's set to a value large enough to store all the multi selections. You can set a text field from 1 up to 255 bytes. If you plan to store more than 255 characters, you may need to go to a Memo Data Type field.

Paul

 

[zambrtp]

I set the private to public now it inserts BUT doesnt wait for the rest of the record to fill....so it is its own reacord then when I hit the ADD button the rest of the data fills a new record...
Almost there...please bare with me

 

[PaulBricker]

Try this. I didn't see any other Add info going into the new record but you should be able to get it all in this procedure.

Public Sub CmdAdd_Click()
On Error GoTo Err_CmdAdd_Click


DoCmd.GoToRecord , , acNewRec
Dim varItm As Variant
Dim ctl As Control
Dim mystr As String
Dim strSQL As String
Set ctl = Me.levels
For Each varItm In ctl.ItemsSelected
mystr = mystr & ctl.ItemData(varItm) & ","
Next varItm
mystr = Left(mystr, Len(mystr) - 2)
strSQL = "Insert into Details (Levels) VALUES ('" & mystr & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
Docmd.SetWarnings True


Exit_CmdAdd_Click:
Exit Sub

Err_CmdAdd_Click:
MsgBox Err.Description
Resume Exit_CmdAdd_Click

End Sub

Paul

 

[PaulBricker]

Sorry, I just got out of a meeting and didn't really look at my answer. Please just post what you are trying to get into the new rec. We don't need to go to a new record if we use the Insert Into Statement. We may be able to get it all into that one procedure.

More info please.

Paul

 

[zambrtp]

Paul...Sorry..I was pulled away on another DB issue...I will try this today..thanks for the help

 

[zambrtp]

Paul: I noticed two things...When I put the code at the CmdADD when I get to the subform the selections I made release, and when I select the ADD button the error is
Invalid proceudre call or argument. The rest of the data goes into the table but the list is lost...
any ideas?

 

[PaulBricker]

OK we'll have to back up a little here and retrace some steps. The CmdAdd button code that you posted simply goes to a new record. So I'm not sure how that ties in with our Insert Into statement. Do you want to populate the new record with the values selected in the Listbox? If that's the case then we don't want the Insert Into statement because if your form is tied to the Table the values can get to the table by setting the value of the control to our variable "myStr". Also, are you adding values to other controls on your form at the same time you are running the code to Insert Into your Table? You may need to give me a better picture of just what the process is doing.

Paul

 

[zambrtp]

Paul:

This is a form with a subform...The main form has the list box we are dealing with...along with a number of other txtboxes, combo boxes to enter data....then the subform also has a number of fields to enter data that have a one-to-many relationship with the main form..There is one add new record button for the whole form...

I want to have the user select the data from that list box then continue entering the rest of the data..when they are done I want them to press one button and have all the data fill the tables at one time...
Yes I want the listbox and the other fields to populate at the same time ...
I could email you the whole *.mdb ..if that would help..

Tereza

 

[PaulBricker]

Then what we need to do is gather all the info into one place and use it in the Insert Into statement like this (I'm sure there are a few ways to do this but we'll go this route)

Dim sbfrm as Form
Dim ctl as Control
Dim varItm as Variant
Dim myStr as String
Dim strSQL as String
Set sbfrm = Me.SubFormName
Set ctl = Me.ListboxName
For Each varItm in ctl.ItemsSelected
myStr = myStr & ctl.ItemData(varItm) & " ,"
Next
myStr = Left(myStr,Len(myStr)-2)
strSQL = Insert Into Table(Field1, Field2,....FieldX) Values('" & Me!Field1Text & "', " & FieldTwoNumber & ", '" & Me.SubFormName!Field1aText & "', #" & Me.SubFormName!Field1bDate & "#, '" & FieldXText & "')"
DoCmd.SetWarnings False
Docmd.RunSQL strSQL
DoCmd.SetWarnings True

You'll have to fill in your subform name, listbox name and form field names. I have given you the syntax for Text, Number and Date field types. You should be able to get it close using this expression. Try it out and post back. If you can't get it, post your email address and I'll send you my address.

Paul