Solved

Getting info from current record in subform

Posted on 2014-04-23
3
1,028 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 27

Assisted Solution

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

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

829 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