Save and Clear

[MitchR]

I Have created a table for data entry. The table and accompanying form used to enter data is used for returning leased assets back to a lease holder. My question is this: I want to script a Save and Clear Button on my data entry form to write the data back to the table so that I can run reports against the table & clear the form for the next item to be entered. I do not want the data to be visible to the user once the button is pressed. Here is what I have but I receive errors upon launch:

Private Sub SaveMonitor_Click()
On Error GoTo Err_SaveMonitor_Click
'Set the save statement to disable command button once Save button is clicked.

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunSQL "UPDATE MonitorReturn SET completed = " & -1 & " WHERE DateShip = '" & [Forms]![MonitorReturn]![MonitorSerial#] & "' ;"
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SetWarnings True
'Requery checkbox to show checked
'after update statement has ran
'and disable send mail command button
Form_MonitorReturn.SetFocus
Form_MonitorReturn_SaveMonitor.Enabled = False
Recordset.CreateNew

Exit_SaveMonitor_Click:
Exit Sub


I am pulling my hair out!!!

[NeoPa]

You really need to provide more information about the error.
Please read this post ( http://www.thescripts.com/forum/thread559246.html ) as it will tell you how best to get your questions answered.
Specifically here, post the code as code and provide some info about what error you're getting.

[MMcCarthy]

In the form properties set the Data Entry property under the Data tab to Yes.

 
Private Sub SaveMonitor_Click()
On Error GoTo Err_SaveMonitor_Click
'Set the save statement to disable command button once Save button is clicked.
 
DoCmd.SetWarnings False
 
 ' change DoCmd.RunCommand acCmdSaveRecord to
 DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
 
DoCmd.RunSQL "UPDATE MonitorReturn SET completed = " & -1 & " WHERE DateShip = '" & [Forms]![MonitorReturn]![MonitorSerial#] & "';"
 
 ' remove this DoCmd.RunCommand acCmdSaveRecord line
 
DoCmd.SetWarnings True
 
 'Requery checkbox to show checked
 Me.completed.Requery
 
Form_MonitorReturn.SetFocus
Form_MonitorReturn_SaveMonitor.Enabled = False
 
 ' go to new record
 DoCmd.GoToRecord acDataForm, "MonitorReturn", acNewRec
 
Exit_SaveMonitor_Click:
Exit Sub
 
Err_SaveMonitor_Click:
 
End Sub
 
 

[MitchR]

I still have one problem... I want to Grey Out the Save and Clear command button. I have followed the exmples from other threads and simplified my code and the save and clear function works great! Thank you for those. My issue is this : When I click my save button I would like to " Grey out the save button. Unfortunately when I execute the save command I get a Runtime error '2164' You Can't Disable a control while it has the focus and prompts me to debug. I have attempted to every which way to fix the issue with no success. The Debug point is Me.SaveMonitor.Enabled = False I am must appreciative for any assistance. Otherwise I can just comment out the grey out piece and go along as is... Here is my code :

 Private Sub SaveMonitor_Click()
Dim mydate As Variant
mydate = Date
MsgBox "Please Remember that Monitors are to be shipped directly to Site." & vbCrLf & _
       "Monitor" & vbCrLf & _
       "Address1" & vbCrLf & _
       "Address2" & vbCrLf & _
       "Address3", , "Dell Monitor Alert"
    DoCmd.SetWarnings False
    'Requery checkbox to show checked after statement has ran and disable command button
    Me!Completed = -1
    Me.Completed.Requery
    DoCmd.SetWarnings True
    Me.SaveMonitor.SetFocus
    'Diasble the Save & Continue button
    Me.SaveMonitor.Enabled = False
    DoCmd.GoToRecord acDataForm, "MonitorReturn", acNewRec
 
Exit_SaveMonitor_Click:
    Exit Sub
End Sub 

[MitchR]

Ok I moved the focus to a "Return to Switchboard" Control button and that resolved the issue, But ... One problem though .... it greys out permanently. I was thinking that an IF statement might be key but I cannot get the IF statement to see Completed = -1 in the completed column of the table. My Completed column in my table is a yes/no with lookup as textBox. 0 = not completed & -1 = completed Here is my updated code:
Code:

Private Sub SaveMonitor_Click()
Dim mydate As Variant
Dim Completed As Varian
tmydate = Date
MsgBox "Please Remember that Dell Monitors are to be shipped directly to Dell." & vbCrLf & _       
"Address1" & vbCrLf & _       
"Address2" & vbCrLf & _       
"Address3" & vbCrLf & _       
"Address4", , 
"Dell Monitor Alert"    
DoCmd.SetWarnings False    
'Requery checkbox to show checked after statement has ran and disable command button    
Me!Completed = -1    
Me.Completed.Requery    
DoCmd.SetWarnings True    
Me.Exit.SetFocus    
'Diasble the Save & Continue button   
 If Me!Completed = -1 Then
Me.SaveMonitor.Enabled = False
Else    
If Me!Completed = 0 Then        
Me.SaveMonitor.Enabled = True    
End If    
DoCmd.GoToRecord acDataForm, "MonitorReturn", acNewRecExit_SaveMonitor_Click:    Exit SubEnd Sub
 

[NeoPa]

I can't see that the code to ungrey would ever be executed.
You need to trigger the ungreying from somewhere that will get run even when the command is greyed out.
Your solution to the earlier problem is good - well done.
The coding to set the status could be more clearly written as :

...
Me.Exit.SetFocus
'Disable the Save & Continue button   
Me.SaveMonitor.Enabled = Not Me.Completed
...

I'm assuming here that there is a point to enabling it in this code.
I don't presume to know everything that goes on in your code, although it seems illogical from here.

[MitchR]

Ok ... Thank you to All who have helped me with this project. I got a Final Solution to my issue of Greying out the Button. On Form properties ON Current, The event procedure should be scripted :

 Private Sub Form_Current()
 
    Dim Completed As Variant
 
    If Me.Completed = True Then
        Me.SaveMonitor.Enabled = False
 
    Else
        Me.SaveMonitor.Enabled = True
    End If
End Sub 

Thank you NeoPa and MMCCarthy For all your Support !!! I could not have doe this with out you.