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

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 03:35
Joined
Apr 1, 2019
Messages
713
June7, thanks. Will get back to you & let you know how i go. Appreciate it.
 

Micron

AWF VIP
Local time
Today, 10:35
Joined
Oct 20, 2018
Messages
3,476
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:35
Joined
Feb 19, 2002
Messages
42,984
In your code you have ScheduleDate and txt_duedate defined as STRINGs and later, you are trying to format them as a date to store them in what is presumably defined as a date and you are also using date functions to operate on them.

1. Change the definition of ScheduleDate to be DateTime
2. Remove ALL format instructions on date fields. Formatting a date field turns it into a STRING! NEVER, EVER format a date EXCEPT for display purposes.
 

June7

AWF VIP
Local time
Today, 06:35
Joined
Mar 9, 2014
Messages
5,425
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:35
Joined
Feb 19, 2002
Messages
42,984
We are looking at code in #19 NOT controls on a form. So yes, the Format property is different from the Format() function but we were talking about code. "Instruction" is code. "property" is settings on a control.
 

June7

AWF VIP
Local time
Today, 06:35
Joined
Mar 9, 2014
Messages
5,425
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:35
Joined
Feb 19, 2002
Messages
42,984
We weren't talking about formatting fields in tables but now that you bring it up, formatting a field in a table does nothing to prevent "bad" data from being entered. All the format does is hide it. I did talk at length about this in another active thread. There is never a reason that I am aware of where formatting on a table is a good idea.

Formatting a date using the Format() function is frequently performed when working with non-US dates but most often it is done when it shouldn't be and therefore causes more problems than it solves. Formatting is necessary ONLY when you must supply a string value. So, if you are building an SQL statement in VBA, you are building a STRING and all the elements of it will be strings. When you pass a string date to SQL Server, it must be either standard US field order - mdy OR some unambiguous other format. The standard European dmy is ambiguous and so people format it to mm/dd/yyyy.

But, if you use a querydef that references a date in another field or table or a control on a form, you do NOT need to format anything, nor should you.

Select ... From ... Where mydate = Forms!yourform!yourdate

Does not need formatting as long as the control on the form is bound to a datetime data type or if it is unbound and there is a format property that defines the control as a date.

However, the very same query constructed as a string in VBA MUST format the date.

strSQL = "Select ... From ... Where mydate = #" & Format(Forms!yourform!yourdate, 'yyyy/mm/dd') & "#;"
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 03:35
Joined
Apr 1, 2019
Messages
713
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

AWF VIP
Local time
Today, 06:35
Joined
Mar 9, 2014
Messages
5,425
Maybe should provide latest version of db for analysis.
 
Last edited:

Micron

AWF VIP
Local time
Today, 10:35
Joined
Oct 20, 2018
Messages
3,476
@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

To train a dog, first know more than the dog..
Local time
Today, 03:35
Joined
Apr 1, 2019
Messages
713
Appreciate it, see attached. I understand that you are voluntarily helping me & I have nothing but praise for the generosity of all involved. Cheers
 

Attachments

  • Calibration V7.zip
    266.1 KB · Views: 130

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:35
Joined
Feb 19, 2002
Messages
42,984
Are we supposed to know what userID and password to use?

PS - giving a list of valid userID's isn't exactly good practice. You remove half of the security that way.
 

June7

AWF VIP
Local time
Today, 06:35
Joined
Mar 9, 2014
Messages
5,425
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.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:35
Joined
Feb 19, 2002
Messages
42,984
Colin, the link doesn't work for me. But don't worry about it. I don't have time to examine the database any more.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 03:35
Joined
Apr 1, 2019
Messages
713
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

MVP / VIP
Local time
Today, 14:35
Joined
Jan 14, 2017
Messages
18,186
Fixed the link in my previous post. Thanks for telling me Pat
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 03:35
Joined
Apr 1, 2019
Messages
713
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;
Code:
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

AWF VIP
Local time
Today, 06:35
Joined
Mar 9, 2014
Messages
5,425
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.
 

Users who are viewing this thread

Top Bottom