Edit specific (from NO to YES) record in listbox with button

[pinstripe]

Hi,

i have field (type-yes/no)ammong others in a listbox
i would like when specific row is selected, change this one record form default No to Yes when clicked on a button!

i have tryed several different ways On_Click event but with no success

thank's for your help
Stan

 

[ProgramError]

how about using the wizard to create a toggle button on your form which has its control source set to the yes/no field

Ian M (UK)




Program Error
Programmers do it one finger at a time!

 

[pinstripe]

Thank's Ian for your response

hmm... i am not familiar with the toggle button, but enyway - in my case NO is by default and i need to change to YES just one single time that is whay i would prefer button

maybe even this toggle button wouldn't be bad idea but if you can give me short instruction how to deal with it
Many thank's

Stan

 

[PaulF]

I'm not sure how you've set up your form but this example uses the same source table for the form and the ListBox (lstYN). The ListBox's Multi-Select property is set to False (can Select only one at a time). After selecting the item in the ListBox, clicking on command button cmdChg will cause the code to search for the Key Field value (the bound column of the ListBox) and when found will change the value in the Yes/No field (YNField), and then requery the ListBox to show the updated value. Selecting the same item again will change it back to False and so on (toggle effect).

Private Sub cmdChg_Click()
If Len(Nz(lstYN, "")) > 0 Then
With Me.RecordsetClone
.FindFirst "EmployeeID = " & lstYN
If Not .NoMatch Then
.Edit
!YNField = Not !YNField
.UPDATE
End If
End With
End If
lstYN.Requery
End Sub

PaulF

 

[pinstripe]

Thank you PaulIF,

this is the right stuff!

I have just a small question:
always when i press the button, after it changes from NO to YES it highlights the first row again(by itself) or even one random row
Have you maybe a suggestion ?

Stan

 

[PaulF]

Stan
You can add lstYN = "" after the requery

PaulF

 

[ProgramError]

For future reference...

Doing it the toggle button way would require you to click on the toggle button icon in the tools menu and place it on the form in the position you require (making sure the wizard is off). Then set its control source to the yes/no field which it is displaying.

Ian M (UK)

Program Error
Programmers do it one finger at a time!

 

[pinstripe]

sorry,i was absent for a while!

" You can add lstYN = "" after the requery"

Yes, PaulIF this do come handy in some situations but i wanna the row with the changed field to stay selected even after requery
and there is another thing (bug?)
after testing this code i have found out that it doesn't wanna to change every [YNField] field! Looks like it is random (some yes and some don't)
how can i fix this
here is the code:
Dim db As DAO.Database
Set db = CurrentDb()
Dim rst As DAO.Recordset

Set rst = db.OpenRecordset("Equip-Stock Code from Work Orders", dbOpenDynaset)
If Len(Nz(List100.Column(7), "")) > 0 Then
With Me.RecordsetClone
rst.FindFirst "[Stock Code] = '" & Me.List100.Column(7) & "'"
If Not rst.NoMatch Then
rst.Edit

rst![Remove?] = Not rst![Remove?]

rst.Update

End If
End With
End If
rst.Close
Set rst = Nothing
List100.Requery

thank's for the help

 

[PaulF]

the code will only change the one record selected.. if you are using a multi-select listbox then you have to loop through the ItemsSelected and change the records. The code I provided changed only one record, and it had the ability to change it back to No if you clicked on it again (thats why the "Not !YNField" in the code... If you want to use a multi-select Listbox and only change selected Items to Yes without having the ability to change it back then use something like this:

Dim varItem As Variant
For Each varItem In Me![List100].ItemsSelected()
With Me.RecordsetClone
rst.FindFirst "[Stock Code] = '" & Me.List100.Column(7) & "'"
If Not rst.NoMatch Then
rst.Edit
rst![Remove?] = True
rst.Update
End If
End With
Next varItem
List100.requery


If you are using a Non Multi-Select ListBox and you don't want to be able to change the data back to No then change

rst![Remove?] = Not rst![Remove?]
to
rst![Remove?] = True

and you should be able to select the same item by declaring a variable and setting it to the ListBox value before the requery

dim varItem
varItem = List100

List100.Requery
List100 = varItem

PaulF