afterupdate autofill combo box, selecting if there is only one

I have two combo boxes that are used to pull up records on a form.  The first combo is the Last Name and second is the first name of people in the table.  I have the first combo box narrow the selection of the second combo box but I would like to add an if statement.  If there is only one selection(ie. Smith in first combo and Joe in the second) then I would like it to automatically put Joe in the second combo if you select Smith in the first combo.  

Let me know if this makes sense.  thanks!
eunuch_provocateurAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

harfangCommented:
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°)
       
eunuch_provocateurAuthor Commented:
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
harfangCommented:
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°)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

eunuch_provocateurAuthor Commented:
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!
harfangCommented:
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°)
eunuch_provocateurAuthor Commented:
I'm still having problems getting this work.  It highlights the .select statement and says "method or data member not found"

Any suggestions?
harfangCommented:
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°)
eunuch_provocateurAuthor Commented:
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
harfangCommented:
Curious and curiouser...

If you comment out these two lines:
        ' cboFirstName.Select
        ' cboFirstName.Dropdown

Does the rest work as expected? This is reall just a "nice to have" feature: the automatic drop-down of the second combo when needed. The only other idea I have right now is that the combo is disabled.

When you say that you "get stuck", do you mean a compile error or a run-time error? What is the message?

We'll find it.
(°v°)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eunuch_provocateurAuthor Commented:
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!
harfangCommented:
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°)
eunuch_provocateurAuthor Commented:
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!
harfangCommented:
No, there are no worries, it was just a "nice to have" feature.
Good luck with your application!
(°v°)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.