Validating a primary key on a form 1

[idd]

Hi,

The scene

I am using MS Access 97

I have a problem where I have a table which holds information on orders the key field is the requisition number

req_no

The problem

When a user adds a record using the form I have created for data input, they may enter a previously entered req_no in by mistake and when this happens access lets them know at the end of all data entry for the form that this will create duplicate entries in the primary key. Access lets the user know with its very technical and un-user friendly message.

I want to be able to control this by means of testing what the user has input into the req_no text box after they update the field, or the control loses focus to another control or when the next control on the form gains focus, any of these methods will do as long it works.

I have looked around on this site in both faqs and using the search, I managed to find a few possible solutions but they didnt work for me.

can anyone please help.

Iddris

 

[mp9]

1 solution: use VBA in the primary key field's AfterUpdate event to manually save the record (

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

in A2000). Use an error handler to trap the error that occurs if a duplicate value is entered and display a nice message instead of the default.

So your code (assuming your primary key field is called req_no) would be:

Private Sub req_no_AfterUpdate()
On Error GoTo Err_req_no_AfterUpdate

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_req_no_AfterUpdate:
    Exit Sub

Err_req_no_AfterUpdate:
    If Err.Nmber = 3022 Then
        MsgBox Err.Number & ": this value is not allowed as it would be a duplicate.", vbExclamation
    Else
        MsgBox Err.Number & ": " & Err.Description, vbExclamation
    End If
    Resume Exit_req_no_AfterUpdate
    
End Sub

http://www22.brinkster.com/accessory/

 

[idd]

MP9

Reply I cant do that because I have a few other fields which are required a record to e complete, and cannot be null.

Iddris

 

[mp9]

Try this instead then - I hate it because it uses DCount but it should do the job. Obviously replace tblOrders with the name of your orders table.

Private Sub req_no_AfterUpdate()
On Error GoTo Err_req_no_AfterUpdate

    If DCount("*","tblOrders","req_no=" & req_no)>0 Then
         Msgbox "This value would cause a duplicate!", vbExclamation
         req_no.SetFocus
    End If

Exit_req_no_AfterUpdate:
    Exit Sub

Err_req_no_AfterUpdate:
    MsgBox Err.Number & ": " & Err.Description, vbExclamation
    Resume Exit_req_no_AfterUpdate
    
End Sub

http://www22.brinkster.com/accessory/

 

[idd]

MP9,

I've tried the solution you have given above but it keeps giving me an error
62506 data type mismatch in criteria expression.

i've have included the code below,


On Error GoTo Err_req_no_AfterUpdate

If DCount("[requisition_no]", "tbl_Order", "[requisition_no]= " & Me.Requisition_No) > 0 Then
MsgBox "This value would cause a duplicate!", vbExclamation
Me.Requisition_No.SetFocus
End If

Exit_req_no_AfterUpdate:
Exit Sub

Err_req_no_AfterUpdate:
MsgBox Err.Number & ": " & Err.Description, vbExclamation
Resume Exit_req_no_AfterUpdate


idd

 

[mp9]

I'm guessing therefore that requisition_no is a text field? In which case you'll need to replace your DCount line with

.
.
.
If DCount("*", "tbl_Order", "[requisition_no]= '" &  Me.Requisition_No & "'") > 0 Then
.
.
.

Note: DCount("*"... is faster than DCount([fieldName]...

http://www22.brinkster.com/accessory/

 

[idd]

MP9,

I have attained success, it works, I went in the help file and played about with the solution that you gave me and now it works.

here is the solution that works for me.

thanx MP9 for your help.


Private Sub Requisition_No_AfterUpdate()
On Error GoTo Err_req_no_AfterUpdate

If DCount([Requisition_No], "tbl_Order", "[Requisition_No] = '" & Me.Requisition_No & "'&quot > 0 Then
MsgBox "This value would cause a duplicate!", vbExclamation
Me.Requisition_No.SetFocus
End If

Exit_req_no_AfterUpdate:
Exit Sub

Err_req_no_AfterUpdate:
MsgBox Err.Number & ": " & Err.Description, vbExclamation
Resume Exit_req_no_AfterUpdate
End Sub



Idd

 

[idd]

MP9,

I must have been typing my last reply when you were writing the last answer you gave me, but thats exactly what I did, thanx for the tip of using "*" I have changed that in the code now.

just as a point of interest if it was'nt a text field and was a numeric value how would it differ ? if it differs at all.

idd

 

[mp9]

If it was numeric you wouldn't have to encapsulate Me.Requisition_no in single-quotes.

http://www22.brinkster.com/accessory/