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

Inserting data giving error message

P: 3
Hello All,

I'm really new to this, so sorry ahead of time. Trying to get data to be posted into a table along with other information that's already being put into there.

I'm using access 2003.

After hitting to post I get the error message : Run-time error '3346'


Here is the code:
Expand|Select|Wrap|Line Numbers
  1.     If Me.redeem > 0 Then
  2.         DoCmd.RunSQL "INSERT INTO closingsales ( redeem ) " & _
  3.             "SELECT #" & startdate & "# AS Expr1, " & _
  4.             Me.redeem & " AS Expr5"
  5.     End If
  6.  
I have added the column for redeem into closingsales already and checked other queries to try and find out if there's something I'm missing.
Sep 18 '08 #1
Share this Question
Share on Google+
4 Replies


Expert Mod 2.5K+
P: 2,545
Hi. Can't say I know anything about that error message, but one obvious problem is that you have a select statement which is selecting two items, and you are using that for an insert which lists only one item (redeem). Two into one won't go.

The insert will create new rows for any data added; is it the case that you want to update existing rows instead? It is not clear from what you have posted, but if you really want to update the value you will need an update query (not an insert).

-Stewart
Sep 18 '08 #2

P: 3
Hi. Can't say I know anything about that error message, but one obvious problem is that you have a select statement which is selecting two items, and you are using that for an insert which lists only one item (redeem). Two into one won't go.

The insert will create new rows for any data added; is it the case that you want to update existing rows instead? It is not clear from what you have posted, but if you really want to update the value you will need an update query (not an insert).

-Stewart
There are 3 things already being posted to the table. I am trying to get this posted along with them as well. I have tried to add redeem into the code already being used, but that did not work.

This is the code that is being used already:

Expand|Select|Wrap|Line Numbers
  1. 'DoCmd.OpenQuery "CRS Append Closingsales", acViewNormal, acEdit
  2.     DoCmd.RunSQL "INSERT INTO closingsales ([Date], Cash, iou, change) " & _
  3.         "SELECT DISTINCTROW #" & startdate & "# AS Expr1, " & _
  4.         "[forms]![depositform]![closecash] AS Expr2, [forms]![depositform]![closeiou] " & _
  5.         "AS Expr3, [forms]![depositform]![rollchange] AS Expr4 WITH OWNERACCESS OPTION"
  6.     If Me.IOUCheck > 0 Then
  7.         DoCmd.RunSQL "INSERT INTO deposits ( [date], id, Amount ) " & _
  8.             "SELECT #" & startdate & "# AS Expr1, 99 AS Expr2, " & _
  9.             Me.IOUCheck & " AS Expr3"
  10.  
Sorry for not being as clear as I should be, I'm still trying to figure all this out. Thank you for the help!

I'm not sure if that helps any on what it is I'm trying to get done.
Sep 18 '08 #3

Expert Mod 2.5K+
P: 2,545
Hi. You cannot include the form control references within your SQL string - these will not be recognised as valid field names by the Access database engine. It is possible to include their values, which should work. If the control contains a string value you need to ensure that the value is preceded and followed by a single quote, as shown below (for the first of the form control references only - you will need to adjust the others yourself if they too are strings, or remove them from the first field if the closecash value is just a number).

Expand|Select|Wrap|Line Numbers
  1. "SELECT DISTINCT #" & startdate & "# AS Expr1, '" & _
  2.         [forms]![depositform]![closecash] & "' AS Expr2, " & [forms]![depositform]![closeiou] & _
  3.         " AS Expr3, " & [forms]![depositform]![rollchange] & " AS Expr4 WITH OWNERACCESS OPTION"
I do not know why you are using the OpenQuery method as well as RunSQL; I am sure there will be a valid reason for it.

I have substituted the more general SELECT DISTINCT in place of the Access-specifc SELECT DISTINCTROW syntax in your select clause. I'm not sure why you need the WITH OWNERACCESS part - it should work perfectly well without it unless you have some form of access restriction to work round or something.

-Stewart

ps there is no mention of field 'redeem' in the code you posted. There is only a field called 'Date' - not a good choice of name, although allowable as a field name. It can cause considerable confusion with the built-in function to obtain today's date - the Date function.
Sep 18 '08 #4

P: 3
Hi. You cannot include the form control references within your SQL string - these will not be recognised as valid field names by the Access database engine. It is possible to include their values, which should work. If the control contains a string value you need to ensure that the value is preceded and followed by a single quote, as shown below (for the first of the form control references only - you will need to adjust the others yourself if they too are strings, or remove them from the first field if the closecash value is just a number).

Expand|Select|Wrap|Line Numbers
  1. "SELECT DISTINCT #" & startdate & "# AS Expr1, '" & _
  2.         [forms]![depositform]![closecash] & "' AS Expr2, " & [forms]![depositform]![closeiou] & _
  3.         " AS Expr3, " & [forms]![depositform]![rollchange] & " AS Expr4 WITH OWNERACCESS OPTION"
I do not know why you are using the OpenQuery method as well as RunSQL; I am sure there will be a valid reason for it.

I have substituted the more general SELECT DISTINCT in place of the Access-specifc SELECT DISTINCTROW syntax in your select clause. I'm not sure why you need the WITH OWNERACCESS part - it should work perfectly well without it unless you have some form of access restriction to work round or something.

-Stewart

ps there is no mention of field 'redeem' in the code you posted. There is only a field called 'Date' - not a good choice of name, although allowable as a field name. It can cause considerable confusion with the built-in function to obtain today's date - the Date function.
Hi, thank you again for the help. I was unaware of the different types of code in there. The person that wrote this is long gone from here and this is my first attempt at any of this. So thanks for being patient.
Sep 18 '08 #5

Post your reply

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