something up with this before update code?

[eggwater]

Okay fellas and lasses please help me out if you can.

I had this code below on the before update event of my form and it works like a dream - creating different flags for different levels of data... howver I want to add a new event that pprompts the user to dsave the record if the make any alterations. (skip through the code to the end.)

Private Sub Form_BeforeUpdate(Cancel As Integer) 'Important for the entire EXPORT function 

    With Me
        
         
        
            If .NewRecord Then
            If IsNull(Room_Number) Or IsNull(AllocatedHall) Or IsNull(Duration) Or IsNull(RType) Then
                .date_stamp = Now()
                .Export_ctrl = "4" 'this code creates a status four based on the above criteria
            Else
            If Not IsNull(Room_Number) And Not IsNull(AllocatedHall) And Not IsNull(Duration) And Not IsNull(RType) Then
            .Export_ctrl = "1" 'this code creates a status 1 based on the above criteria
            .date_stamp = Now()
            End If
            End If
            Else
            If .Export_ctrl = "4" And Not IsNull(Room_Number) And Not IsNull(AllocatedHall) And Not IsNull(Duration) And Not IsNull(RType) Then
            .date_stamp = Now()
            .Export_ctrl = "1" 'this code creates a status 1 based on the above criteria and if a stus 4 already
                        
            Else
            If .Export_ctrl = "3" Then
                .date_stamp = Now()
                .Export_ctrl = "2" 'this code creates a status 2 if amended data is already status 3 ie. exported
            End If
            End If
            End If
            
        
    End With
End Sub

..... so I added this code at the start before the With me part...

Dim Msg, Style, Title, Response, MyString 
MsgBox "Do you wish to save the changes . ", vbOKOnly, "" 
Style = vbYesNo + vbExclamation + vbDefaultButton1 
Title = "Save changes?" 
Response = MsgBox(Msg, Style, Title) 
If Response = vbYes Then 
DoCmd.RunCommand acCmdSaveRecord 

Else: 
Me.Undo 
End If

it appears to run alright until I choose yes to save. then I get a runtime error that says runtime error '2115' - it basically says that the code that I added is preventing the record being saved

why might this be?!

 

[Mile-O]

If you are going to post code like that can you please use the [ code ] [/ code] tags? Indentation is also nice.

 

[IMO]

If MsgBox("Do you wish to save the changes?", vbYesNo, "Save?") = vbYes Then
    DoCmd.RunCommand acCmdSaveRecord
Else
    DoCmd.CancelEvent
End If

 

[Mile-O]

MsgBox "Do you wish to save the changes . ", vbOKOnly, ""

By the way, that message box is wrong. You are asking the user a question where they should be able to say "Yes" or "No" but only offering the option of "OK"

MsgBox "Do you wish to save the changes . ", vbQuestion + vbYesNo

 

[eggwater]

still getting the same error message

see attached image

 

[Mile-O]

What's the validation rule for the field causing the problems?

 

[eggwater]

no validation rule

i don't have any validation rules - just the code that determines whether a record is meeting certain criteria...

 

[eggwater]

on Dirty

i have solved it by using the On Dirty event - it's not ideal but it seems to be the only solution for my meagre programming skills.

thanks

how do you implement the

 tags anyway?

 

[Mile-O]

By having a look at the FAQ

 

[eggwater]

i did that

and it didn't work...

 

[Mile-O]

Put [ CODE] and [/ CODE] on either side of your code (remove the spaces.

Always works for me. :cool:

 

[Mile-O]

Or, when writing a message you can press the button above your reply with the # mark.

 

[eggwater]

ahhh sorry

i wasn't concentrating i just cut and paste what you wrote

dozy bastid that i am

please forgive me this weekend i have mostly been completely wasted at a wedding...

 

[Pat Hartman]

The problem is that you are IN a save action and so you cannot recursively save again. Remove the save from your code:

Dim Msg As String 
Dim Style As String 
Dim Title As String 
Dim MyResponse As Integer
Dim MyString As String 
MsgBox "Do you wish to save the changes . ", vbOKOnly, "" 
Style = vbYesNo + vbExclamation + vbDefaultButton1 
Title = "Save changes?" 
MyResponse = MsgBox(Msg, Style, Title) 
If MyResponse = vbNo Then 
    Cancel = True
    Me.Undo 
End If

If the user chooses not to save, you must cancel the update event to prevent the record from being updated. If he chooses Yes to save, you don't do anything because Access is already in the process of saving the record. That's why this event was executed. It is the LAST event executed prior to a record being saved.

Also:
1. Dim all your variables with specific data types.
2. Do not use reserved words such as Response as variable names.

 

[eggwater]

thanks

that makes much more sense

cheers

eggwater