Problem referencing a control on a tabbed subform

[HillTJ]

June7, I still have no success!. I don't understand why. If you or any other forum user can help I'm really desperate to finalise this one issue. I'm sure it's due to referencing as all worked fine till I had a rush of blood to the head & decided it would be a nice layout to have tabbed forms. Problem occurs upon running the Text37_AfterUpdate event on the 'FRM_Inspect_Record subform'.
I'm trying to capture the 'Inspector' from the 'getuser' logon script & change 'postedflag' to true to prevent a reoccurance. It's driving me nuts & i don't believe it should be so. Please help. I've taken onboard the other recommendations so have attached a later version of the database for review. Really appreciate anyones assistance with this matter.

 

[isladogs]

A debug.print shows that the value of GetUserName is being retrieved.

However, as far as I can tell, there are no controls called Inspector or PostedFlag on that subform
Also why is your Schedule Date control locked?

 

[HillTJ]

Isladogs, upon reflection, I was suspecting this maybe my dilemma. My intention is for both controls 'Inspector' &'PostedFlag' to be Hidden from the user & the data entered behind the scenes into the appropriate fields on the table. This all worked (I think, but there is a lot of water under the bridge) until I placed the form on the tabbed mainform. I could add the two controls back then just hide them? Schedule date locked? I'll investigate. Appreciate the heads up. Maybe in the confusion....

 

[HillTJ]

Isladogs, So, I placed both Controls 'Inspector' & 'PostedFlag' back & my code still fails at the first of the two lines. I don't understand why?.Got any ideas?

 

[isladogs]

Can you upload a new copy with the controls restored and the date control unlocked

 

[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.