Pop up ListBox form that updates text box on main form 1

[Vittles]

I have a main form (Main Admin Book) that has several fields where users enter 'degrees' for personnel. I want to be able to have a pop up form where they can select multiple degrees and have those degrees then sent to the textbox when the pop up form closes.
Table: CTU Info
Field & TextBox name=CTU PI
MultiSelect ListBox: DegreesLB

I found the following code on this site (from Richard?) and have used it as a starting point and it does 'insert' the values into the base table,but it also creates a new record each time, when I need it to 'Update' the current record instead. I have tried to work with the CTU ID=CTU ID from main form, but haven't gotten it to work... Any ideas?

Private Sub Form_Close()
'if there is more than one degree selected then merge the two together and then save them in the database

Dim i As Integer
Dim textvalues As String
For i = 0 To DegreesLB.ItemsSelected.Count - 1
If DegreesLB.Selected(i) Then textvalues = textvalues + DegreesLB.Column(0, i) & ","
End If
Next
CurrentDb.Execute "INSERT INTO [CTU Info] ([CTU PI Degree]) VALUES ('" & textvalues & "');"
End Sub

 

[jebry]

Hi!

You will need to change your INSERT INTO query to an UPDATE query to do what you are wanting, but I wouldn't recommend it. If you put multiple degrees into one field in a table then you will have a difficult time working with the information when you want to retrieve it. Instead, make a new table:

NewTable

CTU ID
DegreeCode

You may need other fields but these two will be the primary key for the table. Now you will have one record for each degree a person may have and you will find that querying, reporting and even displaying this data will be much simpler.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[Vittles]

I thought about doing what you are saying, but this is pretty much only for a name spot - so I was hoping to keep in all in one field. We wouldn't be querying on the degree - just listing it by the last name on reports.... If there isn't an easy way to do it, then I may just leave it a plain text box. I am new to using multiselect list boxes like this...so was hoping they could do this fairly simply so the end users would save a bit of time.

 

[TheAceMan1]

How are ya Vittles . . . . .

Try this ([blue]You![/blue] substitute proper names in [purple]purple[/purple]):

[blue]   Dim itm, Pack As String
   
   For Each itm In Me!DegreesLB.ItemsSelected
      If DegreesLB.Selected(itm) Then
         If Pack <> "" Then
            Pack = Pack & "' " & Me!DegreesLB.Column(0, itm)
         Else
            Pack = Me!DegreesLB.Column(0, itm)
         End If
      End If
   Next
   
   If Pack <> "" Then Forms![[purple][b]MainFormName[/b][/purple]]![[purple][b]TextboxName[/b][/purple]] = Pack[/blue]

See Ya! . . . . . .

 

[Vittles]

Thank you so much!! That is perfect! It sounds like I really need to spend some time learning VB as coding like that seems so easy when others know it. Thanks again!