Inserting data giving error message

[zaklost]

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:

    If Me.redeem > 0 Then
        DoCmd.RunSQL "INSERT INTO closingsales ( redeem ) " & _
            "SELECT #" & startdate & "# AS Expr1, " & _
            Me.redeem & " AS Expr5"
    End If

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.

[Stewart Ross]

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

[zaklost]

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:

'DoCmd.OpenQuery "CRS Append Closingsales", acViewNormal, acEdit
    DoCmd.RunSQL "INSERT INTO closingsales ([Date], Cash, iou, change) " & _
        "SELECT DISTINCTROW #" & startdate & "# AS Expr1, " & _
        "[forms]![depositform]![closecash] AS Expr2, [forms]![depositform]![closeiou] " & _
        "AS Expr3, [forms]![depositform]![rollchange] AS Expr4 WITH OWNERACCESS OPTION"
    If Me.IOUCheck > 0 Then
        DoCmd.RunSQL "INSERT INTO deposits ( [date], id, Amount ) " & _
            "SELECT #" & startdate & "# AS Expr1, 99 AS Expr2, " & _
            Me.IOUCheck & " AS Expr3"

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.

[Stewart Ross]

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

"SELECT DISTINCT #" & startdate & "# AS Expr1, '" & _
        [forms]![depositform]![closecash] & "' AS Expr2, " & [forms]![depositform]![closeiou] & _
        " 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.

[zaklost]

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

"SELECT DISTINCT #" & startdate & "# AS Expr1, '" & _
        [forms]![depositform]![closecash] & "' AS Expr2, " & [forms]![depositform]![closeiou] & _
        " 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.