submit and close button code does not work 1

[shaunacol]

I am getting my syntax all wrong can anyone help? I want the user to have to fill in a field called 'reason' before they sibmit. It is a required field but I wanted a message box to pop up too. In addition, before the form is submitted I want the fields StaffAllocated and Status to be changed. The reason is that this form is requesting a reallocation of sales staff so I want to delete the current allocation and change the status to 14 (awaiting allocation). I hope this makes sense. I tried writing code with a while loop but it didnt recognise my syntax then I tried the code below but it keeps saying I am missing an IF statement. Any help appreciated.


Private Sub submit_Click()

If Reason = Null Then MsgBox "You must enter a reason"
Else
Forms![frm_client].Form.[StaffAllocated] = Null
Forms![frm_client].Form.[Status] = 14
DoCmd.Close
End If

End Sub

 

[dhookom]

Nothing ever "equals" Null. Null is "unknown" so it can't be compared to anything.
Is this code running in frm_Client? If so, you should need to use "Forms!frm_client", just "Me."
You might want to try:

Private Sub submit_Click()
  If IsNull(Me.Reason) Then MsgBox "You must enter a reason"
   Else
    Forms![frm_client].Form.[StaffAllocated] = Null
    Forms![frm_client].Form.[Status] = 14
    DoCmd.Close
  End If

End Sub

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Anyway, replace this:
If Reason = Null
with this:
If IsNull(Reason)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

A better check is
If trim(me.reason & " ") = "" then

This will check for null, 1 or more spaces, and empty strings as well. Also you cannot combine one line ifs and multiple line ifs. That is why you are getting the specific error

If trim(me.reason & " ") = "" then 
  [b]MsgBox "You must enter a reason"[/b]
Else
   Forms![frm_client].Form.[StaffAllocated] = Null
   Forms![frm_client].Form.[Status] = 14
   [b]DoCmd.Close acform, me.Name[/b]
End If

 

[dhookom]

Thanks MajP,
I completely missed the stuff after the "Then". I often put comments on the If line so it didn't look "off" at first glance.

Duane
Hook'D on Access
MS Access MVP

 

[shaunacol]

Fantasitc, this works great, I have never heard of trim before!