Changing values in listbox

[DKORO]

Please help me... almost finished project

I have a multiselect list box
many columns and many rows,
ie patientid, callid, appointmentdate, fname, lname, batchno etc.

I have an unbount text box called "bn"

I have a command button which I am trying to make on click event make all batchno selected in the multiselect list box change to the text value in the text box bn one types

Having great difficulty

I have got this far but it does not work

this is driving me crazy aft 3 days

enclosed is what i have done

------------------------------------------------------
Private Sub Command22_Click() '-------Attempt to change col 8 based on bn
Dim frm As Form, ctl As Control
Dim varItm As Variant, intI As Integer


For Each varItm In Forms!batch!List14.ItemsSelected ' show me the row/s selected
intI = 8 ' 8 column across please to the batch row


Me.batchno.Value = bn.Value


MsgBox ("HELLO " & Forms!batch!List14.Column(8, varItm))

' Next intI
Next varItm
End Sub

 

[Eupher]

Something like this should work:

Dim CurDB As DAO.Database
Dim Rs As DAO.Recordset
Dim varItem As Variant
Dim SQLStmt As String

SQLStmt = "SELECT * FROM tblListboxRowSource"
Set CurDB = CurrentDb()
Set Rs = CurDB.OpenRecordset(SQLStmt, dbOpenDynaset)

For Each varItem In Me![List14].ItemsSelected
     Rs.FindFirst "[PatientID] = " & Me![List14].ItemData(varItem)
     Rs.Edit
     Rs![batchno] = Me![bn]
     Rs.Update
     Me!List14.Selected(varItem) = False
Next varItem
Rs.Close
Me![List14].Requery
Set Rs = Nothing
Set CurDB = Nothing

This code assumes your listbox is based on a table called "tblListboxRowSource", that PatientID is a unique identifier for records in the table, and that PatientID is the bound column.

HTH...

Ken S.

 

[rdodge]

Given how Access Events works and I have stringent data validation checks along with the fact that my users requires user friendliness of the forms, I do not use bound forms. Bound forms may allow for the stringent data validation checks, but only if you are willing to have those same stringent checks done 100% of the time the control loses focus. In my experience, there are those few minor times you don't want the stringent checks to take place.

In your case with the list box, if you don't have your listbox bound, you can use the following code:

Dim item as variant, I as Long,strCurItem as String
Dim J as Long

For each item in Me.List14.ItemsSelected
strCurItem = ""
For J = 0 to Me.List14.ColumnCount - 1
If J = 5 Then
strCurItem = strCurItem & ";" & CStr(Me.bn.Value)
Else
strCurItem = strCurItem & ";" & Me.List14.Column(J,item)
End If
Next J
strCurItem = VBA.Strings.Mid(strCurItem,2)
Me.List14.RemoveItem(item)
Me.List14.AddItem(strCurItem,item)
Next

Assumptions:

Listbox is named "List14"

Textbox name is "bn"

Listbox, Textbox, and Command button are all on the same form.

Listbox is unbound.

Hope this is of help to you.

Sincerely,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[DKORO]

Dear Ronald

I attemplted to use your code

However, the last line comes up in red
Me.List14.AddItem(strCurItem,item)
and says
Compile error:
Expected:=

would appreciate any help

thanks
Daniel

 

[DKORO]

Dear Ken S.,
""This code assumes your listbox is based on a table called "tblListboxRowSource", ""

um it is based on a query called batching,

um how do I reference that?
have tried replacing tblListboxRowSource with batching

however it stops at the start
Dim CurDB As DAO.Database

and says
Compile Error:
User-defined type not defined

um ? what should I do?
Hope you can help

Daniel

 

[rdodge]

Within the code:

strCurItem = VBA.Strings.Mid(strCurItem,2)
Me.List14.RemoveItem(item)
Me.List14.AddItem(strCurItem,item)


Insert just above the code the line:

J = item

Then in the lines above where you see "item", change that to "J" (but only without the double quotes).

My mistake as item is just a reference to the object and since the object deletes, the item variable no longer refers to anything.

Sincerely,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[DKORO]

Dear Ronald,
could I please trouble you to spell it our for me
I did attempt to do as you suggested see included
but to no avail it still does not like the last line

----------------
Dim item as variant, I as Long,strCurItem as String
Dim J as Long

For each item in Me.List14.ItemsSelected
strCurItem = ""
For J = 0 to Me.List14.ColumnCount - 1
If J = 5 Then
strCurItem = strCurItem & ";" & CStr(Me.bn.Value)
Else
strCurItem = strCurItem & ";" & Me.List14.Column(J,J)
End If
Next J
J = item
strCurItem = VBA.Strings.Mid(strCurItem,2)
Me.List14.RemoveItem(item)
Me.List14.AddItem(strCurItem,item)
Next
-----------------
sorry to bother you

kind regards
Daniel

 

[rdodge]

----------------
Dim item as variant, I as Long,strCurItem as String
Dim J as Long

For each item in Me.List14.ItemsSelected
strCurItem = ""
For J = 0 to Me.List14.ColumnCount - 1
If J = 5 Then
strCurItem = strCurItem & ";" & CStr(Me.bn.Value)
Else
strCurItem = strCurItem & ";" & Me.List14.Column(J,item)
End If
Next J
J = item
strCurItem = VBA.Strings.Mid(strCurItem,2)
Me.List14.RemoveItem(J)
Me.List14.AddItem(strCurItem,J)
Next
-----------------

 

[DKORO]

Dear Ronald,
3:30 am here in the morning

the last line still comes up in red

Me.List14.AddItem(strCurItem,J)

and says
Compile error:
Expected:=

would it be easier if I emailed it to you?

Kind regards
Daniel

 

[DKORO]

opps
sorry it says
compile error
syntax error

 

[rdodge]

For each item in Me.List14.ItemsSelected
strCurItem = ""
For J = 0 to Me.List14.ColumnCount - 1
If J = 5 Then
strCurItem = strCurItem & ";" & CStr(Me.bn.Value)
Else
strCurItem = strCurItem & ";" & Me.List14.Column(J,item)
End If
Next J
J = item
strCurItem = VBA.Strings.Mid(strCurItem,2)
Me.List14.RemoveItem(J)
If J = List14.ListCount Then
'Last Item was deleted, so add item at end of list
Me.List14.AddItem strCurItem
Else
Me.List14.AddItem strCurItem,J
End If
Next

Note, there's 2 things I did. One, of which I should have known better since I have ran into it several times before, can't use the parantheses around the arguments, when the function is being used as a sub procedure, and secondly, when it's the last item that's deleted, just need to readd the item to the list at the end of the list, which means, no row index number.

Sorry for the confusion earlier

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[DKORO]

Wow this is proving to be tricky...
it no longer comes up in red however when i click the command button
this comes up....

Run-time Error '6014'
The RowSourceType property must be set to 'Value List' to use this method.

can anyone offer ideas how to get around this please
Daniel

 

[Eupher]

Daniel,
I think Ronald and I are offering two rather different approaches. In Ronald's approach, he is using the AddItem and RemoveItem methods to directly add to or remove items from your listbox; however, this technique requires that the listbox's Row Source Type is "Value List". In my approach, I'm assuming the Row Source Type is "Table/Query" and am using code to alter the underlying table or query, then requerying the listbox to reflect the changes.

Either approach will work, depending on how you have your listbox set up.

Regarding the error message you mentioned earlier - "user type not defined" - that is a references error. To fix that, open any code window and select Tools->References from the menu, then select "Microsoft DAO 3.6 Object Library" from the list. Re-compile, and your code should now work.

Ken S.

 

[DKORO]

Thank you Eupher
You have solved my problem

Thank you also Ronald for all you patience


Guys
what can I say

Have a wonderful day


Daniel

 

[rdodge]

Yes, Ken is correct in that we both showed 2 rather different approaches. Sorry, if the List Value setting on the Row Source Type Property confused you, but then I did mention in my first response, the control would need to be unbound as my approach is editing the control itself, not the data in a table. Main reason as I stated before, I don't particularly care for how the Events are setup for data validation purposes, as there are those minor few times when you don't want the exact same stringent checks take place, such as if users are use to clicking on a command button to back out of a form (Such as how it's setup in Seagull FE to the JDE DB System in the BE), the way events are setup in Access simply doesn't allow for that. Yes, pressing "Esc" key to back out of a form is real easy to do and not a tough thing to learn (Or is it?), but I literally have users that doesn't seem to pick up that concept. That's why not only do I require the stringent data checks, but also require the forms to be a much higher standard of user friendly.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000