Populating ListBox in Access

[goldstar]

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,tblorder. [Clothingtype1]"     
strSQL = strSQL & "FROM tblorder;"

   
    
    lstOrderDetails.RowSource = strSQL
End Sub

This is the code that i am currently using. i am stuck plz halp!!!!

[ADezii]

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

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

strSQL = "SELECTtblorder .orderno,tblord er.ProductID1,t blorder. [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:
[CODE=sql]strSQL = "SELECT tblorder.ordern o, tblorder.Produc tID1, tblorder.[Clothingtype1] FROM tblorder WHERE tblorder.[OrderID] =" & Me![txtOrderID][/CODE]

[goldstar]

'I don't know the specifics, so I can just generalize:
[CODE=sql]strSQL = "SELECT tblorder.ordern o, tblorder.Produc tID1, tblorder.[Clothingtype1] FROM tblorder WHERE tblorder.[OrderID] =" & Me![txtOrderID][/CODE]

im still having problems, this is what i entered within the populate box

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

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

[ADezii]

im still having problems, this is what i entered within the populate box

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

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?

[goldstar]

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

[TerryDM]

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.

[goldstar]

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

[ADezii]

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
'strcustomernam e = txtorderno.Valu e

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


strSQL = "SELECT tblorder.ordern o,tblorder.Prod uctID1,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>:
[CODE=sql]
'If OrderNo is Text
strSQL = "SELECT tblOrder.OrderN o, tblOrder.Produc tID1, tblOrder.[Clothingtype1] "
strSQL = strSQL & "FROM tblOrder WHERE tblOrder.[OrderNo] = '" & Forms![<Your Form Name>]![txtOrderNo] & "';"

'If OrderNo is Numeric
strSQL = "SELECT tblOrder.OrderN o, tblOrder.Produc tID1, tblOrder.[Clothingtype1] "
strSQL = strSQL & "FROM tblOrder WHERE tblOrder.[OrderNo] = " & Forms![<Your Form Name>]![txtOrderNo] & ";"

Me![lstOrderDetails].RowSource = strSQL[/CODE]

[goldstar]

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

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


strSQL = "SELECT tblorder.ordern o,tblorder.Prod uctID1,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>:
[CODE=sql]
'If OrderNo is Text
strSQL = "SELECT tblOrder.OrderN o, tblOrder.Produc tID1, tblOrder.[Clothingtype1] "
strSQL = strSQL & "FROM tblOrder WHERE tblOrder.[OrderNo] = '" & Forms![<Your Form Name>]![txtOrderNo] & "';"

'If OrderNo is Numeric
strSQL = "SELECT tblOrder.OrderN o, tblOrder.Produc tID1, tblOrder.[Clothingtype1] "
strSQL = strSQL & "FROM tblOrder WHERE tblOrder.[OrderNo] = " & Forms![<Your Form Name>]![txtOrderNo] & ";"

Me![lstOrderDetails].RowSource = strSQL[/CODE]

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

[goldstar]

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

[ADezii]

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.

[goldstar]

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!