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.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

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.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

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(80040e14) 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.GoToRecord , , 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.GoToRecord , , 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.GoToRecord , , 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.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

[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_TAble)

[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_TAble)

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

[hexagram]

i use one table in 2 different subform because my purpose
is in one of the subform is the list of all product with no tags, then i will
past to my new subform and still with a complete list of products with no tags
then i will tag the product manually in the new form then save it
so in my table i captured all the list of my product wig tag or without tag,

is my analysis wont make any sense? would you give me another idea?

[MMcCarthy]

i use one table in 2 different subform because my purpose
is in one of the subform is the list of all product with no tags, then i will
past to my new subform and still with a complete list of products with no tags
then i will tag the product manually in the new form then save it
so in my table i captured all the list of my product wig tag or without tag,

is my analysis wont make any sense? would you give me another idea?

I'm not sure why you need the two tables but you don't need to subform records.

Once you add the record to the POR_Table the subform will populate automatically with the existing records.

Just remove the code to add the records to the subform and see what happens.

Mary

[hexagram]

Only the Value in the Text Box will appear, but no records appearing in the subform : (

[MMcCarthy]

Only the Value in the Text Box will appear, but no records appearing in the subform : (

Try adding this to the end of the code.

 
Me![JMTPORDETAILSUBFORM].Requery

[hexagram]

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
Me![JMTPORDETAILSUBFORM].Requery
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


Mary Do i put the code correctly? still no records appearing only the textbox