Cascading combo boxes in a sub form

[Dave888]

Hi,

I'm having trouble getting my 2nd combo box to work after I select a value from cboVendor1. The 2nd combo box is cboVPO1.
and is on form CreateOrModifyPayment.

I get the following error:

Complie Error:

Method or data member not found

I've attached the database hoping someone can have a look at this and point me where I went wrong.

Long story short, the purpose of this database is to track payments against various VPOs. VPOs can have more than 1 payment, but I need to change the database so that the payments are VPO and VPOitem specific.

 

[boblarson]

You have a few problems.

1. Your afterupdate code should not be:

Private Sub cboVendor1_AfterUpdate()
 Me.cboVPO1 = vbNullString
  Me.cboVPO1.Requery
End Sub

It should be like THIS:

Private Sub cboVendor1_AfterUpdate()
   Me.VPO_Payment_Subform.Form.cboVPO1 = vbNullString
   Me.VPO_Payment_Subform.Form.cboVPO1.Requery
End Sub

And your rowsource on the cboVPO1 should have bracketing in the form reference because you have spaces (that's a good reason to avoid spaces in object names). So it needs to change to:

SELECT VPO.VPONumber FROM VPO WHERE (((VPO.VendorCode)=Forms!CreateOrModifyPayment![VPO Payment Subform]![cboVendor1])) ORDER BY VPO.VPONumber;

 

[Dave888]

I made those changes and it's not showing any of the created VPOs in the subform after selecting a vendor?

did it work for you?

 

[boblarson]

Actually, now that I look at it, you don't need to store the VPO number there at all, if you have it on the main form. You shouldn't store the same data twice (once in the Payment ID table and the VPO Payment table). Take that field out of VPO Payment or, if they are going to be different for multiple payments, take it out of the Payment ID table.

 

[Dave888]

Okay, I removed VPONumber from the PaymentID table like you suggested. I also renamed all the objects with spaces, must have got lazy there.

so it's still not working. I reattached it with changes. Would you mind having a look.

 

[boblarson]

I may have to wait until I get home to work on this, so if anyone else wants to try, feel free.

As for the VPO number - how does it get created?

 

[Dave888]

The VPOnumber is manually entered. It is taken from our ES system at work. There's no easy way to track all these payments in the ES so I'm hoping using this database will slove a lot of our issues. Right now we're tracking everything in excel and it's a headache!

 

[Dave888]

bump... can someone look at this pleeeeease???

 

[JANR]

The control cboVPO1 on your subform refrenced to a control on the subform which wasen't there but is insted back on your mainform. Just remove the subform-bit of your where-clause to this:

[Forms]![CreateModifyVPOPayment]![cboVendor1]

When you select something from cboVendor1, cboVPO1 on your subform populates.

JR

 

[Dave888]

okay, I did that and it populates. however, it's only showing the VPONumbers that correlate to the first vendor selected...

i.e. it's only showing VPONumbers 111111 and 125456 if you select vendor ALLFEE, if I change the vendor it's not requerying the vponumbers for then new vendor

 

[JANR]

You have to add this in the after_update event of cbovendor1 to force a requery i think.

Private Sub cboVendor1_AfterUpdate()
   Me.VPO_Payment_Subform.Form.cboVPO1 = vbNullString
   Me.VPO_Payment_Subform.Form.cboVPO1.Requery
End Sub

JR

 

[Dave888]

that's what i have in there it it's still the same restult
did you download my db and change the code and itworked?

 

[JANR]

I'll take a second look

JR

 

[JANR]

I'v looked at it and the combo populates according to your reqest, however in your code you have an afterUpdate event for a control named "VendorCode" which isen't on your form, perhaps it's an old control you have deleted?

that's what i have in there it it's still the same restult
did you download my db and change the code and itworked?

Did you put it in the AfterUpdate of cboVendor1 or the "old" VendorCode"??

The VPOnumber is manually entered.

?? Are you gonna type it or is it determend by what the user selects in cboVendor as in a cascade, if the former then I can't see why you cascade in the first place.

Any hoo here is a revized version of what I think you asked for.

JR

Edit: I'v added some comments in your code.

 

[Dave888]

yes, that is what I wanted it to do! thanks muchly for the help!

 

[JANR]

Glad you got it to work.

JR

 

[Dave888]

okay next problem....

when you are on the CreateModifyVendorPayment form the VPONumber in the subform is dependent on the vendor selected above. the Product ID is depenent on the VPONumber selected.

So the combo boxes are working for the VPO Number,
When I select a VPONumber from the list it gives me an "Enter Parameter Value" If I manually type the VPO Number I want the ProductID drop down populates with the corressponding Product IDs.

i've attached the db if someone wouldn't mind having a look to see where I've gone wrong.

 

[JANR]

To make it work change this:

[Forms]![VPOPaymentSubform]![cboVPO1]

¨

to:

[Forms]![CreateModifyVPOPayment]![VPOPaymentSubform].[Form]![cboVPO1]

I'm not sure why this works instead of your original statement but it could be that it's a nested subform.

JR

edit: To refrence control's of forms try and use the wizzard to get the syntax correct. That way you get things to work faster, just a tip.

 

[Dave888]

great! works now!

thank you very much for all the help!

 

[boblarson]

To make it work change this:

[Forms]![VPOPaymentSubform]![cboVPO1]

¨

to:

[Forms]![CreateModifyVPOPayment]![VPOPaymentSubform].[Form]![cboVPO1]

I'm not sure why this works instead of your original statement but it could be that it's a nested subform.

No, it is just to reference a control on a subform you need to use the .FORM part to let Access know you want something on the subform and not on the subform CONTAINER which is the part that needs to be referenced (not the subform itself - although if the container and subform are named the same then it doesn't matter as you will use the correct name).

See here for more about that:
http://www.btabdevelopment.com/main...rhowtoreferencesubforms/tabid/76/Default.aspx

edit: To refrence control's of forms try and use the wizzard to get the syntax correct. That way you get things to work faster, just a tip.

Yes, and here's a visual on that:
http://www.btabdevelopment.com/main...bformsincontrolsources/tabid/106/Default.aspx