none
If record exists, then delete current record and reset ID to previous RRS feed

  • Question

  • Good morning,

    I have a form named "Détails clientBT" that users are using to create new customers, I would like to check if that customer exists based on 2 fields (Prénom & NomClient) if it does, open a message okOnly telling that this customer already exist and when the user click on OK, then the current record is not saved and the previous ID is reset, so I don't have missing numbers in the ID's field.

    Here is an image:

    I tried this code below and I received an error 3061 - 2 parameters missing???

    Private Sub Nom _AfterUpdate()
    On Error GoTo Err_Handler
    
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
            Dim strSql As String
    
            Set dbs = CurrentDb
            strSql = "SELECT [Clientèles (étendu)].ID, [Clientèles (étendu)].Prénom, [Clientèles (étendu)].NomClient " & _
        "FROM [Clientèles (étendu)] " & _
        "WHERE ((([Clientèles (étendu)].Prénom)=[Forms]![Détails du clientBT].[Prénom]) AND (([Clientèles (étendu)].NomClient)=[Forms]![Détails du clientBT].[NomClient]))"
            Set rst = CurrentDb.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)
    
        If rst.RecordCount > 0 Then
        MsgBoxOKOnly "Cet administrateur où ce parent existe déjà dans cette base de données, ce dernier enregistrement sera détruit"
        DoCmd.SetWarnings False
            DoCmd.RunCommand acCmdSelectRecord
            DoCmd.RunCommand acCmdDeleteRecord
        DoCmd.SetWarnings True
        End If
        
    Closerst:
        rst.Close
        dbs.Close
        Set rst = Nothing
        Set dbs = Nothing
    Exit_Handler:
        Exit Sub
    Err_Handler:
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Avant mise-à-jour de Classer sous"
        Resume Exit_Handler
    End Sub
    

    thanks for your time


    Claude Larocque

    Thursday, August 25, 2016 9:47 AM

Answers

  • From your description, it seems that your 'ID Number' is an autonumber. Autonumbers are not meant for human consumption. While there are ways to reset the autonumber, I would not recommend doing that on a regular basis while the system is in use. If not having gaps in the sequence is important, then you would be better off rolling your own.

    Change the field to not be and autonumber and in the before update event of the form add the following:

    If Me.NewRecord Then
    Me!IdNumber = Nz(Dmax("IdNumber", "<yourtable>"), 0) + 1
    End If

    Thursday, August 25, 2016 10:26 AM
  • If you wish to make a generated number available again after aborting the insertion of a new row into a table, then use a key of straightforward long integer number data type rather than an autonumber, and compute the next number in sequence.  You'll find examples in CustomNumber.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the option for 'Sequential Numbering' caters for conflicts in a multi-user environment and allows the next number to be 'seeded' if desired.  The number is generated, and adjusted in the case of a conflict with another user inserting a new row simultaneously, before the row is saved, so there is no question of the row being deleted if the insertion is undone.

    I'm concerned that you appear to be treating the Prénom & NomClient columns as a candidate key, however.  Personal names can legitimately be duplicated, and this is not uncommon, even in quite small populations.  To distinguish between two clients of the same name you might be able to use some other data in the row.  In your case you are also entering a company name, but as I once worked with two Maggie Taylors, that cannot be assumed to be a reliable tie-breaker.

    BTW the error which you received is due to your referencing the form's controls when building the SQL statement, rather than concatenating the values of the controls (delimited with literal quotes characters in each case) into the string expression.

    Ken Sheridan, Stafford, England



    Thursday, August 25, 2016 5:54 PM

All replies

  • From your description, it seems that your 'ID Number' is an autonumber. Autonumbers are not meant for human consumption. While there are ways to reset the autonumber, I would not recommend doing that on a regular basis while the system is in use. If not having gaps in the sequence is important, then you would be better off rolling your own.

    Change the field to not be and autonumber and in the before update event of the form add the following:

    If Me.NewRecord Then
    Me!IdNumber = Nz(Dmax("IdNumber", "<yourtable>"), 0) + 1
    End If

    Thursday, August 25, 2016 10:26 AM
  • Alphonse is perfectly correct.

    The primary key should not be messed with.  Instead, if numbering is of some importance, then create your own field to store it (Perhaps ClientNo) in the same table and then you can use a simply DCount() or DMax() to determine the last number and act accordingly.


    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Thursday, August 25, 2016 1:13 PM
  • If you wish to make a generated number available again after aborting the insertion of a new row into a table, then use a key of straightforward long integer number data type rather than an autonumber, and compute the next number in sequence.  You'll find examples in CustomNumber.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the option for 'Sequential Numbering' caters for conflicts in a multi-user environment and allows the next number to be 'seeded' if desired.  The number is generated, and adjusted in the case of a conflict with another user inserting a new row simultaneously, before the row is saved, so there is no question of the row being deleted if the insertion is undone.

    I'm concerned that you appear to be treating the Prénom & NomClient columns as a candidate key, however.  Personal names can legitimately be duplicated, and this is not uncommon, even in quite small populations.  To distinguish between two clients of the same name you might be able to use some other data in the row.  In your case you are also entering a company name, but as I once worked with two Maggie Taylors, that cannot be assumed to be a reliable tie-breaker.

    BTW the error which you received is due to your referencing the form's controls when building the SQL statement, rather than concatenating the values of the controls (delimited with literal quotes characters in each case) into the string expression.

    Ken Sheridan, Stafford, England



    Thursday, August 25, 2016 5:54 PM
  • Thank you all for your answer,

    Ken, this link was very useful, I understand and did the appropriate changes.

    Good Sunday

    Claude


    Claude Larocque

    Sunday, August 28, 2016 6:37 AM