Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Getting info from current record in subform

Posted on 2014-04-23
3
Medium Priority
?
1,072 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 1000 total points
ID: 40017875
txtID = Forms!DeleteRecord![DisplayOrder subform].Form.ItemID
0
 
LVL 39

Accepted Solution

by:
PatHartman earned 1000 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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 …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

722 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