Combo Box Default Value Setting

[HardingR2000]

I have FormA with a combo box. When I select a value from the combo box it is saved in a global variable. I click the OK button on FormA and it opens FormB and closes itself.

When I close FormB the closing process opens FormA again using the following commands:

DoCmd.Close acForm, "FormB", acSaveNo
DoCmd.OpenForm "FormA"
Forms!FormA!comboPipeline = GlobalPipelineValue

This works great. However, when the combo box's dropdown arrow is clicked the displayed list starts from the beginning.

What I would like to happen when I click on the dropdown arrow for the combo box is for the displayed list to start at the location specified in the setting of the value in code line three above.

I have tried setting the DefaultValue property for the combo box using the following:

Forms!FormA!comboPipeline.DefaultValue = GlobalPipelineValue

This only screws up the value displayed in the combo box.

Is it possible to do what I want to do?

 

[HarleyQuinn]

Just loop the combo until the ItemData is equal to your text then set the Text to the ItemDatavalue, something along the lines of:

For i = 0 To Combo1.ListCount
If Combo1.ItemData(i) = [red]YourTextValue[/red] Then
        Combo1.SetFocus
        Combo1.Text = Combo1.ItemData(i)
        i = Combo1.ListCount
    End If
Next i

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HardingR2000]

This works almost perfectly. I need to complicate it with an additional test in the IF statement.

The selection of a value from the dropdown populates a second field on the form. This value comes from the result set displayed in the dropdown. When I come back into this form I need the IF check to include an AND which compares the value I selected originally against the result set list. This will position the dropdown list more accurately. As it works now I am only positioned at the first of what could be many possible selections.

I tried using:

If Combo1.ItemData(i) = YourTextValue and
Combo1.ItemData(i).Column(1) = YourTextSubValue then

However Access does not like the .Column(1) or .Column(0)

Is there syntax that will accomplish what I want?

 

[HardingR2000]

I have figured out that to reference the other columns on the row I can use the syntax:

Combo1.Column(col,row)
where col is the column number and row is the row number.

I can get the loop to find the exact row by using the compound IF test, but when the dropdown arrow is clicked it still shows me the first entry associated with the value in column 0. For example the dropdown list contains:

A,a
A,b
A,c
B,a
B,b
B,c

I select B,c and click OK on FormA and go off to FormB. When I close the FormB, the close logic opens FormA and accurately finds B,c (row 5 in base 0), but when I click on the dropdown arrow the list is displayed starting at B,a (row 3 base 0).

I would guess the Combo1.Text command I execute after finding the exact row is telling Access on which value to position the list, not the exact row. Since Combo1 is only the A and B values it positions the list at the first occurrance (in my example this is B). It appears that the only way I can get Access to be more accurate is to make Combo1 a combination (concatenation) of column 0 and column 1. This I do not want to do because Combo1 would be too difficult to read using my actual data.

If you have any suggestions I would appreciate further information. Thanks.