Subform - 2nd Combo box dependant on 1st Combo box error

[Arvin]

Hello,

I have 2 forms
Main Form - Carrierfrm
Sub Form - CarrerServiceDetailsfrm

On my sub form I have 2 cbo's

1st - CarrierTypeID

Row Source = SELECTCarrierTypetbl.CarrierTypeID, CarrierTypetbl.CarrierType
FROM CarrierTypetbl ORDER BY CarrierTypetbl.CarrierType;

AfterUpdate =
Private Sub CarrierTypeID_AfterUpdate()
[CarrierServiceTypeID].Requery
End Sub

2nd - CarrierServiceTypeID
Row Source =
SELECT CarrierServiceTypetbl.CarrierServiceTypeID, CarrierServiceTypetbl.CarrierServiceType, CarrierServiceTypetbl.CarrierTypeID
FROM CarrierServiceTypetbl
WHERE (((CarrierServiceTypetbl.CarrierTypeID)=[forms]![CarrierServiceDetailsfrm]![CarrierTypeID]))
ORDER BY CarrierServiceTypetbl.CarrierServiceType;

When I select a value from the 1st cbo (CarrierTypeID) I get the following error/ msg box that states the following

"Enter Parameter Value" - [forms]![CarrierServiceDetailsfrm]![CarrierTypeID]

I have read many diff forums and have tried numerous suggestions however none of them resolve my issue.

What am i doing wrong?

Any assistance is appreciated ...Thank you in advance

 

[pbaldy]

Your reference to the subform is wrong:

http://www.mvps.org/access/forms/frm0031.htm

 

[Arvin]

Hello,

Thank you pbaldy for that link, however I think that confused me even more

I have tried the following and none of them work ....

Me!ControlName
Me!Subform2.Form!ControlName
Me.RecordSource

 

[pbaldy]

Me is only valid in VBA. Try this one:

Forms!Mainform!Subform1.Form!ControlName

 

[Arvin]

Me is only valid in VBA. Try this one:

Forms!Mainform!Subform1.Form!ControlName

G'day.....

Thank you, I tried that and got the same error/ msg window. However ...when i entered and tested the following

[Forms]![Carrierfrm].[Form]![CarrierTypeID]

The error/msg window doesn't appear but the drop down is empty?

I think I'm getting there .....i think

 

[pbaldy]

That doesn't follow the posted format. Based on your description of the forms:

Main Form - Carrierfrm
Sub Form - CarrerServiceDetailsfrm

and the link/format I posted:

Forms!Mainform!Subform1.Form!ControlName

It would be

Forms!Carrierfrm!CarrerServiceDetailsfrm.Form!CarrierTypeID

 

[Arvin]

My apologizes .....

that is what I had .....and I still get the error .... so just to refresh ....

1st cbo on the subform has an AfterUpdate event

Private Sub CarrierTypeID_AfterUpdate()
[CarrierServiceTypeID].Requery
End Sub

2nd cbo has the following in the Row Source

SELECT CarrierServiceTypetbl.CarrierServiceTypeID, CarrierServiceTypetbl.CarrierServiceType, CarrierServiceTypetbl.CarrierTypeID
FROM CarrierServiceTypetbl
WHERE (((CarrierServiceTypetbl.CarrierTypeID)=[Forms]![Carrierfrm]![CarrierServiceDetailsfrm].[Form]![CarrierTypeID]))
ORDER BY CarrierServiceTypetbl.CarrierServiceType;

 

[pbaldy]

You need the name of the subform control rather than the subform itself, as mentioned on the link:

[Forms]![Carrierfrm]![CarrierServiceDetails subform]![CarrierTypeID]

Though this technique doesn't work with bound records in a datasheet like that. You'll find when you add a second record, it might wipe out the first. I'll try to find a thread on the subject.

 

[Arvin]

Ummmmm... hold that thought Paul ...i think I found something ... I will post again in a few mins .....

 

[Arvin]

Okay .... I thought we solved it ....but ...exactly as you stated before ...

"Though this technique doesn't work with bound records in a datasheet like that. You'll find when you add a second record, it might wipe out the first. I'll try to find a thread on the subject. "

So ...it working .... kinda ...but when I go to add the second record .... it wipes out the value in the CarrierServiceTypeID field