Solved: Qwerky Code

[JustJerry]

I have 8 ComboBoxes on one form that ALL have this code implemented for the On GotFocus:
Private Sub cboHGID_GotFocus()
'Requery ComboBox to refresh current data
DoCmd.Requery "cboHGID"
If IsNull(cboHGID) Then
Me.cboHGID.Dropdown
Else
Me.cboHGID.SetFocus
End If
End Sub .

Obviously, I just want to open the list of values automatically if the control is Null, and if a value is already selected, to just setfocus to the control.

If I am in a new record, and all comboboxes are empty, I can tab into any of the 8 comboboxes and the Dropdown function does not work.

However, if I go into any of the comboboxes, select a value, then delete that value, then tab back into that same cbo, the DropDown function works.

Why, on a new record when I tab into any empty cbo, does the Me.cboany.Dropdown code work?
Dazed and confused...Jerry

[xCav8r]

Jerry,

After having added a check for the value being zero, I had no problems getting your code to work in 2000 format in Access 2003. It works as intended when I click or tab into a combo box with or without a value.

A few comments:

It never hurts to provide an attachment that demonstrates your problem. You'll get quicker and more focused replies because people won't have to recreate your problem, and you won't have to worry about them inadvertently recreating a different problem. For example, if someone recreated this scenario but didn't set the form's record source or the controls' sources, your unaltered code would work.
Requering the control at this point seems superfluous. That's usually something you do when you've affected the underlying data source and need to update the control so that it displays the change.
Setting the focus to the control that just got the focus also seems superfluous. It already has the focus.
I created a small database where I did this work, and I've attached it this reply. I altered the code as follows...

Option Compare Database
Option Explicit
Sub OnFocusDropDown(ByRef ControlName As String)
If Me.Controls(ControlName).Value = 0 _
Or IsNull(Me.Controls(ControlName).Value) Then
Me.Controls(ControlName).Dropdown
End If
End Sub
Private Sub Combo10_GotFocus()
OnFocusDropDown (Me.Combo10.Name)
End Sub
Private Sub Combo12_GotFocus()
OnFocusDropDown (Me.Combo12.Name)
End Sub
Private Sub Combo14_GotFocus()
OnFocusDropDown (Me.Combo14.Name)
End Sub
Private Sub Combo16_GotFocus()
OnFocusDropDown (Me.Combo16.Name)
End Sub
Private Sub Combo2_GotFocus()
OnFocusDropDown (Me.Combo2.Name)
End Sub
Private Sub Combo4_GotFocus()
OnFocusDropDown (Me.Combo4.Name)
End Sub
Private Sub Combo6_GotFocus()
OnFocusDropDown (Me.Combo6.Name)
End Sub
Private Sub Combo8_GotFocus()
OnFocusDropDown (Me.Combo8.Name)
End Sub



A few comments about the changes:

As you can see, I just used the default names.
The GotFocus event procedure of each control points to a single procedure. This just makes dealing with the repetition slightly easier.
I removed the Requery and SetFocus methods that I deemed superfluous.
HTH!

[JustJerry]

Hello XCav8r,

Just got to read your message. Never thought about attaching my database, but I'll try and remember that in the future. I am using Access 2003 version, and if you got it to work, makes me wonder why mine isn't then. It's seeems so simple to me. I even tried deleting the code, and retyping it, but that was a waste of time. I am going to try and alter the code to what you have suggested, if problem not solved, I should try and create a new small database and see if it works.

The reason I used the requery in my code was that the particular combobox I pasted the code for is queried off a choice the user makes in an different combobox. Basically, the user has to select a product in cbo#1. That selection then bases the options available for the remaining seven cbo's. This is to match up corresponding equipment based on manufacturer, etc.

If the user goes back and changes the value in cbo#1, then it is imparitive then the remaining seven cbo's must be able to match and be compatible to what is selected in cbo#1. I found that the only way the values for the other cbo's would be come available, was to requery the datasource for the respective cbo's. This then updates the selections to be compatible components. If I didn't to a requery, then the values would be for the original selection in cbo#1. There are hidden fields that are filled in when cbo#1 is selected, and these are what the other cbo's 'datasource queries' look at which determines the valued displayed in their dropdown list.

I agree with the redundant 'SetFocus'. I'll change that for sure.

I should have time to look at this tonight, then I'll let you know.

Thanks again.

[JustJerry]

Quick Update.

I created a new small database. I tried the code exactly as I have it now on my current project, and it works fine. So, I have to investigate some more.

[JustJerry]

Ok, it works now that I have this in the equation: Of course substituting the generics with actual control names.
If Me.Controls(ControlName).Value = 0 _
.

But, odd that I could get it to work without it in the test database. Oh well, the learning goes on :yes