Link to home
Start Free TrialLog in
Avatar of ghaphis
ghaphis

asked on

primary key violation error when running sql insert into table

In this database, in the form "Child Information" I wrote SQL code to add all the data in the unbound fields into the table. In particular I am concerned about ensuring that birth date information is entered in a single format (MDY). When I press the button which runs the VBA code, I get the message "The changes you requested ... were not successful because they would create duplicate values..." But as far as I can tell, none of the data I have entered is actually duplicate. Is there something wrong with the SQL I used? Or is the database not structured well?

Thanks

JTT
IP-Data-Base-v2-EE-210410.mdb.zip
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

There might be a unique key/index on another column/s of the table.
It is also possible the SQL has an error, what does it look like?
I think the problem is that the primary key on your table is IPID but this is comprised of information that a user enters manually.

When I tried it,  I picked village 1 and entered Record 001 -  these were combined to form IPID 1001 which already existed and so the insert failed.

I suggested you change IPID to an autonumber field and don't mention it in your insert statements - access will take care of it for you.
Check the correct code below:
Code didn't attach...
Private Sub Command19_Click()
On Error GoTo Err_Command19_Click
    
'~+~+~+~+~+~+~+~+~+~+~+~

' ==> These steps are adding a new record with the new Key before the Insert SQL

'    'It was not possible to put together the villageid and varrecord values without adding them. For instance,
'    'it kept taking villageid = 1 and record = 2 and make IPID 3 when I wanted it to be 12. As a result, to get
'    'it not to add, I had to create unbound fields and add them together in an unbound field called varipid.
'    Me.record = Me.varrecord
'    Me.varipid.Value = Me.varvillageid + Me.varrecord
'    'Then we set the bound field IPID equal to the unbound field varipid.
'    Me.IPID.Value = Me.varipid

'~+~+~+~+~+~+~+~+~+~+~+~

'First thing to do is check the validity of data
'If checkValid() Is False Then
 '   MsgBox ("Data not valid")
  '  Exit Sub
'End If

'
''Next, create the sql string
''missing syntax operator between childname and record
'sqltext = ""
'sqltext = sqltext + "insert into Child_Information values('"
'sqltext = sqltext + varvillageid.Value + "',"
'sqltext = sqltext + "'" + varchildname.Value + "',"
'sqltext = sqltext + "'" + varrecord.Value + "',"
'sqltext = sqltext + "'" + varipid.Value + "',"
'sqltext = sqltext + makeUKDate(bdate_day.Value, bdate_month.Value, bdate_year.Value)
'sqltext = sqltext + ")"


Dim intID As Integer
' When concatenating strings, use ampersand '&' instead of the plus sign '+'
intID = Me.varvillageid & Me.varrecord

sqltext = ""
sqltext = sqltext & "insert into Child_Information values('"
sqltext = sqltext & varvillageid.Value & "',"
sqltext = sqltext & "'" & varchildname.Value & "',"
sqltext = sqltext & "'" & varrecord.Value & "',"
sqltext = sqltext & "'" & intID & "',"
sqltext = sqltext & makeUKDate(bdate_day.Value, bdate_month.Value, bdate_year.Value)
sqltext = sqltext & ")"


Debug.Print sqltext
DoCmd.RunSQL (sqltext)

'Close the form if there is any data in it
If Me.Dirty Then Me.Dirty = False
DoCmd.Close
    

Exit_Command19_Click:
    Exit Sub

Err_Command19_Click:
    MsgBox Err.Description
    Resume Exit_Command19_Click
    
End Sub

Open in new window

You should use & for string concatenation, not +
Date vaues between # characters should be in US format (i.e. mm/dd/yy).  Your makeUKDate() function is building a UK date, which is incorrect.
For example, makeUKDate("12", "03", "10") currently yields the string "#12/03/10#", which corresponds to the 3rd of December 2010 - not the 12th of March 2010 as you want.
 
You should also start every module with the statement "Option Explicit", to force you to declare all the variables that you use.
Otherwise, a simple spelling mistake in a variable name will cause a new variable to be silently created, leading to very confusing behaviour that's difficult for us mere humans to debug.
All the above is true and good advice.

However, the question you asked was about the duplicate records message and the design of your database.

The failure to insert the record is due to violation of the uniqueness requirement of the primary key coun - IPID

Making this autonumber will solve the problem you asked about.
Now you know how to concatenate fields (rather than add them), you can use bound controls on the form, which simplifies to just:

Option Explicit
Option Compare Database

Private Sub Command19_Click()

    On Error GoTo DoneError
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close

    Exit Sub

DoneError:
    MsgBox Err.Description

End Sub

Private Sub Form_BeforeUpdate(pintCancel As Integer)
    varipid = varvillageid & varrecord
    Dateofbirth = DateSerial(bdate_year, bdate_month, bdate_day)
End Sub

Open in new window

By the way, the "Me." and ".Value" bits in your original code are superfluous too!  :-)
Come to think of it, the save operation happens implicitly on close, so you don't need it either.
You'd probably do well to remove the Navigation Buttons and Record Selectors and also disallow Deletions and Edits too.

Option Explicit
Option Compare Database

Private Sub Command19_Click()
    DoCmd.Close
End Sub

Private Sub Form_BeforeUpdate(pintCancel As Integer)
    varipid = varvillageid & varrecord
    Dateofbirth = DateSerial(bdate_year, bdate_month, bdate_day)
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Jez Walters
Jez Walters
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Jez -
If you check an earlier question from this individual, you will see that he, in fact, wants his date to be formatted as "DD/MM/YYYY" (see: https://www.experts-exchange.com/questions/25659990/Forcing-date-format-with-session-LCID.html)

Paul -
With the code as it was originally set up, simply changing the key to an auto-number would not thoroughly solve the problem. The problem in the original code was that he was actually creating a new row in the VBA before running the INSERT SQL. Since he was trying to insert a record with the same key as a row that he had just created he got the error. If he had just changed the key to autonumber, the VBA would have created a new record, then the subsequent INSERT would create a second record with a different key. This second record would be valid, but it would leave the first (invalid) record  which was created by the VBA.
This, along with the use of an ampersand instead of a plus sign, are all documneted in the code I submitted  at 05:54 AM this morning.
If you want the date formated as DD/MM/YYYY, open your "Child_Information" table in Design View and click anywhere in the "dateofbirth" row.  Next, look at the General tab of the Field Properties at the bottom of the Window and set the Format propert value to dd/mm/yyyy.
The code I supplied in http://#a31375785 above is region agnostic.  In other words, it works in the UK (where dd/mm/yy is the norm) and in the US (where mm/dd/yy is the norm).
As I said earlier (http://#a31371666), enclosing a date value between # characters requires the enclosed date value to be in mm/dd/yy form.
Yes, Jez, you are correct that your code is region aqnostic; but it appears in this case that is not what the person wants. Again, I refer you back to the previous question (see: https://www.experts-exchange.com/questions/25659990/Forcing-date-format-with-session-LCID.html)
from which there is this quote:

"I am trying to use VB to force my date format to be DD/MM/YYYY in Access. Some computers we use for data entry will have the session.LCID set to US time (and thus MM/DD/YYYY) and some set to UK time (DD/MM/YYYY). For the purposes of this question, let's just say that we can't change the setting manually in the Control Panel."
shambalad,
I spotted the double-creation of records too, which is why I reverted the form to using bound controls.  This considerably simplifies the form and should be reason enough to adopt the solution I proposed - unless there's something I've missed, of course!  :-)
As you're no doubt aware (but for the benefit of ghaphis) Date/Time values are always stored internally as a double-precision number representing the date as the number seconds since 30th December 1899.
The format that Date/Time values are displayed in is entirely independent of this, and is specified in the Format property of the Data/Time field as part of the table Design.
No problem. As is often the case, there can be more than one way to get to the same destination.
Todd
Which just leaves the matter of how ghaphis has got along with the bound form I posted in http://#a31375785.  Did it all work as expected?
Just in case you weren't aware there are rather a lot of issues with your database as it stands, not least of which is the number of orphaned Functions/Sub.
Let me know if you'd like of few pointers as to which problems to address.
For example, you'd do well to name your controls to reflect their purpose (e.g. cmdImmunizationData instead of Command20 etc.).
It would also be a good idea to adopt a better naming convention, with standard prefixes for each Data Type (e.g. "cmb" for Combo Box "txt" for Text Box, "str" for String, "var" for Variant etc.).
Sorry, I was mistaken about the orphaned Functions/Subs - just to stop you going on a wild goose chase!  :-)