Insert Into = Type Mismatch

[joelmeaders]

I built a function that automatically searches through unbound text boxes based on certain tags they have. The text boxes are named with the field name they belong to. It takes the names and the values, creates an sql string and adds a new record.



In this case the function builds an INSERT string to add a record and the string is as follows (example):

INSERT INTO [tbl_Invoices] ([InvoiceEntered], [InvoiceType], [InvoiceNo], [InvoiceDate], [InvAmount], [Unit], [ReceivedAgency], [UserName], [PaymentType], [ContractNo], [AgencyRefNo]) VALUES ('2/7/2012 9:22:58 AM', 'Misc', '123', '3/2/2012', '56', 'MOOET', '2/7/2012', 'meadejx', 'General A/P', '606M15', '606M15');

It is assigned to mySQL and it is ran using:
CurrentDb.Execute mySQL, dbFailOnError

This runs perfectly the first time. The form and all values are then reset with a reset function and can be used again to enter or work another invoice.

I have never had problems with this function before and it always has worked until I just upgraded everything to Access 2007 formats. Now on the second consecutive entry I get:
ERROR - Type Mismatch
on the CurrentDb.Execute line.

I can close the form, open it again, enter all of the same values that failed previously and it saves perfectly. Once I enter another it fails.

I never had issues with 2003...

[aytee111]

Dates generally are delimited by #, not quote: #01/01/2012#

[joelmeaders]

Dates generally are delimited by #, not quote: #01/01/2012#

Yeah but what gets me is that this has always worked, and works the first time around on the form. I figured it only worked with quotes because it was a multiple field insert query. In fact, it has always worked with all data types regardless some how.

[joelmeaders]

So it turns out that Access randomly got to pick and choose the errors to display here.

Putting the dates inside of #'s gave the same errors. After following the whole process through there was a prerequisite record (temporary misc contract) not being saved in another table on the second go around because the function which saved the contract was picking up a vbNullString in a box it shouldn't have been accessing causing the prereq save to fail (and not throwing an error). That should have caused an error for this save that said matching record required in tbl______.

Once that was fixed it throws no type mismatch error even though the dates are again enclosed within quotes instead of #'s. It only threw that error instead of the missing record error.