Combo Box with a event procedure

P

PJ

I have a form tied to a table called "WMP Input Table" with Text, Combo &
List Boxes. The list box control source named "Joint Call"has a record
source named (table) "Capital Market Partners" that you can highlight
multiple data like "FX, Derivative etc. " After you fill in the data I have a
command button "Command29" that has the below Event Procedure tied to it. I
want all data that I input on the form to go to the "WMP Input Table" and
then clear the form. It works but the list box does not clear on the form
and make a seperate record from the rest of the text & combo boxes that are
entered at the sametime. So I have two record when I want one. Any ideas??

Thanks in advance.


On Error GoTo Err_Command29_Click

Dim dbs As Database
Dim rst As Recordset
Dim varitem As Variant
Dim strlist As String
Dim strlista As String
Dim inti As Integer

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("WMP Input Table")

i = 0
With Me![Capital Market Partners]
For Each varitem In .ItemsSelected
i = i + 1
strlist = strlist & .Column(1, varitem) & vbCrLf
strlista = strlista & .Column(1, varitem) & ", "

Next varitem
With rst
..AddNew
![Joint Call] = strlista
![number of groups] = i
..Update
End With
End With

Me![Capital Market Partners].Requery

DoCmd.GoToRecord , , acNext

Exit_Command29_Click:
Exit Sub

Err_Command29_Click:
MsgBox Err.Description
Resume Exit_Command29_Click
 
J

Jeanette Cunningham

Hi PJ,

this code
With rst
..AddNew
![Joint Call] = strlista
![number of groups] = i
..Update
End With

Simply adds 2 fields to a new record - the fields Joint Call and number of
groups.

If you want all the fields saved to the table easily, that happens when the
user clicks Save, closes the form or goes to the next record.

Below is untested air code for you to try.

Private Sub ListA
Dim varitem As Variant
Dim strlist As String
Dim strlista As String
Dim inti As Integer

i = 0
With Me![Capital Market Partners]
For Each varitem In .ItemsSelected
i = i + 1
strlist = strlist & .Column(1, varitem) & vbCrLf
strlista = strlista & .Column(1, varitem) & ", "

Next varitem
Me![Joint Call] = strlista
Me![number of groups] = i

End With
End Sub



In the Before Update event for the form, you could put this code
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top