Solved Table key violation error

[Gr3g0ry]

this is my query:

DoCmd.RunSQL "INSERT INTO CUSTOMERDETAILS (CUSTOMERID, CUSTOMERNAME, CUSTOMERADDRESS, CONTACT, EMAIL, ORGANIZATIONTYPE, ORGANIZATIONID,STATUS) VALUES (" & Me.CUSTOMERID.Value & ", '" & Me.cusname.Value & "', '" & Me.cusaddress.Value & "', '" & Me.CONTACT.Value & "', '" & Me.EMAIL.Value & "', '" & Me.orgtype.Value & "', '" & Me.orgid.Value & "', " & stat & ");"

i have checked all my field names and they are correct. i have copied and pasted the field names just to be sure.
stat is an integer variable with the value of 1.

Please help.

 

[theDBguy]

Hi. Have you checked to make sure you're not creating a duplicate customer ID?

 

[arnelgp]

how about the Me.CustomerID, does it have same value on the table?
before running the query, test first if there is a Customer with same CustomerID:

If DCount("1", "CustomerDetails", "CustomerID = " & Nz(Me.CustomerID, -9999)) > 0 Then
'There is duplicate, show some message
Msgbox "Duplicate"

Else
'Run the query
DoCmd.RunSQL …
End If

 

[Gr3g0ry]

Hi. Have you checked to make sure you're not creating a duplicate customer ID?

yes sir on numerous occasions, ive also made sure to enter only numbers

 

[Gr3g0ry]

how about the Me.CustomerID, does it have same value on the table?
before running the query, test first if there is a Customer with same CustomerID:

If DCount("1", "CustomerDetails", "CustomerID = " & Nz(Me.CustomerID, -9999)) > 0 Then
'There is duplicate, show some message
Msgbox "Duplicate"

Else
'Run the query
DoCmd.RunSQL …
End If

and i still get the same key violation error

 

[The_Doc_Man]

The error message contains something I don't see very often, a combination of errors. It is telling you (I think) that your CustomerID field isn't the data type you think it is. If that is true then what has actually happened is that the data type doesn't convert correctly so is trying to enter an invalid value, probably zero. The other fields are of type text so it would be rare for that to fail on a type conversion. You told us what was in the status variable, but you didn't tell us what was in the CustomerID control.

Speaking of value, all of your query's .Value references can be omitted. For access controls, the rule is that if the control HAS a value, the default property is .Value so you don't need to name it. That should save you some typing.

 

[Gr3g0ry]

The error message contains something I don't see very often, a combination of errors. It is telling you (I think) that your CustomerID field isn't the data type you think it is. If that is true then what has actually happened is that the data type doesn't convert correctly so is trying to enter an invalid value, probably zero. The other fields are of type text so it would be rare for that to fail on a type conversion. You told us what was in the status variable, but you didn't tell us what was in the CustomerID control.

Speaking of value, all of your query's .Value references can be omitted. For access controls, the rule is that if the control HAS a value, the default property is .Value so you don't need to name it. That should save you some typing.

so i took you advice with the .Value thing. however the error still persists even after i declared an integer variable and assigned CustomerID's value to it. CustomerId control is a text box in which an integer is entered.

 

[arnelgp]

maybe Cast it to Integer:

Dim cus as Integer
cus = CInt(Me.CustomerID & "")
f DCount("1", "CustomerDetails", "CustomerID = " & cus) > 0 Then
'There is duplicate, show some message
Msgbox "Duplicate"

Else
'Run the query
DoCmd.RunSQL …
End If

////Edit:
Integer — range from -32,768 to +32,767.
Long Integer — range from -2,147,483,648 to +2,147,483,647

 

[Gr3g0ry]

maybe Cast it to Integer:

Dim cus as Integer
cus = CInt(Me.CustomerID & "")
f DCount("1", "CustomerDetails", "CustomerID = " & cus) > 0 Then
'There is duplicate, show some message
Msgbox "Duplicate"

Else
'Run the query
DoCmd.RunSQL …
End If

////Edit:
Integer — range from -32,768 to +32,767.
Long Integer — range from -2,147,483,648 to +2,147,483,647

did as you suggested. same error persists. i totally wanna know whats happeneing here. . thanks man

 

[The_Doc_Man]

Next suggestion/question: Have you run a Compact&Repair on this database recently? This kind of behavior CAN indicate a damaged record. I've seen it before.

 

[Gr3g0ry]

Next suggestion/question: Have you run a Compact&Repair on this database recently? This kind of behavior CAN indicate a damaged record. I've seen it before.

no i havent. how do i do this please ?

 

[Gr3g0ry]

no i havent. how do i do this please ?

never mind. i just remembered

 

[theDBguy]

yes sir on numerous occasions, ive also made sure to enter only numbers

Hi. Can you post a sample copy of your db with test data? We may have to physically look at it to help you find out what's actually happening.

 

[Gr3g0ry]

Next suggestion/question: Have you run a Compact&Repair on this database recently? This kind of behavior CAN indicate a damaged record. I've seen it before.

YUP. did the Compact & Repair. then closed the file, then reopened. same eroor persists. attached is the file im working with. the from is called CUSTOMER FORM

 

[Gr3g0ry]

Hi. Can you post a sample copy of your db with test data? We may have to physically look at it to help you find out what's actually happening.

just did

 

[theDBguy]

YUP. did the Compact & Repair. then closed the file, then reopened. same eroor persists. attached is the file im working with. the from is called CUSTOMER FORM

Hi. Saw it. The explanation for the "key violation" error is because you have a 1-to-1 relationship between CUSTOMERDETAILS and RENT and RESERVATION tables. Which means, you cannot add a record into the CUSTOMERDETAILS table without a corresponding CUSTOMERID in the RENT and RESERVATION table. Hope that helps...

 

[Gr3g0ry]

Hi. Saw it. The explanation for the "key violation" error is because you have a 1-to-1 relationship between CUSTOMERDETAILS and RENT and RESERVATION tables. Which means, you cannot add a record into the CUSTOMERDETAILS table without a corresponding CUSTOMERID in the RENT and RESERVATION table. Hope that helps...

WOW .... thanks a million man.

 

[theDBguy]

WOW .... thanks a million man.

You're very welcome. We're all happy to assist. Sometimes, it is easier/faster when we can see the issue first hand. Good luck with your project.