Populating Listbox (Multiple Select) in a textbox and in a subform

[hexagram]

Hi guys good day, can anybody help for my problem

The Scenario is
A Listbox (ID) - Multiple Select - everytime i choose in the list box

the following outbound textbutton and subform will retrieve in my form-

the folliwng value will get from another form
i have a code but i need to customize it because everytime i choose ID in my list box my value in subform is multiplying by row i mean the value in subform is duplicating

this my code in my command button:
'For Retrival of Values in another form'

Option Compare Database
Option Explicit
Dim cboOriginator As ComboBox

Private Sub cmdtest_Click()
Dim rs As New ADODB.Recordset
Dim cnn As New ADODB.Connectio n
Dim i As Integer
Dim sSql As String
Dim ssql1 As String
Dim rs1 As New ADODB.Recordset

Set cnn = CurrentProject. Connection
sSql = "select * from [DR_Table] where id =" & List20.Value
rs.Open sSql, cnn, adOpenKeyset, adLockOptimisti c
If rs.RecordCount > 0 Then
Company = rs!Company
Address = rs!Address
Contactperson = rs!Contactperso n
Designation = rs!Designation
Telno = rs!Telno
Faxno = rs!Faxno
rs.Close

ssql1 = "SELECT * FROM [Dr_Detail_Table] WHERE ID=" & List20.Value
rs.Open ssql1, cnn, adOpenKeyset, adLockOptimisti c
If rs.RecordCount > 0 Then
Me.[JMTPORDETAILSUB FORM].SetFocus
Do Until rs.EOF
Me.[JMTPORDETAILSUB FORM]![Particular] = rs!Particular
Me.[JMTPORDETAILSUB FORM]![Qty] = rs!Qty
Me.[JMTPORDETAILSUB FORM]![PartNumber] = rs!PartNumber
Me.[JMTPORDETAILSUB FORM]![id] = rs!id
Me.[JMTPORDETAILSUB FORM]![flag] = rs!flag
Me.Refresh
'DoCmd.GoToReco rd , , acNewRec
rs.MoveNext
Loop
rs.Close
Else
Company = vbNullString
Address = vbNullString
Contactperson = vbNullString
Designation = vbNullString
Telno = vbNullString
Faxno = vbNullString
End If
End If
End Sub

This is my code for Saving in the value in table

'For Saving
Private Sub Command23_Click ()
Dim rs As New ADODB.Recordset
Dim cnn As New ADODB.Connectio n
Dim i As Integer
Dim sSql As String
Dim ssql1 As String
Dim rs1 As New ADODB.Recordset

Set cnn = CurrentProject. Connection
sSql = "select * from [POR_Table] where id =" & List20.Value
rs.Open sSql, cnn, adOpenKeyset, adLockOptimisti c
If rs.RecordCount > 0 Then
rs!Company = Company
rs!Address = Address
rs!Contactperso n = Contactperson
rs!Designation = Designation
rs!Telno = Telno
rs!Faxno = Faxno
rs.Update
rs.Close

ssql1 = "SELECT * FROM [DR_Detail_Table] WHERE ID=" & List20.Value
rs.Open ssql1, cnn, adOpenKeyset, adLockOptimisti c
'If rs.RecordCount > 0 Then
Me.[JMTPORDETAILSUB FORM].SetFocus
Do Until rs.EOF
rs!Particular = Me.[JMTPORDETAILSUB FORM]![Particular]
rs!Qty = Me.[JMTPORDETAILSUB FORM]![Qty]
rs!PartNumber = Me.[JMTPORDETAILSUB FORM]![PartNumber]
'DoCmd.GoToReco rd , , acNewRec

rs.Update
rs.MoveNext
Loop

Else
Company = vbNullString
Address = vbNullString
Contactperson = vbNullString
Designation = vbNullString
Telno = vbNullString
Faxno = vbNullString
End If
End Sub

Thank you so much in advance

[hexagram]

It seems that my post is not much clear because i dont get any reply from you guys i reconstruct my wording

In my Form

I have a listbox and the Value of this "column" (Listbox) is ID, the user can select a multiple id
then a subform that must display a "record" in a table then a textbox (unbound) and a commandbutton to execute

the scenario is like this, The user will select a ID in column then click the commandbutton
the textbox will get the value from a table then in subform it also get the record in another table

The value for Textbox is working properly my problem is in the subform i noticed that everytime i choose
an id and click the commandbutton the record that display in my subform is duplicating, and if i change the
listbox property into multiselect (simple) i got an error

this is the error " Run-time error -2147217900(8004 0e14) Syntax error (missing operator)in query expression
'ID =',

This is my code in Retriving Value into the form

 
Private Sub cmdtest_Click()
Dim rs As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim i As Integer
Dim sSql As String
Dim ssql1 As String
Dim rs1 As New ADODB.Recordset

Set cnn = CurrentProject.Connection
sSql = "select * from [DR_Table] where id =" & List20.Value
rs.Open sSql, cnn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
Company = rs!Company
Address = rs!Address
Contactperson = rs!Contactperson
Designation = rs!Designation
Telno = rs!Telno
Faxno = rs!Faxno
rs.Close

ssql1 = "SELECT * FROM [Dr_Detail_Table] WHERE ID=" & List20.Value
rs.Open ssql1, cnn, adOpenKeyset, adLockOptimistic
'If rs.RecordCount > 0 Then
Me.[JMTPORDETAILSUBFORM].SetFocus
Do Until rs.EOF
Me.[JMTPORDETAILSUBFORM]![Particular] = rs!Particular
Me.[JMTPORDETAILSUBFORM]![Qty] = rs!Qty
Me.[JMTPORDETAILSUBFORM]![PartNumber] = rs!PartNumber
Me.[JMTPORDETAILSUBFORM]![id] = rs!id
Me.[JMTPORDETAILSUBFORM]![flag] = rs!flag
Me.Refresh
'DoCmd.GoToRecord , , acNewRec
rs.MoveNext
Loop
rs.Close
Else
Company = vbNullString
Address = vbNullString
Contactperson = vbNullString
Designation = vbNullString
Telno = vbNullString
Faxno = vbNullString
End If
'End If
End Sub

And this is the Code for Saving Value to Table

Private Sub Command23_Click()
Dim rs As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim i As Integer
Dim sSql As String
Dim ssql1 As String
Dim rs1 As New ADODB.Recordset

Set cnn = CurrentProject.Connection
sSql = "select * from [POR_Table] where id =" & List20.Value
rs.Open sSql, cnn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
rs!Company = Company
rs!Address = Address
rs!Contactperson = Contactperson
rs!Designation = Designation
rs!Telno = Telno
rs!Faxno = Faxno
rs.Update
rs.Close

ssql1 = "SELECT * FROM [DR_Detail_Table] WHERE ID=" & List20.Value
rs.Open ssql1, cnn, adOpenKeyset, adLockOptimistic
'If rs.RecordCount > 0 Then
Me.[JMTPORDETAILSUBFORM].SetFocus
Do Until rs.EOF
rs!Particular = Me.[JMTPORDETAILSUBFORM]![Particular]
rs!Qty = Me.[JMTPORDETAILSUBFORM]![Qty]
rs!PartNumber = Me.[JMTPORDETAILSUBFORM]![PartNumber]
'DoCmd.GoToRecord , , acNewRec

rs.Update
rs.MoveNext
Loop

Else
Company = vbNullString
Address = vbNullString
Contactperson = vbNullString
Designation = vbNullString
Telno = vbNullString
Faxno = vbNullString
End If
End Sub

Thanks for advance hope its kinda clear

[MMcCarthy]

Try this ...

 
Private Sub cmdtest_Click()
Dim rs As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim valSelect As Variant
Dim i As Integer
Dim sSql As String

Set cnn = CurrentProject.Connection
For Each valSelect In Me!List20.ItemsSelected
   sSql = "select * from [DR_Table] where id =" & Me!List20.ItemData(valSelect)
   rs.Open sSql, cnn, adOpenKeyset, adLockOptimistic
   If rs.RecordCount > 0 Then
	  Me!Company = rs!Company
	  Me!Address = rs!Address
	  Me!Contactperson = rs!Contactperson
	  Me!Designation = rs!Designation
	  Me!Telno = rs!Telno
	  Me!Faxno = rs!Faxno
   End If
   rs.Close
   Set rs = Nothing

   ssql = "SELECT * FROM [Dr_Detail_Table] WHERE ID=" & Me!List20.ItemData(valSelect)
   rs.Open ssql, cnn, adOpenKeyset, adLockOptimistic
   rs.MoveLast
   rs.MoveFirst
   If rs.RecordCount > 0 Then
	  Me![JMTPORDETAILSUBFORM].SetFocus
	  Do Until rs.EOF
		 Me![JMTPORDETAILSUBFORM]![Particular] = rs!Particular
		 Me![JMTPORDETAILSUBFORM]![Qty] = rs!Qty
		 Me![JMTPORDETAILSUBFORM]![PartNumber] = rs!PartNumber
		 Me![JMTPORDETAILSUBFORM]![id] = rs!id
		 Me![JMTPORDETAILSUBFORM]![flag] = rs!flag
		 Me.Refresh
		 DoCmd.GoToRecord , , acNewRec
		 rs.MoveNext
	  Loop
	  rs.Close
	  Set rs = Nothing
   Else
	  Me! Company = vbNullString
	  Me! Address = vbNullString
	  Me! Contactperson = vbNullString
	  Me! Designation = vbNullString
	  Me! Telno = vbNullString
	  Me! Faxno = vbNullString
   End If
Next valSelect

End Sub

If you bind the form to the table where you want the records saved then you won't need the second piece of code.

Mary

[hexagram]

hi mary i tried the code i have errors

Run-Time errors '2499'
You cant use the GoToRecord action or method on an object in Design view

Then if i click Debug

It point to this code
DoCmd.GoToRecor d , , acNewRec

[hexagram]

Hi no errors now i forgot to change the properties of my list box into a multiselect but i have still some issues the record in subforms are duplicating

[MMcCarthy]

Try substituting this code ...

If rs.RecordCount > 0 Then
	  Me![JMTPORDETAILSUBFORM].SetFocus
	  Do Until rs.EOF
		 Me![JMTPORDETAILSUBFORM].Form![Particular] = rs!Particular
		 Me![JMTPORDETAILSUBFORM].Form![Qty] = rs!Qty
		 Me![JMTPORDETAILSUBFORM].Form![PartNumber] = rs!PartNumber
		 Me![JMTPORDETAILSUBFORM].Form![id] = rs!id
		 Me![JMTPORDETAILSUBFORM].Form![flag] = rs!flag
		 DoCmd.GoToRecord , , acNext
		 rs.MoveNext
	  Loop
	  rs.Close
	  Set rs = Nothing
   Else

[hexagram]

It has an error saying 'Run -Time Error '2105';
You cant go to the specified record

I try to comment the

DoCmd.GoToRecor d , , acNewRec and it has no errors but it still duplicating the records in subform

[MMcCarthy]

It has an error saying 'Run -Time Error '2105';
You cant go to the specified record

I try to comment the

DoCmd.GoToRecor d , , acNewRec and it has no errors but it still duplicating the records in subform

The new code I gave you had

DoCmd.GoToRecord , , acNext

[hexagram]

This is the structure of my table
from table DR_Detail_Table : I have 3 records

ID Particular QTY PARTNUMBER Flag
DRF-00001 CPU 1 Desc No
DRF-00001 MOnitor 1 Desc No
DRF-00001 PowerCord 1 Desc No

In Subform View - This is the one i view everytime i run the commandbutton
and i observe it always the last record only

ID Particular QTY PARTNUMBER Flag
DRF-00001 PowerCord 1 Desc No
DRF-00001 PowerCord 1 Desc No
DRF-00001 PowerCord 1 Desc No

[hexagram]

Yes mary this is the code that you gave no errors but still duplicates the record on the subform

sSql = "SELECT * FROM [Dr_Detail_Table] WHERE ID=" & Me!List20.ItemD ata(valSelect)
rs.Open sSql, cnn, adOpenKeyset, adLockOptimisti c
rs.MoveLast
rs.MoveFirst
If rs.RecordCount > 0 Then
Me![JMTPORDETAILSUB FORM].SetFocus
Do Until rs.EOF
Me![JMTPORDETAILSUB FORM]![Particular] = rs!Particular
Me![JMTPORDETAILSUB FORM]![Qty] = rs!Qty
Me![JMTPORDETAILSUB FORM]![PartNumber] = rs!PartNumber
Me![JMTPORDETAILSUB FORM]![id] = rs!id
Me![JMTPORDETAILSUB FORM]![flag] = rs!flag
Me.Refresh
DoCmd.GoToRecor d , , acNewRec
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Else
Me!Company = vbNullString
Me!Address = vbNullString
Me!Contactperso n = vbNullString
Me!Designation = vbNullString
Me!Telno = vbNullString
Me!Faxno = vbNullString
End If
Next valSelect
End Sub

[MMcCarthy]

Run this query substituting a valid ID for 1.

SELECT * FROM [Dr_Detail_Table] WHERE ID=1

What results are you getting?

[hexagram]

This what i got if i run the query uve gave

DRF - 00001 Memory 2GB 1 RTS-0909TY-STYA No
DRF - 00001 CPU 1 WR1-0FGQE-0091 No
DRF - 00001 PowerCord 1 QDSG7689 No

[MMcCarthy]

This what i got if i run the query uve gave

DRF - 00001 Memory 2GB 1 RTS-0909TY-STYA No
DRF - 00001 CPU 1 WR1-0FGQE-0091 No
DRF - 00001 PowerCord 1 QDSG7689 No

OK now is the subform of the New form by any chance set to the Record Source of the Dr_Detail_Table . If so then we are taking records from that table and readding them. Or is there a new table for these records?

[hexagram]

YEs the subform of the new form is set to the record of DR_Detail_Table ,
and there no new table for these record, it will appear in a same table (DR_Detail_TAbl e)

[MMcCarthy]

YEs the subform of the new form is set to the record of DR_Detail_Table ,
and there no new table for these record, it will appear in a same table (DR_Detail_TAbl e)

But these records are already in that table so by adding them you are creating duplicates.