msgbox

[clarke24]

Hi i have a database with a form that i have added a checkbox to (check325), what i want to do is have a msgbox appear if command 261 is clicked to say 'please complete check box'. here is the original code.

Private Sub Command261_Click()
'On Error GoTo Err_Command261_Click
    If IsNull(Me.Text266) Or Trim(Me.Text266) = "" Then
    MsgBox "Please provide Work Status", vbInformation, "Cworks"
    Me.Text266.SetFocus
    Exit Sub
    End If
 
  Dim rspmsched As DAO.Recordset
  Dim response
 
    If Me.WorkStatus.Column(0) = 2 Then
    If IsNull(Me.ActDateStart) Or IsNull(Me.ActDateEnd) Then
        MsgBox "Please provide an Actual Start Date and  Actual End Date", vbOKOnly + vbExclamation
        Me.ActDateStart.SetFocus
    Else
        response = MsgBox("Closed work orders cannot be modified. Do you want to proceeed ?", vbYesNo + vbInformation + vbDefaultButton2, "Confirm Save")
        If response = vbYes Then
           If Me.WorkType = 2 And Me.WorkStatus = 2 Then
                
               Set rspmsched = CurrentDb.OpenRecordset("Select * from [PM Schedule] where [PM Schedule].PMNo='" & Replace(Me.PMNo, "'", "''") & "' and [PM Schedule].TypePMgen='2'")
                    If rspmsched.EOF = False Then
                            rspmsched.Edit
                            rspmsched![ActualCompDate] = Format(Me.ActDateEnd, "Short Date")
                            rspmsched![nextdate] = rspmsched![ActualCompDate] + (rspmsched![Frequency] * rspmsched![FreqUnits])
                            rspmsched.Update
                    End If
                    rspmsched.Close
            End If
            DoCmd.Close
        Else
            Exit Sub
        End If
    End If
  ElseIf Me.WorkStatus.Column(0) <> 2 Then
    DoCmd.Close
  End If
 
End Sub

[Hack]

Welcome to the forums.

I guess what you are saying is that what you have doesn't work, so try this

If Text266.Text = vbNullString Then

Also, give your controls meaningful names. It makes it a little easier to figure out what does what when you use things like txtWorkStatus or cmdCheckStatus as opposed to Text266 and Command261

[clarke24]

Hi thanks for the response, the original code does work but what i want to do is add in some code so that if the new checkbox that i have added is not checked a message box appears to say 'please complete checklist'.

[Hack]

Oh, ok. Then do something like

If Check1.Value = vbUnchecked Then
     Msgbox "Please Complete The CheckList"
     Exit Sub
End If

[Max Peck]

Hi Clarke,

One other comment, too.

The test "IsNull(Me.Text266)" is always going to return false if this is referring to a textbox on your form. VB never sets the default property (which is Text) for a textbox to Null. That's only necessary if the item you're testing for Null is coming from a database - I.E. testing a recordset column. So you can just do the one test on Trim(Me.Text266) = ""

BTW ... suggest that you indent your code - it will be easier to read. (That section wasn't indented as far as I could see).

- Max

[Hack]

Good point Max Peck.

It should be

If Text266.Text = vbNullString

[Max Peck]

Hi Hack!

Good point Max Peck.

It should be

If Text266.Text = vbNullString

Actually he needs to say:

If Trim(Text266.Text) = vbNullString. Otherwise an imbedded blank will fail that test. vbNullString is not the same as a SQL NULL - it's simply ("").

-Max

[clarke24]

thanks again, i entered the code before the line 'If Me.WorkStatus.Column(0) = 2 Then' but it didnt work when i clicked command 261 it just jump straight to the closed work orders cannot be modified msgbox. any hints would be appreciated.

[Hack]

What is Me.WorkStatus.Column(0) and what does this have to do with your checkbox?

[clarke24]

its in the original code, where should i insert the new code then?

[Hack]

its in the original code, where should i insert the new code then?

Its not my program, so I'm not sure where it should go. Where would it, to you, make the most sense to do this check?

[clarke24]

it needs to go in before it checks the actual dates are null. but when i enter it in it doesnt run properly

[Hack]

Post what you have for Command261_Click now, with that checkbox check added.

[clarke24]

Private Sub Command261_Click()
'On Error GoTo Err_Command261_Click
    If IsNull(Me.Text266) Or Trim(Me.Text266) = "" Then
    MsgBox "Please provide Work Status", vbInformation, "Cworks"
    Me.Text266.SetFocus
    Exit Sub
    End If

  Dim rspmsched As DAO.Recordset
  Dim response
If Check325.Value = False Then
     MsgBox "Please Complete The CheckList"
     Exit Sub
End If
    If Me.WorkStatus.Column(0) = 2 Then
    If IsNull(Me.ActDateStart) Or IsNull(Me.ActDateEnd) Then
        MsgBox "Please provide an Actual Start Date and  Actual End Date", vbOKOnly + vbExclamation
        Me.ActDateStart.SetFocus
    Else
           response = MsgBox("Closed work orders cannot be modified. Do you want to proceeed ?", vbYesNo + vbInformation + vbDefaultButton2, "Confirm Save")
        If response = vbYes Then
           If Me.WorkType = 2 And Me.WorkStatus = 2 Then
                
               Set rspmsched = CurrentDb.OpenRecordset("Select * from [PM Schedule] where [PM Schedule].PMNo='" & Replace(Me.PMNo, "'", "''") & "' and [PM Schedule].TypePMgen='2'")
                    If rspmsched.EOF = False Then
                            rspmsched.Edit
                            rspmsched![ActualCompDate] = Format(Me.ActDateEnd, "Short Date")
                            rspmsched![nextdate] = rspmsched![ActualCompDate] + (rspmsched![Frequency] * rspmsched![FreqUnits])
                            rspmsched.Update
                    End If
                    End If
                    rspmsched.Close
            End If
         DoCmd.Close
        Else
            Exit Sub
        End If
    End If
  ElseIf Me.WorkStatus.Column(0) <> 2 Then
    DoCmd.Close
  End If

End Sub

[Hack]

I added [code]your code goes here[/code] tags to your post so it would be easier to read. If would be good if you could use them in the future. Thanks.

'Not
If Command325.Value = False Then
'but rather
If Command325.Value = vbUnchecked Then

[clarke24]

tried that but it comes up with variable not defined

[Max Peck]

tried that but it comes up with variable not defined

Command325 is undefined (not in your code anyway) ... I think Hack meant to say ...

if Check325.value = vbUnChecked ...

-Max

[Hack]

tried that but it comes up with variable not defined

On what line does the error occur?