Primary Key field validation in forms

[Guest]

Hi ;

I use the forms function to record information into a table .

There is a primary key defined for one of the fields that allows me not to
input duplicate records.

However when using this form to input information, the validation on this
primary key field does not work immidiately , waits until to start to input a
new record in a new form page .
So , its time consuming for me to input all the information,complete the
form and then realize that was a duplicate record .

What i would like to have is an immidiate validation on this primary key
field as soon as i input any data in .

Thanks in advance for any help .

Steve

 

[Guest]

One method that you could consider is to code the primary key's OnExit
eventHandler along the lines of

If DCount("yourPrimaryKeyColumn", "YourTable", "yourPrimaryKeyColumn = " &
yourPrimaryKeyControl.Value) > 0 Then
MsgBox "Primary Key Already Exists", vbOkOnly
Cancel = True
End If

The above assumes that the primary key is a number. If it were a text
field, then the third parameter of the DLookUp statement would be
"yourPrimaryKeyColumn = '" & yourPrimaryKeyControl.Value & "'"

Hope This Helps
Gerald Stanley MCSD

 

[Guest]

Hi.

Thank you for your reply.

In below statement , i have changed the paremeters according to
the field and primary key names however i could not complete as i
could not understand how to replace "yourPrimaryKeyColumn = " &
yourPrimaryKeyControl.Value) box .

My primary key is a text .
Primary column name is " Dec ZET No"
Table name is "ZET Check Sheet"

Would you mind writing the DLookup statament again with the informations
above ?

Thank you very much
Steve

 

[Guest]

You haven't said what the name of the control on the form is called so I will
assume it is the same as the column name. The statement should be

If DCount("[ZET Check Sheet]" , "[Dec ZET No]" , "[ZET Check Sheet] ='" &
Me.[ZET Check Sheet].Value & "'") > 0 Then

It is not considered good practice to hav spaces in column and table names
as they then have to be enclosed in [].

Hope This Helps
Gerald Stanley MCSD

 

[Guest]

Sorry that does not work somehow .
Is there any other option for doing this validation?

Steve

 

[Guest]

I have noticed that my previous answer was incorrect due to some bad cut and
paste work resulting in the table and column name being transcribed. It
should have read

If DCount("[Dec ZET No]" , "[ZET Check Sheet]" , "[Dec ZET No] ='" &
Me.[Dec ZET No].Value & "'") > 0 Then

Apart from that, there is nothing else I can suggest.

Hope This Helps
Gerald Stanley MCSD

 

[Guest]

Thank you for your patience.
I guess i am getting close .
Two questions i have ;

1) Do i need to write anything after " Then "
2) I am not good in writing codes or macros .
I do not excatly know how to apply the code in the form.
Do i need to write it in a macro page ?
When i click on "On Exit" tab , there is "Event Procedure" or some
options like "Macro Builder" "Code Builder" etc .

Could you give more information about how to apply/input the
code you wrote .

Thank you very much
Steve

 

[Guest]

To answer Q2 first,

Step1. Select your form, then click on the 'Design' icon to put the form
into design mode.
Step2. Click on the 'Properties' icon, then select the control 'Dec Zet No'
from the dropdown list.
Step 3. Click on the 'Event' tab, go to the 'On Exit' event and click on the
drop down button for that event. The drop down should contain only 1
listItem called 'Event Procedure'. Select it then click on the button next
to the dropdown button (it is marked ...). This will open up the Visual
Basic Editor and you should see the opening and closing preocedure lines as
follows

Private Sub Dec_Zet_No_Exit (Cancel As Integer)

End Sub

To answer Q1, you need to put the following code in between the opening and
closing procedure lines

If DCount("[Dec ZET No]" , "[ZET Check Sheet]" , "[Dec ZET No] ='" &
Me.[Dec ZET No].Value & "'") > 0 Then
MsgBox "This is a duplicate code", vbOkOnly
Cancel = True
End If

When you have put this code in place, click on the save icon in the Visual
Basic Editor, then close it. You should then be able to close the form in
the design window and you can start testing.

The firt parameter of the Msgbox statement is the message that you will see
when you enter a code that already exists so youmay wish to change my message
above to something more appropriate.

Hope This Helps
Gerald Stanley MCSD