"Insert Into..." both values from a ta ble and a form. 1

[DomDom3]

Hi there,

I have a click button on a form that I'd like to use to insert data from a textbox into certain records on a table.

I've used this code:-

DoCmd.RunSQL "INSERT INTO [tblpartnercosts] (submitter) values ('" & Me![username] & "') " _
& "Where " _
& "[tblPartnerCosts].[service] = 'I&C'" _
& "And " _
& "[tblPartnerCosts].[unique ID] = " & Forms![frmInstallationoutlook]![Unique ID]

but I get a syntax error near "where".

CAn anyone help please?

 

[TheAceMan1]

How are ya DomDom3 . . .

See missing space in red:

[blue]    & "[tblPartnerCosts].[service] = 'I&C'[COLOR=red red] [/color]" _[/blue]

See Ya! . . . . . .

 

[PWise]

Have not tested this but I dont think that you can do a insert using the values Keyword with a where clause.
You can do a

insert into tablename1 (field1,field2,field3,field4..)
Select field1,field2,1,"litreal"
from tablename2
where
criteria1='xxxx'
and 
criteria2=Forms![fromname]![Controlname]

 

[Golom]

The presence of [blue]WHERE[/blue] in your INSERT statement implies that you want to change existing records rather than insert new ones. Is that the case?

If so then you need to use an UPDATE statement and not an INSERT. As pwise said, the use of WHERE in an INSERT ... VALUES isn't allowed (nor does it make logical sense.)

 

[DomDom3]

Thanks for your input Aceman but the error message remains.

hi Pwise, this was the conclusion that I'd reached, my problem is that the data I'm selecting is contained in a a form and not another table, so I can't work out how to use that method.

 

[Golom]

More to the point ...

What is your intent when you specify WHERE conditions on an INSERT?

The INSERT statement puts new records into the table and it doesn't look at records that already exist in the table to do that. The DBMS may look at them to ensure that keys are not being duplicated but the INSERT statement itself does not do so.

If what you intend to do is change the field submitter to Me![username] on all records WHERE [tblPartnerCosts].[service] = 'I&C' and [tblPartnerCosts].[unique ID] = " & Forms![frmInstallationoutlook]![Unique ID] then use

DoCmd.RunSQL "UPDATE [tblpartnercosts] " & _
             "SET submitter = '" & Me![username] & "' " & _
             "WHERE [tblPartnerCosts].[service] = 'I&C' " & _
             "  AND [tblPartnerCosts].[unique ID] = " & _
                    Forms![frmInstallationoutlook]![Unique ID]

 

[DomDom3]

Thanks Golom,

I was just floundering around trying to work out the update line I couldn't get the ' with Me![username] correct but you gave me the answer.

This statement comes after an insert where I move data from one table to another, but then want to Update the table with the Username and Date. This is the data that's held in the form and not the table.

Hope it makes sense - you gave me the answer all the same.

Cheers

 

[Golom]

OK. You can do it that way (i.e. INSERT and then UPDATE) or you can do it all in the INSERT statement.

DoCmd.RunSQL _
"INSERT INTO [tblpartnercosts] (fld1, fld2, ..., Submitter, TheDate) " & _
"Select fld1, fld2, ..., '" & Me![UserName] &',#" & Date() & "# " & _
"From SourceTable "
"Where ... Some Where conditions to select SourceTable Records ... "