Hi. I am new to this group and I am not a profesional. I am 66 years old and a retired Bank Officer active with Retired Bank Officers community. (1 Viewer)

[Suresh1957]

I get error as "the changes you requested to the table were successful because they would create duplicate values in the index, primary key or relationship" when I try to insert a record into local table, details gathered from a form. My exposure to computer programming is only functional and not exhaustive. I learnt this as a hobby after my retirement. So, please bear with me if I sound unprofessional or not worthy of this community. The code is as follows:

Dim sqlInsrt As String

Dim rstDbase As DAO.Recordset

Dim sqldbase As String
sqldbase = "Select * from LifeMembersTemp order by StaffNo"
Set rstDbase = CurrentDb.OpenRecordset(sqldbase, dbOpenDynaset, dbSeeChanges)

Dim zStaffNo As Long
zStaffNo = Me.txtLMStaffNo
Dim rstLife As DAO.Recordset
Dim sqlLife As String

sqlLife = "Select * from dbo_LifeMembers where StaffNo=""" & zStaffNo & """"
Set rstLife = CurrentDb.OpenRecordset(sqlLife, dbOpenDynaset, dbSeeChanges)

If rstLife.RecordCount = 0 Then
rstDbase.AddNew
rstDbase!MemGender = Me.txtMEMGENDER

rstDbase!MemName = Me.txtMemName
'rstDbase!NOMGENDER = Me.txtNOMGENDER
rstDbase!Spouse = Me.txtASSONAME
Me.txtNominee = Me.txtASSONAME
rstDbase!Add1 = Me.txtAdd1
rstDbase!Add2 = Me.txtAdd2
rstDbase!Add3 = Me.txtAdd3
rstDbase!Place = Me.txtPlace
rstDbase!Pincode = CLng(Me.txtPin)
rstDbase!Mobile = Me.txtMOBILENO
rstDbase!District = Me.txtDistrict
rstDbase!Email = Me.txtEmail
rstDbase!State = Me.txtState
rstDbase!Scale = Me.cmbScale
rstDbase!MemDOB = Me.txtMEMDOB
rstDbase!LastWorked = Me.txtBranch
Nz (rstDbase!MemDOJ = Me.txtDOJ)
rstDbase![Felicitation75] = Me.txtFeli75
rstDbase![Felicitation90] = Me.txtFeli90
rstDbase![LastCircle] = Me.txtCircle
rstDbase![MemDOR] = Me.txtDOR
rstDbase![PenPf] = Me.cmbPenPF
rstDbase!Mode = Me.cmbMode
If Me.txtDOD = Null Or Me.txtDOD = "" Then
rstDbase!MemDOD = Null
End If
rstDbase!PPONumber = Me.txtPPO
rstDbase![PenAccount] = Me.txtPENACCOUNT
If Me.txtResign = "" Or Me.txtResign = Null Then
rstDbase!MemExitDate = Null
End If
rstDbase!STAFFNO = Me.txtLMStaffNo
If Me.txtAssoNum = "" Then
rstDbase!ASSONUM = Null
End If
rstDbase!MEMNO = Me.txtMemNo
Nz (rstDbase!DtOfEnrol = Me.txtEnrollDt)
rstDbase!MemType = Me.txtMEMTYPE
rstDbase!MemStatus = Me.txtMEMSTATUS
rstDbase!Updated = Now()
'AT THIS POINT I GET ERROR
rstDbase.Update

MsgBox "Record with Membership No : " & Me.txtMemNo & "added !", vbExclamation
DoCmd.RunCommand acCmdSaveRecord
Else
MsgBox "Data Already Exists! Add New Member process aborted!", vbExclamation
End If

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[bob fitz]

I get error as "the changes you requested to the table were successful because they would create duplicate values in the index, primary key or relationship" when I try to insert a record into local table, details gathered from a form. My exposure to computer programming is only functional and not exhaustive. I learnt this as a hobby after my retirement. So, please bear with me if I sound unprofessional or not worthy of this community. The code is as follows:

Dim sqlInsrt As String

Dim rstDbase As DAO.Recordset

Dim sqldbase As String
sqldbase = "Select * from LifeMembersTemp order by StaffNo"
Set rstDbase = CurrentDb.OpenRecordset(sqldbase, dbOpenDynaset, dbSeeChanges)

Dim zStaffNo As Long
zStaffNo = Me.txtLMStaffNo
Dim rstLife As DAO.Recordset
Dim sqlLife As String

sqlLife = "Select * from dbo_LifeMembers where StaffNo=""" & zStaffNo & """"
Set rstLife = CurrentDb.OpenRecordset(sqlLife, dbOpenDynaset, dbSeeChanges)

If rstLife.RecordCount = 0 Then
rstDbase.AddNew
rstDbase!MemGender = Me.txtMEMGENDER

rstDbase!MemName = Me.txtMemName
'rstDbase!NOMGENDER = Me.txtNOMGENDER
rstDbase!Spouse = Me.txtASSONAME
Me.txtNominee = Me.txtASSONAME
rstDbase!Add1 = Me.txtAdd1
rstDbase!Add2 = Me.txtAdd2
rstDbase!Add3 = Me.txtAdd3
rstDbase!Place = Me.txtPlace
rstDbase!Pincode = CLng(Me.txtPin)
rstDbase!Mobile = Me.txtMOBILENO
rstDbase!District = Me.txtDistrict
rstDbase!Email = Me.txtEmail
rstDbase!State = Me.txtState
rstDbase!Scale = Me.cmbScale
rstDbase!MemDOB = Me.txtMEMDOB
rstDbase!LastWorked = Me.txtBranch
Nz (rstDbase!MemDOJ = Me.txtDOJ)
rstDbase![Felicitation75] = Me.txtFeli75
rstDbase![Felicitation90] = Me.txtFeli90
rstDbase![LastCircle] = Me.txtCircle
rstDbase![MemDOR] = Me.txtDOR
rstDbase![PenPf] = Me.cmbPenPF
rstDbase!Mode = Me.cmbMode
If Me.txtDOD = Null Or Me.txtDOD = "" Then
rstDbase!MemDOD = Null
End If
rstDbase!PPONumber = Me.txtPPO
rstDbase![PenAccount] = Me.txtPENACCOUNT
If Me.txtResign = "" Or Me.txtResign = Null Then
rstDbase!MemExitDate = Null
End If
rstDbase!STAFFNO = Me.txtLMStaffNo
If Me.txtAssoNum = "" Then
rstDbase!ASSONUM = Null
End If
rstDbase!MEMNO = Me.txtMemNo
Nz (rstDbase!DtOfEnrol = Me.txtEnrollDt)
rstDbase!MemType = Me.txtMEMTYPE
rstDbase!MemStatus = Me.txtMEMSTATUS
rstDbase!Updated = Now()
'AT THIS POINT I GET ERROR
rstDbase.Update

MsgBox "Record with Membership No : " & Me.txtMemNo & "added !", vbExclamation
DoCmd.RunCommand acCmdSaveRecord
Else
MsgBox "Data Already Exists! Add New Member process aborted!", vbExclamation
End If

Why don't you use the table as the Record source of your form and bind the controls on the form to the fields of the table?

 

[Suresh1957]

Why don't you use the table as the Record source of your form and bind the controls on the form to the fields of the table?

Sir, thank you very much for the response. I felt by directly displaying the data on the form, the same could be changed inadvertently by my friends/colleagues. Hence, I thought it fit to use recordset and display them on the form, so that the table remains unaltered unless the same is done with Update Button. I am not aware how the form could be made read only. Further, I use the same form for Display of records, Add New Record, and Update existing Record.

As regards the issue I posted, I realised later that the LifeMembersTemp table was not cleared before appending new record and it created appending duplicate records to the LifeMembers table. On deleting the existing records in LIfeMemberTemp I could add new record to it and then append it to the LifeMember Table. Lateron, I dispensed with the LifeMembersTemp table and directly adding the recordset to LifeMembers table.

I do not whether my narration above is clear. Thanks for everything. I will do come back later when encounter any fresh issues. Thank you.

 

[MajP]

1. Welcome
2. An admin will likely move this thread from the New Member forum to one of the other forums for answering a question. You should not have to create a new thread for your question.
3. You can make your code nicer to read using the edit icons at the top. Especially the code tag </>

Dim sqlInsrt As String
Dim rstDbase As DAO.Recordset
Dim sqldbase As String

sqldbase = "Select * from LifeMembersTemp order by StaffNo"
Set rstDbase = CurrentDb.OpenRecordset(sqldbase, dbOpenDynaset, dbSeeChanges)

Dim zStaffNo As Long
zStaffNo = Me.txtLMStaffNo
Dim rstLife As DAO.Recordset
Dim sqlLife As String

sqlLife = "Select * from dbo_LifeMembers where StaffNo=""" & zStaffNo & """"
Set rstLife = CurrentDb.OpenRecordset(sqlLife, dbOpenDynaset, dbSeeChanges)

If rstLife.RecordCount = 0 Then
rstDbase.AddNew
rstDbase!MemGender = Me.txtMEMGENDER

4. Almost everyone on this forum is a hobbyist. Many are first time access users

So, please bear with me if I sound unprofessional or not worthy of this community

you are probably more knowledgeable than the majority.

5. 99% of the time when someone is using an unbound form in access it is an overly complicated solution to a non problem. 99% of the time an unbound form is not necessary, way more complicated, and loses a lot capability. So you are working way harder for a poorer solution.

I could replace all that code with a bound form and 2 or 3 lines of code

Properties that lock / unlock a form are
allow additions
allow deletions
alllow edits

At the individual control level
enable
locked

Most important access event is the Form's Before Update event
Here you can validate data and cancel any update
So the simplest solution is to have in the forms before update a message
"Do you want to save Changes" yes/no
If they hit no it cancels the before update and returns to previous values.

You could make it more advanced by having a button to unlock the form for editing. So in order to edit they have to do something purposeful.

 

[MajP]

I reviewed this code and that are a lot of problems and it would never works as written. I put some suggestions for in general if you were attempting this. Again I would scrap this idea and do it simply with a bound form. There are a lot of ways to make it better. It is not very readable.

Public Sub insertValues()
    'All declarations at the top. Far easier to read and write

    Dim sqlInsrt As String
    Dim rstDbase As DAO.Recordset
    Dim sqldbase As String
    Dim rstLife As DAO.Recordset
    Dim sqlLife As String
    Dim zStaffNo As Long

    sqldbase = "Select * from LifeMembersTemp order by StaffNo"
    Set rstDbase = CurrentDb.OpenRecordset(sqldbase, dbOpenDynaset, dbSeeChanges)

    'I would check this value exists
    If zStaffNo & "" <> "" Then
      zStaffNo = Me.txtLMStaffNo
    Else
      Exit Sub
    End If

    ' single Quotes are far easier to read.

    sqlLife = "Select * from dbo_LifeMembers where StaffNo='" & zStaffNo & "'"
    Set rstLife = CurrentDb.OpenRecordset(sqlLife, dbOpenDynaset, dbSeeChanges)

    ' Whenever taking a record count you will throw an error if no records returned. Also you must traverse the
    ' RS to get an accurate count
    ' Check that records exist and then traverse the recordset
    ' As written this will likely always return 0 since you did not traverse the rs

    ' If Not (rstLife.BOF And rstLife.EOF) Then
    '   rstLife.moveLat
    '   rstLife.MoveFirst
    ' Else
    '   Exit Sub
    ' End If
    ' If rstLife.RecordCount = 0 Then

    ' However a far easier solution is the dcount
    If DCount("*", dbo_LifeMembers, "StaffNo = '" & zStaffNo & "'") <> 0 Then
      MsgBox "Staff Exists", vbInformation
      Exit Sub
    End If

    'People overuse with end with blocks but this is better in this case
    With rstDbase
        .AddNew
              Me.txtNominee = Me.txtASSONAME
             !MemGender = Me.txtMEMGENDER
             !MemName = Me.txtMemName
            ' !NOMGENDER = Me.txtNOMGENDER
             !Spouse = Me.txtASSONAME
             !Add1 = Me.txtAdd1
             !Add2 = Me.txtAdd2
             !Add3 = Me.txtAdd3
             !Place = Me.txtPlace
             !Pincode = CLng(Me.txtPin)
             !Mobile = Me.txtMOBILENO
             !District = Me.txtDistrict
             !Email = Me.txtEmail
             !State = Me.txtState
             !Scale = Me.cmbScale
             !MemDOB = Me.txtMEMDOB
             !LastWorked = Me.txtBranch
              ![Felicitation75] = Me.txtFeli75
             ![Felicitation90] = Me.txtFeli90
             ![LastCircle] = Me.txtCircle
             ![MemDOR] = Me.txtDOR
             ![PenPf] = Me.cmbPenPF
             !Mode = Me.cmbMode
             !PPONumber = Me.txtPPO
             ![PenAccount] = Me.txtPENACCOUNT
             !StaffNo = Me.txtLMStaffNo
             !MEMNO = Me.txtMemNo
             !MemType = Me.txtMEMTYPE
             !MemStatus = Me.txtMEMSTATUS
             !Updated = Now()
      
            ' This code will NEVER WORK always returns false. You can not check for null with an =
            ' me.txtDOD = null   will always return false even when null
            ' The check is
            ' if isnull(me.txtDOD)
            ' The better check for null or empty string is
            ' if Me.txtDoD & "" = ""   this uses null concatenation in vba
             'Also you can use a single line if in these cases easier to read

              If Me.txtDOD & "" = "" Then !MemDOD = Null
              If Me.txtResign & "" = "" Then !MemExitDate = Null
              If Me.txtAssoNum = "" Then !ASSONUM = Null
             ' However what is the point? This code does nothing. A new record will be null
            

            'This code does not make sense and suprised it compiles
            Nz (!MemDOJ = Me.txtDOJ)
            Nz (!DtOfEnrol = Me.txtEnrollDt)
           'Also people forget the second argument of NZ. I always explicitly assign a value for null case

        .Update
end sub

 

[arnelgp]

also, you can put a Unique index on field StaffNo of LifeMembersTemp table (why temp?)
and immediately run an Insert Query( using Currentdb), without the need to test whether or not it exists or not.
there is no need for a lengthly VBA.

 

[The_Doc_Man]

As our colleague MajP suggested, I have moved your thread to a more appropriate heading. We use the "Intro" section for personal introductions.

 

[Suresh1957]

I reviewed this code and that are a lot of problems and it would never works as written. I put some suggestions for in general if you were attempting this. Again I would scrap this idea and do it simply with a bound form. There are a lot of ways to make it better. It is not very readable.

Public Sub insertValues()
    'All declarations at the top. Far easier to read and write

    Dim sqlInsrt As String
    Dim rstDbase As DAO.Recordset
    Dim sqldbase As String
    Dim rstLife As DAO.Recordset
    Dim sqlLife As String
    Dim zStaffNo As Long

    sqldbase = "Select * from LifeMembersTemp order by StaffNo"
    Set rstDbase = CurrentDb.OpenRecordset(sqldbase, dbOpenDynaset, dbSeeChanges)

    'I would check this value exists
    If zStaffNo & "" <> "" Then
      zStaffNo = Me.txtLMStaffNo
    Else
      Exit Sub
    End If

    ' single Quotes are far easier to read.

    sqlLife = "Select * from dbo_LifeMembers where StaffNo='" & zStaffNo & "'"
    Set rstLife = CurrentDb.OpenRecordset(sqlLife, dbOpenDynaset, dbSeeChanges)

    ' Whenever taking a record count you will throw an error if no records returned. Also you must traverse the
    ' RS to get an accurate count
    ' Check that records exist and then traverse the recordset
    ' As written this will likely always return 0 since you did not traverse the rs

    ' If Not (rstLife.BOF And rstLife.EOF) Then
    '   rstLife.moveLat
    '   rstLife.MoveFirst
    ' Else
    '   Exit Sub
    ' End If
    ' If rstLife.RecordCount = 0 Then

    ' However a far easier solution is the dcount
    If DCount("*", dbo_LifeMembers, "StaffNo = '" & zStaffNo & "'") <> 0 Then
      MsgBox "Staff Exists", vbInformation
      Exit Sub
    End If

    'People overuse with end with blocks but this is better in this case
    With rstDbase
        .AddNew
              Me.txtNominee = Me.txtASSONAME
             !MemGender = Me.txtMEMGENDER
             !MemName = Me.txtMemName
            ' !NOMGENDER = Me.txtNOMGENDER
             !Spouse = Me.txtASSONAME
             !Add1 = Me.txtAdd1
             !Add2 = Me.txtAdd2
             !Add3 = Me.txtAdd3
             !Place = Me.txtPlace
             !Pincode = CLng(Me.txtPin)
             !Mobile = Me.txtMOBILENO
             !District = Me.txtDistrict
             !Email = Me.txtEmail
             !State = Me.txtState
             !Scale = Me.cmbScale
             !MemDOB = Me.txtMEMDOB
             !LastWorked = Me.txtBranch
              ![Felicitation75] = Me.txtFeli75
             ![Felicitation90] = Me.txtFeli90
             ![LastCircle] = Me.txtCircle
             ![MemDOR] = Me.txtDOR
             ![PenPf] = Me.cmbPenPF
             !Mode = Me.cmbMode
             !PPONumber = Me.txtPPO
             ![PenAccount] = Me.txtPENACCOUNT
             !StaffNo = Me.txtLMStaffNo
             !MEMNO = Me.txtMemNo
             !MemType = Me.txtMEMTYPE
             !MemStatus = Me.txtMEMSTATUS
             !Updated = Now()
     
            ' This code will NEVER WORK always returns false. You can not check for null with an =
            ' me.txtDOD = null   will always return false even when null
            ' The check is
            ' if isnull(me.txtDOD)
            ' The better check for null or empty string is
            ' if Me.txtDoD & "" = ""   this uses null concatenation in vba
             'Also you can use a single line if in these cases easier to read

              If Me.txtDOD & "" = "" Then !MemDOD = Null
              If Me.txtResign & "" = "" Then !MemExitDate = Null
              If Me.txtAssoNum = "" Then !ASSONUM = Null
             ' However what is the point? This code does nothing. A new record will be null
           

            'This code does not make sense and suprised it compiles
            Nz (!MemDOJ = Me.txtDOJ)
            Nz (!DtOfEnrol = Me.txtEnrollDt)
           'Also people forget the second argument of NZ. I always explicitly assign a value for null case

        .Update
end sub

Thanks a lot Sir. I have learnt a lot from this discussion. I will adopt the above in my codes and come back.