List Box question

[AccessWater]

I have created a list box that shows all the projects' names. Now, I want to add another column in this list box that allow user to choose YES or No for each individual project. In creating a table, you can set a variable as YES or No type. Can I do something like this in the list box.

Thank you for your help.

 

[ejstefl]

There is really no easy way to edit data from a list box. You could do somthing funky using the double-click event or something whereby it would stich the selected record to the opposite of whatever it was, but typically listboxes aren't used for data entry.

 

[epicmove]

Try This....

Hi,

The following would work. May not be the best solution but can't think of anything else.

I presume your list box is based on a query of the table containing the yes/no field?
I will presume you have three fields in your query: Project ID, Project Name, Yes/No.

Add a button to your form containing the list box. Add the following code to the buttons onclick event:

Private Sub YourButton_Click()
Dim strSQL As String
 strSQL = "UPDATE TblProjects SET TblProjects.YesNoField = 1 " & _
"WHERE (((TblProjects.Project_ID)=" & Me.ListBox.Value & "));"

CurrentDb.Execute (strSQL) 
'updates the yes/no field to 1 (yes) where the ID of the record = the list box selection
Me.ListBox.Requery 'requeries your list box

End Sub

This would update the value to "Yes". To switch between the two values (may be a better way of doing it) you could do the following:

Dim intYesNo as Integer
intYesNo = Dlookup("[YesNoField]", "YourTableName", "[Project_ID] =" & Me.Listbox.Value & "")
If intYesNo = 1 Then
intYesNo = 0
'check the value of the yes/no field for the selected record. if it is yes then
'set the variable to no
'THEN INSERT ABOVE CODE HERE USING intYesNo instead of "1"
End If

Hope that helps