Getting the data from a Db using selected values in a listbox

[metalheadstorm]

OK i have two listboxes that are populated by data from a Access Db, its populated by a if statement sayin if the data is the same as todays date put it in list box1 and if its tomorrows date but it in listbox 2.

Private Sub orderstatus()
'-------
imputdate = Text30.Text
customDate = DateValue(imput date)
'--------
Dim sql As String

sql = "Select * from Orders"

With Data1
.RecordSource = sql
.Refresh
.Recordset.Move First
Do Until .Recordset.EOF
On Error Resume Next
If .Recordset.Fiel ds(7) = customDate Then
List4(1).AddIte m .Recordset("Ord er_ID")

ElseIf .Recordset.Fiel ds(7) = customDate + 1 Then
List4(0).AddIte m .Recordset("Ord er_ID")
End If

.Recordset.Move Next
Loop
End With
End Sub


The listboxes display an order_ID
that works fine, the problem im having is that i want to click on a order_Id in either of the list boxes and the data for that order id to populate a numer of text boxes,
this is kinda what i got so far

Private Sub Command25_Click ()
rs.MoveFirst
Do Until rs.EOF
If rs.Fields(1) = List4(1). Then

Text29.Text = rs.Fields(0)
Text1.Text = rs.Fields(1)
Text2.Text = rs.Fields(2)
Text3.Text = rs.Fields(3)
Text4.Text = rs.Fields(4)
Text5.Text = rs.Fields(5)
Text6.Text = rs.Fields(6)
Text7.Text = rs.Fields(9)
Else: rs.MoveNext
End If
Loop

Im sure there should be something after the list4(1). but ive been looking though the net and i cant find anything :((

I hope this make sence :D
and i hope you can help !

Thanks a lot

Stu

P.s im using Vb6

[hariharanmca]

Explain your problem detail with, what you need? and can you explain why you are using 30 text boxes?

OK i have two listboxes that are populated by data ..........
the problem im having is that i want to click on a order_Id in either of the list boxes and the data for that order id to populate a numer of text boxes,
this is kinda what i got so far

In Op: then you have to use Control array.

[metalheadstorm]

well when i click a cmd button it looks at the record collection_Date in the form "orders" if that date is equal to todays date then it displays the Order_ID in one of the list boxes, and if the Collection_Date is + 1 Eg. tomorrow, then it puts the Order_ID of that order into the second listbox.

As i said that works fine,
what im trying to do is that either if the a order_ID is selected in either of the listboxes and then a comand button is pressed it populates a number of textboxes with the data from the form "Orders" or so that when the order id in the listboxes i highlighted / selected it populates the text boxes.


I dunno any other way of explaining it ;( sorry if this doesnt make a load of sence.

Oh ye about the text boxes :D ive done about 6 forms in vb on one SS Tab and its a lot of input boxes for creating orders / companies etc so thats why ther 30 boxes, i know i can do them in an array but i did it like this to begin with so that i can just get it to work and then when its near completion change them to an array, so its more "proffesion al" :D

[metalheadstorm]

can anyone else help ?? :D

[QVeen72]

Hi,

u r looking for something like this :

[code=vb]

Private Sub Command25_Click ()
Dim RST As RecordSet
RST = DB.OpenRecordSe t("Select * From Orders Where Ord_ID = " & List4(1).Text)
IF RST.RecordCount >0 Then
RST.MoveFirst
Text29.Text = RST.Fields(0)
Text1.Text = RST.Fields(1)
Text2.Text = RST.Fields(2)
Text3.Text = RST.Fields(3)
Text4.Text = RST.Fields(4)
Text5.Text = RST.Fields(5)
Text6.Text = RST.Fields(6)
Text7.Text = RST.Fields(9)
End If
End Sub
[/code]

Note few things:
Rename text Boxes with relevant names .. say:
txtName, txtOrdId, txtOrdDate....
and use Field Names for RS say : RS.Fields("Orde r_ID") RS.Fields("OrdD ate")

and also ListBox Names.. this will avoid confusion..

REgards
Veena

[metalheadstorm]

thanks a lot Veena for your reply

i tried out your code u gave me and it didnt work, but not to worry i edited a tad and came up with:

Private Sub Command25_Click ()
Dim rst As Recordset
Dim rstdb As Database

set rstdb = OpenDatabase(Ap p.Path + "/cjmillers.mdb")

Set rst = rstdb.OpenRecor dset("Select * From Orders Where Order_ID = " & List4(1).Text)
If rst.RecordCount > 0 Then
rst.MoveFirst
txtorder(0).Tex t = rst.Fields(0)
txtorder(1).Tex t = rst.Fields(1)
txtorder(2).Tex t = rst.Fields(2)
txtorder(3).Tex t = rst.Fields(3)
txtorder(4).Tex t = rst.Fields(4)
txtorder(5).Tex t = rst.Fields(5)
txtorder(6).Tex t = rst.Fields(6)
txtorder(7).Tex t = rst.Fields(7)
End If
End Sub

this works for list4(1) as it says in the code, now the finaly step is to get this for work for list4(0) as well... well i will be changing them to listordertoday which is list4(1) and listordertomorr ow which is list4(0)

i dont know if u can have both the list4(1) and list4(0) in one command button ?

If you could please comment ^^

Thanks once again, You've been a great help!

P.s i changed the text boxes to Txtorder as you can see, as its on the order tab of the SStab but that was before i read your post so ill probally follow your advise and call it something like txtOrderOrdName , txtOrderOrdId etc

txt being what it is Order being what tab its on OrdName being the field name in the DB

[QVeen72]

Hi,

U can keep one Form Level Variable and Say MyOrderID.. and Whenevr user Click's any List Item, In ListBox's Click Event Populate this Variable. In Command Click Event 's SQL Statement Instead of using List4(1).Text, use MyOrderID..
I hope it is clear..


Regards
Veena

[metalheadstorm]

Ye i got what you meen although i dont know the function of the List4(1). to use, i get what u meen so in

Private Sub List4_Click(Ind ex As Integer)

MyOrderId = list4(1).......


then have

Set rst = rstdb.OpenRecor dset("Select * From Orders Where Order_ID = " & MyOrderID)

[metalheadstorm]

Can anyone help ?? :(

[QVeen72]

Hi,
It shud be:


Private Sub List4_Click(Ind ex As Integer)
MyOrderId = list4(Index).Te xt
End Sub

Regards
Veena

[metalheadstorm]

hmm ok i got

Private Sub List4_Click(Index As Integer)
OrderstatusId = List4(Index).Text

End Sub

and

Private Sub Command25_Click()
    Dim rst As Recordset
    Dim rstdb As Database
      
    
    Set rstdb = OpenDatabase(App.Path + "/cjmillers.mdb")
    
    Set rst = rstdb.OpenRecordset("Select * From Orders Where Order_ID = " & OrderstatusId)
 
    If rst.RecordCount > 0 Then
    rst.MoveFirst
    txtorder(0).Text = rst.Fields(0)
    txtorder(1).Text = rst.Fields(1)
    txtorder(2).Text = rst.Fields(2)
    txtorder(3).Text = rst.Fields(3)
    txtorder(4).Text = rst.Fields(4)
    txtorder(5).Text = rst.Fields(5)
    txtorder(6).Text = rst.Fields(6)
    txtorder(7).Text = rst.Fields(7)
 End If
End Sub

but it comes up with an error on the line

Set rst = rstdb.OpenRecordset("Select * From Orders Where Order_ID = " & OrderstatusId)

and says OrderstatusId ="empty"

run-time error 3075
syntax error (missing operator) in query expression 'Order_ID ='.


P.s how do u make the code look colourful and pritty :D