Newbie data entry help

[specialvat]

Hi,


Sorry if this is an easy question I am not very good with access.I have created a table and a data entry form.
Example:
ID Test Date Serial Number
1 16/02/16 90001234
2 16/02/16 90002134
3 16/02/16 90009999
4 16/02/16 90001234

The Serial Number is a validate rule Like"9000????" to make sure data is entered correctly.

Is there any way to check if the serial number is unique.
If its now unique I want it give a warning but still enter the data.

Thanks for any help it will be greatly appreciated.

[Bob Fitz]

Use the forms Before Update event to check for existing entries, perhaps with DCount() function and criteria. If DCount returns >0 then give warning with message box

[specialvat]

Hi
Thanks for the reply,
Can you give me an example of DCOUNT and how to send a warning

[Bob Fitz]

Is the field "Serial Number" defined in the table as Number or Text

[specialvat]

HI Bob
ID is auto number.
Date is a date and time.
Serial is an 8 digit text string.
I can change it to a number if needed.

Thanks

[specialvat]

Hi
Really struggling with this.
So far
=DCOUNT([Serial Number],"Table1",{Serial Number]="???")

This this look ok?
I want to use the serial number enter on the data entry sheet for the search how do I do this.
Also how do I call about a warning after the check.

Thanks

[Bob Fitz]

Something like:

Private Sub Form_BeforeUpdate(Cancel As Integer)    
    If DCount("ID", "YourTableName", "[Serial Number] = '" & Me.[Serial Number] & "'") > 0 Then
      MsgBox "Serial Number already used"
    End If
    
End Sub

[specialvat]

Hi Bob,
Thanks for the great help it works.
Can you please explain the above code so I can add it to the another number without asking for help again.
What is "ID"
Plus Me.[Serial Number] and "'"

Cheers

[Bob Fitz]

Hi Bob,
Thanks for the great help it works.
Can you please explain the above code so I can add it to the another number without asking for help again.
What is "ID"
Plus Me.[Serial Number] and "'"

Cheers

Glad it worked for you. I'll do my best to explain the syntax but you may be better of using a google search for the DCount() function or looking at the Access help file for a more detailed explanation.

The DCount() function returns a count of the records in a domain (table/query)

"ID" is the name of the field to be counted

"YourTableName" is the domain that has the records to be counted

"[Serial Number] = '" & Me.[Serial Number] & "'" is the criteria that tells it which matching records are to be counted


Serial Number is the name of the field in the domain
Serial Number is the name of the control who's current value is to be equal to Serial Number

Quotations are required around Serial Number when the Serial Number is a string but not if if a number

[specialvat]

Hi Bob,
Thanks for help and your explanation.
Your have been a great help.

Regards

[Bob Fitz]

Hi Bob,
Thanks for help and your explanation.
Your have been a great help.

Regards

Glad to be of some help. thank you for your kind words and appreciation.