By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,959 Members | 1,139 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,959 IT Pros & Developers. It's quick & easy.

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

P: 54
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
Mar 20 '07 #1
Share this Question
Share on Google+
19 Replies


P: 54
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
Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdtest_Click()
  3. Dim rs As New ADODB.Recordset
  4. Dim cnn As New ADODB.Connection
  5. Dim i As Integer
  6. Dim sSql As String
  7. Dim ssql1 As String
  8. Dim rs1 As New ADODB.Recordset
  9.  
  10. Set cnn = CurrentProject.Connection
  11. sSql = "select * from [DR_Table] where id =" & List20.Value
  12. rs.Open sSql, cnn, adOpenKeyset, adLockOptimistic
  13. If rs.RecordCount > 0 Then
  14. Company = rs!Company
  15. Address = rs!Address
  16. Contactperson = rs!Contactperson
  17. Designation = rs!Designation
  18. Telno = rs!Telno
  19. Faxno = rs!Faxno
  20. rs.Close
  21.  
  22. ssql1 = "SELECT * FROM [Dr_Detail_Table] WHERE ID=" & List20.Value
  23. rs.Open ssql1, cnn, adOpenKeyset, adLockOptimistic
  24. 'If rs.RecordCount > 0 Then
  25. Me.[JMTPORDETAILSUBFORM].SetFocus
  26. Do Until rs.EOF
  27. Me.[JMTPORDETAILSUBFORM]![Particular] = rs!Particular
  28. Me.[JMTPORDETAILSUBFORM]![Qty] = rs!Qty
  29. Me.[JMTPORDETAILSUBFORM]![PartNumber] = rs!PartNumber
  30. Me.[JMTPORDETAILSUBFORM]![id] = rs!id
  31. Me.[JMTPORDETAILSUBFORM]![flag] = rs!flag
  32. Me.Refresh
  33. 'DoCmd.GoToRecord , , acNewRec
  34. rs.MoveNext
  35. Loop
  36. rs.Close
  37. Else
  38. Company = vbNullString
  39. Address = vbNullString
  40. Contactperson = vbNullString
  41. Designation = vbNullString
  42. Telno = vbNullString
  43. Faxno = vbNullString
  44. End If
  45. 'End If
  46. End Sub
  47.  
And this is the Code for Saving Value to Table
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command23_Click()
  2. Dim rs As New ADODB.Recordset
  3. Dim cnn As New ADODB.Connection
  4. Dim i As Integer
  5. Dim sSql As String
  6. Dim ssql1 As String
  7. Dim rs1 As New ADODB.Recordset
  8.  
  9. Set cnn = CurrentProject.Connection
  10. sSql = "select * from [POR_Table] where id =" & List20.Value
  11. rs.Open sSql, cnn, adOpenKeyset, adLockOptimistic
  12. If rs.RecordCount > 0 Then
  13. rs!Company = Company
  14. rs!Address = Address
  15. rs!Contactperson = Contactperson
  16. rs!Designation = Designation
  17. rs!Telno = Telno
  18. rs!Faxno = Faxno
  19. rs.Update
  20. rs.Close
  21.  
  22. ssql1 = "SELECT * FROM [DR_Detail_Table] WHERE ID=" & List20.Value
  23. rs.Open ssql1, cnn, adOpenKeyset, adLockOptimistic
  24. 'If rs.RecordCount > 0 Then
  25. Me.[JMTPORDETAILSUBFORM].SetFocus
  26. Do Until rs.EOF
  27. rs!Particular = Me.[JMTPORDETAILSUBFORM]![Particular]
  28. rs!Qty = Me.[JMTPORDETAILSUBFORM]![Qty]
  29. rs!PartNumber = Me.[JMTPORDETAILSUBFORM]![PartNumber]
  30. 'DoCmd.GoToRecord , , acNewRec
  31.  
  32. rs.Update
  33. rs.MoveNext
  34. Loop
  35.  
  36. Else
  37. Company = vbNullString
  38. Address = vbNullString
  39. Contactperson = vbNullString
  40. Designation = vbNullString
  41. Telno = vbNullString
  42. Faxno = vbNullString
  43. End If
  44. End Sub 
  45.  
Thanks for advance hope its kinda clear
Mar 20 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdtest_Click()
  3. Dim rs As New ADODB.Recordset
  4. Dim cnn As New ADODB.Connection
  5. Dim valSelect As Variant
  6. Dim i As Integer
  7. Dim sSql As String
  8.  
  9. Set cnn = CurrentProject.Connection
  10. For Each valSelect In Me!List20.ItemsSelected
  11.    sSql = "select * from [DR_Table] where id =" & Me!List20.ItemData(valSelect)
  12.    rs.Open sSql, cnn, adOpenKeyset, adLockOptimistic
  13.    If rs.RecordCount > 0 Then
  14.       Me!Company = rs!Company
  15.       Me!Address = rs!Address
  16.       Me!Contactperson = rs!Contactperson
  17.       Me!Designation = rs!Designation
  18.       Me!Telno = rs!Telno
  19.       Me!Faxno = rs!Faxno
  20.    End If
  21.    rs.Close
  22.    Set rs = Nothing
  23.  
  24.    ssql = "SELECT * FROM [Dr_Detail_Table] WHERE ID=" & Me!List20.ItemData(valSelect)
  25.    rs.Open ssql, cnn, adOpenKeyset, adLockOptimistic
  26.    rs.MoveLast
  27.    rs.MoveFirst
  28.    If rs.RecordCount > 0 Then
  29.       Me![JMTPORDETAILSUBFORM].SetFocus
  30.       Do Until rs.EOF
  31.          Me![JMTPORDETAILSUBFORM]![Particular] = rs!Particular
  32.          Me![JMTPORDETAILSUBFORM]![Qty] = rs!Qty
  33.          Me![JMTPORDETAILSUBFORM]![PartNumber] = rs!PartNumber
  34.          Me![JMTPORDETAILSUBFORM]![id] = rs!id
  35.          Me![JMTPORDETAILSUBFORM]![flag] = rs!flag
  36.          Me.Refresh
  37.          DoCmd.GoToRecord , , acNewRec
  38.          rs.MoveNext
  39.       Loop
  40.       rs.Close
  41.       Set rs = Nothing
  42.    Else
  43.       Me! Company = vbNullString
  44.       Me! Address = vbNullString
  45.       Me! Contactperson = vbNullString
  46.       Me! Designation = vbNullString
  47.       Me! Telno = vbNullString
  48.       Me! Faxno = vbNullString
  49.    End If
  50. Next valSelect
  51.  
  52. End Sub
  53.  
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
Mar 22 '07 #3

P: 54
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
Mar 22 '07 #4

P: 54
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
Mar 22 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Try substituting this code ...

Expand|Select|Wrap|Line Numbers
  1. If rs.RecordCount > 0 Then
  2.       Me![JMTPORDETAILSUBFORM].SetFocus
  3.       Do Until rs.EOF
  4.          Me![JMTPORDETAILSUBFORM].Form![Particular] = rs!Particular
  5.          Me![JMTPORDETAILSUBFORM].Form![Qty] = rs!Qty
  6.          Me![JMTPORDETAILSUBFORM].Form![PartNumber] = rs!PartNumber
  7.          Me![JMTPORDETAILSUBFORM].Form![id] = rs!id
  8.          Me![JMTPORDETAILSUBFORM].Form![flag] = rs!flag
  9.          DoCmd.GoToRecord , , acNext
  10.          rs.MoveNext
  11.       Loop
  12.       rs.Close
  13.       Set rs = Nothing
  14.    Else
  15.  
Mar 22 '07 #6

P: 54
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
Mar 22 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Expand|Select|Wrap|Line Numbers
  1. DoCmd.GoToRecord , , acNext
Mar 22 '07 #8

P: 54
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
Mar 22 '07 #9

P: 54
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
Mar 22 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Run this query substituting a valid ID for 1.
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM [Dr_Detail_Table] WHERE ID=1
What results are you getting?
Mar 22 '07 #11

P: 54
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
Mar 22 '07 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
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?
Mar 22 '07 #13

P: 54
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)
Mar 22 '07 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Mar 22 '07 #15

P: 54
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?
Mar 22 '07 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Mar 22 '07 #17

P: 54
Only the Value in the Text Box will appear, but no records appearing in the subform : (
Mar 22 '07 #18

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Me![JMTPORDETAILSUBFORM].Requery
Mar 22 '07 #19

P: 54
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
Mar 22 '07 #20

Post your reply

Sign in to post your reply or Sign up for a free account.