help with no duplicate in form whitin the same day.

[hmho]

Hello,

I have table name TbleProductSales and it has these fields [IDNumber], [SerialNumber], [EndingNumber] and [SaleDate]. What I like to do is prevent duplicate SerialNumber whitin each day's SaleDate for example SerialNumber 123 can have ending number only once a day. IDNumber is the key and it is auto number SerialNumber is text and SaleDate is date. Also if someone tries to enter duplicate within the same date I would like pop up message and delete the entry. I tried little bit in BeforeUpdate but I'm not good in codes and I recently started using access so i need help.

Thanks

 

[CameronM]

If your user choses the serialnumber from a combo box, then you can code the BeforeUpdate event of that combobox to check that the serialnumber has not already been used.

In the snippet below I am using a combobox called cboSerialNumber.

Private Sub cboSerialNumber_BeforeUpdate(Cancel As Integer)
    'check that this serial number has not already been entered for the current date
    Dim strSQL As String
    Dim rst As DAO.Recordset
 
    'create a recordset to return values from TbleProductSales
    strSQL = "SELECT IdNumber FROM TbleProductSales WHERE SerialNumber = '" & Me.cboSerialNumber & "' AND SaleDate = Date()"
    Set rst = CurrentDb.OpenRecordset(strSQL)
 
    'if the recordset contains any records then this serial number has already been used
    If rst.RecordCount > 0 Then
        MsgBox "This serial number has already been used"
        'cancel the combobox selection which forces the user to select another value
        Cancel = -1
    End If
 
    'dispose of the recordset
    rst.Close
    Set rst = Nothing
End Sub

 

[hmho]

Hello,

I tried it but I got error in "Dim rst As DAO.Recordset" the error says "Compile error User defined type not defined" Please help.

Thanks

 

[CameronM]

The syntax works for Access 2000-2003, but you will have to make sure you have referenced DAO (Microsoft DAO 3.6 Object Library). Go to Tools -> References in the VB editor screen.