Bound form
I am trying to fully understand how bound forms work (VBA in Access XP)
I have a form bound to a query, but I need to "redefine" the query at execution time so I use
set mrst=dbs.openrecordset( ....whatever SQL clause ....) ' module variable
set me.recordset=mrst
me.requery
It seems to work up to here.....
But,,... the Form Before Update and AfterUpdate events never fire (they used to fire until to yesterday!!). The Form_BeforeUpdate event would allow the user to control if changes in data are acceptable or not, in which case the Cancel=True would be issued in the Before Update event.
Additionally, I am trying to use custom navigation buttons (next, previous, first, last) to allow navigation.
I imagine they should contain code like this
private sub next_click()
' mrst is a module variable so it is visible in all subs in the form
mrst.movenext
me.bookmark=mrst.bookmark
end sub
Well, nothing of this works, and I would like to get advice in order to use bound forms and work properly, with the 3 features I mentioned:
custom navigation buttons
form beforeUpdate event
form recordsource built at execution time
thanks
[1221 byte] By [
amc] at [2008-2-4]
Per one of our support engineers:
Base on my understanding, our buddy wants to change the data source dynamically. But he met some problem on firing BeforeUpdate and AfterUpdate events after that. If there’s any misunderstanding, please inform me ASAP.
Here’s the code snippet. I added some comments inline.
=================
Option Compare Database
Private Sub First_Click()
On Error GoTo Err_First_Click
`Leverage DoCmd here
DoCmd.GoToRecord , , acFirst
Exit_First_Click:
Exit Sub
Err_First_Click:
MsgBox Err.Description
Resume Exit_First_Click
End Sub
Private Sub btLast_Click()
On Error GoTo Err_btLast_Click
DoCmd.GoToRecord , , acLast
Exit_btLast_Click:
Exit Sub
Err_btLast_Click:
MsgBox Err.Description
Resume Exit_btLast_Click
End Sub
Private Sub btPrev_Click()
On Error GoTo Err_btPrev_Click
DoCmd.GoToRecord , , acPrevious
Exit_btPrev_Click:
Exit Sub
Err_btPrev_Click:
MsgBox Err.Description
Resume Exit_btPrev_Click
End Sub
Private Sub btNext_Click()
On Error GoTo Err_btNext_Click
DoCmd.GoToRecord , , acNext
Exit_btNext_Click:
Exit Sub
Err_btNext_Click:
MsgBox Err.Description
Resume Exit_btNext_Click
End Sub
Private Sub Form_AfterUpdate()
MsgBox ("AfterUpdate fired!!") ‘Fire AfterUpdate event
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
MsgBox ("BeforeUpdate fired!!") ‘Fire BeforeUpdate event
End Sub
Private Sub Form_Load()
‘Change the datasource in Onload Event
Set Me.Recordset = CurrentDb.OpenRecordset("SELECT * FROM EMPLOYEES WHERE COUNTRY='USA'")
End Sub
========================
Here’s my repro step
1. Open Northwind sample database and create a bind form which bind to Employees table.
2. Change the datasource property in onload event
3. Modify some fields and click ‘Next’ button
4. BeforeUpdate event fired
5. Navigate to next record
6. AfterUpdate event fired.
I can successfully capture BeforeUpdate & AfterUpdate event on my local machine. Please have a try. Anything unclear, please feel free to let me know. Thanks.
Regards, Ming
hope this helps,
-brenda (ISV Buddy Team)
