afterupdate autofill combo box, selecting if there is only one

Something like...

Private Sub cboLastName_AfterUpdate()

    cboFirstName.Requery

    If IsNull(cboLastName) Then
        cboFirstName.Value = Null
    ElseIf cboFirstName.ListCount > 1 Then
        cboFirstName.Value = Null
        cboFirstName.Select
        cboFirstName.DropDown
    Else
        cboFirstName.Value = cboFirstName.ListItem(0)
    End If

End Sub

Basically, use .ListCount and .ListItem (or .Column) after refreshing the firstname combo.

Good Luck!
(°v°)
       

It's not quite working.  I think it's close but it's not working yet.  Let me know if you see something wrong with my code(I played around with a few different things because the original code did not work)

Private Sub LastName_AfterUpdate()
cboFirstName.Requery
If IsNull(LastName) Then
cboFirstName.Value = Null
    ElseIf cboFirstName.ListCount > 1 Then
    cboFirstName.Value = Null
    cboFirstName.RowSource = "SELECT [ID#], FieldAdjusterFirstName FROM qry_Field_Adjuster_Name Where FieldAdjusterLastName = '" & Me.LastName.Column(1) & "'"
    cboFirstName.Dropdown
Else
cboFirstName.Value = cboFirstName.Column(0)
End If
End Sub

Hi,

I did make a mistake in my post. The property .ListItem() does not exist, I should have written .ItemData(0). Did something else not work?

In your code, I see these details:

You either do not need to requery cboFirstName or you don't need to change its .RowSource.
If the rowsource contains the name of the first combo -- let's call it cboLastName, as in:

    SELECT [ID#], FieldAdjusterFirstName
    FROM qry_Field_Adjuster_Name
    WHERE FieldAdjusterLastName = cboLastName

Then the first command needs to be cboFirstName.Requery. If not (if you pass the value in the .RowSource), you need to do that instead as first command of the Sub. When you change the .RowSource, the combo is automatically requeried as a side effect.

Finally, when you write "cboFirstName.Value = cboFirstName.Column(0)", you really need "... .ItemData(0)" [again, sorry about that, should have been right in the sample].

Not far from the end, keep it up!
(°v°)

Again, I think I'm close but I'm still confused on some parts.  

What do I need to put after cboFirstName.select?

I'm getting an error on this part.  I put in the Item data and here is the code now:
     If IsNull(LastName) Then
        cboFirstName.Value = Null
    ElseIf cboFirstName.ListCount > 1 Then
        cboFirstName.Value = Null
        cboFirstName.Select
        cboFirstName.Dropdown
    Else
        cboFirstName.Value = cboFirstName.ItemData(0)
    End If

Shouldn't I have to put in that rowsource for cboFirstName somewhere in case there are cases where there are more than one name?  thanks for you help!

Well. The first line should be either one of:

    cboFirstName.Requery
    cboFirstName.RowSource = " ... query ... "

I personally never change the .RowSource -- I feel there is always a better way -- but perhaps that's a matter of style.

If your query uses cboLastName as parameter, requerying it will automatically produce the new list of first names. This would be the sample query from my last post.

If you prefer to rewrite the row source each time (as you did in your code), you need to do this before checking cboFirstName.ListCount, because you want to inspect the new list, corresponding to the new LastName... Again, changing the .RowSource automatically requeries the combo.

Does that help?
(°v°)

I'm still having problems getting this work.  It highlights the .select statement and says "method or data member not found"

Any suggestions?

Does your code compile without errors? (From VB: "Debug / Compile <your project name>")
From the error message, I would guess that the left part before the .Select contains a typo. If it does refer to an existing combo, you should not get this error.
(°v°)

Well I can't figure it out.  No typos that I can find.  Here is what I have:

Private Sub LastName_AfterUpdate()
cboFirstName.RowSource = "SELECT [ID#], FieldAdjusterFirstName, FROM qry_Field_Adjuster_Name Where FieldAdjusterLastName = '" & Me.LastName.Column(1) & "'"
     If IsNull(LastName) Then
        cboFirstName.Value = Null
    ElseIf cboFirstName.ListCount > 1 Then
        cboFirstName.Value = Null
        cboFirstName.Select
        cboFirstName.Dropdown
     Else
        cboFirstName.Value = cboFirstName.ItemData(0)
    End If
End Sub

I tried doing the requery instead of the rowsource also but with the same results.  It always gets stuck on the .Select

Aha!  This seems to work now with those two commented out.  I'm curious though how i can make things that I had in my afterupdate of the cboFirstName populate.  I had some txt boxes, ie: Me.txtOfficeAddress = Me.cboFirstName.Column(7), generated when the first name was selected but now these do not populate.  Essentially the rowsource was the same for LastName as FirstName.

I know this out of the scope of the original question just thought I'd ask real quick.  It does work but it doesn't have the same properties as if I selected it from the dropdown.  Thanks for your help!

Yes, the AfterUpdate and Change Events are not fired when the value is set through code. You can do something like:

        cboFirstName.Value = cboFirstName.ItemData(0)
        cboFirstName_AfterUpdate   ' call event handler manually

However, given the SQL your place as RowSource, there is no Column(7) at the moment:

    SELECT [ID#], FieldAdjusterFirstName FROM qry_Field_Adjuster_Name [etc...]

This defines only two columns: ID# and FieldAdjusterFirstName...

BTW: Did you find out why cboFirstName.Select does not work yet? Is the combo disabled?

Cheers!
(°v°)

I did not find out why the cboFirstName.Select does not work.  It works with those two lines commented out so I guesse there are no worries?

My Select statament was much larger but to simplify things on here I left only the parts I thought were necessary.  

Thanks for you help!  It seems to be working great!

No, there are no worries, it was just a "nice to have" feature.
Good luck with your application!
(°v°)