Validation rule

[bknight]

Current table has a Profit field. I searched the table for "profits" in records that are opening trades. There can't be "profits" in opening trades only closing trades, so I want to make it impossible to enter any entry in records that are opening trades. I assume that would be a validation rule but I have never used a validation rule before. The closing trades have a 47 or 49 in an actionID field. What is the syntax to allow entries into the Profit field only if the actionID filed contains 47 or 49?

[HansV]

You have to do this in a Validation Rule for the table as a whole, i.e. the check will take place when the record is about to be saved, for example because you're moving to another record.
Open the table in design view.
Activate the Property Sheet.
In the Validation Rule property, enter

IIf([actionID] In (47,49),[Profit] Is Null)

Enter an appropriate warning in the Validation Text property.

S2366.png

Alternatively, you could use VBA in the form used to enter/edit the records.

[bknight]

I get the following error: Invalid SQL syntax-cannot use multiple columns in a column-level check constraint. This was generated when applying the validation to existing records. When I ran my query this morning to find any invalid errors, I corrected the one that was found. So the table doesn't need to be validated just the future entries.

[bknight]

Here is the design page

[HansV]

As I wrote in my first reply: "You have to do this in a Validation Rule for the table as a whole".
It is not possible to refer to another field in the validation rule of a field, as you have tried to do.
Please read my previous reply again.

[bknight]

Well I don't see "It is not possible to refer to another field in the validation rule of a field, as you have tried to do." in your previous reply. But if that is the rule so be it, I'll enter an event in the after update of profit.
Thanks

[HansV]

That sentence is not inside the quotes in MY reply!

[bknight]

I should have referred to your first post, my bad. The issue is solved.
Thanks

[bknight]

Code: Select all

Private Sub Profit_BeforeUpdate(Cancel As Integer)
    If Me.ActionID = 47 Or Me.ActionID = 49 Then
    Else
        MsgBox "You cannot enter a profit on an opening trade", vbInformation
            Me.Profit = Null
        End If
End Sub

I receive an error message.
Selecting end brings another error Property not found.
I next tried to enter the code into an AfterUpdate and received the same error. Then I changed to Me.Profit = ""
followed by an error message Property not found. So how do I prevent an entry or delete the entry?

ETA: Would it be in the Notes section which is before Profit and ignore the profit tab stop?

[HansV]

I'd do it like this:

Code: Select all

Private Sub Profit_BeforeUpdate(Cancel As Integer)
    If Not (Me.ActionID = 47 Or Me.ActionID = 49) And Not IsNull(Me.Profit) Then
        MsgBox "You cannot enter a profit on an opening trade", vbInformation
        Cancel = True
    End If
End Sub

[bknight]

Property not found.

[HansV]

Which line is highlighted if you click Debug?

[bknight]

The msg box appears and when closed, the error box appears, no debug on this one.

[HansV]

I'd have to see a copy of the database...

[HansV]

It works for me:

S2368.png

S2367.png

[bknight]

What happens when you press ok? That is when I received the error message.

[CData]

consider using a form for the data entry, rather than direct into a table - or even a dataview form. a normal form object offers much greater opportunity to control the user experience

[bknight]

The entry is from a form.

[CData]

as soon as it is known to be a buy - you can disable or lock the profit field

[bknight]

as soon as it is known to be a buy - you can disable or lock the profit field

Exactly how is a field disabled or locked? I mean what commands or codes?