No Destination field name in INSERT INTO statement (04/22/2005)

[rmtiptoes]

I cannot figure this one out. What is it looking for? Can someone please help me?

Private Sub Form_AfterUpdate()
Dim db As Database
Dim today As Date
today = Date

Set db = CurrentDb
db.Execute "INSERT INTO [Comments] " _
& " SELECT [Assets].[Mylan Asset ID Number], [Assets].[PO Number] " _
& "," & Date & "," & "[Assets].Comments FROM [Assets] WHERE " _
& " [Assets].[Mylan Asset ID Number]=" & Me![Mylan Asset ID Number] & ";"
Set db = Nothing
End Sub

Homer: But every time I learn something new, it pushes out something old! Remember that time I took a home wine-making course and forgot how to drive?
Marge Simpson: That's because you were drunk!
Homer: And how.

 

[rmtiptoes]

This is the error statement I am getting. I need simply to insert a date into a table.

Homer: But every time I learn something new, it pushes out something old! Remember that time I took a home wine-making course and forgot how to drive?
Marge Simpson: That's because you were drunk!
Homer: And how.

 

[alvechurchdata]

I usually get this error when the names of the fields of the destination table don't match the names being generated by the SQL.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[rmtiptoes]

The names of the Date field is "Date" in the comments table. What else can it be?

Homer: But every time I learn something new, it pushes out something old! Remember that time I took a home wine-making course and forgot how to drive?
Marge Simpson: That's because you were drunk!
Homer: And how.

 

[alvechurchdata]

What else can it be?{/quote]

Are all the other field names the same in the Comments table and are they in the same order as in the SQL.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[rmtiptoes]

Yes. They are as follows:

Mylan Asset ID Number Number
PO Number Text
Date Date/Time
Comments Text

Homer: But every time I learn something new, it pushes out something old! Remember that time I took a home wine-making course and forgot how to drive?
Marge Simpson: That's because you were drunk!
Homer: And how.

 

[PHV]

db.Execute "INSERT INTO Comments ([Mylan Asset ID Number],[PO Number],[Date],[Comments])" _
& " SELECT [Mylan Asset ID Number],[PO Number]," _
& "#" & Date & "#,Comments FROM Assets WHERE " _
& "[Mylan Asset ID Number]=" & Me![Mylan Asset ID Number]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rmtiptoes]

HOOOOOOORAY!!!!! Worked like a charm!!!! Thank you, thank you, thank you!!What do the '#' signs accomplish?

Homer: But every time I learn something new, it pushes out something old! Remember that time I took a home wine-making course and forgot how to drive?
Marge Simpson: That's because you were drunk!
Homer: And how.

 

[PHV]

What do the '#' signs accomplish?
Signal to the Jet engine that the value is of type DateTime.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rmtiptoes]

I understand.

Thank you again

Homer: But every time I learn something new, it pushes out something old! Remember that time I took a home wine-making course and forgot how to drive?
Marge Simpson: That's because you were drunk!
Homer: And how.