Make field blank. 1

[lars7]

Hi there,
Could anyone help we with this, I am trying to get a field to go blank if a check box is unchecked. When the check box is checked it puts a date in "reviewdate" so if it is unchecked I would like the date to be erased. I have tried is null, or null, and this code here:

CurrentDb.Execute "UPDATE tblassimilation SET reviewdate= "" WHERE paynumber='" & Forms!frmjedswitchboard!text6 & "'"


Thanks in advance.

 

[PHV]

CurrentDb.Execute "UPDATE tblassimilation SET reviewdate= Null WHERE paynumber='" & Forms!frmjedswitchboard!text6 & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi PHV,
I have a "Write conflict" message box when the form closes saying that "A record has been changed by another user..." this is the code "on close":


Private Sub Form_Close()
On Error GoTo Err_Form_Close
If Not (Me.ReviewRequested) Then
[Forms]![frmjedswitchboard]![text6].DefaultValue = ""
Exit Sub
Else
CurrentDb.Execute "UPDATE tblassimilation SET reviewdate=Date() WHERE paynumber='" & Forms!frmjedswitchboard!text6 & "'"
[Forms]![frmjedswitchboard]![text6].DefaultValue = ""
End If
Exit_Form_Close:
Exit Sub
Err_Form_Close:
MsgBox Err.Description
Resume Exit_Form_Close
End Sub


and this is the full code I'm using:


Private Sub ReviewRequested_AfterUpdate()
If (Me.ReviewRequested) Then
CurrentDb.Execute "UPDATE tblassimilation SET reviewdate=Date() WHERE paynumber='" & Forms!frmjedswitchboard!text6 & "'"
DoCmd.OpenReport "ReportReviewLetters", acViewPreview, "", "[PAYNO]=[Forms]![FrmJedSwitchboard]![text6]"
Exit Sub
Else
CurrentDb.Execute "UPDATE tblassimilation SET reviewdate= null WHERE paynumber='" & Forms!frmjedswitchboard!text6 & "'"
End If
End Sub

 

[PHV]

Is by chance your form bound to tblassimilation ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi again,
Yes it is, here is the SQL:

SELECT tblAssimilation.PayNumber, tblAssimilation.Name, tblAssimilation.JobDescription, tblAssimilation.ReviewRequested
FROM tblAssimilation;

 

[PHV]

So, use DoCmd.RunCommand acCmdSaveRecord (or Me.Dirty = False) before using CurrentDb.Execute.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zion7]

Why the execute command? I know it'll work but...

ReviewDate = IIF(chkReview,Date,Null)

 

[lars7]

Thanks PHV,
DoCmd.RunCommand acCmdSaveRecord worked and I moved the "on close" code to the "after update" too so this is all I need now:

Private Sub ReviewRequested_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
If (Me.ReviewRequested) Then
CurrentDb.Execute "UPDATE tblassimilation SET reviewdate=Date() WHERE paynumber='" & Forms!frmjedswitchboard!text6 & "'"
DoCmd.OpenReport "ReportReviewLetters", acViewPreview, "", "[PAYNO]=[Forms]![FrmJedSwitchboard]![text6]"
Exit Sub
Else
CurrentDb.Execute "UPDATE tblassimilation SET reviewdate= null WHERE paynumber='" & Forms!frmjedswitchboard!text6 & "'"
End If
End Sub

and also thanks for replying Zion, I will try your code.