• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 56
  • Last Modified:

Error 438"Object Doesn't Support This Property or Method"

I have code on a save button as i only want the record to save when the save button is clicked, I am getting  Error 438"Object Doesn't Support This Property or Method" I tried defining the button as Boolean in a module but no Joy.  

Form Code
Option Compare Database

Private Sub btnSave_Click()
btnSave = True
DoCmd.RunCommand acCmdSaveRecord
End Sub

Private Sub Form_AfterUpdate()
btnSave = False
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If btnSave = True Then
Else
If MsgBox("You did not press the save button. Press 'Yes' to Save and 'No' to Cancel and remove all changes.", vbYesNo) = vbNo Then
Me.Undo
Cancel = True
Exit Sub
End If
End If
End Sub

Private Sub Form_Current()
btnSave = False
End Sub

Private Sub Form_Unload(Cancel As Integer)
If btnSave = True Then
Cancel = True
Exit Sub
End If
End Sub
 

Open in new window


Module Code
Option Compare Database

Dim btnSave As Boolean

Open in new window


error
0
skull52
Asked:
skull52
  • 11
  • 4
  • 3
  • +1
4 Solutions
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You have
Private Sub btnSave_Click() ... a Button named btnSave

and you have
Dim btnSave As Boolean    
a variable with the same name.  These are conflicting.

I suggest changing this to

Dim fSave As Boolean

You have several places to change it then in your code.

OR ... change the Button name
0
 
PatHartmanCommented:
i only want the record to save when the save button is clicked,
Your validation code needs to go into the Form's BeforeUpdate event.  If the data is not valid, you would cancel the  event to prevent the data from being saved.

The problem with your approach is that Access takes personal responsibility for saving data.  It is its mission in life to make sure that data never goes unsaved.  The key to controlling this is knowing how to use the Form's BeforeUpdate event.  Think of it as the flapper at the bottom of a funnel.  You let the good data through to be saved but keep the bad data locked up.  No matter what action causes a save, the action passes through the Forms BeforeUpdate event.  That is where you need to control the process.  Your save button should only issue the save command.  Everything else needs to move to the BeforeUpdate event and be modified to cancel the update.

I see that you have Cancel  = True in your code.  That is the correct way to cancel the save but you are using the wrong event.  You MUST move this code to the Form's BeforeUpdate event.  Your button click has nothing to do with controlling what gets saved or not and it has no cancel property.  Look at the procedure heading for the BeforeUpdate event.  You will see that it has a Cancel property.  THAT is what Cancel = True is setting and THAT is what is telling Access to abandon the save.  Using the Me.Undo cleans up the form and makes it not dirty so Access won't keep trying to save it.

PS - you also need to define btnSave in the header of the form's class module so that all the events of the form can see it.
0
 
Dale FyeCommented:
in addition to the other comments, you might consider adding a Cancel button to your form if you don't already have one.  Then, using Joe's idea of changing your boolean value to bSaveClicked, modify your BeforeUpdate event something like:
Private Sub Form_BeforeUpdate(Cancel As Integer)

    If bSaveClicked = True Then
        Cancel = false
    Else
        MsgBox("You did not press the save button. Press 'Save' or 'Cancel' to exit this record
       Cancel = True
    End IF

Exit Sub

Open in new window

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
skull52Author Commented:
can you give me an example of hoy to assign the Dim fSave As Boolean to work with the save button
0
 
Dale FyeCommented:
Joe's recommendation regarding the fSave, and mine regarding bSaveClicked are basically the same

1.  At the top of your code, after the Option Compare Database line, you need another line;
Option Explicit

Open in new window

This will force all variables to be checked and declared in your code.  The easiest way to do this is to go to the VBA window, click on Tools => Options, and check "Require Variable Declarations"
This will cause Access to automatically add Option Explicit to all new code modules as well as form and report code modules.

Right below that, add a line as below.  This will make that variable private but available to all procedures behind the form.
Private bSaveClicked as Boolean

Open in new window

The default value of this variable will be False

2.  Then in the Click event of btnSave change that value bSaveClicked to True
3.  Use the code I provided in the Form_BeforeUpdate event to prevent the record from being saved
4.  And like you already have, change the value of bSaveClicked back to False
5.  If you added a Cancel button to your form, as I suggested, then that is where you would use the me.Undo command, which will clear any changes to the forms recordset and undirty the form.
0
 
skull52Author Commented:
OK, here is my updated code, if i enter a value in the form and click the save button it fires the message that I did not save the record if I click yes it saves the record, if I click No it throws a 2501 run-time error on "DoCmd.RunCommand acCmdSaveRecord"  . if I try and close the form without saving the message also fires. if i select no to save it clears the form but pops a "You Can't Save this record at this time" message
Main Code
Option Compare Database
Private Sub btnSave_Click()
bSaveClicked = True
DoCmd.RunCommand acCmdSaveRecord
End Sub

Private Sub Form_AfterUpdate()
bSaveClicked = False
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
  If bSaveClicked = True Then
        Cancel = False
    Else
       If MsgBox("You did not press the save button. Press 'Yes' to Save and 'No' to Cancel and remove all changes.", vbYesNo) = vbNo Then
       Me.Undo
       Cancel = True
       Exit Sub
    End If
End If
End Sub

'Private Sub Form_Current()
'bSaveClicked = False
'End Sub

'Private Sub Form_Unload(Cancel As Integer)
'If bSaveClicked = False Then
'Cancel = True
'Exit Sub
'End If
'End Sub

Open in new window


Module Code
Option Compare Database
Option Explicit
'Default Value is False
Private bSaveClicked As Boolean

Open in new window

0
 
Dale FyeCommented:
Try this:
Private Sub Form_BeforeUpdate(Cancel As Integer)

    If bSaveClicked = False Then
        MsgBox("Click 'Save' or 'Cancel' to exit this form!")
        Cancel = True
    End If

End Sub

Open in new window

Then add a Cancel button to your form:
Private Sub cmdCancel_Click

    me.undo
   
End sub

Open in new window

0
 
skull52Author Commented:
ok now when I click save i get noe current record and run-time error 3021
0
 
skull52Author Commented:
and it still fires the message to save
0
 
skull52Author Commented:
Still need some help, code not working
0
 
PatHartmanCommented:
Change this
Option Compare Database
Private Sub btnSave_Click()

Open in new window

To:
Option Compare Database
Option Explicit
Public bSaveClicked AS Boolean
Private Sub btnSave_Click()

Open in new window


It is important to force variable declaration so that errors can be found at compile time. When you don't force variables to be defined, typos will get you every time.  You mis type something and you think you'vd set bSaveClicked and what you have set is bSaveClickd

The Option Explicit needs to go in the header of ALL code modules and you should set it to be the default so Access will add it whenever you create a new module.  Fix any compile errors that this generates.

This procedure needs error trapping because if the update is cancelled in the BeforeUpdate event, the error will rise to this procedure where you need to handle it.
Private Sub btnSave_Click()
On Error GoTo ErrProc
    bSaveClicked = True
    DoCmd.RunCommand acCmdSaveRecord
ExitProc:
    ExitSub
ErrProc:
    Select Case Err.Number
        Case 2501, 3201
            Resume ExitProc
        Else
            Resume ExitProc
    End Select
End Sub

Open in new window

0
 
skull52Author Commented:
Pat do you mean Change that code on the Module
Option Compare Database
Option Explicit
Public bSaveClicked AS Boolean
Private Sub btnSave_Click()
0
 
PatHartmanCommented:
The two lines of code in the first snip need to have two lines inserted between them as in the second snip.

Once you  are sure there are no compile errors, we can continue searching for the problem.  Don't forget to set

Option Explicit

In ALL code modules.  Not just this one
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
As far as I can tell, what I posted originally should ... solve your initial problem

I suggested you change

Option Compare Database

Dim btnSave As Boolean

to

Option Compare Database

Public fSave As Boolean  'I use f to represent Boolean. I only use one character prefixes (b is for Byte - just my preference)

and then in your code in all places (except Private Sub btnSave_Click()  ) where you have btnSave ... change that to fSave
0
 
skull52Author Commented:
Pat I only have one Module, is that where i add the code
0
 
skull52Author Commented:
I have changed this code at least 5 times times I am now completely lost, I don't know where to put any of this code. I am going back to the original and go from there
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well .... again ... as I see it, you have conflicting name

Private Sub btnSave_Click()
which is a Sub Procedure

and

Option Compare Database
Dim btnSave As Boolean
which is a variable definition

and later in the Current Event

you have

btnSave = False
and you get this error  Option Compare Database
438"Object Doesn't Support This Property or Method"

And apparently Access thinks you are trying to set the Sub btnSave_Click()  to False ... which is not possible ... hence the Error 438
0
 
skull52Author Commented:
Ok, here is what I have now. If I click save it fires the ("You did not press the save button. Press 'Yes' to Save and 'No' to Cancel and remove all changes.") message, If  I click Yes it saves the record, but it shouldn't fire when I click save. If I click No it fires the 3021 Run-time error on the DoCmd.RunCommand acCmdSaveRecord of the save button click event. Pat I tried yor error trapping  but it won't compile because the ELSE does not have an IF statement
Private Sub btnSave_Click()
On Error GoTo ErrProc
    bSaveClicked = True
    DoCmd.RunCommand acCmdSaveRecord
ExitProc:
    ExitSub
ErrProc:
    Select Case Err.Number
        Case 2501, 3201
            Resume ExitProc
        Else
            Resume ExitProc
    End Select
End Sub

Open in new window



Main Code
Option Compare Database
Private Sub btnCancel_Click()
Me.Undo
End Sub
Private Sub btnSave_Click()
bSaveClicked = True
DoCmd.RunCommand acCmdSaveRecord

End Sub

Private Sub Form_AfterUpdate()
bSaveClicked = False
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
 If bSaveClicked = True Then
        Cancel = False
    Else
       If MsgBox("You did not press the save button. Press 'Yes' to Save and 'No' to Cancel and remove all changes.", vbYesNo) = vbNo Then
'       Me.Undo
       Cancel = True
       Exit Sub
    End If
End If

End Sub
Private Sub Form_Current()
bSaveClicked = False
End Sub

Open in new window


Module
Option Compare Database
Option Explicit
'Default Value is False
Private bSaveClicked As Boolean

Open in new window

0
 
skull52Author Commented:
Ok where I am getting confused with regard to declaring a boolean variable do I do that in the main module or create a separate Module
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You can do it in the Form Module ... which I guess you are referring to 'main module' ?
OR
You can do it in a separate Module, although not necessary.  If you do, be sure you Declare it as Public

Is it possible you can upload this db removing any sensitive data ?
0
 
skull52Author Commented:
I finally got it to work, my brain was fuzzy by the end of the day.  I moved declaring a boolean variable from the Form Module to a separate  Module but it was still declared as Private so after reading Joe's post, thanks Joe, I looked at it and changed it to Public, and it worked. This has been a patchwork of everyone's suggestions, thanks all for the help.

New Code
Option Compare Database
Private Sub btnCancel_Click()
Me.Undo
End Sub

Private Sub btnSave_Click()
bSaveClicked = True
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub Form_AfterUpdate()
bSaveClicked = False
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If bSaveClicked = True Then
        Cancel = False
    Else
       If MsgBox("You did not press the save button. Press 'Yes' to Save and 'No' to Cancel and remove all changes.", vbYesNo) = vbNo Then
      Me.Undo
       Cancel = True
       Exit Sub
    End If
End If

End Sub
Private Sub Form_Current()
bSaveClicked = False
End Sub

Open in new window

Option Compare Database
Option Explicit
'Default Value is False
Public bSaveClicked As Boolean

Open in new window

1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 11
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now