error" The object doesn't contain the automation object ...

I am getting the error" The object doesn't contain the automation object "TRdetails".  I saw an exact post of my scenerio, but the guy figure it out on his own ... I don't appear to be that savy.

I have a Form which is for a Treatment Record which is the main form.  This has a subform which has all the treatment details ... from a table TRdetails (the culprit) .. I have the forms linked with Treatment Record.TreatmentID and TRdetails.TreatmentID ....

The wierd thing is is that it gives me this error, and then it allows me to add the record ... well, not really ... at that point i can't figure out how to send the TreatmentID (from the Treatment Record) to the detail line of the subform of the TRdetail.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Please post your access version, os version, and any sql you are using - are you using macros or vba? and then it would be helpful to know a bit about your table structure including any relationships or referential integrity settings.

tljprincessAuthor Commented:
access 2002, os version is windows 2000 .... i am using vba, not macros

it is pretty basic; the treatment record table has the base information and is linked to the subform which uses the TRdetails table ... linked by the treatmentid (text 13 char)
post your vba code please? this error can occur in several ways, including bad syntax. in particular when building strings with sql and forgetting to quote them properly.

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

tljprincessAuthor Commented:
i have setup the main form Treatment Record (from teh tables Treatment Records) and inserted a subform TRsubform (from the table TR details) ... linked on TreatmentID ... there really isn't any code here for me to post, eh?
hrm, then you arent using vba :)

you you have 2 tables and a form, no module code, no querys,
Treatment Record (form) is based on Treatment Record (table)
TRSubform is based on TRDetails

each contain a field named TreatmentID which is (should be ) the primary key in Treament Record (AutoNumber?) and is a number (long int) in TRSubform.

you used the subform object to add the subform to the main form and allowed the link to be established on TreatmentID.

that about sum it up?

perhaps this would be easier if you just sent me the db, however im using access 2000 - can you convert it?
Tools -> Database Utilities -> Convert -> To Prior access verion

If you can then send it along to jobrien AT acscience DOT com


er, typo there the line :

each contain a field named TreatmentID which is (should be ) the primary key in Treament Record (AutoNumber?) and is a number (long int) in TRSubform.

should be:

each contain a field named TreatmentID which is (should be ) the primary key in the Treament Record table (AutoNumber?) and is a number (long int) in TRDetails.

and now i see you have already said treamentID is a text field. Is the rest correct?

Mike EghtebasDatabase and Application DeveloperCommented:
If you have a FK (say Treatment_ID) in you subform, and its SQL return zero records, you will get that kind of error.  This is how I deal with that (depending what even the error occurs in your case, you need apply the concept).

If VarType(Me!SubForm.Form!Treatment_ID)=3 Then   '3 is Long

' It has good value will not cause error

If VarType(Me!SubForm.Form!Treatment_ID)=9 Then   '9 is object

' returns zero records, will produce error

End IF

Mike EghtebasDatabase and Application DeveloperCommented:

ElseIf VarType(Me!SubForm.Form!Treatment_ID)=9 Then   '9 is object
VarType in this case is 10 apparently. He is using text.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperCommented:

I thought text/String is 8.  I guess tljprincess could use:

MsgBox VarType(Me!SubForm.Form!Treatment_ID)

to see what number is paged in each case.  The problem could be something else.

Alan WarrenApplications DeveloperCommented:
Hi tljprincess,

If you have any buttons on your forms, then you have code.

Try this to find the source of the error:

Highlight your form in the database window
Menu > View > Code

Menu > View > Options
On the General Tab for Code Options, Tick Break on all errors. Click OK

Then try to make the error happen again, it should open the code window on line that is causing the error.

A quick fix would be to insert a line above the offending line and type:

On Error Resume Next

Then place your cursor on the new line you typed in, and choose:
 Menu > Debug > Set Next Statement

Menu > Run > Run...

Post the offending code block here for further information.
Dont forget to set the options back to break on all unhandled errors when fixed.

Alan :)

Alan WarrenApplications DeveloperCommented:
Hi tljprincess,

This is often caused by a field which has a default value that is a Lookup value, but the field for the lookup has been removed or renamed.

defaultvalue:  = DLast("Unit_Price", "Catalog")

Done any renaming table fields lately?
Alan :)
tljprincessAuthor Commented:
I can't believe that the solution was this easy ... but I went into the subform's query and added the criteria to TRdetails.TreatmentID of Treatment Records.TreatmentID ... and all is fine ... just now the TreatmentID of the TRdetails on the subform is showing as ?Name ... but that automation error is now gone.  Any ideas on the ?Name ???
tljprincessAuthor Commented:
ok, so now i too have figured out the stupid "?Name" issue ... i am not sure how to award points on this ... any advice?
Mike EghtebasDatabase and Application DeveloperCommented:
I don't expect any points because my shot-in-the dark was very off the target.
This question has been abandoned and needs to be finalized.
 You can accept an answer, split the points, or get a refund (information at http:/help.jsp#hs5)
  If you need a moderator to help you, post a question at Community Support (http:/Community_Support/)


Cleanup Volunteer

jadedataMS Access Systems CreatorCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: jobrienct {http:#9844766}

Please leave any comments here within the next seven days.

EE Cleanup Volunteer
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.