add Item to comboBox

[Mister-B]

Hi there,

I'm new to Access but I have a lot of experience with Excel and also with VBA in Excel.

I'm trying to use VBA to achieve the following on an Access form:

I have four ComboBoxes, with names "cboBew1" - "cboBew4". I also have another ComboBox "cboTN1". I am trying to get my macro to run through the 4 Boxes and add the displayed contents of these boxes to the drop down menu of cboTN1. Here's my code:

Function Makro1()
Dim i As Long
With Forms("Daten - Formular")


For i = 1 To 4
strItem = .Controls("cboBew" & i).Value
.cboTN1.AddItem Item:=strItem, Index:=0
Next
End With


End Function

When I run the code, cboTN1 is empty although there should be 4 entries. What am I doing wrong? Also how would I deal with the possibiliby of one of the 4 ComboBoxes being empty.



Kind regards
Martin

[Minty]

I assume cboTN1 is set to be have a value list as it's rowsource.

The normal route would be to make a query that referenced your other combo's and use that as the recordsource for the final combo.
That can also deal with the null values automatically.

What is the overall purpose of this - a search form ?

[Gicu]

Is the rowsourcetype property of thecboTN1 set to Value List? Try this:

Function Makro1()
Dim i As Long,strItem as string
With Forms("Daten - Formular")


For i = 1 To 4
   strItem =nz( .Controls("cboBew" & i),"")
   if stritem<>"" then .cboTN1.AddItem Item:=strItem, Index:=0
Next
End With


End Function

Cheers,
Vlad