Runtime Error 424: Object Required

[dark11984]

Hi I'm trying to count the number of records in a subform if a value in the subform matches a value in my main form.

TxtActiveContracts = IIf(tblcontract.Carrier = txtSAPVendor, DCount("[terminated]", "tblcontract", "[terminated]<>-1"), Null)

Thanks

 

[vbaInet]

Are you using that code in the control source of the text box?

By the way, I don't think an IIF() function can return a NULL. Use the Empty String instead.

..."[Terminated]<>-1","")

 

[dark11984]

Its on the On Open event of the main form. I've replaced the null with "" and still get the same error.

 

[vbaInet]

Its on the On Open event of the main form. I've replaced the null with "" and still get the same error.

That was just for your information.

Things to check:

1. Is Carrier a field in the form's record source?
2. Is tblContract the table in the form's record source?
3. Are txtSAPVendor and txtActiveContracts text box control's on the form?

 

[dark11984]

Things to check:

1. Is Carrier a field in the form's record source?
2. Is tblContract the table in the form's record source?
3. Are txtSAPVendor and txtActiveContracts text box control's on the form?

1. Carrier is a field in the subform's record source.
2. tblContract is a table in the subform's record source
3. txtSAPVendor & txtActiveContracts are textbox controls on the main form.

 

[vbaInet]

Have you solved this problem?

Try this:

TxtActiveContracts = IIf(Me![Carrier] = txtSAPVendor, DCount("[terminated]", "tblcontract", "[terminated]<>-1"), "")

In what event did you put this code? You should put it in TxtActiveContracts Control Source property like this:

= IIf([Carrier] = txtSAPVendor, DCount("[terminated]", "tblcontract", "[terminated]<>-1"), "")

 

[dark11984]

Not yet.

TxtActiveContracts = IIf([B]Me![/B][Carrier] = txtSAPVendor, DCount("[terminated]", "tblcontract", "[terminated]<>-1"), "")

Tried this and now I get an error message

Run-time error '-2147352567 (80020009)': You can't assign a value to this object.

txtsapvendor is in the main form and carrier is in the subform i thought it should've been

TxtActiveContracts = IIf(Me!txtSAPVendor = tblcontract.Carrier, DCount("[terminated]", "tblcontract", "[terminated]<>-1"), "")

but that didn't work either.

The code is in the on open event of the main form. I've already got the control source as tblcarrier.activecontracts so i can't put the code in the control source.

 

[vbaInet]

Post your db and I'll have a look. Remember to Compact & Repair and zip before posting.

 

[dark11984]

Don't really want to post the DB because it contains sensitive information.

 

[vbaInet]

I've just re-read your post #6 again and realised you actually said Carrier is a field in the SUBFORM's record source. Your problem is a referencing issue. You need to refer to the Carrier control correctly if you're on the main form. Have a look at this:

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

 

[dark11984]

This is sending me insane!

I've had a look at the link and still can't get it to work.

This is what i've got, but i keep gettign an error saying that access can't find the field.

TxtActiveContracts = IIf(Me!txtSAPVendor = Me!FrmrelatedContractsSub.Form!Carrier, DCount("[terminated]", "tblcontract", "[terminated]<>-1"), "")

 

[vbaInet]

Coding isn't easy at first. This is how you learn

You said txtSAPVendor is a text box, you don't use the exclamation mark to refer to a text box.

You either do Me.txtSAPVendor OR just txtSAPVendor.

You would only use the exclamation mark with Me! if you're referencing a FIELD in the form's recordsource.

 

[dark11984]

Now i'm getting a new error. I think i fixed the first one with the code below because when i debug on the second error if i hove my cursor over the sapvendor and carrier i get the right values.

TxtActiveContracts = IIf(Me.txtSAPVendor = Me.Related_Contracts.Form!Carrier, DCount("[terminated]", "tblcontract", "[terminated]<>-1"), "")

The new error message is:

Run-time error '-2147352567 (80020009)': You can't assign a value to this object.

 

[dark11984]

No errors! I changed the event. to on Load ratehr then On open and it worked. Thanks for all your help vbaInet!

Next problem is that it counts all of the contracts not just the contracts for the main form vendor.

 

[vbaInet]

Next problem is that it counts all of the contracts not just the contracts for the main form vendor.

Add an AND to the third argument of your DCount to select the right vendor if this is what you're after?

 

[dark11984]

Do I put an AND after or before

"[terminated]<>-1"

?

 

[vbaInet]

TxtActiveContracts = IIf(Me.txtSAPVendor = Me.Related_Contracts.Form!Carrier, DCount("[terminated]", "tblcontract", "[terminated]<>-1 AND [Vendor_Field] = [COLOR=Red][B]'[/B][/COLOR]" & Textbox1.Value & "[COLOR=Red][B]'[/B][/COLOR]"), "")

Something like the above. Play around with that syntax. Notice the single quotes in red surrounding the textbox value, that's to handle strings. If your vendor_field was of type Number then you wouldn't require those.

This would only work if the vendor_field is a field in the tblContract table.

 

[dark11984]

That worked great! thanks again for all your help!!

 

[vbaInet]

No worries. Glad that worked for you.