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?
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?
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
"Knowledge is gained when different people look at the same information in different ways"
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 & " ';"
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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.
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:
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! ) cheers
Have fun!!!
"Knowledge is gained when different people look at the same information in different ways"
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.
"Knowledge is gained when different people look at the same information in different ways"
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.
"Knowledge is gained when different people look at the same information in different ways"
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
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.
"Knowledge is gained when different people look at the same information in different ways"
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.
"Knowledge is gained when different people look at the same information in different ways"
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.
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!!
"Knowledge is gained when different people look at the same information in different ways"
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
"Knowledge is gained when different people look at the same information in different ways"
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 )
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!!!)
"Knowledge is gained when different people look at the same information in different ways"
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.
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!!
"Knowledge is gained when different people look at the same information in different ways"
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
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.
"Knowledge is gained when different people look at the same information in different ways"
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
Reply With Quote
) cheers
