Problem referencing a control on a tabbed subform

[HillTJ]

Isladogs, To explain my intentions, User logs on & 'Main-Form' opens. On 'Main-Form' is 'Frm_Equipment' & user can either search existing equipment or add a new record. Subform "FRM-item_Attachments subform' (1 to many) allows documents to be copied to a nominated location. Subform 'Frm_Inspect_Record_Subform' (1 to many) is where user enters the inspection Details. Upon entry of a new record, control 'Schedule Date' is filled with the current date (Hence locked so that it cannot be altered). At the same time the Inspector name is recorded against that record. I see no value in displaying that users name as it's the same as displayed at the top of the form. This did work OK. Postedflag is set to prevent a repeat generation of the Outlook task. This also worked. I've added back the two fields & would be totally grateful if anyone can help me with the problem that it doesn't work now. Please see latest copy attached. This is my first database where I've tried to encompass all I've learn't. I welcome any feedback. I wish I could contribute to the forum.

 

[isladogs]

Hi Terry
I’ve got a problem with both phone and internet which may not be resolved until Thursday. Unlikely I’ll be able to look at your app as a result. Hopefully someone else can do so.


Sent from my iPhone using Tapatalk

 

[HillTJ]

Colin, No problem, do what you can. I can wait but maybe someone else can assist. Thanks.

 

[HillTJ]

Hi, Can anyone assist. Please see post #46 for details of my dilemma & a copy of my program. Thanks.

 

[theDBguy]

Hi, Can anyone assist. Please see post #46 for details of my dilemma & a copy of my program. Thanks.

Hi HillTJ. I might have time to look at your file today but reading only post #46, it doesn't tell me what the problem is, so I'm not sure what to check or solve, and I am feeling lazy to read the whole thread (I imagine the discussion went in many directions with the number of participants). So, would you mind giving me/us a step-by-step for duplicating the problem using your latest file? Thanks.

 

[June7]

"Doesn't work" means what - error message, wrong result, nothing happens?

 

[HillTJ]

June7 Open database. Enter login details Usernameelilah poaswordel. Select the Calibrations tab. Go to "Inspection Record" subform (left bottom form). Enter a new inspection. Upon leaving the Inspection date control, you will be asked whether you wish to make an outlook reminder. Select 'Yes'. Code will crash on this line of code;
Forms!Main_Form.[FRM_Equipment].[Form]![FRM_Inspect_Record subform].[Form]![Inspector] = Nz(GetUserName, "") on private sub Text37_AfterUpdate(). The intention of the line of code is to capture the username (behind the scenes- doesn't need to be displayed). If I comment out this line, same happens for the following line. All worked till I went for a tabbed form layout!, so I guess it's a referencing issue?.

DRIVING ME MAD!!! Appreciate any help. I's got to be simple.

 

[June7]

Again, use of a Tab control is irrelevant in referencing.

Well, I can't get that far. I get "invalid use of null" for Frequency selection because ID 298 record has null fields and DLookups are returning Null to non-variant variable. Had to fix data and try again and yes funky things happen and db crashes. Had to use TaskManager to close it.

Disable error handler in CreateTask procedure. Access tells me there is a popup I must respond to. I find there is an error message from the Outlook task window "An error occurred while calling the callback: 'Ribbon_GetIMEXImage'". I OK this popup and get another message "You must be in a public folder to change the Owner field of a task. The original owner name will be restored. To assign the task, right-click the task, and then click Assign Task." I OK this popup. Access has not crashed.

Active error handlers during testing and debugging are a nuisance. They should only be activated when ready to deploy db.

 

[HillTJ]

June7, try creating a completely new record (or go to the last one). Maybe some early test data was incomplete. The outlook error I know, you've hit the nail on the head. I need to fix this, but not my prime concern. Yeah, I thought the error checking was smart, only to find it was a nuisance too. Lesson learn't. Appreciate your involvement.

 

[June7]

Okay, this time I get the issue.

Ran into similar issue earlier with another thread. Could not get referencing to work with OP's db. I imported their table into my db. Created same form/subform structure with same calculations referencing subform controls and it worked. Imported those forms into their db and they worked there as well even though OP's original forms would not. When testing path reference in VBA immediate window I get "Automation error" message. That's what I was getting in the other db.

However, your runtime error is: "Method 'Item' of object 'Forms' failed"

I tested a similar tab/form/subform structure in my db and referencing works.
Forms!ProjectsData.ctrFinancial.Form!ctrFunding!Comment

All very weird.

 

[HillTJ]

June7, excellent (I guess), but what does all this mean? All I wish to do is capture the User & posted flag. It all worked fine prior to putting the forms on tabbed layout! Thanks

 

[theDBguy]

June7, excellent (I guess), but what does all this mean? All I wish to do is capture the User & posted flag. It all worked fine prior to putting the forms on tabbed layout! Thanks

What this could mean is your form may have somehow got corrupted between the time it used to work and when it stopped working. As June7 said, the structure you want is very possible and shouldn't be a problem.

 

[June7]

You can try decompile http://www.fmsinc.com/MicrosoftAccess/Performance/Decompile.asp then Debug>compile then Compact & Repair. But it is quite possible the form is corrupted beyond repair. Maybe even db. If all that fails, suggest you build a new database to test if this tab/form/subform arrangement will work in new file.

When I tried to create a new form/subform with same calcs in that OP's db, even those would not work. My imported forms did work. I really cannot explain it other than to say weirdness is often due to corruption.

 

[HillTJ]

Thank you all whom have offered assistance. I'll try the decompile/recompile route. Glad that there does not seem to be anything wrong with my logic or intentions. What a pain!!

 

[HillTJ]

All, I did the decompile thing & it made no difference!. Guess I have no other choice other than to remake my tabbed form.....Don't relish the thought.

 

[June7]

You can try what I did. Build in another db. Might even be able to copy/paste controls and have it work. Then import into your original db.

However, if you can't build a new form/subform like this within existing db, might better build a brand new file. Unfortunately, importing existing forms in total to my db did not fix them.

 

[HillTJ]

June7 thanks. It took me so long to get to this point. I've learn't a lot but I cannot imagine starting again.

 

[June7]

I know it's frustrating but unfortunately if file is corrupt you will likely have more problems with it. As I said, I could not create new forms in OP's db to do these calcs. That tells me there is something wrong with file. I suggest you confirm this by building test forms in a new file.

 

[HillTJ]

June7, by way of a 'new file' do you mean like a new database project? Or should I recreate only the form(s) & try again?. Should I attempt to build like a dummy query & see whether I can locate the offending control(s) by building the reference using code builder? Just in case there is something strange with the control names that I haven't figured? Really appreciate the assistance.

 

[June7]

Yes, a 'new file' means a new db project. You can try any number of things but it still might come down to having to create new file when all those other things fail. So yes, try recreating forms in existing db. If calcs still won't work, then something must be wrong with that file that corrupts forms. As I said, importing forms into my db did not correct them. Importing table and building forms in my db then importing forms to existing worked. But still could not build forms with those calcs in original db.