[RESOLVED] [Access 2003] Form ComboBox Blank Item problem

[Torc]

I have a form in Access where I have a ComboBox which displays a value taken from a table.

I need to be able to have the first item be Blank so that when I first create a
new record I can display the blank field and prevent the user from moving
to a new record without changing the value.

The place where I am having a problem lies in this code

Form_EmailList.CmbActioned.SetFocus
Form_EmailList.CmbActioned.ListIndex = TheRS.Fields(4).Value

My problem is that where the value of TheRS.Fields(4).Value is 0
Form_EmailList.CmbActioned.ListIndex is being set to -1

Can anyone offer any suggestions?

I am using the following code to load the data.
This is called in Form_Load.

Private Sub LoadEmailsUsed()
' Loads all used emails so that they can be quickly selected by the user if necessary
Dim Idx As Long
Dim EmailRS As ADODB.Recordset
On Error GoTo Err_LoadEmailsUsed

Form_EmailList.CmbActioned.RowSourceType = "Value List"
' Clear the List
Do While Form_EmailList.CmbActioned.ListCount > 0
Form_EmailList.CmbActioned.RemoveItem 0
Loop

' Set the first Item in the list to a Blank
Form_EmailList.CmbActioned.AddItem " ", 0

Set EmailRS = New ADODB.Recordset
EmailRS.Open "Emails", TheConn, adOpenKeyset, adLockPessimistic, adCmdTable

EmailRS.MoveFirst
Do Until (EmailRS.EOF) Or (EmailRS.BOF)
Form_EmailList.CmbActioned.AddItem EmailRS.Fields(1), EmailRS.Fields(0)
EmailRS.MoveNext
Loop
EmailRS.Close
Set EmailRS = Nothing

Exit_LoadEmailsUsed:
On Error GoTo 0
Exit Sub

Err_LoadEmailsUsed:
MsgBox "Form_EmailList - LoadEmailsUsed - " & Err.Number & " - " & Err.Description
Resume Exit_LoadEmailsUsed

End Sub

[Ecniv]

Private Sub LoadEmailsUsed()

'*** if this sub is in the forms module of hte form you are updaing you 
'*** don't need to refer to the form

' Loads all used emails so that they can be quickly selected by the user if necessary
    Dim Idx As Long
    Dim EmailRS As ADODB.Recordset
    On Error GoTo Err_LoadEmailsUsed

    Form_EmailList.CmbActioned.RowSourceType = "Value List"
' Clear the List
    CmbActioned.RowSource="-1;"

    Set EmailRS = New ADODB.Recordset
    EmailRS.Open "Emails", TheConn, adOpenKeyset, adLockPessimistic, adCmdTable

    EmailRS.MoveFirst
    Do Until (EmailRS.EOF) Or (EmailRS.BOF)

' Assuming the rst(0) has the id number - rst(1) the email address
        CmbActioned.rowsource=CmbActioned.rowsource & ";" & EmailRS.Fields(0) & ";" EmailRS.Fields(1)
        EmailRS.MoveNext
    Loop
    EmailRS.Close
    Set EmailRS = Nothing

Exit_LoadEmailsUsed:
    On Error GoTo 0
    Exit Sub

Err_LoadEmailsUsed:
    MsgBox "Form_EmailList - LoadEmailsUsed - " & Err.Number & " - " & Err.Description
    Resume Exit_LoadEmailsUsed

End Sub

Try the above - Make a copy of your mdb first!!

[Torc]

Hi Ecniv,

Thanks for the code, unfortunately I still get the problem where I try to set
the ListIndex property to 0, but it is being set to -1.

Form_EmailList.CmbActioned.SetFocus
Form_EmailList.CmbActioned.ListIndex = TheRS.Fields(4).Value

[Ecniv]

hmmm.

whats in 'TheRS.Fields(4).Value' ?

is it null?
if so use

nz(TheRS.Fields(4).Value,-1)

Did the list fill ok? you may need

CmbActioned.RowSource="-1;' '"

or

CmbActioned.RowSource="-1;''" 'single quotes on inside of speechmarks

[Torc]

TheS.Fields(4).value is a positive integer value from 0 on up, where I am
having the problem is where the value is 0

I tried changing it to

CmbActioned.RowSource="-1;' '"

but this gave me a list with -1 as the first entry and then a blank entry.

So I tried

CmbActioned.RowSource="' '"

And for some wonderful, unknown reason this worked.

Although I am still getting CmbActioned.ListIndex set to -1 instead of 0 it is not falling over.

Many Thanks.

[Ecniv]

I assumed you had set the combo/list box to two columns (under properties) with widths of '0;1'

listindex should be set once you select an item.... not sure why it is -1 when you select the nothing line, perhaps access is being confused.

Glad you got it working. Post up if you get any more problems.