Tech Support Guy banner
Status
Not open for further replies.
1 - 17 of 17 Posts

·
Registered
Joined
·
2,871 Posts
Discussion Starter · #1 ·
Have an Access 2000 input form (Signups) that contains a subform (HowPaidSubform). The subform contains two bound fields, [Posted], a checkbox, and [PostDate], a date field with m/d/yy format. I want the current date automatically entered into [PostDate] when [Posted] is checked, so wrote a simple macro:

Item: [Forms]![HowPaidSubform]![PostDate]
Expression: Date()

If I open the subform by itself, the macro works perfectly. But with the parent form open with the subform in it, it does not work; error message says it can't find the HowPaidSubform.

What am I doing wrong?
 

·
Registered
Joined
·
3,086 Posts
You're referencing the subform control incorrectly, I believe. Within a form, a subform is a control not a standalone form, so you have to reference it as a control--it doesn't exist within the Forms collection because it isn't instanced separately.

The proper syntax would be

Forms(parent form name).[subform control name].Form.Controls(target control name)​
So, for example, given the main form is frmParent with a subform control named sfrmChild and a control within the subform called txtSomeText, to map to the text box you would say

Forms("frmParent").sfrmChild.Form.Controls("txtSomeText").Value​
Note, sfrmChild is not the name of the saved subform. It's the name of the subform control within the parent. In Access 2000 the default name for a subform control was Child#, where # was automatically generated number corresponding to the number of controls on the form (e.g., if the subform was the first control you added to the main form, its default name would be Child0).

Here's a FAQ that does a better job of explaining what I'm saying using a variety of techniques:

HTH

chris.
 

·
Registered
Joined
·
7,837 Posts
I think - but I will admit that I would rather use a wizard than mess with writing anything (lazy) but if your macro is written correctly, the subform may give you an error message, but will work within the parent form. Is that correct, Chris?
 

·
Registered
Joined
·
3,086 Posts
That's correct if the child form has a dependency on the parent form--that is, if they're mutually referential (the only possibility in this scenario), or if the directionality of the dependency is from the child to the parent. In layman's terms, if the subform control were referring to the parent form for whatever reason (to use a value in a control on the parent, e.g.), it would almost certainly behave incorrectly when instanced independently unless you implemented conditional/try-catch logic to avoid the problem. This is a very common issue for people using mutually referential forms, and it is very easy to overlook unless you're stepping through code at run-time; the code compiles because it is syntactically correct, but you get a run-time error because either the subform or parent form object does not exist in the Forms collection.

So yes, the reverse of Don's problem will also cause errors: if the subform were referring to a control on the parent form, the parent form would behave correctly when opened by itself (because it contains the subform) but the subform would not.

chris.
 

·
Registered
Joined
·
3,086 Posts
[bump]

Also I'll say (because I've had this issue before), it is extremely difficult to author a heavyweight form that acts as both a referential subform and a standalone form. I almost always ended up copying the thing and maintaining two: a subform (with its particular dependency code) and a standalone form (without the dependency code). It might be impossible to use a lightweight bound form both ways without getting errors, although Access used to provide tricky, undocumented operator hacks that might have facilitated it slightly.

chris.
 

·
Registered
Joined
·
3,086 Posts
lol, sorry. I could take another stab at explaining dependencies and directionality if you're curious. The bottom line is, yes, the logic could be arranged such that the subform would fail to work when opened by itself but would work perfectly if opened within its parent form.

chris.
 

·
Registered
Joined
·
2,871 Posts
Discussion Starter · #8 ·
. . .the logic could be arranged such that the subform would fail to work when opened by itself but would work perfectly if opened within its parent form.

chris.
That I wouldn't mind, because the subform is never used by itself. However, I tried entering [Forms].[SignupsForm].[HowPaidSubform].[Controls].[PostDate], [Forms]![SignupsForm]![HowPaidSubform]![Controls].[PostDate] (with ! instead of . separators), and [Forms].[SignupsForm].[HowPaidSubform].Controls.PostDate and none worked. Error messages are different, sometimes saying the OLE object is not an OLE object or is not available for automation (whatever the hell that means), but the net result is the same -- no workee.
 

·
Registered
Joined
·
2,871 Posts
Discussion Starter · #10 ·
Don, can you zip and post your DB? I could take a quick look and see if I can help more that way.
That would be tough. First, this is a very complex application. It involves 16 tables, upwards of 100 queries, forms, reports and macros, plus a fair amount of VBA code. It's also a split database, to I'd have to give you both the client and server sides. In addition, the records, which you will need to be able to do anything with are confidential in that they reveal people's names, addresses, phone numbers, etc.

All I'm looking for is a way to make one very simple macro work in a form when the form is a subform in another form. The original code I wrote worked perfectly when the form is opened standalone, but bombs when you try to use it the way it was intended -- as a subform within another form. There must be a simple answer to this.
 

·
Registered
Joined
·
3,086 Posts
You're still not referencing the subform control correctly. The word Form in my example (immediately after the subform control name) is a literal and must be present. This is what I'm assuming your string needs to be:

Forms("SignupsForm").HowPaidSubform.Form.Controls("PostDate")​
I have a feeling that won't work, though, because I suspect those aren't the control names. Also, I've never used macros, so I'm not sure if this is an acceptable notation. You might also try

[Forms]![SignupsForm]![HowPaidSubform].Form![PostDate]​
But either way, it's critical to use the subform control name not the subform name.

chris.
 

·
Registered
Joined
·
2,871 Posts
Discussion Starter · #13 ·
lol, sorry. I could take another stab at explaining dependencies and directionality if you're curious. The bottom line is, yes, the logic could be arranged such that the subform would fail to work when opened by itself but would work perfectly if opened within its parent form.

chris.
Cristobal03: Overnight, as usual, my subconscious was working on this problem. It usually wakes me up when it gets the answer (about 3am), but this time it didn't "speak" to me until I awoke this morning. I immediately came down to my office and tried it -- and it worked. And it works exactly as you describe in the quote above; does NOT work in the subform when opened by itself, but DOES work when the subform is opened within the parent. The answer was simple:

Item: Forms![SignupsForm]![HowPaidSubform]![PostDate]
Expression: iif(Forms![SignupsForm]![HowPaidSubform]![Posted],Date(),null)

My thanks to all for your input; it was the combination of ideas that caused my feeble brain to finally arrive at a logical fix. I will mark the thread as solved.
 

·
Registered
Joined
·
2,871 Posts
Discussion Starter · #15 ·
1 - 17 of 17 Posts
Status
Not open for further replies.
Top