Append list box and Text box 1

[postmanplod]

Hello,

I have a list box and a text box on a form. When I user clicks a button, the highlighted fields from the list box are appended to a table. However, how would I also append the contents of a text box at the same time? For example if a form had the following:

TextBox Listbox
2 123
124
543
321

How would I append so the table looks like:

Disc Listbox
2 123
2 124
2 543
2 321

I have the working code the that appends the selected fields from the list box:

Private Sub Command13_Click()
'append serial numbers from list box to tbldisc
Dim lst As Access.ListBox
Dim itm As Variant
Dim strInsert As String
Dim strValues As String
Dim strSql As String
Dim colOne As Long
Dim stDocName As String

Set lst = Me![lstSerial]
strInsert = "Insert into [tblDisc]([Serial]) values "
For Each itm In lst.ItemsSelected
colOne = lst.Column(0, itm)

strValues = "(" & colOne & ")"
strSql = strInsert & strValues
CurrentDb.Execute strSql
Next itm

End Sub


Bus assuming the text box is txtDisc and the table\field is tblDisc\DiscNo, how do I get it to append?

Many thanks,

Michael

 

[dhookom]

Try something like:

Private Sub Command13_Click()
'append serial numbers from list box to tbldisc
  Dim lst As Access.ListBox
  Dim itm As Variant
  Dim strInsert As String
  Dim strValues As String
  Dim strSql As String
  Dim colOne As Long
  Dim stDocName As String
  Dim intDiscNo as Integer
  intDiscNo = Me.txtDisc
  Set lst = Me![lstSerial]
  strInsert = "Insert into [tblDisc]([Serial],DiscNo) values "
  For Each itm In lst.ItemsSelected
    colOne = lst.Column(0, itm)
    
    strValues = "(" & colOne & "," & intDiscNo & ")"
    strSql = strInsert & strValues
    CurrentDb.Execute strSql
  Next itm
  
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[postmanplod]

That works!! Thank you.