Solved

Getting info from current record in subform

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

685 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