what is the best way?

i have a database and in this database there are different departments. each department has different machines in it, is there a way to show just the department numbers in a VB6 app?

Yes, but we will need more info on your tables and the expected returned data.

ok each department has a different number of machines in it. each machine has its own number. but they all have to have a department. and i was asked to make it where you can just click on a certain department and veiw all the machines in that department. then be able to click on any of the machines found there. does that help out?

i have a table for machines that has the number of the machine the department, and a description of the machine.

you could first populate a combo box with all the departments:

"select distinct department from tblMachines"

Then code the click event of the combo box to call a procedure that takes the department from the combo box as a paremeter and that would run this sql:

"select machine, machineDescription from tblMachines where department='" & paremeter & "'"

then populate your favorite container with that sql (listbox, listview, etc)...if you need actual code, just let us know. cheers

Is this going to be an Access Form solution?
If so you could populate a combo box with the distinct names of
the departments from the departments table. Then add a button
the execute a query based on the selected combo box item.
Query to populate the combo box with the departments.

VB Code:

"SELECT DISTINCT DeptName FROM Departments ORDER BY DeptName ASC;"

Query to retrieve all machines in the selected department.

VB Code:

SELECT * FROM Machines WHERE DeptName = ' " & cboDept.Text & " ';"

the departments are not named they are numbered. i mean they have names but in the DB they are id'd by department number. one more question though, if i use the select statement as shown, won't that give me all the entries. like instead of just showing the 11 departments, won't it give me all the entries in the machine table.

no, because of the 'Distinct' clause - no duplicates will be shown

ok thanx i see what that means not but i'm not sure how to put that into a list either, sorry i'm terrible at VB. and i am shooting from the hip here, thanx for all your help people.

VB Code:

lstDept.Additem "SELECT DISTINCT DeptName FROM Departments ORDER BY DeptName ASC;"

the following example uses a combo box combined with a listbox to display the machines

Code:

---

dim rs as New ADODB.Recordset
dim cmdCommand as New ADODB.Command
dim sql as String

set cmdCommand.ActiveConnection = DBConnection 'valid connection object
cmdCommand.CommandType = adCmdText

sql = "select DISTINCT deptName, deptID from tblDepartments"
cmdCommand.CommandText = sql

set rs = cmdCommand.Execute

do while not rs.EOF
    cmbDept.AddItem rs.Fields("deptName").Value
    cmbDept.ItemData(cmbDept.NewIndex) = rs.Fields("deptID").Value
    rs.MoveNext
loop

---

then, you could put this code in the cmbDept_Click event:

Code:

---


call showMachineList(cmbDept.ItemData(cmbDept.ListIndex))

---

and then the code for the procedure showMachineList could be:

Code:

---


private sub showMachineList(deptID as Integer)

dim rs as New ADODB.Recordset
dim cmdCommand as New ADODB.Command
dim sql as String

set cmdCommand.ActiveConnection = DBConnection
cmdCommand.CommandType = adCmdText

sql = "select * from tblMachines where deptID = " & deptID
cmdCommand.CommandText = sql

set rs = cmdCommand.Execute

do while not rs.EOF
    lstMachine.AddItem rs.Fields("machine_name").Value & " - " & rs.Fields("machine_desc").Value
    rs.MoveNext
loop

---

so the above takes the dept names and id's and populates the combo box. Notice that I used the ItemData property of the combo box to store the ID. This is typical as users don't need to see the ID, but rather the name - and YOU need the id so you can query the DB. Hope it is not too confusing (and I just hammered it out without testing it - so there may be syntax flaws - but hey - ya gotta do some of the work!:bigyello: ) cheers

Have fun!!!

thank you so much, i will try it out and let you know if there are errors, just one more question, i already have a record set open for the same form from the same table, so could i just use that recordset?

I suppose you could use the same recordset as long as you move to the first record first (rs.MoveFirst). In terms of design however, I find it preferable to use variables private to each sub. I did not include in the code:

all objects should be set to nothing after they are used, ie.

set cmdCommand = Nothing

And if you follow this practice, your app's design will have greater cohesion and will be easier to debug.

ok then thanx i'll give it a shot. in my table tho, there is just numbers, the names of the departments aren't given any where in that table. so in light of that is it possible to change the data types?

if you only have access to the ID field, then yeah you can use that - no need to use ItemData, you can just assign the number to the list in the combo - I guess I misunderstood and thought you needed both. If the name is in another table, you could probably join the two if you wanted to show both.

thanx for all your help, i just have one more question and i think that i can get it. the active connection part. if i have already opened the a connection to the data base do i have to type

VB Code:

set cmdCommand.ActiveConnection = DBConnection 'valid connection object
cmdCommand.CommandType = adCmdText

Yes you do - you need to assign the connection object to the command object's ActiveConnection property...If you think about it this makes a lot of sense....you pass a command to the command object, but it needs to know which database to execute the command against. Typically, you declare your connection object as Public in a module. This way you can keep reusing this object throughout your app and it only needs to be opened once at startup, and then closed upon unload and end.

i do have a connection in the module which was why i was asking. i know that it will cause problems if u already have it open and try and open it again. i kinda understand what you mean about the activeconnection property, but if it is possible could you explain it differently. the way that i kinda understand it now is that even tho i have already opened the connection to the DB in my module i still have to set my active connection? am i way off?

yes you still have to set the ActiveConnection which is a property of the command object. When you do this, you are not actually opening a connection, but simply creating a reference to a connection object that is already open. Hope that clarifies.

ok thanx a bunch, i'll give it all a shot (prolly after lunch) and let you know how it turned out.

i took all that code and changed it accordingly and i got an error that says

invalid property array index.

can any one help me out on this?

if you post your code, I'd be happy to look at it
:D

here it is, this part works, i can see all the department names in the combo box.

VB Code:

Private Sub Command1_Click()
Dim rs As New ADODB.Recordset
Dim cmdCommand As New ADODB.Command
Dim sql As String
 
Set cmdCommand.ActiveConnection = DBConn 'valid connection object
cmdCommand.CommandType = adCmdText
 
sql = "select DISTINCT DeptName, DeptID from Departments"
cmdCommand.CommandText = sql
 
Set rs = cmdCommand.Execute
 
Do While Not rs.EOF
    cmbTest.AddItem rs.Fields("deptName").Value
    cmbTest.ItemData(cmbTest.NewIndex) = rs.Fields("deptID").Value
    rs.MoveNext
Loop
 
End Sub

this part doesn't work.

VB Code:

Private Sub Command2_Click()
Call showList(cmbTest.ItemData(cmbTest.ListIndex))
End Sub

and this is the sub that it calls

VB Code:

Private Sub showList(deptID As Integer)
 
Dim Trs As New ADODB.Recordset
Dim cmdCommand As New ADODB.Command
Dim sql As String
 
Set cmdCommand.ActiveConnection = DBConnection
cmdCommand.CommandType = adCmdText
 
sql = "select * from tblMachines where deptID = " & deptID
cmdCommand.CommandText = sql
 
Set rs = cmdCommand.Execute
 
Do While Not Trs.EOF
    lstTester.AddItem Trs.Fields("machine_name").Value & " - " & rs.Fields("machine_desc").Value
    rs.MoveNext
Loop
End Sub

Okay....just give me a bit of time and I will see what is what - I should be able to post back in about 30 - 45 minutes.

i have been testing it out and i found some errors and i figure out if i click on something in the combo box and then try and call the function i get an error that says i'm not passing the required parameters. but if i don't click on it and try to call the function i get the invalid array property error.

ok that sounds like a plan. thanx very much for all your help.

Well...it looks like there is a mystery on your hands as I did the following:

I quickly created two tables defined as yours are

I pasted the code into a project

It ran without error and did what it was supposed to do


There must be something else somewhere in your code. If you wish, you may post the project if it is possible. Sorry I don't have an immediate answer for you - but for me it does not crash!!:confused:

ok, here is my code.

VB Code:

Private Sub Form_Load()
Call Connect
Dim rs As New ADODB.Recordset
Dim cmdCommand As New ADODB.Command
Dim sql As String
 
Set cmdCommand.ActiveConnection = DBConn 'valid connection object
cmdCommand.CommandType = adCmdText
 
sql = "select DISTINCT DeptName, DeptID from Departments"
cmdCommand.CommandText = sql
 
Set rs = cmdCommand.Execute
 
Do While Not rs.EOF
    cmbTest.AddItem rs.Fields("deptName").Value
    cmbTest.ItemData(cmbTest.NewIndex) = rs.Fields("deptID").Value
    rs.MoveNext
Loop
End Sub
 
Private Sub Command2_Click()
Call showList(cmbTest.ItemData(cmbTest.ListIndex))
End Sub
 
Private Sub showList(deptID As Integer)
 
Dim Trs As New ADODB.Recordset
Dim cmdCommand As New ADODB.Command
Dim sql As String
 
Set cmdCommand.ActiveConnection = DBConn
cmdCommand.CommandType = adCmdText
 
sql = "select * from tblMachines where deptID = " & deptID
cmdCommand.CommandText = sql
 
Set Trs = cmdCommand.Execute
 
Do While Not Trs.EOF
    lstTester.AddItem Trs.Fields("machine_name").Value & " - " & Trs.Fields("machine_desc").Value
    rs.MoveNext
Loop
End Sub

sorry...I meant post the entire project (zipped up)

o ok then. i've never done this before so if you get it let me know ok.

I got it and I see the problem right away....

Code:

---


Private Sub Command2_Click()
    Call showList(cmbTest.ItemData(cmbTest.ListIndex))
End Sub

---

you need the above code in the click event of the combo box:

Code:

---


Private Sub cmbTest_Click()
    Call showList(cmbTest.ItemData(cmbTest.ListIndex))
End Sub

---

the idea behind this is to capture the click event of the combo box which will fire when a user selects an item from its list. Now I couldn't run anything, so if there is still a problem, upload db1.mdb, and db2.mdb so I can run it

actually db1 is not needed i got rid of it, so here is db2, i still get the same error about the parameters not being passed. i do however see about the click event tho, that makes complete sense and now i feel like a ding dong for not looking @ that way.

alrighty then!!! now we are in business (your gonna kick yourself:bigyello: )

Code:

---


Private Sub showList(deptID As Integer)

Dim Trs As New ADODB.Recordset
Dim cmdCommand As New ADODB.Command
Dim sql As String

Set cmdCommand.ActiveConnection = DBConn
cmdCommand.CommandType = adCmdText

sql = "select * from tblMachines where deptID = " & deptID
cmdCommand.CommandText = sql

Set Trs = cmdCommand.Execute

Do While Not Trs.EOF
    lstTester.AddItem Trs.Fields("machine_name").Value & " - " & Trs.Fields("machine_desc").Value
    Trs.MoveNext
Loop
End Sub

---

your sql statement above asks for deptid's from the tblMachines table but you misspelled the field name:

it is not deptID

you have it in the db as dept

After I made the change, there was no error


also: you will want to add some logic: The listbox continues to grow each time you select an item from the combo - you will want to add a line at the beginning of the sub that populates the listbox that will clear the list first.

that otta do it (I HOPE!!!)

um...........there must be something wrong with me bc i still get the same error. could you post all the code? i don't understand why i still keep getting the same error no matter what i change, there has to be some thing wrong with my sub call or some thing.

never mind about that last post i fixed it. thanx so very much. that helps alot.as far as logic i'll do that later as i have to leave but i'll prolly need help on that too. you've helped out so much, you're the man.....or the woman.

no problem chief

Dave Ahara

:wave:

ok now i am going to try to add 2 things to this project. i want to add the logic like you said, i also want to add a small portion that will make it possible to click on the results in the list box and show the info for that specific record. for example if we have a screwdriver that shows up in the list box i want to be able to click on screw driver and then it will load the record for that screw driver. is this possible?

First off, I am a firm believer that anything is possible - it just depends on how much time you have!!! Question: Let's say a user clicks on an item in the list box that shows a screw driver....how are you able to identify the screwdriver's matching record in the database? Which table is it in? Is there an ID field that uniquely identifies it and if so, what is it called? This is what you first need to ask yourself. Assuming there is a way to identify items in the listbox, it should be no problem to display the corresponding data. Once you figure out how to identify them, you need a way to get that value when the user clicks on the item. Go back to the combo box and look at how you used the ItemData property to store dept id's. The listbox also supports this property. So when you generate a list in the listbox - you could add additional info such as machinePart ID or whatever it is that you can use to identify the listbox items in the database (you could modify the showList sub). Then, when a user clicks on a listbox item, you call a sub similar to showList, and you show the detail of that record. The only remaining question is what are you going to use to show the detail? You could use textboxes, labels (my old school teaches students to use the picture box - don't ask me why but it does allow you to 'print' data to it).....so hopefully that's enough to get you going....have fun!!

yeah that is eough to get me going. thanx for all the help so far. i just have one more question, i am working on the logic part andi think that i know how to do it, i just need some one to help me out as far a the

VB Code:

lstbox.ItemData(n)=m

i saw this in my book and i think that i could use this for the logic part and i need to know about the n and the m. thanx

just like with the part you did with the combo box, n is the index of the item in the ItemData collection (m is the value you are adding to it), so if you are populating a listbox, you could use

Code:

---

List1.AddItem "[new value]"
List1.ItemData(List1.NewIndex) = "[id]"

---

the NewIndex property returns the index of an item that has just been added - so in the above example, the new value id will be in the ItemData collection at the same index as the new value that was just added - hence they will be linked by indexes. Its just like what you already did with the combo box.

so i couldn't use that for logic then huh, that seems to be the wrong thing for logic. i think that i am going to use and if statement and basically say this

Code:

---

if the list box is empty then
Do while not trs.eof
code code
loop
else
clear the list box then
do while not trs.eof
code code
loop
end if

---

is that too redundant?