Record Selectors and VBA - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Record Selectors and VBA

Author

Topic

JoeJaz
Starting Member

5 Posts

Posted - 2004-08-13 : 15:00:03

Hi,
I have built an MS Access form with record selectors on the bottom that I use to navigate between records accessed by that form. Each time I move between records within that form, I want a VBA script to update some information on the form. However, as far as I can tell, I can't find a method in VBA (for the form) that executes the script every time I hit a record selector button. I have tried, without success, the following Form methods in hopes that one of them will trigger the appropriate event: AfterUpdate, Query, SelectionChange, ViewChange, DataSetChange, DataChange, and a variety of others. I have even tried executing the script on the Change event of one of the text boxes that is bound to a table field. I have not had any luck and this ability is very important to what I am trying to do. If anyone has any ideas of how I might trigger an event after using a record selector button, I would be very appreciative if you were to share you knowledge with me. Thank you for reading this. Have a nice day,
Joe

Sam Freeman
Starting Member

8 Posts

Posted - 2004-08-13 : 16:51:35

The OnCurrent Event will work, but maybe more than you want. It will fire everytime you change from one record to another, including when the form opens. You may be able to avoid the first firing when the form opens by writing code that will detect the form opening.

In fact, try this code behind the form in question:

Option Compare Database
Option Explicit
Dim fFlag As Boolean 'set up the flag - defaults to false

Private Sub Form_Current()
If fFlag = True Then MsgBox "hi" 'your code to run when event fires
fFlag = True 'set it to true but only after form opens
End Sub

Private Sub Form_Open(Cancel As Integer)
fFlag = False 'even tho defaults to false - let's make sure
End Sub

JoeJaz
Starting Member

5 Posts

Posted - 2004-08-13 : 17:25:39

Thank you so much for your response. Unfortunatly, I do not see an OnCurrent event listed for the form. Perhaps I am looking under the wrong object? Maybe I don't have the correct activeX plugins selected? I'm not sure why that isn't showing up. Any ideas? Thanks again for your input.
Joe

Sam Freeman
Starting Member

8 Posts

Posted - 2004-08-13 : 17:35:53

OnCurrent should be the first event listed.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-13 : 23:02:16

make sure you have the whole form selected as an object, and not the detail section or the header or some other control.

- Jeff

vux
Starting Member

45 Posts

Posted - 2004-09-03 : 04:31:43

I think I had a similar problem.

For me this worked.

Open the form in design view

right click on the textbox which should be refreshed each time the user moves on it, choose properties

then choose Event

open the VB Editor by clicking "..." button on the right (first click into the blank field)

then use the REQUERY function, it requeries the SQL used in DataSource

my examle code:

Private Sub Sec_Enter()
Sec.Requery
End Sub

hope that helped

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources