Error: The Command or Action 'SaveRecord' isn't available now

[parth00]

Hi All,
I am getting this Error " The Command or Action 'SaveRecord' isn't available now"

I am using one of those Ready Made Save Button (Drag and Drop button) that Access Provides. I made small change to "Save Button" Code to call two functions for data validation before saving the current Record.


Here is the Code...

Private Sub Command61_Click()
On Error GoTo Err_Command61_Click

If Check_Data Then
If Project_Status_Tracking Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Insert_Project_CheckPoint
End If
End If

Exit_Command61_Click:
Exit Sub

Err_Command61_Click:
MsgBox Err.Description
Resume Exit_Command61_Click

End Sub

After executing -- "DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70" - it prompts error.

Both functions Check_Data and Project_Status_Tracking opens Recordset.. Opening a Recordset is the Reason for Error?

Can any one suggets how should I fix it.

Thanks

 

[RuralGuy]

Unless someone is just plain lucky, you will probably need to post the code for both Check_Data and Project_Status_Tracking functions before you get a reasonable answer.

 

[parth00]

Here is the Code

Public Function Project_Status_Tracking() As Boolean

Dim Assigned_Project_Status As String
Dim DB_Project_Status As String
Dim SSQL As String
Dim SSQL1 As String
Dim Project_ID As Integer
Dim RecordCount1 As Integer
Dim RecordCount2 As Integer

Project_ID = Oppt_ID_Text
Assigned_Project_Status = Status

SSQL = "Select Status from Sales_Pipeline_Current where Oppt_Id =" & Project_ID & ";"
SSQL1 = "Select Oppt_Id from Sales_Pipeline_Current where Oppt_Id = " & Project_ID & ";"

Set db = CurrentDb()
Set rs1 = db.OpenRecordSet(SSQL)
Set rs2 = db.OpenRecordSet(SSQL1)
RecordCount1 = rs1.RecordCount
RecordCount2 = rs2.RecordCount

If RecordCount1 = 0 And RecordCount2 = 0 Then
'New Record
Project_Status_Tracking = True

ElseIf RecordCount1 > 0 And RecordCount2 > 0 Then
DB_Project_Status = rs1.fields("Status")

If Assigned_Project_Status = DB_Project_Status Then
'Not Changing Project Status
Project_Status_Tracking = True
Else

Select Case Assigned_Project_Status

Case "Pre"
If Proposal_Text = "No" Then
MsgBox "Error Setting Project Status to PRE: Project Proposal Missing"
Project_Status_Tracking = False
Else
Project_Status_Tracking = True
End If

Case "Post"
If DB_Project_Status = "Pre-Pre" Or DB_Project_Status = "Pre" Then
If Proposal_Text = "No" Then
MsgBox "Error Setting Project Status to POST: Project Proposal Missing"
Project_Status_Tracking = False
Else
Project_Status_Tracking = True
End If
Else
MsgBox "Error Setting Project Status to Post: Project Proposal Missing"
Project_Status_Tracking = Flase
End If

Case "Verbal"
If DB_Project_Status = "Post" Then
If Proposal_Text = "No" Then
MsgBox "Error Setting Project Status to Verbal: Project Proposal Missing"
Project_Status_Tracking = False
Else
Project_Status_Tracking = True
End If
Else
MsgBox "Error Setting Project Status to Verbal: Wrong Porject Flow"
Project_Status_Tracking = False
End If

Case "Won"
If DB_Project_Status = "Post" Or DB_Project_Status = "Verbal" Then
If Folder_Text = "No" Or SOW_Text = "No" Then
MsgBox "Error Setting Project Status to WON: SOW or Intraspect Folder Missing"
'Exit Function
Project_Status_Tracking = False
Else
Project_Status_Tracking = True
End If
Else
MsgBox "Error Setting Project Status to WON: Wrong Porject Flow"
'Exit Function
Project_Status_Tracking = False
End If

Case "WIP"
If DB_Project_Status = "Won" Then
If Folder_Text = "No" Or SOW_Text = "No" Then
MsgBox "Error Setting Project Status to WON: SOW or Intraspect Folder Missing"
'Exit Function
Project_Status_Tracking = False
Else
Project_Status_Tracking = True
End If
Else
MsgBox "Error Setting Project Status to WIP: Wrong Porject Flow"
'Exit Function
Project_Status_Tracking = False
End If

Case "Complete"
If DB_Project_Status = "WIP" Then
If Delieverable_Text = "No" Or Quals_Text = "No" Then
MsgBox "Error Setting Project Status to Complete: Deliverables Or Quals Missing "
'Exit Function
Project_Status_Tracking = False
Else
Project_Status_Tracking = True
End If
Else
MsgBox "Error Setting Project Status to Complete: Wrong Porject Flow"
'Exit Function
Project_Status_Tracking = False
End If

Case "Close"
If DB_Project_Status = "WIP" Then
If Delieverable_Text = "No" Or Quals_Text = "No" Then
MsgBox "Error Setting Project Status to Complete: Deliverables Or Quals Missing "
'Exit Function
Project_Status_Tracking = False
Else
Project_Status_Tracking = True
End If
Else
MsgBox "Error Setting Project Status to Close: Wrong Porject Flow"
'Exit Function
Project_Status_Tracking = False
End If

End Select

End If

End If


Private Function Check_Data() As Boolean

If IsNull(Oppt_ID_Text) Then
MsgBox "Project Id not assigned "
Check_Data = False
Exit Function
ElseIf IsNull(Status) Then
MsgBox "Please Assign Project Status"
Check_Data = False
Exit Function
ElseIf IsNull(Project_Name) Then
MsgBox "Please Assign Project Name"
Check_Data = False
Exit Function
Else
Check_Data = True
End If

End Function

Thanks in Advance...

 

[RuralGuy]

While I am formatting your code so I can read it how about putting:
Option Compare Database
Option Explicit
at the top of your form's code module and recompile and see what turns up.

 

[parth00]

Not Working....
I am getting error on this line
Set db = CurrentDb()

Error Message : "Variable not defined"

 

[RuralGuy]

Option Explicit insists that all variables be defined before they are used. In this case you have not defined db yet. You have several of these types of problems and a misspelled false as well. Make whatever corrections are required and see if the problem you were having goes away.

 

[parth00]

Dim rs As Recordset
Dim db As Database

Give Error : User Defined type not defined....
Any clue?

I even Tried
Dim rs As DAO.Recordset
Dim db As DAO.Database

 

[RuralGuy]

You are using DAO and there is probably no reference set to DAO. From any code page go to Tools>References and put a check next to Microsoft DAO 3.6 Object Library. Then specify DAO when you define your variables. Is this a new mdb you have been making or one you inherited and are working on? If it is a new one from you then you could turn off ADO in the references and eliminate the need for disambiguation. Uncheck Microsoft ActiveX Data Objects 2.x Library and we can find out if ADO is required.

 

[parth00]

Microsoft DAO 3.6 Library was not selected....
Moving forward.............

 

[parth00]

I did change my code and made sure all variables/database/Recordsets are defined before they are used.... still getting the same error....

 

[pdx_man]

Is this only occuring when you are stepping through the code, or when it is actually run? I know that DoMenuItem is not something that cannot be executed in code break mode.

 

[parth00]

check this link

http://forums.aspfree.com/t31575/s.html

Its working by adding

If Me.Dirty Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End If

I don't understand what it is doing....but its working

 

[RuralGuy]

You can replace that code with:

If Me.Dirty Then
   DoCmd.RunCommand acCmdSaveRecord
End If

The wizards create obsolete code and what I posted is the replacement.

Access maintains a Dirty property that when true requires a save of the current record. Your error was probably caused because Access does not care to save a record when what you have in memory is exactly what is in the record in the table. By not saving until the Dirty property is true eliminates that particular error and is more efficient code.