Full Version: Forms
UtterAccess Forums > Microsoft® Access > Access Forms
mike60smart
Hi
I have a Comand Button that runs the following piece of Code (Attached) but it always gives me the error
shown in the attachment.
It highlights DoCmd.RunSQL mySQL
Cam anyone help in pointing out where the error is??
Your help appreciated
Mike
ScottGem
One issue is you are using + instead of &.
When you print mySQL, what does it show? Are all the value in the right place?
mike60smart
Hi Scott
That do you mean "When you Print mySQL?"
Mike
ScottGem
Yolu have a line in your code Debug Print MySQL. That line is usually used as a debugging tool to display the actual resulting text string.
mike60smart
How would I run that?
mike60smart
Scott
I have attached the database for you to have a quick look at what happens.
When you seolect "Enter New Applicant" - "frmApplication" opens.
Enter details and then select "Enter Record and Continue" the error msg appears.
ScottGem
hate to tell you this but it worked fine for me. Brought me to the next form. Checked the tables and the application info as well as the phone info was added correctly.
If you get the error, Press Ctrl+G to open the VBE. The immediate window will show the generated SQL
mike60smart
Did that and the error shows - did Ctl+G and the Immediate Window was displayed showing the following:
INSERT INTO tblapplication (DateReceived, ApplicantFirstName, ApplicantLastName, address, city, state, zip, homeowner, landlordID, yearlyincome) VALUES (#12/12/2000#, 'a', 'a', 'a', 'Lansing', 'MI', '12345', 'Homeowner', 0, )
Dont know why it will not work for me?
jzwp11
Mike,
id you try and compact and repair the database (tools->database utilitites->compact and repair database) and then try entering another record?
jzwp11
jzwp11
Mike,
You must enter a value in the yearly household income field. If you leave it blank the INSERT command will give you the error.
jzwp11
kbrewster
I got an error as well. I had to change the following
ySQL = "INSERT INTO tblapplication (DateReceived, ApplicantFirstName, ApplicantLastName, address, city, state, zip, homeowner, landlordID, yearlyincome) "
mySQL = mySQL + "VALUES (#" & Me.DateReceived & "#, '" & adhHandleQuotes(Nz(Me![ApplicantFirstName], " ")) & "', '" & adhHandleQuotes(Nz([ApplicantLastName], " ")) & "', '" & adhHandleQuotes(Nz(Me![Address], " ")) & "'"
mySQL = mySQL + ", '" & adhHandleQuotes(Nz(Me![City], " ")) & "', '" & adhHandleQuotes(Nz(Me![State], " ")) & "', '" & adhHandleQuotes(Nz(Me![Zip], " ")) & "', '" & adhHandleQuotes(Nz(Me![cboHomeowner], " ")) & "'"
mySQL = mySQL + ", " & Nz(Me![cbolandlord], 0) & ", " & Nz(Me![YearlyHouseHoldIncome], " ") & ")"
Nz(Me![YearlyHouseHoldIncome], "0")
jzwp11
Mike,

Oadded the code to prompt you to fill in the income field if it is left blank.

FOr you can make the change Kristen suggested.
jzwp11
Edited by: jzwp11 on Tue Jun 13 14:28:56 EDT 2006.
kbrewster
Is this a school project or something...because I swear I have seen this exact database from another UA member!
mike60smart
HI
qny thanks -0 things are so simple when you know what you are doing.
Cheers - worked just fine
Mike
ScottGem
The reason for the error is shown in the generated SQL. You have 10 fields in your field list but only 9 values in your value list. I don't know whether its the LandlordID that's missing or the yearly income. But the Value list needs to match the field list.
mike60smart
Hi Kristen
any thanks for the help - and Yes you have seen this DB from another UA member. I have been
trying to help with it as well - though my input is very limited.
Thanks
Mike
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.