Prevent duplicate entries of a field on an input form (1 Viewer)

[chriscook]

Hi All,

I have recently started working for a new company and have inherited a nightmare of a system!

I have basic knowledge of Access and have been asked to stop duplicate entries under the same PO Number.

I have tried using DLookups by looking at other posts on the forum with no success.

The input form is called Purchase Order Entry, with the field called PO_No. The table is called Orders, with the field called Purchase Order Number.

I have tried to make the Purchase Order Number in the Orders table, the primary key but an error that it cannot contain a null value appears.

The Purchase orders may not be entered into the database in numerical order also.

I hope the above makes sense and would appreciate any assistance anyone can provide.

 

[burrina]

Here is one suggestion: Example: Welcome to the Forum!
Create a new module and past this code: Save as ModTimeCardCounter

Function TimeCardCounter() 'This is for the OrderDetails form only.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'For this code to work, it is required that you reference the:
' Microsoft ActiveX Data Objects 2.x Library
'To reference this library, go to the Tools menu, click
'References, and select the library from the list of available
'references (version 2.1 or higher).
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
On Error GoTo TimeCardCounter_Err

Dim rs As ADODB.Recordset
Dim NextCounter As Long

Set rs = New ADODB.Recordset

rs.Open "TTimeCardCounter", CurrentProject.Connection, adOpenKeyset, adLockOptimistic 'Open the ADO recordset.

NextCounter = rs!NextAvailableCounter 'Get the next counter.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Open table and get the current value of NextAvailableNumber,
'increment the value by 1, and save the value back into the table
'
'The next line can be changed to conform to your custom counter
'preferences. This example increments the value by 1 each time.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
rs!NextAvailableCounter = NextCounter + 1
NextCounter = rs!NextAvailableCounter
rs.Update

'MsgBox "Next available counter value is " & Str(NextCounter)
rs.close

Set rs = Nothing

TimeCardCounter = NextCounter

Exit Function
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'The following error routine should be replaced with a custom
'error routine. This example only resumes execution when an error
'occurs. If a record locking error occurs this is fine; however,
'any non-record locking error will result in an infinite loop.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

TimeCardCounter_Err:
MsgBox "Error " & Err & ": " & Error$
If Err <> 0 Then Resume
End
End Function

Next,have a field in your orders table named InvoiceNumber Text

Next, create a new table named TTimeCardCounter
TTimeCardCounter
NextAvailableCounter Date/Type Number Indexed No Duplicates

If for example you have a Customer ComboBox on your Orders form, use this:

Private Sub CustomerID_Click()
If Not IsNull(CustomerID) And IsNull(TimeCounter) Then
Call TimeCardCounter
TimeCounter = Format(DLookup("[NextAvailableCounter]", "TTimeCardCounter"), "######")
End If
DoCmd.RunCommand acCmdSave
End Sub

On your form add a new text box and edit your label to Invoice Number
Control source is set to

=[TimeCounter]
Enabled = No Locked = Yes

HTH

 

[chriscook]

Hi,

Yes each invididual purchase order number is unique and never gets repeated.

I would just like a pop up to appear if a duplicate PO number is entered by accident.

Thanks

 

[burrina]

Better to not allow this period. Let the code generate for for you.

 

[chriscook]

If im correct from reading the code you have suggested. It will automatically generate the next purchase order number when you go to enter a PO.

The issue with this is that we may enter PO number 12 one day and then 10 the next day if quotations take a long time, or there is a delay.

 

[burrina]

In that Case you should save it as a Quote until posted as a Invoice.
You should have a Type of Invoice for your Orders.

 

[chriscook]

Thanks for your suggestions.

Unfortunately, I do not really want to change the system dramatically.

Is it not possible to use a DLookup to stop the duplicate entry of a PO Number???

Thanks again for your help

 

[CazB]

Try this, and let me know if it works? Hoping I've picked up your field names etc properly...

Private Sub PO_No_BeforeUpdate(Cancel As Integer)
 
Dim CheckPO As Variant
CheckPO = DLookup("[Purchase Order Number]", "Orders", "[Purchase Order Number] = '" & Me.PO_No & "'")
If Not IsNull(CheckPO) Then
MsgBox "Duplicate PO Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
Cancel = True
Me.PO_No.Undo
 
 
Else:
End If
End Sub

 

[chriscook]

Hi,

Thanks for the code, I have tried this but it still lets me enter a duplicate PO number.

 

[CazB]

check I've got the table and field names right...

 

[chriscook]

They seem correct.

The Table is called Orders. With the field in Orders called Purchase Order Number

The field in the form is called PO_No

 

[Minty]

I would remove the nulls from your PO field, then set the index to not allow duplicates.
Might take a bit of initial tidying up but then you are good to go.

 

[jdraw]

As burrina said in post 7, you may want to keep "things" separate or treat things as they really are in your business ---Orders, quotes...


See this link to see how Statement can represent

Invoice, Order or Quote

This may not apply to you, but may be worth considering.
Good luck.

 

[chriscook]

I believe i have removed all null values.

However, when I then change the index to not allow duplicates, the following error message appears:

The changes you requested to the table were not suxxessful because they would create duplicate values in the index, primary key or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

Currently everything is seperate on the database. We do not use Access for quotations or Invoices.

 

[Minty]

If there are no nulls and no PO's set to "" (empty string - not the same) I would check for duplicates using the query wizard.

 

[CazB]

to look for duplicates... create a query based on your table, group by the PO Number and count the number of records.... look for any where the count is >1

 

[chriscook]

Brilliant, Thanks for your help. Got rid of a couple of duplicate PO numbers and it now allows me to set the index of the Purchase order number not to allow duplicates.

Thanks for your help everyone.