SQL with WHERE that is on Subform (1 Viewer)

WaiveIt

Registered User.
Local time
Today, 07:52
Joined
Dec 2, 2013
Messages
58
Hello All!
(this is a Form and a Query question, but I think the answer will come as a change to the query)

I have a query "qryQVVehsTD" that show the number of vehicles a lead has.
I have a continuous form "frmToDo" which shows a list of leads that have an open action, and each row shows (among many other things) how many vehicles the lead has. When frmToDo is a standalone form it works well, but I want it to be a subform on "frmHome"
Code:
SELECT Count(*) AS NumberOfVehicles
FROM dVehicle
WHERE (((dVehicle.LeadID)=[Forms]![frmToDo]![txtLeadID]));

I've changed the code to the following so I can use "frmToDo" as a subform, but I get #error
Code:
SELECT Count(*) AS NumberOfVehicles
FROM dVehicle
WHERE (((dVehicle.LeadID)=[Forms]![frmHome]![frmToDo].[Form]![txtLeadID]));
 

WaiveIt

Registered User.
Local time
Today, 07:52
Joined
Dec 2, 2013
Messages
58
So,,, it is expected that I cannot reference a control a subform?
Bummer
 

David R

I know a few things...
Local time
Today, 09:52
Joined
Oct 23, 2001
Messages
2,633
I think you're close but no cigar. http://support.microsoft.com/kb/113352. Try
Code:
WHERE (((dVehicle.LeadID)=Forms![frmHome]![frmToDo].Form![txtLeadID]));
Your other option is to use the Expression Builder > Loaded Forms to double-check yourself.
 

WaiveIt

Registered User.
Local time
Today, 07:52
Joined
Dec 2, 2013
Messages
58
No luck.
Also, MS Access adds the brackets.
 

David R

I know a few things...
Local time
Today, 09:52
Joined
Oct 23, 2001
Messages
2,633
Did you try the Expression Builder?

If none of that works, you can try uploading a stripped-down copy of your database for someone to look at.
 

WaiveIt

Registered User.
Local time
Today, 07:52
Joined
Dec 2, 2013
Messages
58
I found loaded forms in the Expression builder, but didn't see how to use that to show me anything.

I uploaded a stripped down version of my db.

Load frmToDo as the main form, and load frmHome to see frmToDo as a subform.

The text box on the left should work in the subform and the text box on the right does work in the main form. Then take a look at the two queries "qryQV..." to see the SQL.

Thanks a bunch!!

Edit: Things have changed a bit since my initial post, but the db that I uploaded should easily show my issue.
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:52
Joined
Jan 20, 2009
Messages
12,849
The reference to a subform must name the subformcontrol rather than its SourceObject.

[Forms]![frmHome]![Agency Automator by effortlessIQ, LLC].Form![txtLeadID]

BTW That is a ridiculously clumsy name for any object.
 

WaiveIt

Registered User.
Local time
Today, 07:52
Joined
Dec 2, 2013
Messages
58
Yeah, that is a horrible name for an object. I didn't know it took the caption and used that for the subform name,,, probably becuase in the past I hadn't set any captions on forms that I used as a subform.

I just renamed the object to frmToDo and Swwoooppp go to go.

Thanks!!

Edit: "it" = MS Access | "subform name" = main form object that is a subform
 
Last edited:

David R

I know a few things...
Local time
Today, 09:52
Joined
Oct 23, 2001
Messages
2,633
Your edit is cracking me up. :p
 

WaiveIt

Registered User.
Local time
Today, 07:52
Joined
Dec 2, 2013
Messages
58
Glad you like it. :cool:

Most of what I know about Access, VBA, and SQL I learned from reading Tech Net, w3schools, and forums. "It" is a very bad word to learn from. Ambiguity, while usually being a bit guilty of myself, is something I will try to avoid in my posts.

Thanks again for the help David and Gal
 
Last edited:

Users who are viewing this thread

Top Bottom