Solved

Getting info from current record in subform

Posted on 2014-04-23
3
964 Views
Last Modified: 2014-04-24
Dear Experts,

I am trying to retrieve some field information from the active record within a subform, and I am not able to.
I get the runtime error 424 "Object required".  I have tried with getValue, without getValue, etc. but cannot get it right.
I have attached the code and design of the form.
Please advise.

*****Code*******
Option Compare Database

Private Sub Command2_Click()

Set db = CurrentDb()
SubCurrent = Forms!DeleteRecord![DisplayOrder subform].Form.CurrentRecord

'   this next statement returns error.
 
 txtID = Forms!DeleteRecord![DisplayOrder subform].Form.CurrentRecord.Fields.getValue("ItemID")
 
 StrSQL1 = "Delete MainTable.* from MainTable Where ItemID = " & txtID
 
 DoCmd.SetWarnings False
 DoCmd.RunSQL StrSQL1

 ' refresh
 
 Forms![MainTable].[DisplayOrder subform].Requery

End Sub
Untitled.jpg
0
Comment
Question by:yballan
3 Comments
 
LVL 26

Assisted Solution

by:MacroShadow
MacroShadow earned 250 total points
Comment Utility
txtID = Forms!DeleteRecord![DisplayOrder subform].Form.ItemID
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
Comment Utility
The concept of a current record applies ONLY within the confines of that form's class module.  "Me.somecontrol" refers to the current value of somecontrol but ONLY when the code exists/runs in the form's class module.

When you reference form controls from outside of the form's class module, the reference only works if the form is open and the reference is ALWAYS to the FIRST record in the form's recordsource.  You cannot address any other row in the recordsource except the first.

That means that whatever you are doing, MUST be done either with a query (if you know the unique ID of the record you want to retrieve) or within the form where that record is "current".

Keep in mind that it rarely makes sense to show info from a sub form on a main form since you have no way of knowing what sub form record it relates to.  In this case it looks like you have a delete button on the main form that you want to refer to a record in the subform.  (Personally, I NEVER do this.  I consider it too dangerous)  You would need to change the expression around and move it to the Current event of the subform.  That way, each time the subform moves to a new record, you copy the ID from the subform to the hidden control on the main form.

Me.Parent.txtID = Me.ItemID
0
 

Author Closing Comment

by:yballan
Comment Utility
Thank you, MacroShadow and PatHarman,

My code now works, and I have a better understanding of how a subform behaves within a form.
PatHarman, I appreciate your explanation.
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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

771 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

10 Experts available now in Live!

Get 1:1 Help Now