AddNew

[leighcloon]

Hi,

Based on a value entered in a text box, once the user presses enters, I want to assign values to a combo box.

I have read that the method to do this is AddItem, but it does not appear in the properties for a combo box when I write it in the code.

When i run it, i get the error, Data or method not found

Any help would be much appreciated

Here is my code:

Dim rs As New ADODB.Recordset
Dim strsql
strsql = "Select * from Products2 where [Product Group] = " & "'" & Spend_Category & "';"
rs.Open strsql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic


If Not rs.EOF And Not rs.BOF Then
rs.MoveFirst
Do While Not rs.EOF
cboSubCat.AddItem rs!Product
rs.MoveNext
Loop
End If

 

[RoyVidar]

Depends on version, the .additem/.removeitem methods of combos and lists became available in 2002 (xp).

If valuelist is what you're doing, you could do something like this:

strsql = "Select Product from Products2 where [Product Group] = " & "'" & Spend_Category & "';"
rs.Open strsql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Not rs.EOF And Not rs.BOF Then

cboSubCat.rowsource= rs.getstring(adclipstring,,";",";")

or you could concatenate the string

 Do While Not rs.EOF
  strrowsource= strrowsource & rs!Product & ";"
  rs.MoveNext
loop
cboSubCat.rowsource=strrowsource

But why not use query/table as rowsource type, and assign the sql string directly:

cboSubCat.rowsource=strsql

 

[Mile-O]

I have read that the method to do this is AddItem, but it does not appear in the properties for a combo box when I write it in the code.

AddItem is not recognised within Access VBA - it exists in Visual Basic, Excel, etc.

 

[leighcloon]

solved

Thanks both RoyVidar and SJ McAbney. Using the rowSource it worked.

Thanks again.

 

[RoyVidar]

Originally Posted by SJ McAbney
AddItem is not recognised within Access VBA - it exists in Visual Basic, Excel, etc.

See my first reply. It is recognized within Access for combos and lists starting with 2002 (xp) version. Here's a reference:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac10/html/acmthadditem.asp

 

[Pat Hartman]

The .addItem method is available but keep in mind that the addition isn't permanent. When the form is closed and reopened, the list is as it was originally.