List Boxes

Hi Everyone!!

I was wondering if some VB/Access GOD may be able to help me?

Basically I have a database with a table called 'ARTable' and I need to take 2 colunms of the data, namley 'StaffNumber' & 'Surname' and populate a Listbox. ** I cant do this **

Secondly I need to set the Listbox to multi select and take whatever the user has selected and put it into an array (variant).
** I cant do this **

My form is called 'choice' and my list box is called 'choices'.

Im using Access 03 but im not sure what method of programming im using....ill add some code below so you can see!

I am TOTALLY stuck and help anyone may have would be greatly appreciated!

Thanks In anticipation,
Nick

-------------------------------------------------------------------------------------------------------------------------------
'Pull Data From the STAFF table

'Create another record set
Dim checkGrade As Recordset

'Create an Array Variable
Dim gradeArray As Variant

'Pull grade from the staff table to check against the required grade
Set checkGrade = CurrentDb.OpenRecordset("SELECT Grade, Trade, ShopId, HangerId FROM Staff WHERE
StaffNumber = '" & DataValue2 & "' ")

If checkGrade.RecordCount > 0 Then

'Populate the array with the grade value from the database
gradeArray = checkGrade.GetRows(10)

'Create a variable to be the arrtibute at position 0 to get the users GRADE
Dim grade As Integer
grade = gradeArray(0, 0)

'Create a variable to be the arrtibute at position 1 to get the users TRADE
Dim trade As String
trade = gradeArray(1, 0)

'Create a variable to be the arrtibute at position 2 to get the users SHOP
Dim shop As String
shop = gradeArray(2, 0)

'Create a variable to be the arrtibute at position 3 to get the users HANGER
Dim hanger As String
hanger = gradeArray(3, 0)


Else
MsgBox "Not A Valid User"
Exit Sub
End If
----------------------------------------------------------------------------------------------------------------------------

Hi balltheheed,

Here is some generic code that will satisfy the two questions you have, however substitute the items shown as bold and italic with those from your application.

1) For your list box (choices), use this for the recordsource and set the first column to "0" width and then the other two to fit your control. The first column will be the ID of your table ARTable.

SELECT ARTable.ID, ARTable.StaffNumber, ARTable.Surname FROM ARTable;

Obviously, if StaffNumber is the Primary ID, then you would use that in place of the ID and would only have two columns. The first column however, must be the bound column and the Primary Key field of your table. Set the listbox properties to multiselect Extended.

The following code will assume that the Primary Key in your table will be a number and not text.

2) Create your array from the items selected in your listbox. Place a command button on your form and add the following code to the On Click event.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Listarray As Variant, varItem As Variant
Dim strCriteria As String, strSQL As String

For Each varItem In Me!choices.ItemsSelected
strCriteria = strCriteria & "," & Me!choices.ItemData(varItem)
Next varItem

If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list"
Exit Sub
End If

strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT * FROM ARTable " & _
"WHERE ARTable.ID IN(" & strCriteria & ");"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.RecordCount > 0 Then

Listarray = rs.GetRows(10)

FieldName1 = Listarray(0, 0)
FieldName2 = Listarray(1, 0)

'And so forth for each array you want

End If

Again, if your Primary ID is StaffNumber, use that in place of the ID.

Cheers,
lwells

That make a little more sense than other explainations ive tried looking at!! Thanks!!!!

Basically im a student and Ive just had to learn VB/Access in the last 2 weeks as my groupmates who were supposed to do this have let me down. So my knowledge is basic...so without sounding like a complete idiot.....I dont know how to write code to make a listbox...or where to put the SQL code "SELECT ARTable.StaffNumber, ARTable.Surname FROM ARTable;"

Do I use access to make a list box and then set the value to be the SQL code? ie ListBoxName.value = "SELECT ARTable.StaffNumber, ARTable.Surname FROM ARTable;"???

PS my StaffNumber is the primary Key.

I created a listbox using access
I went into the row source and entered my SQL code, so I have the box populated...yey!!

I created a button and in the 'on click' ive tried putting the above code, but nothing happens
So ive chopped the code up and have made it more understandable for me, and im trying various different things but with no luck, its got to be something simple as ive got code that works...

This is my code so far....

Private Sub Command8_Click()


Dim varItem As Variant

For Each varItem In Me!choices.ItemsSelected
strCriteria = strCriteria & "," & Me!choices.ItemData(varItem)
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list"
Exit Sub
End If

Dim strCriteria As String
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

Dim Listarray As Variant
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM ARTable WHERE ARTable.StaffNumber IN(' & strCriteria & ')")
If rs.RecordCount > 0 Then
Listarray = rs.GetRows(10)
fieldName1 = Listarray(0, 0)
fieldName2 = Listarray(1, 0)

MsgBox fieldName1
MsgBox fieldName2

End If

End Sub

Perhaps you can maybe explain a little what you are wanting to do with the array. The array was created by the code...but you have to do something with it. I left the code with the array created as is, because you never said what you actually wanted to do with the array once it was created.

Just for the benefit of others, I will be more than happy to assist anyone, but it is not my intention to do someone's homework. I will tutor, but not do it for you. You won't learn anything by me doing it.

lwells

I need to extract variables that are selected from a listbox, so if 3 StaffNumbers are selected I need 3 variables. Then from that I need to do all the validation that I already have. I just needed help to populate a list box (that I have now done) and just some simple code that puts them into an array, so I can extract the variables. So if someone clicks on StaffNumber 000133 then I need that value in a variable.

Also I dont want anyone to do my work for me, I do everything myself. My problem is the timescale I have been left with....thats why I was needing some help!!

your taking each selection the user makes and put them into a string; seperated by a comma....instead of that can you put it into an array...then thats all I need!!!!

The FieldName1 and FieldName2 IS your variable.

Change the name of FieldName1 and FieldName2 and so forth, to the variable names that you have declared and use it where you need it.

You might try posting the code where these variables are to be used and what the result of that code is suppose to do. The use of an array with the GetRows method, might not be the best or the most effective method for what you are actually wanting to do. There are both good points and not so good points when using the GetRows methods. For example if you state GetRows(10) and if a record is deleted in an OpenDynaset, leaving a skip in sequence the GetRows method will stop at that end of that sequence without an error message. For the same reason, if you state GetRows(10) and the user selected let's say 12 items, only the first ten will be populated into your array.

lwells

The reason for the string separated by a comma, is to set up the criteria for the SQL for the record set "WHERE ARTable.ID IN(" & strCriteria & ");" is the same as WHERE ARTable is equal to the following 1,2,3 ID values etc based on what was selected in the list box. A list box that has been set to Multiselect Simple or Extended always has a value of 0 therefore you can't just take the value of what is selected and place them into a variable directly. You can however create a record set that is filtered by what was selected in the listbox and then take the values from the resulting recordset and place them into a variable.

Does that help clear things up a bit?

lwells

Yeah I see what you are saying....so your not using the actual input from the listBox....your storing the matching values in the table into the variables....now it makes sense! I did wonder why there were 2 arrays!!!

I think i have managed to sort the problem however. It is probably TOTALLY wrong and very bad practice, but Im not a database EXPERT like yourself. I did use your code to loop through selections made in the listBox, but I then started stripping off more than just the commas, and storing the result in a temporay table. See below


Private Sub Command9_Click()

Dim strCriteria As String
Dim varItem As Variant
Dim increment As Integer
'increment is initially set to 1 to remove the first ',' only
increment = 1

'put all choices into a string seperated by a ',' via a loop
'ie each time through the loop you put another choice in the string
For Each varItem In Me!choices.ItemsSelected
strCriteria = strCriteria & "," & Me!choices.ItemData(varItem)

'removes the ',' at the 'incremental' position of the string
'which is 1 the first time and 7 each subsequent time.
'each time it loops through it removes the previous parts of the string.
strCriteria = Right(strCriteria, Len(strCriteria) - increment)

'create a recordSet to allow you to write values to a table
Dim test2 As Recordset

'set the recordSet to open the database (Temp_1)
Set test2 = CurrentDb.OpenRecordset("Temp_1")

'add a new row to the database
test2.AddNew

'populate the StaffNumber collumn with the value from the string
test2!StaffNumber = strCriteria
'update the row in question
test2.Update

'set the increment to be 7 as after the first run through the loop as you now need
'to remove the first 7 digits of the string. Note that this is now fixed and it
'removes the ',' AND the part of the string you have just saved to the database
increment = 7

Next varItem

'if there is nothing chosen, inform the user
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list"
Exit Sub
End If

'once the data from the list box is safley in the database you can then open a recordSet
'and create an array to pull each value out and store it in a variable!!


End Sub

Anyway im nearly sorted this bit now, and I cant thank you enough for helping me. Seriously I couldn't have solved it without your help. Have a great Xmas and a happy new year!
Nick