Little help with dup records . . .

[JPFred]

I am writing an app that requires me to validate that a record does not existence in the database. The following is the information:

Database name: main
Record in question: access (primary key (no-dupes))
Form name: Add
Text box name: txtaccess
Event: lost focus

Goal: To check for a existing record in a table.

What I am attempting to do: I need to create a module that can be triggered on a lost focus event that will check the value entered in the text box (txtaccess) against a field (access) in a defined table (main). If a record is not found no action is necessary otherwise display a message box stating "Duplicate record found" and turn control back to the form / txtaccess control while clearing the input data that was entered.

A snippet of what I was thinking of doing is:

Dim strfound (What type should this be?)

DLookup( ...) "Could use an example using the information stated above"

if  strfound then
    MsgBox "Duplicate found"
endif

I know this is real basic stuff for you old timers but not so for an old batch programmer as myself. The code is very different then RPGLE and I am having some difficulty in making the conversion in thought process.

Once I get a good starting point I can run with it.

Thanks in advance for all of your help,

Doc

 

[Minty]

You need to use the Forms Before Update event.
This fires before any change to the record is saved.
You can use it to validate all the fields on the form you need to check.

If it's a duplicate you can use the Cancel = True method to stop the form data being saved, and Me.Undo to cancel the entries on the form

 

[plog]

Also, DCount is better than Dlookup for this. It is guaranteed to return a number, where DLookup can return NULL which can screw up assigning its value to a variable.

 

[JPFred]

You need to use the Forms Before Update event.
This fires before any change to the record is saved.
You can use it to validate all the fields on the form you need to check.

If it's a duplicate you can use the Cancel = True method to stop the form data being saved, and Me.Undo to cancel the entries on the form

I am using the control event since I need to check that database as the field(access) is entered that way no more data needs to be keyed for that record. The record has about 15 fields.

 

[arnelgp]

as Minty pointed out, you should do it in BeforeUpdate event, not in LostFocus.
supposed the user evades the field access by clicking on another field. Lostfocus of control only occurs when it has focus.


it is better to have an autonumber field in your table so checking will be simple.


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim lngID As Long

If Me.NewRecord Then
If DCount("*", "yourTableName","[access]=" FixSQL(txtAccess))<>0 Then
Cancel - True
Msgbox "Duplicate record found. Cannot add this record."
Else
lngID = DLookup("autoNumberField","YourTableName","access"=FixSQL(txtAccess))
If lngID <> Me.ID Then
Cancel = True

Msgbox "Duplicate record. Cannot change this field to this value."
End If
End If
End Sub




Public Function FixSQL(p As Variant) As Variant
Select Case VarType(p)
Case VbVarType.vbNull
FixSQL = "Null"
Case VbVarType.vbString
FixSQL = Chr(34) & p & Chr(34)
Case VbVarType.vbBoolean, VbVarType.vbByte, _
VbVarType.vbCurrency, VbVarType.vbDecimal, _
VbVarType.vbDouble, VbVarType.vbInteger, _
VbVarType.vbLong, VbVarType.vbSingle, _
FixSQL = p
Case VbVarType.vbDate
FixSQL = "#" & Format(p, "mm/dd/yyyy") & "#"
#If VBA7 Or Win64 Then
Case VbVarType.vbLongLong
FixSQL = p
#End If
End Select
End Function