Updating Query When Saving Form

[RyanB]

Hi All,

I have a form where a user reviews a request or declines it, they have a approve and decline button to choose from.

The results that are shown to them are from a query which breaks it up into requests that come under there control.

What I want is for when they click approve or decline for the form to refresh the query so basically it will get rid of the request that they just viewed.

Currently I have it coded so that when the approval is put through it just goes to the next record (which is not ideal) and comes to an error when it runs out of records to view as the form does not allow additions.

My code is posted below (keep in mind I haven't coded the delcine function yet)

Option Compare Database

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String, strTitle As String

strMsg = "Do You Want To Save This Record?"
strTitle = " Save Record ?"

If MsgBox(strMsg, vbQuestion + vbYesNo, strTitle) = vbNo Then
Me.Undo

End If

End Sub
Private Sub ButApprove_Click()
On Error GoTo Err_ButApprove_Click

SuperApprove.Value = True

DoCmd.GoToRecord , , acNext

Exit_ButApprove_Click:
Exit Sub

Err_ButApprove_Click:
MsgBox Err.Description
Resume Exit_ButApprove_Click

End Sub

Can anyone help??

Cheers,

MB

 

[WayneRyan]

Ryan,

In your BeforeUpdate event, if they respond No, you should issue a
Me.Cancel = True

In your Approve button code,

when you automatically go to the Next Record, your can put:

DoCmd.SetWarnings False
DoCmd.GoToRecord , , acNext
DoCmd.SetWarnings True

But, why must can't they move to the next record themselves.

Wayne

 

[RyanB]

Thanks for the reply

I want to keep some order to the approval process so would rather they finish with there first record before moving to the next record...

So my theory is once they have approved/declined the record won't be vaild in the query (as the query finds unapproved records only) so if it updates the query it will be like moving to the next record as the record they just viewed won't be in the query anymore.

I'll have a go at what you have said and let you know how i go!

Thanks again

 

[RyanB]

Just an update:

The code works nicely for the approval button, but I can't quite understand where to put the other code you described.

Couple of things tho, with the approval button it has an error when there isn't another form to goto, is it possible for it to just close the form and display a textbox with something like 'There are no more requests awaiting your approval'???

Also when you use the scroll button on the mouse it will still scroll upto the completed records, is there a way to disable this, as I keep doing so by accident.

Thanks again,

Ryan.

 

[WayneRyan]

Ryan,

When you leave Access's native abilities (forcing the user to a new record),
you can leave it to Access to inform you (those silly messages), OR YOU can
do a bit of work:

Clone Recordset,
Move to Next record,
See (in VBA) that it's EOF and display and do what you want.

Personally, I don't think it's worth the effort, but ... post back if you want.

As for the Mouse's scroll wheel, you can use the Search Facility here and look
for "Scroll" and you will find the answers.

Wayne

 

[RyanB]

Thanks again Wayne, the scroll thing works fine now

Not quite sure what you mean about the clone recordset tho, I had a look in the help files, but couldn't make heads or tails of it.

Cheers,

Ryan

 

[WayneRyan]

Ryan,

When Access is traversing a recordset (your form's records), it doesn't
know that it's at the end until it gets there. You can shut warnings
off prior to that, but then the user sees nothing.

You can "clone" your recordset, and move through it, Since YOU (not Access)
is moving through it, you can trap the rst.EOF (end-of-file) and present
your own message to the user (or do whatever).

Personally, I don't think that this is worth all of the trouble. Let the
user navigate through the records.

Wayne