Ms Access AddItem with VBA

Question

I have a Form that has a button on it. The button basically copies records from one Table to the other. As records are read and copied, it is also checked for specific values. E.g. If one of the fields in the table has a value of "" then it should call another form that allows me to enter a date. Once the date is entered and the form is closed the programme carries on with copying. It can also happen that the key fields in the table that is being copied are duplicate. In this case I should a 'listbox form' should be launched with a listbox displaying the values of the duplicate records. I should then select the correct record that I need copied.

 Dim NumberCount As Long
     NumberCount = RecordsetElementValue.RecordCount
     If NumberCount > 1 Then
         With Form_F_ListBox.List30
             RecordsetElementValue.MoveFirst
             Do
                With Forms!F_ListBox.List30.AddItem(RecordsetElementValue!E_ElementValue)
                End With
                RecordsetElementValue.MoveNext
             Loop Until RecordsetElementValue.EOF = True
             DoCmd.OpenForm "F_ListBox", acNormal
         End With
     End If

The code sample above is what I have for in case there are duplicate records (NumberCount > 1) The listbox in my F_ListBox form should be filled with the values in my recordset.

I now run into a runtime error 6014. The RowSourceType property must be set to 'Value List' to use this method.

What am I doing wrong?

Answer 1

The usual way to set the row source of a combo or list box in MS Access is to use an SQL statement, however, you can also use a list. This is controlled by the row source type.

Me.MylistBox.RowSourceType = "Value List"

From your notes, it seems that an SQL statement for the row source would be easier:

Me.MylistBox.RowSource = "SELECT ID FROM MyTable"