Looking for an else

[UT227]

I have an "if" statement in current on a form. It was working fine without an "else" as part of it until I needed to delete the record. That's when not having and "else" gets in the way. Here's the statement:

If FormLocked Then
Dim ctl As Control

For Each ctl In Me


If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
ctl.Locked = True
ctl.BackColor = RGB(220, 220, 220) 'gray


End If
Next

I need an "else" in my statement so that it will just continue to do what it needs to do. In this case, delete the record. How would I write that?

[June7]

How about rest of the code? How is FormLocked set? Presumably record can only be deleted if form is not 'locked'.

[UT227]

FormLocked is a yes/no field. Yes. It can only be deleted if it is unlocked.

[June7]

No idea why delete would cause issue. The form is not locked, controls are locked. This does not prevent deleting record.

What method are you using to delete record? Why does record even need to be deleted? Deleting data should be a rare event.

[Missinglinq]

No idea why delete would cause issue.

As I read it, the OP question was how to write code to add an Else clause to delete the record, not that deleting was causing a problem. If he actually has tried and failed, it's probably because he tried placing it in the wrong If...Then clause.

This should do it:

Dim ctl As Control

  If FormLocked Then

    For Each ctl In Me.Controls
     
     If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
       ctl.Locked = True
       ctl.BackColor = RGB(220, 220, 220) 'gray
     End If
    
    Next
   
   Else
     
     'Delete code goes here
   
   End If

But as June7 said, deleting a Record is seldom done by experienced developers...best practice is usually to have a Yes/No field to indicate if the Record is Active/Inactive...and set a Form's Record Source to only show Active Records, Inactive Records, or both, as appropriate.

Linq ;0)>

[UT227]

Won't that delete the record right away if FormLocked is False? Is there a better way of doing it other than the If/Then statement?

[Missinglinq]

Won't that delete the record right away if FormLocked is False

Well...yes! Which is what your statement

...I need an "else" in my statement so that it will just continue to do what it needs to do. In this case, delete the record...

appears to be saying!

Linq ;0)>

[UT227]

Yes. That was the question. Looks like I didn't give enough details. When the form is brought up, it is to be reviewed. Once it is approved, it's "locked". That is, a check mark is placed in the FormLocked field. Once in a while, it will need to be deleted. Most of the time it will just be approved or edited. Deleting a record will be almost not done. It did come up once and that's how I found the problem. Almost all the time, a form will only be edited or locked. However, I would like to find a way to delete the record without having the error. That's why I need to find an ELSE. Isn't there a VBA command to just continue and overlook the error?

[June7]

Again, what method are you using to delete record? Just the keyboard delete?

And what error are you getting?

[UT227]

Clicking on END will take me straight to the warning about deleting a record. DEBUG of course goes to the debugger.

[Missinglinq]

For the third time...what method are you using to delete the record?

[UT227]

I forgot. I had a delete macro at first. Then, converted it to VBA:

Private Sub btnDelete_Click()
On Error GoTo btnDelete_Click_Err

' Delete Record

On Error Resume Next
DoCmd.GoToControl Screen.PreviousControl.Name
Err.Clear
If (Not Form.NewRecord) Then
DoCmd.RunCommand acCmdDeleteRecord
End If
If (Form.NewRecord And Not Form.Dirty) Then
Beep
End If
If (Form.NewRecord And Form.Dirty) Then
DoCmd.RunCommand acCmdUndo
End If
If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If


btnDelete_Click_Exit:
Exit Sub

btnDelete_Click_Err:
MsgBox Error$
Resume btnDelete_Click_Exit




End Sub

[June7]

Tried your code in my db and cannot replicate the issue.

If you want to provide db for analysis, follow instructions at bottom of my post.

[UT227]

TestDB.zip
Took a while to get out the information.
Go to Initiated Contacts
Go to View History, click Check
Double click on a record. The first one will do. If it's locked, unlock it and refresh it.
Click on Delete
Should get the error

[June7]

The Delete procedure causes FormLocked to return as Null. So the conditional If FormLocked Then evaluates to If Null Then which makes no sense. Deal with possibility of Null. Use Nz() to convert Null to some value the code can deal with.

If Nz(FormLocked, False) Then

However, the code does not 'unlock' a record. If you want users to see the record unlocked before deleting, eliminate the If FormLocked Then conditional:

    Dim booLocked
    Dim ctl As Control
    booLocked = Nz(Me.FormLocked, 0)
    For Each ctl In Me
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
            ctl.Locked = booLocked
            ctl.BackColor = IIf(booLocked, RGB(220, 220, 220), vbWhite) 'gray
            'ctl.Enabled = Not booLocked 'alternative
        End If
    Next
    btnDelete.Enabled = Not booLocked
    RaceKnown.Enabled = Not booLocked
    SearchConducted.Enabled = Not booLocked
    ContrabandFound.Enabled = Not booLocked
    ArrestMade.Enabled = Not booLocked
    Injury.Enabled = Not booLocked

Instead of setting Locked and Backcolor properties why not Enabled property? This would achieve mostly the same effect as setting Locked and Backcolor properties. Can even use Conditional Formatting and eliminate VBA for the textbox and combobox controls.

Now the OfficerContacts form shows a #DELETED row. Need to requery the form. The form uses a dynamic parameterized query which triggers popup input because the criteria form is no longer open. I NEVER use dynamic parameterized queries. I prefer using WHERE CONDITION argument of OpenForm/OpenReport.

Sorry for making you go to the trouble of attaching a file. I did not use a Yes/No field in test and therefore did not replicate the issue. But providing file did allow further testing.