validating an entry (for not empty and duplicate values)

[markjkubicki]

a text box's value on the form is the unique identifier for the record, so when a user changes it,
which is an acceptable action provided it is valid on (3) points, I need to be certain that it complies...

- the user wanted to change it in the first place
- the entry is not blank
- and the new entry is not already being used

if any of these are false, then the entry should revert back to what it had been
HOWEVER:
1) nothing is going back to what it was
2) if I do try to accept the new entry, I get a validation error (?)

I'm stuck (with many thanks in advance)

the table that is the data source for the record is linked to other tables, and the relationships are: one-to-many (internal, with referential integrity), one-to-many ...which probably needs to be changed to internal, with integrity, or (in one case) indeterminate

here's the code:

(vtxtType is a public variable that catches the value of the text box on the record's current event)

Private Sub txtType_BeforeUpdate(Cancel As Integer)
Dim vType As String
If Len(Nz(Me.txtType, "")) < 1 Then
strText = "You must enter a fixtue type before moving on..." & vbCrLf & _
"Please try again"
strTitle = "FIXTURE TYPE MISSING"
Response = MsgBox(strText, vbCritical + vbRetryCancel, strTitle)
Cancel = True
Else
If Me.Type <> vtxtType Then
strText = "You are about to change the existing type " & UCase(vtxtType) & " to " & UCase(Me.Type) & vbCrLf & _
vbCrLf & _
"Do you wish to continue?"
strTitle = "CHANGE OF FIXTURE TYPE"
Response = MsgBox(strText, vbCritical + vbYesNo + vbDefaultButton2, strTitle)
If Response = vbYes Then
' check for duplication with an existing record


If DCount("[type]", "tbeFixtureTypeDetails", "[type]= '" & Me.Type & "'") > 0 Then
vmsgtxt = "This fixture type is already being used; please try again"
vmsgTitle = "DUPLICATE FIXTURE TYPE"
Response = MsgBox(vmsgtxt, vbCritical + vbRetryCancel, vmsgTitle)
Cancel = True
End If
' save the new entry
Me.Dirty = False
Else
Cancel = True
End If
End If
End If
End Sub

[June7]

What is vType for? It isn't used.

Have you step debugged?

Post the code between CODE tags and it will retain indentation and be easier to read.

[markjkubicki]

What is vType for? It isn't used.
ME --> currently nothing; it should be deleted

Have you step debugged?
ME --> yes, all of the code runs without any hitches

Post the code between CODE tags and it will retain indentation and be easier to read.
ME --> aahhhh, I didn't know that! absolutely will do so in future posts. (thnx)

[June7]

I thought you said you get a validation error?

I can't spot logic flaw. If you want to provide db for analysis, follow instructions at bottom of my post.

[ssanfu]

If you read this article
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

you might want to rethink using text fields for the PK/FK fields. You could use autonumbers for the PK field and set an index on the field(s) that you don't want to have duplicates. And as far as I know, it is not accepted practice to change the PK field data.

In any case, I am a little confused by your code. First, "Type" is a reserved word in Access (and it is not very descriptive - "FixtureType" or "Fix_Type" or "TypeOfFix" would be better - IMO). Second, you have three different variables for the "Type'. And you might need to get the "Text" value property because the data hasn't been saved yet.
(The ".Value" property is the current value in the control, the ".Text" property is the uncommitted value being entered.)

Private Sub txtType_BeforeUpdate(Cancel As Integer)
    Dim vType As String
    
    If Len(Nz(Me.txtType, "")) < 1 Then
        strText = "You must enter a fixtue type before moving on..." & vbCrLf & _
                  "Please try again"
        strTitle = "FIXTURE TYPE MISSING"
        Response = MsgBox(strText, vbCritical + vbRetryCancel, strTitle)
        Cancel = True
    Else
        If Me.Type <> vtxtType Then
            strText = "You are about to change the existing type " & UCase(vtxtType) & " to " & UCase(Me.Type) & vbCrLf & _
                      vbCrLf & _
                      "Do you wish to continue?"
            strTitle = "CHANGE OF FIXTURE TYPE"
            Response = MsgBox(strText, vbCritical + vbYesNo + vbDefaultButton2, strTitle)
            If Response = vbYes Then
                ' check for duplication with an existing record
                If DCount("[type]", "tbeFixtureTypeDetails", "[type]= '" & Me.Type & "'") > 0 Then
                    vmsgtxt = "This fixture type is already being used; please try again"
                    vmsgTitle = "DUPLICATE FIXTURE TYPE"
                    Response = MsgBox(vmsgtxt, vbCritical + vbRetryCancel, vmsgTitle)
                    Cancel = True
                End If
                ' save the new entry
                Me.Dirty = False
            Else
                Cancel = True
            End If
        End If
    End If
End Sub

The control name is "txtType"
The public variable is named "vtxtType"

What is "Type"??

[June7]

Interesting comment about Text. I've never used this property and I am sure I refer to Value of bound controls before record is committed to table. Example (Value is default property):

Private Sub tbxFieldPL_AfterUpdate()
Me.tbxFieldPL = UCase(Me.tbxFieldPL)
End Sub

[ssanfu]

I might have misunderstood the Help info. A long time ago, I was trying to code something using the Text property and was having problems; was then told to use the default value property.

Re-reading Help, it appears that as long as the control (text box) has the focus, the Text and Value properties are different. Once the control loses focus, the Value property is updated to the Text property value. (This is different than updating the underlying record source.)

The Text property may be of value when using the change event... hmmm... will have to read more on this.

Thanks, June

[markjkubicki]

ahhh... those nuances; they'll get you every time !