Solved

Reference Field on Sub-From from a Module

Posted on 2012-03-31
2
344 Views
Last Modified: 2012-03-31
When the user enters a quanity in a field on the main form, my program calls a procedure to perform a calculation.  The calculation needs to reference another field on the subform of the main form - this is an unbound field.  This is what I came up with, but I receive an error - 2450 can't find the referenced form fsub_equipment_selected.intTrainTotal

Main Form:  frm_Opportunity_Quote
Sub Form:  fsub_Equipment_Selected
Field on Sub Form:  intTrainTotal (unbound field displays totals on subform)
Calculation:  intNoFin = intNoFin - intTrainTotal
0
Comment
Question by:Marilync1266
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 37791147
you use this format

Forms!frm_Opportunity_Quote!SUBFORMCONTROLNAME.intTrainTotal

open main form in design view
select/click  the outer edge of the subform control, then hit F4
select the ALL tab, the entry that you will find in the NAME property is the SUBFORMCONTROLNAME, use that in the code above

see this too as reference
Refer to Form and Subform properties and controls
http://access.mvps.org/access/forms/frm0031.htm
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 37791166
A more dynamic method (without specifically pointing to a static field in a form):
Create a public variable in the DECLARATION SECTION of a code module.

Public glbYourFieldName as string   '--- or integer, or single, whatever data type.

Now, create a fuction to retrieve this value:

Function get_glbYourFieldName() as string '--- or integer, or single, whatever data type.
get_glbYourFieldName = glbYourFieldName
End Function

Now, create a query and in the criteria section - simply enter  "=get_glbYourFieldName()

Now, in the after update event of the textbox, simply assign a value to the public variable glbYourFieldName

glbYourFieldName = "" '-- or 0 if numeric data type
if not isnull(me.txtYourTextBox) then
       glbYourFieldName = me.txtYourText Box
end if

Now, you can run that query FROM ANYWHERE and you will get your expected results.
You can also use this same query from ANY FORM simply by assigning a value to the public variable.

It's a little work (not much) but you can design whole systems around this type of procedure and reuse queries over and over again.

Scott C
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Export to flat file from Access 2003, not using saved spec 9 21
word 2016 1 29
Documents and settings folder 30 76
What's wrong with CopyFromRecordset 6 33
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now