Subform ComboBox Keeps Duplicating

[Bill Holmes]

Using Access 2003, and the following code in a ComboBox - GoFocus.
When used in the main form works perfectly, but when used on the
subform everytime you move back to the control the ComboBox list is
duplicated.

E.g.
1) First time selected..
N
Y
2) Second time selected after moving back from another control.
N
Y
N
Y
3) third time....
N
Y
N
Y
N
Y

etc. etc,

Anyone have a solution...
Private Sub B01_CONTAINER_IND_GotFocus()
SQLstring = "SELECT DISTINCT View_CDATA_R02.B01_CONTAINER_IND "
SQLstring = SQLstring & "FROM View_CDATA_R02 "
SQLstring = SQLstring & "WHERE B01_CONTAINER_IND is not null "
SQLstring = SQLstring & "ORDER BY View_CDATA_R02.B01_CONTAINER_IND"
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = SQLstring
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseServer
.Open
End With
Do While Not rs.EOF
Me.B01_CONTAINER_IND.AddItem rs!B01_CONTAINER_IND
rs.MoveNext
Loop
Set rs = Nothing
End Sub

 

[Alejandro Mesa]

Bill,

Do not put the code in the GotFocus event, put it in the form Open or Load
events instead.


AMB

 

[Bill Holmes]

Problem is this will be a multi-user front end with new data being
added all the time so the ComboBox needs to be refreshed everytime
someone selects it.

Added this code to remove the list items before recreating them but if
there is a better way would like to hear.

'-- Clear All Entries from the ComboBox List
i = Me.B01_CONTAINER_IND.ListCount
Do Until i = 0
i = i - 1
Me.B01_CONTAINER_IND.RemoveItem (i)
Loop
'--

Bill.

 

[Alejandro Mesa]

Bill,

You can set the combo box recordset property instead.

Private Sub B01_CONTAINER_IND_GotFocus()
SQLstring = "SELECT DISTINCT View_CDATA_R02.B01_CONTAINER_IND "
SQLstring = SQLstring & "FROM View_CDATA_R02 "
SQLstring = SQLstring & "WHERE B01_CONTAINER_IND is not null "
SQLstring = SQLstring & "ORDER BY View_CDATA_R02.B01_CONTAINER_IND"

Set rs = New ADODB.Recordset

With rs
Set .ActiveConnection = cn
.Source = SQLstring
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseServer
.Open
End With

set Me.B01_CONTAINER_IND.recordset = rs

Set rs = Nothing
End Sub



AMB