By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,011 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

When extending line of code to next line I get error with insert into.

P: 1
I am kind of a newb, however I did do coding back about 10 years ago, but have forgotten much. I now work for none profit and was asked to help build aDB in MS Access, however I never used Access back in my world. So really a newb!

What I'm doing is building a from to input data into a table. and there are many fields that need to be added, so there are multiple lines of a single statement. When I narrow the code down to a single line it works, also if I keep it short enough that just the "values" are extended my code works but when I extend in the "insert into" I get an error, expected end of statement.

Expand|Select|Wrap|Line Numbers
  1. '<<<Moderator Note: The following was stepped from the origianl post, it will not run "as is">>
  2. CurrentDb.Execute 
  3.   "INSERT INTO Unicorn_homes([Applicant_Legal_Name]
  4.     ,[Applicant_Last_Name],[Preferred_Name],[Pronouns]
  5.     ,[Gender],[Sexual_Orientation],[Date_Of_Birth]
  6.     ,[US_Citizen],[Born_in_CA],[Country_of_Birth],[Preferred_Language]
  7.     ,[Ethnicity],[Social_Security_Number],[Cell_Phone],[Email_Address]
  8.     ,[Method_of_Contact],[If_VM_How_to_be_addressed],[Level_of_Education]
  9.     ,[Employment_Status],[Want_help_finding_work]
  10.     ,[probation, foster care, homeless, mental health or drug rehab]
  11.     ,[Ability to make therapist appointment],[Ability to make doctor appointment]
  12.     ,[How to be a good listner],[When to ask questions],[Eat Healthy]
  13.     ,[Budget money],[Education goals],[Career goals]
  14.     ,[How to be addressed when we talk to parent/caregiver?]
  15.     ,[How to be addressed when we talk to therapist/clinician?]
  16.     ,[How to be addressed when we talk to medical proffessional?]
  17.     ,[How to be addressed when we talk to school/counselor?]
  18.     ,[Primary Emergency Contact first name],[Primary Emergency Contact last name]
  19.     ,[Primary Emergency Contact relationship] _
  20.     ,[Primary Emergency Contact address],[Primary Emergency Contact city]
  21.     ,[Primary Emergency Contact state],[Primary Emergency Contact zip code]
  22.     ,[Primary Emergency Contact phone number]) 
  23.   VALUES('" & Me.Applicant_Legal_Name & "', '" & Me.Applicant_Last_Name & "', '" 
  24.     & Me.Preferred_Name & "', '" & Me.Pronouns & "', '" & Me.Gender & "', '" 
  25.     & Me.Sexual_Orientation & "', '" & Me.Date_Of_Birth & "', '" 
  26.     & Me.Preferred_Language & "', '" & Me.Ethnicity & "', '" 
  27.     & Me.Social_Security_Number & "', '" & Me.US_Citizen & "', '" 
  28.     & Me.Born_in_CA & "', '" & Me.Country_of_Birth & "', '" 
  29.     & Me.Cell_Phone & "', '" & Me.Email_Address & "', '" & Me.Method_of_Contact 
  30.     & "', '" & Me.If_VM_How_to_be_addressed & "', '" & Me.Level_of_Education 
  31.     & "', '" & Me.Employment_Status & "', '" & Me.Want_help_finding_work 
  32.     & "', '" & Me.probation__foster_care__homeless__mental_health_or_drug_rehab 
  33.     & "', '" & Me.Ability_to_make_therapist_appointment & "', '" 
  34.     & Me.Ability_to_make_doctor_appointment & "',  '" & Me.How_to_be_a_good_listner 
  35.     & "', '" _
  36.     & Me.When_to_ask_questions & "', '" & Me.Eat_Healthy & "', '" 
  37.     & Me.Budget_money & "', '" & Me.Education_goals & "', '" & Me.Career_goals 
  38.     & "', '" & Me.How_to_be_addressed_when_we_talk_to_parent_caregiver_ & "', '" 
  39.     & Me.How_to_be_addressed_when_we_talk_to_therapist_clinician_ & "', '" 
  40.     & Me.How_to_be_addressed_when_we_talk_to_medical_proffessional_ 
  41.     & "', '" & Me.How_to_be_addressed_when_we_talk_to_school_counselor_ 
  42.     & "', '" & Me.Primary_Emergency_Contact_first_name & "', '" 
  43.     & Me.Primary_Emergency_Contact_last_name & "', '" 
  44.     & Me.Primary_Emergency_Contact_relationship & "', '" 
  45.     & Me.Primary_Emergency_Contact_address & "', '" & Me.Primary_Emergency_Contact_city 
  46.     & "', '" & Me.Primary_Emergency_Contact_state & "', '" 
  47.     & Me.Primary_Emergency_Contact_zip_code & "', '" 
  48.     & Me.Primary_Emergency_Contact_phone_number & "')"
3 Weeks Ago #1
Share this Question
Share on Google+
2 Replies

Expert Mod 5K+
P: 5,387
Welcome to!
I'm always happy to help non-profits when I can as are many of the others here.

THANK YOU for using the [CODE/] formatting tool the first time out.

Not to be mean, I do intend this in the kindest way possible:

It looks like you are trying to use Access as an Excel workbook... You are not the first to do this, I did something like this with my first databases - it's a learning curve. :)

Would you please read thru the following article. It will help you tremendously with your project. Proper table design is VITAL to any good database!
home > topics > microsoft access / vba > insights > database normalization and table structures

From your insert string I notice that you have ALOT of very personal information in the data... such things as Social Security numbers should NOT be stored in easily accessible formats.
At the very minimum, at some point, you simply must enable the database encryption. More advisable would be to encrypt the sensitive data fields. I've done that in a few databases, it's not easy to do but simply required.

You need to create a pointer to the Database - using CurrentDB over and over again will cause you some issues as you progress. There are several threads here on discussing this issue. Basically,CurrentDB is a special construct that allows you to create a pointer to the database, each time you call it, it creates a NEW pointer, which can lead to memory leaks, etc...
Expand|Select|Wrap|Line Numbers
  1. ...
  2. Dim myDB as DAO.Database
  3. Dim myInsert as string
  4. ...
  5. Set myDB = CurrentDB
  6. ...
  7. if not myDB is nothing then set myDB=Nothing
  8. ...

Take that long string "INSERT INTO...." and pull it out of the CurrentDB.Execute and build it separately. This will allow you to debug.printthe resolved string to check for malformation.
Expand|Select|Wrap|Line Numbers
  1. myInsert = ""INSERT INTO Unicorn_homes([Applicant_Legal_Name],[Applicant_Last_Name],[Preferred_Name],[Pronouns],[Gender],[Sexual_Orientation],[Date_Of_Birth],[US_Citizen],[Born_in_CA],(...)"
  2. ...
  3. myDB.Execute myInsert
  4. ...
Your string is also exceptionally long, namely, due to the field names you have chosen. You will hit on the upper limit long before you can achieve your goal... and we haven't even started with queries and SQL lengths.

[probation, foster care, homeless, mental health or drug rehab]
Is a very bad field name in a table!
The length, use of spaces, use of punctuation, etc... I know that it's allowed; however, spaces and special characters, other than the underscore, should typically be avoided. Other's will argue, I will tell you that in piratical applications bad things happen.
When you go to write your SQL for a simple query against this field you're sitting on a ticking time-bomb!
Instead, your table might have a field like this:
In design view
Expand|Select|Wrap|Line Numbers
  1. [Field Name   ][Data Type][Description                                                            ]
  2. [HousingStatus][Short Text][probation, foster care, homeless, mental health or drug rehab]
so that the description hold the desired values.
Better yet, under normalization, you should have a table for the the "probation, foster care,..." values and a foreign key linked between the [SomeTable].[HousingStatus] and [SecondTable].[Primary_Key] - this will make more sense when you read the link above.

Let's start with the normalization.
I'll send you a boiler plate I've sent many others starting out in Access. It has some basic links and guidance that should help.
Check your Bytes.Com>Inbox
3 Weeks Ago #2

Expert Mod 15k+
P: 31,347
Having coded before you probably appreciate how long and messy names make your job trying to read and interpret the code in front of you a nightmare. One field like that makes life difficult enough. What you have there is the sort of thing a teacher wouldn't even start to consider when marking homework. "Go away and make it legible before submitting it again" - or words to that effect.

Now, in your case it's yourself that needs to deal with the result so it's particularly important for you to make it straightforward and easier to interpret. When working with any SQL that isn't working you need to follow the ideas in How to Debug SQL String. That's great, but in it's current state that will still leave you with an incomprehensible mess.

Thus, I advise you to work in simplifying what you're working with. Strip out any Fields that aren't necessary for a simple test; rename Fields that have names that are too complicated (They're all fine when all you have to focus on is a single Field, but consider understanding a string with many such long and windy names in.); test it in its most basic form first and add in any complexities when you have the previous level working (SELECT queries can help you find problems before converting them to action queries of any type).

Always remember though, giving us a set of code that ultimately creates a SQL string on your computer with your chosen values is fairly useless as far as helping you to debug that code. Very rarely is the VBA code even involved with the problem. On those occasions it is there is still much more sense in determining where the SQL problem is before trying to identify any error in your VBA.
3 Weeks Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.