Solved

Getting info from current record in subform

Posted on 2014-04-23
3
1,046 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 250 total points
ID: 40017875
txtID = Forms!DeleteRecord![DisplayOrder subform].Form.ItemID
0
 
LVL 37

Accepted Solution

by:
PatHartman earned 250 total points
ID: 40018362
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
ID: 40020576
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

734 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