How to find record number in Subform 3

[ronphx]

I have a form with a subform that is linked to a table (tbltempdet). I would like to have the user click a line (record) in the subform, then find out what record the user clicked. The navigation buttons show the record number, but I would like to capture it in code because I need to perform certain steps.

Any ideas?

 

[RoyVidar]

Try for instance the on current event of the subform, either me.currentrecord or me.recordset.absoluteposition+1.

Roy-Vidar

 

[ronphx]

This may be posted twice, but I don't think my previous post went through. the me.currentrecord worked fine. However, I am still having trouble. What I would like to do is to have the user select a record in the subform, then click a command button in the main form to delete that record (I don't want the user to right click the record and delete it because at times the program needs to delete more than one record automatically). Do you know how I could have the command button in the main form delete the selected record?

Thanks.

 

[RoyVidar]

You probably have a unique filed in the subform? A Primary Key (PK) of one or more fields? An ADO method for a one field numeric PK could be:

[tt]dim sSql as string
sSql="delete * from mytable where PKfield=" & _
me("subformcontrolname").form("txtcontrolholdingPK").value
currentproject.connection.execute sSql
me("subformcontrolname").form.requery[/tt]

Roy-Vidar

 

[ronphx]

I see what you are saying. I'm using DAO, but I still get the idea. I'm still frustrated by another problem that should be simple. I would like to simply go to the selected record from the main form. I've tried

docmd.gotorecord acdataform "frminvoicedetail",acgoto,3

where frminvoicedetail is the name of the main form, and 3 is just a number I picked to see if I could go to the third record (which does exist in the subform). I get an error message saying "you can't go to the specified record". When I type in the name of the subform in the docmd, I get an error message telling me the subform isn't open. I'm wondering if I'm typing in the wrong name for the subform. Do you know what I'm doing wrong? Also, how do I find out the name of a subform so I can refer to it?

 

[PHV]

HAve you tried to set focus on the subform and only then call the GoToRecord without form name ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

To use the gotorecord method of the docmd object, I think the object where you wish to perform the method on, needs to have focus, so prior to the command, try somethin like this:

[tt]me("subformcontrolname").setfocus
docmd.gotorecord,,acgoto,3[/tt]

- and yes, the issue might also very well be that the subform control name (which is used in referencing) might differ from the name of the subform as viewed in the database window. Easiest way of obtaining the correct reference, in my view, is to enter the control source of a control residing in the form where you wish to invoke the reference, use the expression builder, double click thru forms, loaded forms, main form, subform and a control on the subform. Here's a link on How to refer to a control on a subform or subreport in Access 2000

The DAO method for action queries: currentdb.execute sSql, else the docmd.runsql sSql also works (except you'll need to turn the warnings on and off).

Roy-Vidar

 

[ronphx]

Thank you. The link you sent me was extremely helpful. I still had trouble getting the docmd to work even after I shifted focus to the subform, but what I did do after I had focus on the subform was to open a new recordset, do a movefirst, then use the captured current record number and the move method to get to the current record. It worked fine.

Thanks for your time and ideas. They helped me greatly. (Also thanks for the action query code).