[RESOLVED] Using a List control to update yes/no fields on a table in access using VBA

[Erik Wiggins]

Ok basically what I've done is to create 2 forms each with a list box, a text box and a button. I'm using a table that has on text field and a growing number of yes/no fields

On one form I populated the list box with the field names and want to add a record to the table using the textbox as the first field and I want to use the List box to set the values of the remaining fields.

On the second form I populated the list box with the text field of each record and want to add a field then set the values for each record based on the values selected in the listbox. I've already figured out how to add the field so I just need to learn how to set the record values for the newly created field.

[jp140768]

If I understand you correctly, you are going to use values on the second screen to insert a record into the database?

Is your problem in building the SQL statement? or in issuing the statement on the database?

[Erik Wiggins]

Here I've got a block of code that should work but for some reason doesn't but it should serve to give you a better idea of what I'm trying to do.Dim i As Integer
Dim FieldCount As Integer
i = 1
FieldCount = CurrentDb.TableDefs("Course Requirements").Fields.count
CurrentDb.TableDefs("Course Requirements").OpenRecordset.AddNew
CurrentDb.TableDefs("Course Requirements").OpenRecordset.Fields(0) = Text2.Value
While i < FieldCount
If List0.Selected(i - 1) = -1 Then
CurrentDb.TableDefs("Course Requirements").OpenRecordset.Fields(i) = "True"
Else
CurrentDb.TableDefs("Course Requirements").OpenRecordset.Fields(i) = "False"
End If
i = i + 1
Wend
CurrentDb.TableDefs("Course Requirements").OpenRecordset.Update

unfortunatly this is what I get when I try to use this code.

Run-time error '3020':

Update or CancelUpdate without AddNew or Edit.

[Erik Wiggins]

In case anyone wants to know the solution I was able to get it to work


Dim i As Integer
Dim FieldCount As Integer
Dim rs As Recordset
i = 1
Set rs = CurrentDb.TableDefs("Course Requirements").OpenRecordset
FieldCount = CurrentDb.TableDefs("Course Requirements").Fields.count
rs.AddNew
rs.Fields(0) = Text2.Value
While i < FieldCount
If List0.Selected(i - 1) = -1 Then
rs.Fields(i) = "True"
Else
rs.Fields(i) = "False"
End If
i = i + 1
Wend
rs.Update

I don't know why but all I had to do was create an instance of a recordset class. Set the value of that instance to the recordset of my table and then perform the code on the instance I created. Thank you anyone who was working on a solution for me.