Detecting the highlighted record in an Access table

[Sheldon]

I'm familiar with getting VBA to select a particular record in an Access table, highlighting it as the current record.

But I'm at a loss to do the reverse: if the user manually selects (highlights) a record in an open Access table, how does the VBA program detect which record has been highlighted? Might there be a way, say, to use the indicated record number at the lower navigation bar that contains pointers to the next record, last record, etc.?

Any such help would be appreciated!

 

[moke123]

Users should not have direct access to a table.
Are you talking about a subform or datasheet?

 

[Sheldon]

Users should not have direct access to a table.
Are you talking about a subform or datasheet?

The closest Excel equivalent would be a datasheet, but first and foremost, it's an open Access table where the user manually highlights a record. The code is in a macro that is clicked on by the user above the open table after a record has been highlighted.

 

[moke123]

A subform can be viewed in datasheet view.

You just need to reference the correct path to the record.

Me.YourSubForm.Form.YourField

http://access.mvps.org/access/forms/frm0031.htm

I dont use macros so the syntax may be a little different ie. Forms!Mainform!Subform1.Form!ControlName

 

[theDBguy]

Hi @Sheldon. Welcome to AWF!

Can you post a screenshot showing your open table with the button above it?

Also, I thought you said you wanted to find out the VBA code that is the reverse of the VBA code you already have. Why is the button using a macro? Just curious...

 

[Sheldon]

Here it is - the macro in question is the fourth one on the right:

 

[Sheldon]

When the macro is clicked on, the Phone 2 field of the selected record (the first one) is supposed to become populated, for instance. Note that at the bottom left, the highlighted record shows up as "1 of 4". Is there any VBA command that can reference that particular data?

 

[theDBguy]

When the macro is clicked on, the Phone 2 field of the selected record (the first one) is supposed to become populated, for instance. Note that at the bottom left, the highlighted record shows up as "1 of 4". Is there any VBA command that can reference that particular data?

Hi. Thanks for the additional information. No guarantees, but you could try the following.

Screen.ActiveDatasheet.CurrentRecord

Hope that helps...

 

[Pat Hartman]

Any code running in the class module of a form operates on the CURRENT record which is the one that is highlighted. Running code from a different place on an open form should also run for the Current record.

 

[Sheldon]

Hi. Thanks for the additional information. No guarantees, but you could try the following.

Screen.ActiveDatasheet.CurrentRecord

Hope that helps...

Awesome! Not only did that work, but Screen.ActiveDatasheet.[Phone 1] pulled the contents of the Phone 1 field of the highlighted record of the open table (datasheet view, of course)

 

[theDBguy]

Awesome! Not only did that work, but Screen.ActiveDatasheet.[Phone 1] pulled the contents of the Phone 1 field of the highlighted record of the open table (datasheet view, of course)

Hi. Glad to hear you got it to work. Good luck with your project.

 

[Pat Hartman]

Awesome! Not only did that work, but Screen.ActiveDatasheet.[Phone 1] pulled the contents of the Phone 1 field of the highlighted record of the open table (datasheet view, of course)

Be careful with this. I have no idea where your code is running but you are playing pin the tail on the donkey with commands like this. Access may think a different record is current than you do.

Code that modifies a record should be running IN the class module of the form where the record is CURRENT OR the record should be modified as a result of a query or a DAO code loop. What you are doing is very dangerous.

 

[arnelgp]

as warned Data Macro is Global.
meaning you can't turn it off (unless you Erase it on Table design).
so it will still be in effect even when you are using Forms.