Solved INSERT Query not returning errors - yet not inserting into table (1 Viewer)

[theDBguy]

Apologies that's my bad as I had to type it out since can't highlight it. But the parens are there

I figured, since your function has it. So, try using dbFailOnError next and let us know how it goes.

 

[Uncle Gizmo]

It works OK for me - See Attached

 

[Uncle Gizmo]

And this version, capturing the info from the text-boxes - See Attached - INSERTQryNotReturningErrors_2a.zip

 

[Ryan142]

Okay, if you can get the Debug.Print to work, you could copy and paste the resulting SQL statement into the Query Designer and try to Run it from there. If there's any error with it, you should get a message. Barring that, you could try changing the last line of your code into this:

CurrentDb.Execute strSQL0, dbFailOnError

If you run it again with that new code, let us know if you get an error message.

I managed to fix it and ran the code into a query and got this message:

Microsoft Access can't append all the records in the append query.

Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn't add 1 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.

 

[Uncle Gizmo]

Have you got any unique index's setup on on the table? A unique index will stop you adding an identical copy of a record and it will not return an error message.

 

[Uncle Gizmo]

That error message usually means you're trying to put the wrong type of data in the wrong type of field.

 

[Ryan142]

That error message usually means you're trying to put the wrong type of data in the wrong type of field.

Ok I think I may have found the issue in that within in my table it has a related ID for another table, I wanted to add the code beforehand for to check that it was working so I'll add that new code and see if that should fix it

 

[theDBguy]

Ok I think I may have found the issue in that within in my table it has a related ID for another table, I wanted to add the code beforehand for to check that it was working so I'll add that new code and see if that should fix it

Hi. Glad to hear you found the cause of the problem. Good luck with your project.

 

[plog]

strSQL1 = "INSERT INTO tblUserDetails(FirstName, LastName, HouseNumber, StreetName, City, PostCode, MobileNum, Email, CardNum, ExpiryDate, SecurityCode, Remarks) "
strSQL2 = "Values(

Need a space between table name and parenthesis as well as space between Values and parenthesis.

 

[Micron]

That error message usually means you're trying to put the wrong type of data in the wrong type of field.

A lot of the time someone who posts that message here doesn't read the whole thing (or at least doesn't understand it). I covers 4 situations and tells you how many records didn't get affected and the reasons why for each. If there are 2 reasons, you get both counts. In the post above, there is only one reason and it has a count of 1. It is because of a key violation.

 

[Cronk]

@Ryan142, you have a problem with your formatting of the ExpiryDate data. Date data should be formatted #mm/dd/yyyy#

Access will interpret the 14/5/2020 in your #17 correctly because there is no 14th month. But if you were trying to insert #1/3/2020# meaning 1 March 2020, the SQL wound save 3 January 2020

 

[isladogs]

I changed it to MsgBox strSQL0 as you said and I got a pop up window returning this: "INSERT INTO tblUserDetails(FirstName, LastName, HouseNumber, StreetName, City, PostCode, MobileNum, Email, CardNum, ExpiryDate, SecurityCode, Remarks) Values("Test", "Name", 14, "Generic Road", "Generic City", "1AB 2BC", "12345678900", "generic@email.com", "1111222233334444", #14/05/2020#, 111, "Test")

I'm surprised that nobody has mentioned this but you should NEVER store all 3 fields above due to data protection regulations.
It may be acceptable to store the card number providing it is encrypted but the the expiry date and security code should NEVER be stored.

 

[Ryan142]

I'm surprised that nobody has mentioned this but you should NEVER store all 3 fields above due to data protection regulations.
It may be acceptable to store the card number providing it is encrypted but the the expiry date and security code should NEVER be stored.

Thanks for the tip, and yes I've guessed that this is highly likely to not be good practice if not illegal. This is just a simple A Level project however for my Com Science A Level so need not worry about leaking data . Our teacher's said that anything to do with data storage and such can be largely ignored, as long as in the document we provide we aknowledge this and recognise it as an existing threat

 

[Ryan142]

Also glad to say that everything is now working and I appreciate all of the help on this one thanks guys!