Problem referencing a control on a tabbed subform (2 Viewers)

[HillTJ]

June7, thanks. Will get back to you & let you know how i go. Appreciate it.

 

[Deleted member 147267]

A value that is both zero and a zls (zero length string) evaluates to Empty. However, I've never seen this comparison when referring to the value of a control as opposed to a variable of type variant. A variant initializes to a value that is both 0 and a zls.

 

[June7]

My understanding is Format property does not convert date to string. Format function does that.

However, I never set formatting in table. Do this on forms and reports.

 

[Deleted member 147267]

My understanding is Format property does not convert date to string. Format function does that.

That's my understanding as well.

 

[June7]

Ah, I misread and I thought you meant to remove formatting from fields in table. Formatting date value in code might be necessary for a non-U.S. database. But I agree, doesn't seem appropriate for this situation since formatting used is not correct for non-U.S. date.

 

[HillTJ]

Hi,

I'm still stuck & getting frustrated despite the efforts of the forum. To reiterate, I have a tabbed main form 'Main_Form' into which I have 4 tabs. one of which is 'Calibration' onto which I've copied 'FRM_Equipment'. I use several of the fields from this form to generate the title for the Activity. I use several of the fields from 'FRM_Inspect_Record subform' a subform on 'FRM_Equipment' to generate values for the repeat date etc which becomes the outlook task. No matter how I try, i cannot seem to get the referencing right. It all worked till I decided to move 'FRM_Equipment' to a tabbed main form. Upon disabling error routines, it errors out after update of text37, where it calls routine 'ValidateFields' and crashes @;

If Forms![Main_Form]![FRM_Equipment]!Form![Equipment_Desc] = Empty Or IsNull(Forms![Main_Form]![FRM_Equipment]!Form![Equipment_Desc]) Or Len(Forms![Main_Form]![FRM_Equipment]!Form![Equipment_Desc]) = 0 Then
s = s & vbNewLine & "'Subject'"

If I comment out this routine, the subroutine returns, only to crash at the next subroutine "CreateTask" with the following;

Temp_Site = DLookup("[Site_Name]", "TBL_Site", "[SiteID] =" & Forms![Main_Form]![FRM_Equipment].Form![SiteID])

Clearly, I haven't gotten the referencing right, no matter what I try. I's probably a 2min fix, but driving me mad, leave alone the other tweaks I've been advised to do.

I really need to finish this project. I would be eternally grateful for any assistance the forum could provide.

 

[June7]

Maybe should provide latest version of db for analysis.

 

[Deleted member 147267]

@HillTJ; I have to agree with June7. After 30 posts, it's time to upload something - even if the db contains bogus data and you remove everything not related to this issue. I'd like to see you get it solved, and maybe it is a simple thing like you say, but my problem is that I have a TON of things I have to get done and soon, so for me there's little time for beating around the bush, so to speak. If you need help getting something uploaded here, let us know.

 

[HillTJ]

Appreciate it, see attached. I understand that you are voluntarily helping me & I have nothing but praise for the generosity of all involved. Cheers

 

[June7]

Annoyances:
1. Outlook library reference - I have to change to version 14 because I am still in Access 2010
2. form resizing - I disabled all
3. Main_Form opens with ActiveX communication error which I just OK past

Use .Form instead of !Form.

Now your code gives error "Method 'Item' of object 'Forms' fails". I've never encountered this. Google it.

Deleted code from Main_Form. Now don't get ActiveX error and subform code runs.

So, other than comment about syntax above, nothing wrong with subform referencing.

ActiveX error when form opens apparently due to by-passing login form. Also get that error with Logout button.

Main_Form is missing line:
On Error GoTo Err_Handler
But that does not correct nor does commenting out the procedure calls. That leaves the error handler. Delete that and no error.

 

[isladogs]

For info, the OP is using my example login form complete with the original example username/passwords
e.g. User: Delilah ; Password: del
The complete list is https://www.access-programmers.co.uk/forums/showpost.php?p=1598987&postcount=4

I can open it without error but get an error when I logout
No time to look at the tabbed subform control reference as its very late here

 

[HillTJ]

Isladogs, thanks. I was going to say same, but you beat me. June7 thanks heaps, will instigate your recommendations ASAP. Thanks to all involved.

 

[isladogs]

Fixed the link in my previous post. Thanks for telling me Pat

 

[HillTJ]

Friends, With your help I have taken onboard your recommendations & am happy to say that I've progressed. Now my routine runs 'validateFields' & 'CreateTask' correctly. But I now get Runtime Error 2146500595 Method 'Item' of object 'Forms' failed upon return from CreateTask & when the code hits the first line reproduced below. I have changed the name of 'FRM_Inspect_Record subform' to 'FRM_Inspect_Record_Subform' & replaced it where necessary. I have used June7's recommended referencing see post #18. See below;

Forms![Main_Form].[FRM_Equipment].Form![FRM_Inspect_Record_subform].Form![Inspector] = Nz(GetUserName, "")
Forms!Main_Form.[FRM_Equipment].[Form]![FRM_Inspect_Record_subform].[Form]![PostedFlag] = True

I think these 2 lines are now the only hold up. Got any ideas?

 

[June7]

Referencing must be through subform container control name. I always name container different from object it holds, like ctrEquip and ctrInspect. Then:

Forms!Main_Form.ctrEquipment.Form!ctrInspect.Form!Inspector = Nz(GetUserName, "")
Forms!Main_Form.ctrEquipment.Form!ctrInspect.Form!PostedFlag = True

If container does have same name as its SourceObject, your code should work.

 

[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