Help with AfterUpdate

[flavioso]

Hello:

I have created 3 cascading combo boxes, cboYEAR, cboCUSTOMER, cboOEM.

The database is based off the year. The cboCUSTOMER loads just fine afterUpdate, but I need to load the cboOEM based on the cboCUSTOMER and the cboYEAR. For some reason it won't work.

My code that I have for cascading the other combo boxes is:

Private Sub cboCUST_AfterUpdate()
       Me.cboOEM.RowSource = "SELECT fldOEM FROM" & _
                               " tblOEM WHERE fldCUSTID = " & _
                               Me.cboCUST & _
                               " tblOEM WHERE fldYRID = " & _
                               Me.cboYEAR & _
                               " ORDER BY fldOEM"
 
    Me.cboOEM = Me.cboOEM.ItemData(0)
End Sub
 
Private Sub cboYEAR_AfterUpdate()
 
    Me.cboCUST.RowSource = "SELECT fldCUSTOMER FROM" & _
                               " tblCustomer WHERE fldYRID = " & _
                               Me.cboYEAR & _
                               " ORDER BY fldCUSTOMER"
 
    Me.cboCUST = Me.cboCUST.ItemData(0)
 
End Sub

Does anyone have any ideas for me? I appreciate any help in advance.

the link to the database for you is http://www.djflavi.com/sample/DatabaseOne.mdb

 

[Gannet]

You've set the RowSource but you need to requery it.

such as:

Me.cboCUST.Requery
Me.cboCUST = Me.cboCUST.ItemData(0)

 

[pbaldy]

You're building an invalid SQL string. Try this:

       Me.cboOEM.RowSource = "SELECT fldOEM FROM" & _
                               " tblOEM WHERE fldCUSTID = " & _
                               Me.cboCUST & _
                               " AND fldYRID = " & _
                               Me.cboYEAR & _
                               " ORDER BY fldOEM"

 

[flavioso]

I went and tried your suggestion, but the OEM combo box still doesn't populate. Do you have any suggestions?

 

[pbaldy]

You've received 2 suggestions, so you should probably specify which one you tried that didn't work. I know the requery won't work.

 

[flavioso]

You've received 2 suggestions, so you should probably specify which one you tried that didn't work. I know the requery won't work.

Paul:

I tried your code suggestion, and although I didn't get any errors from it, it still didn't populate. The goal I am trying to achieve is this. I am being asked to create a database where we manage sales quotes for OEMS (sub-distributors) on a yearly basis. The tree is

YEAR
----DISTRIBUTOR
-----SUB DISTRIBUTOR (OEM)

Then the sub distributors will have all the data attached to them. As a novice in Access, I was making an assumption that I could use three cascading combo boxes, and then have a subform with tab controls, all tied to that specific sub distributor, of course based on the overall year. Perhaps I am going about this all wrong. Would you have any suggestions, or be able to point me in a direction? I appreciate any advice or positive criticisms.

Thanks,

Brad

 

[pbaldy]

I only have Access 2000 here, so I haven't been able to look at your sample, but I'll take a look at it when I get home in an hour or so.

 

[pbaldy]

Your other problem is the customer combo returns the name, but the third combo is looking for the ID.