Access Form checking for non unique field (1 Viewer)

Delinquent · Apr 25, 2018

Hi Guys,

first time posting in this forum so let me know if im breaking any etiquette

I have created an Access for to add new entries into a the main Access database. We have a filed that requires a unique value, but when I test this by trying to add an entry using the form and the same value. Nothing happens and the form doesn't say or do anything.

Is there anyway to have an error message pop up to let the user know that they are trying to enter a duplicate value into a unique field?

Kind regards,
Delinquent

arnelgp · Apr 25, 2018

Add Index on that field with No duplicate in table's design view.
If the field is on the form add code to its Before update event:

Private sub ColumnName_BeforeUpdate(Cancel as integer)
If dcount("1","yourtable","fieldname=" & ColumnName)<>0 then
Cancel=True
Msgbix "value already exists!"
End if
End sub

Delinquent · Apr 25, 2018

Hi arnelgp,

thanks for your quick reply. I have tried adding the code you have supplied but am a little confused. I get the following error: Run-Time error 3075. Syntax error (missing operator) in query expression "fieldname=Serial Number'.

I don't know what to put for the fieldname since wouldn't this have to cycle through all of them?

Code:

Private Sub Serial_Number_BeforeUpdate(Cancel As Integer)
    If DCount("1", "Asset Database", "fieldname=" & "Serial Number") <> 0 Then
        Cancel = True
        MsgBox "Value already exists"
    End If
End Sub

arnelgp · Apr 25, 2018

Fieldname is but a placeholder. Put the correct fieldname. The columnnane is the textbox name on your form. If the field is text delimit it with diuble qoute:

If DCount("1", "Asset Database", "fieldname=""" & [textboxname] & """") <> 0 Then

If numeric:

If DCount("1", "Asset Database", "fieldname=" & [textboxname]) <> 0 Then

Delinquent · Apr 25, 2018

Sorry to continue being a pain.

My table is called "Asset Database", my field name is "Serial Number" and my textbox name is "Serial Number textbox". Here is my code:

Code:

Private Sub Serial_Number_BeforeUpdate(Cancel As Integer)
    If DCount("1", "Asset Database", "Serial Number=""" & [serial number textbox] & """") <> 0 Then
        Cancel = True
        MsgBox "duplicate"
    End If
End Sub

I am getting the following error: Run-Time error (missing operator) in query expression 'serial number="test"'

Am I being a huge idiot like always?

CJ_London · Apr 25, 2018

if you have spaces in field and table names, you need to surround with square brackets.

Normal convention is to not use spaces, or use an underscore (_) as an alternative to a space.

as you get into it, you will find spaces are a PITA

Also, your dcount will be looking for a field called '1'. Use a * instead
DCount("*","[Asset Database]", "[Serial Number]='" & [serial number textbox] & "'")

This assumes your serial number is alpha (123A or A123 or abcd,etc). If it is a number, you drop the single quotes

DCount("*","[Asset Database]", "[Serial Number]=" & [serial number textbox])

It always helps to google something you haven't used before - in this case for example you will find

https://support.office.com/en-us/article/dcount-function-f6b5d78b-ad0b-4e42-be7a-11a64acbf3d3

arnelgp · Apr 25, 2018

Also note that i am counting a constant "1", you may also use "*" (all).

Delinquent · Apr 25, 2018

Thank you both for your help and patience. As you can tell im still very new to Access and never liked VBA (much better with PowerShell :banghead: )

This is what I ended up with just encase its useful for anyone down the line:

Code:

Private Sub Serial_Number_textbox_BeforeUpdate(Cancel As Integer)
    If DCount("*", "[Asset Database]", "[Serial Number]='" & [serial number textbox] & "'") Then
        Cancel = True
        MsgBox "This serial number is already in the database: " & Me.Serial_Number_textbox, , "Duplicate Entry!"
    End If
End Sub

Access Form checking for non unique field (1 Viewer)

Delinquent

New member

arnelgp

..forever waiting... waiting for jellybean!

Delinquent

New member

arnelgp

..forever waiting... waiting for jellybean!

Delinquent

New member

CJ_London

Super Moderator

arnelgp

..forever waiting... waiting for jellybean!

Delinquent

New member

Similar threads

Users who are viewing this thread