Solved

ms access - Subform Referencing

Posted on 2014-11-29
10
145 Views
Last Modified: 2015-01-15
Hi, been experiencing difficulties referencing the value on the subform on the attached snapshot.  

In order to ensure I had used the correct syntax, simply wanted the value of the 'PrimaryID' field on the subform named 'SpecificActivitiesHazardsFORM' to appear in a MsgBox  

Resultant error keeps popping up upon clicking Command_65.  Code also pictured.  

Can anyone help with this issue?
Untitled.png
0
Comment
Question by:padrepio2
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 40471908
Me!Subformname.Form!controlname.value is the syntax
! after .Form
0
 

Author Comment

by:padrepio2
ID: 40471915
Still same error message regardless of ! or .
0
 
LVL 18

Assisted Solution

by:SimonAdept
SimonAdept earned 500 total points
ID: 40471942
Check that the control name of the subform (on the main form) is actually the same as the name of the subform object itself. It is the former name that you should be using to refer to it. They are not necessarily the same.

See also http://support.microsoft.com/kb/113352
It is also important to note that when you are referring to controls on a subform, you must specify the value contained in the ControlName property of the subform control. The ControlName property should not be confused with the SourceObject property for a subform control. The SourceObject property is used to indicate which subform to use in the subform control. The ControlName property is used to specify the name by which the subform control is referenced. ControlName is typically set the same as the SourceObject, but it does not have to be.
i.e. Switch to design mode on your main form and click the edge of the subform container, then check the control name in the properties. If it is not "SpecificActivitiesHazardsFORM" you'll have found the cause.

Final thought: fix the error on your main form the '#Size!' error which looks like a calculated field. Probably unrelated, but errors in the main form muddy the waters.
0
 

Author Comment

by:padrepio2
ID: 40471957
SpecificActivitiesHazardsFORM is the name when I select the subform.  Was aware of this potential issue through my research into the error.


Removed the control with the '#Size!' error but made no difference

Thanks for trying
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40471988
Before the message box, try

On error resume.next

The reason is this sub may be running when the form is not ready to be worked with. This just shot in the dark.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 18

Accepted Solution

by:
SimonAdept earned 500 total points
ID: 40472000
Hmm. You've probably tried this already..
1. Compact and repair your db then try again

2. - check the project references in the VBE Tools menu. I've had problems with objects that are defined in more than one DLL (e.g. format function. "Form" may well be another one that is defined in more than one library and has different properties/methods depending on which DLL is taking precedence). The order of the references can be critical, and odd problems can be resolved by unchecking and re-adding references. Most Access DBs will have no more than 4 or 5 references unless interacting with some industry-specific components.

2. Check that the control on the subform has control name PrimaryID (as opposed to txtPrimaryID or something else).
3. Create a tiny new form with a single unbound control on it and drop that on main form as a subform and try to refererence it
4. Try debug.print instead of msgbox and try assigning the control value to a variable of type variant first

If still stuck after the above, please provide some further details to help us...
Are both your main and subforms bound to tables/queries?
Is there a parent/child relationship set up between the two forms?  
Were the forms created using the forms wizard?
Access and operating system version?
Is it a new database or converted from an older version?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40472006
Dumb question.
You actually have a control with the property id on the form?

Can you use the correct syntax to refer to ANY control on the subform, or it fails for them all?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40472193
Sorry, my bad
Me!Subformname.Form!controlname.value is not right
Me.SubformControlName.Form!ControlName.Value

Working sample of the syntax attached
SubformReference.mdb
0
 
LVL 84
ID: 40472434
Here's a reference for this sort of thing:

http://access.mvps.org/access/forms/frm0031.htm
0
 

Author Closing Comment

by:padrepio2
ID: 40551517
Decided to work around issue although got closer to complete solution with this answer
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

12 Experts available now in Live!

Get 1:1 Help Now