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

Populating ListBox in Access

P: 49
Hello All,

I currently have an order form, when a record is selected and saved this should appear within the listbox below. i could get this to add but at the same time the order that i have placed from the previous order would display within the same listbox. when i only want the record from the current order that is being placed
This is what i am using...
Expand|Select|Wrap|Line Numbers
  1. Private Sub populateListbox()
  2.  Dim strSQL As String
  3.  Dim strcustomername As String
  4.  Dim rstLoans As DAO.Recordset
  5.  strcustomername = txtorderno.Value
  6.  
  7.      Set rstLoans = dbase.OpenRecordset("tblorder", dbOpenDynaset)
  8.     lstOrderDetails.RowSource = ""
  9.  
  10. strSQL = "SELECTtblorder.orderno,tblorder.ProductID1,tblorder. [Clothingtype1]"     
  11. strSQL = strSQL & "FROM tblorder;"
  12.  
  13.  
  14.  
  15.     lstOrderDetails.RowSource = strSQL
  16. End Sub
This is the code that i am currently using. i am stuck plz halp!!!!
Jan 26 '08 #1
Share this Question
Share on Google+
11 Replies


ADezii
Expert 5K+
P: 8,679
Hello All,

I currently have an order form, when a record is selected and saved this should appear within the listbox below. i could get this to add but at the same time the order that i have placed from the previous order would display within the same listbox. when i only want the record from the current order that is being placed
This is what i am using...

Private Sub populateListbox()
Dim strSQL As String
Dim strcustomername As String
Dim rstLoans As DAO.Recordset
strcustomername = txtorderno.Value

Set rstLoans = dbase.OpenRecordset("tblorder", dbOpenDynaset)
lstOrderDetails.RowSource = ""

strSQL = "SELECTtblorder.orderno,tblorder.ProductID1,tblord er. [Clothingtype1]"
strSQL = strSQL & "FROM tblorder;"



lstOrderDetails.RowSource = strSQL
End Sub

This is the code that i am currently using. i am stuck plz halp!!!!
'I don't know the specifics, so I can just generalize:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT tblorder.orderno, tblorder.ProductID1, tblorder.[Clothingtype1] FROM tblorder WHERE tblorder.[OrderID] ="  & Me![txtOrderID]
Jan 26 '08 #2

P: 49
'I don't know the specifics, so I can just generalize:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT tblorder.orderno, tblorder.ProductID1, tblorder.[Clothingtype1] FROM tblorder WHERE tblorder.[OrderID] ="  & Me![txtOrderID]
im still having problems, this is what i entered within the populate box
Expand|Select|Wrap|Line Numbers
  1. Private Sub populateListbox()
  2.  Dim strSQL As String
  3.  Dim strcustomername As String
  4.  Dim rstLoans As DAO.Recordset
  5.  'strcustomername = txtorderno.Value
  6.  
  7.      Set rstLoans = dbase.OpenRecordset("tblorder", dbOpenDynaset)
  8.     lstOrderDetails.RowSource = ""
  9.  
  10.  
  11.  strSQL = "SELECT tblorder.orderno,tblorder.ProductID1,tblorder.[Clothingtype1]"
  12.  strSQL = strSQL & "FROM tblorder"
  13.  strSQL = strSQL & "WHERE tblorder.[Orderno] =" & Me![txtorderno]
  14.     lstOrderDetails.RowSource = strSQL
  15. End Sub
It allows the records to be saved but does not display anything within the listbox, if i was to take the where part of the statement out then it displays but with everyone records, hope to hear from you soon
Jan 26 '08 #3

ADezii
Expert 5K+
P: 8,679
im still having problems, this is what i entered within the populate box
Expand|Select|Wrap|Line Numbers
  1. Private Sub populateListbox()
  2.  Dim strSQL As String
  3.  Dim strcustomername As String
  4.  Dim rstLoans As DAO.Recordset
  5.  'strcustomername = txtorderno.Value
  6.  
  7.      Set rstLoans = dbase.OpenRecordset("tblorder", dbOpenDynaset)
  8.     lstOrderDetails.RowSource = ""
  9.  
  10.  
  11.  strSQL = "SELECT tblorder.orderno,tblorder.ProductID1,tblorder.[Clothingtype1]"
  12.  strSQL = strSQL & "FROM tblorder"
  13.  strSQL = strSQL & "WHERE tblorder.[Orderno] =" & Me![txtorderno]
  14.     lstOrderDetails.RowSource = strSQL
  15. End Sub
It allows the records to be saved but does not display anything within the listbox, if i was to take the where part of the statement out then it displays but with everyone records, hope to hear from you soon
Is OrderID Numeric or Text?
Jan 27 '08 #4

P: 49
Is OrderID Numeric or Text?
It is currently set to Text, I have tried it now with it changed to number but still nothing is being displayed within the listbox,,
Jan 27 '08 #5

P: 14
It is currently set to Text, I have tried it now with it changed to number but still nothing is being displayed within the listbox,,
Try changing the Me![txtOrderNo] to forms![frmXXXX]![txtOrderNo]. Me dosn't work in queries.
Jan 27 '08 #6

P: 49
Try changing the Me![txtOrderNo] to forms![frmXXXX]![txtOrderNo]. Me dosn't work in queries.
still no luck, there must be something else that i am doing wrong and i just dont have a clue!!!!!!!
Jan 27 '08 #7

ADezii
Expert 5K+
P: 8,679
still no luck, there must be something else that i am doing wrong and i just dont have a clue!!!!!!!
If [OrderID] is set to Text, then:
Private Sub populateListbox()
Dim strSQL As String
Dim strcustomername As String
Dim rstLoans As DAO.Recordset
'strcustomername = txtorderno.Value

Set rstLoans = dbase.OpenRecordset("tblorder", dbOpenDynaset)
lstOrderDetails.RowSource = ""


strSQL = "SELECT tblorder.orderno,tblorder.ProductID1,tblorder.[Clothingtype1]"
strSQL = strSQL & "FROM tblorder"
strSQL = strSQL & "WHERE tblorder.[Orderno] =" & Me![txtorderno]
lstOrderDetails.RowSource = strSQL
End Sub

I think that it is simply a matter of Syntax, Copy and Paste the appropriate lines of code below,
substituting your Form Name for <Your Form Name>:
Expand|Select|Wrap|Line Numbers
  1. 'If OrderNo is Text
  2.   strSQL = "SELECT tblOrder.OrderNo, tblOrder.ProductID1, tblOrder.[Clothingtype1] "
  3.   strSQL = strSQL & "FROM tblOrder WHERE tblOrder.[OrderNo] = '" & Forms![<Your Form Name>]![txtOrderNo] & "';"
  4.  
  5.  'If OrderNo is Numeric
  6.   strSQL = "SELECT tblOrder.OrderNo, tblOrder.ProductID1, tblOrder.[Clothingtype1] "
  7.   strSQL = strSQL & "FROM tblOrder WHERE tblOrder.[OrderNo] = " & Forms![<Your Form Name>]![txtOrderNo] & ";"
  8.  
  9.     Me![lstOrderDetails].RowSource = strSQL
Jan 28 '08 #8

P: 49
If [OrderID] is set to Text, then:
Private Sub populateListbox()
Dim strSQL As String
Dim strcustomername As String
Dim rstLoans As DAO.Recordset
'strcustomername = txtorderno.Value

Set rstLoans = dbase.OpenRecordset("tblorder", dbOpenDynaset)
lstOrderDetails.RowSource = ""


strSQL = "SELECT tblorder.orderno,tblorder.ProductID1,tblorder.[Clothingtype1]"
strSQL = strSQL & "FROM tblorder"
strSQL = strSQL & "WHERE tblorder.[Orderno] =" & Me![txtorderno]
lstOrderDetails.RowSource = strSQL
End Sub

I think that it is simply a matter of Syntax, Copy and Paste the appropriate lines of code below,
substituting your Form Name for <Your Form Name>:
Expand|Select|Wrap|Line Numbers
  1. 'If OrderNo is Text
  2.   strSQL = "SELECT tblOrder.OrderNo, tblOrder.ProductID1, tblOrder.[Clothingtype1] "
  3.   strSQL = strSQL & "FROM tblOrder WHERE tblOrder.[OrderNo] = '" & Forms![<Your Form Name>]![txtOrderNo] & "';"
  4.  
  5.  'If OrderNo is Numeric
  6.   strSQL = "SELECT tblOrder.OrderNo, tblOrder.ProductID1, tblOrder.[Clothingtype1] "
  7.   strSQL = strSQL & "FROM tblOrder WHERE tblOrder.[OrderNo] = " & Forms![<Your Form Name>]![txtOrderNo] & ";"
  8.  
  9.     Me![lstOrderDetails].RowSource = strSQL
THANKS MATE!!!!! you dont know how much i appreaciate this!!!!!
youve made my day, i was pulling my hairs out on this one!!!

Thanks again
Jan 28 '08 #9

P: 49
THANKS MATE!!!!! you dont know how much i appreaciate this!!!!!
youve made my day, i was pulling my hairs out on this one!!!

Thanks again
you sort one and then another ten questions pop out.

within that code you sent me, i will have be displaying product total. so each prodcut that is added, within the listbox will be displayed the total for that item ,

for example quantity= 10 * 19.99each the total 199.99 for each line will be displayed, i could get this to work so it adds the the total for each line to the listbox.
But now i want to add the total of each line in the listbox and display it in a text box which i have called = txttotalcost

thanks again
Jan 28 '08 #10

ADezii
Expert 5K+
P: 8,679
you sort one and then another ten questions pop out.

within that code you sent me, i will have be displaying product total. so each prodcut that is added, within the listbox will be displayed the total for that item ,

for example quantity= 10 * 19.99each the total 199.99 for each line will be displayed, i could get this to work so it adds the the total for each line to the listbox.
But now i want to add the total of each line in the listbox and display it in a text box which i have called = txttotalcost

thanks again
I must admit that I am confused as to exactly whay youe are asking. Please be more specific, and Post some actual data and the results you would like to achieve.
Jan 28 '08 #11

P: 49
I must admit that I am confused as to exactly whay youe are asking. Please be more specific, and Post some actual data and the results you would like to achieve.
When a customer selects an item , say P00001, this would bring up the record for that item and then the customer selects quantity required this would multilpy the unit price by the the quantity to give a total. When the add button is pressed this would save the item within the listbox.

What i would like to know is say when a customer selects 5 items, this would display the total cost for each product within the listbox. i want to add all the totals up within the listbox and display the total within a seperate text box.
for example:

product ID, Name, quantiy, item price, totalcost
P0001 Top 5 1.00 5.00
P0002 Top 4 2.00 8.00
P0003 Top 3 1.00 3.00


i then have a text box where i want to display the total for these items within. hope this helps. im really stuck on this one!!!

hope this helps!
Jan 28 '08 #12

Post your reply

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