default values for combo box 3

[ocan1]

I have a combo box on a form that is based off of a query based off of the selection of another combo box. The selection in the combo box needs to always be the first line item in the combo box. Is there anyway this 1st row can always come up after the selection in the other combo box. I am having no luck with the ItemData(0)Default method.

I'd appreciate any help. Thanks.

 

[TheAceMan1]

How are ya ocan1 . . . . .

Have a look at the [purple]Selected[/purple] Property.

See Ya! . . . . . .

 

[TheAceMan1]

ocan1 . . . .

Sorry, scratch that!

See Ya! . . . . . .

 

[TheAceMan1]

OK ocan1 . . . . .

Tried a great many ways and [purple]whats needed can't be entered in the Default Value properly[/purple]. So we move to a function.

In the code window for the form, copy/paste the following function:

[blue]Public Function CbxDefault()
   DoEvents [green]'Necessary when form 1st opens.[/green]
   CbxDefault = Me!cbx1.Column(0, 2)
End Function[/blue]

In the Default Value property of the combobox enter:

[blue]=CbxDefault()[/blue]

[purple]Thats it . . . give it a whirl & let me know . . .[/purple]

See Ya! . . . . . .

 

[ocan1]

I am not having any luck with the above code. Let me see if some more info about the form would help. The form is a subform & is continuous forms. In this form we are entering test results. The 1st field is a combo box with the names of tests, you select the name of the test. Next field is the result(classic text box). The next 3 fields are combo boxes for lower spec, upper spec & typical. These combo boxes are based off of product & the test name selected in the 1st combo box. There is always only 1 row/selection that shows up in the combo box(& that's what you always want for the selection). I can't find a way to have these always just automatically show up as the selection in the combo box, without having to manually select it.

 

[earthandfire]

For each combobox that is dependent upon another:

Set the criteria of the rowsource query to incorporate the value of the combobox it depends on.

In one of the exit events of the original combobox, requery the dependent combobox.

eg

cbo2 depends on cbo1

cbo2 rowsource: "select f1 from t1 where f2 = " & cbo1

cbo1 lostfocus event (any of the exit events would probably do): cbo2.requery

NB The exapmle is very crude and if f2 is a text field you would need ..... "where f2 = '" & cbo1 & "'"

Hope this helps.

 

[ocan1]

What code would I write(based off of the above code AceMan provided) if I needed the combo box to default not when the form is activated, but after the selection of another combo box on the form?

 

[earthandfire]

ocan1, did you read my reply?

 

[ocan1]

Yes, I read your reply, but I guess I don't completely understand. I have no problem with the cascading combo boxes based off of each other. My issue is how do I get the first row in the 2nd combo box to automatically always show up in the combo box without manually selecting it. If this is what your suggestion does, I guess I don't know where & how to execute it. Any further help would be great.

 

[earthandfire]

Example:

tbl2: id (PK), f1 (text)
tbl3: id2 (PK), id1 (FK), f2 (text)

Form1
combo0:
RowSource: select id, f1 from tbl2
Columns: 2, Widths 0;1, BoundCol 1
combo1:
RowSource: select id2, f2 from tbl3 where id1 = forms!form1.combo0
Columns: 2, Widths 0;1, BoundCol 1

Private Sub Combo0_Change()

Combo2.Requery
Combo2 = DLookup("id2", "tbl3", "id1=" & Combo0)

End Sub


Hope this helps.

 

[earthandfire]

Actually, Combo_AfterUpdate() is a better location for the code than Combo0_Change.

AfterUpdate changes Combo2 whetherCombo0 is changed with keyboard or mouse, whereas Change only seems to work as you require with the mouse. So just move the code from Change to AfterUpdate.

 

[ocan1]

Thanks for being patient. I appreciate your help. This example helps. Thanks.

 

[earthandfire]

You could also try:

Combo2.Requery
Combo2.SetFocus
SendKeys "%{DOWN}{DOWN}"

which not only sets the text portion of combo2, it also drops the list and shows the first item highlighted.

Explanation:

Sendkeys sends a series of keystrokes to whichever control or application is waiting for them (hence SetFocus to force the correct control).

% is a code for the ALT key
{DOWN} is down arrow.

ALT+DOWN in a combobox drops the list.

The second {DOWN} presses the down arrow into the list and selects the first item

"%{DOWN}{DOWN 2}" would select the second item in the list, whereas "%(DOWN 2}" presses ALT+DOWN, ALT+DOWN, therefore opening and closing the list without doing anything.

Sendkeys is frowned upon in some circles because it can be intercepted by whatever is expecting keystrokes at the time it is processed. In theory, therefore, if someother application popped up a window over your application while the keys were being pressed, that application would receive the keystrokes. Personally I've never had any problems with it.

One word of caution with the Sendkeys approach - if the user types a value into combo0 and then uses the mouse to select a control other than combo2, nothing will happen in combo2.

 

[flyover789]

@earthandfire
have you ever used something like

combo2.dropdown

at least the first of your sent keys can be avoided by this

and furthermore: ThaAceMan's suggestion is superb! TheAceMan, you are a genious!

Greetings,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]

 

[earthandfire]

Flyover,

if we call the comboboxes cb1 and cb2, where cb2 depends on cb1 then after making a selection in cb1, the text portion of cb2 should show the first item in the list of cb2. Both of my solutions do this ie with and without sendkeys.

cb2.droplist does not enter anything in the text portion.

The sendkeys option just extends my solution to allow the user to see the other options, but preselects the desired option.

However, if cb2.droplist follows the Dlookup then it will work as intended.

 

[flyover789]

@earthandfire

the event is not .droplist but it is .dropdown !!

I just want to stress out, that you don't need sendkeys in case there is a way to use proper object oriented event procedures

if you want

The sendkeys option just extends my solution to allow the user to see the other options, but preselects the desired option.

then you should better do a cb2.dropdown on gotfocus of the cb2

Kind regards,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]

 

[earthandfire]

Additionally, I haven't been able to get TheAcemans's solution to work. Which I admit is very surprising because normally they are excellent.

 

[flyover789]

I was able to get TheAceMan's solution to work, and it works perfectly, even in a continuous form

Greetings,
fly


[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]

 

[earthandfire]

Private Sub Combo0_AfterUpDate()

Combo2.Requery
Combo2 = DLookup("id2", "tbl3", "id1=" & Combo0)
Combo2.Dropdown

End Sub


works better than the Sendkeys solution, but I still can't get TheAceman's solution to work. I'm obviously doing something wrong.

 

[RoyVidar]

Have you tried one of the following methods?

[tt]'1 - not to sure on this one, but should work with listboxes
me!cboCombo.setfocus
me!cboCombo.listindex=0
' might have problems due to focus, and if it works,
' it may fire on change and after update events of the combo

'2 - similar/same as posted by TheAceMan1
me!cboCombo.value = me!cboCombo.column(0,0)
' assuming first column is the bound column[/tt]

'3
[tt]me!cboCombo.value = me!cboCombo.itemdata(0)[/tt]

I think the controls would need to be bound.

Roy-Vidar