[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

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!
0
eunuch_provocateur
Asked:
eunuch_provocateur
  • 7
  • 6
1 Solution
 
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°)
       
0
 
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
0
 
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°)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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!
0
 
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°)
0
 
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?
0
 
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°)
0
 
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
0
 
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°)
0
 
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!
0
 
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°)
0
 
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!
0
 
harfangCommented:
No, there are no worries, it was just a "nice to have" feature.
Good luck with your application!
(°v°)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now