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):
Code:
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...