Copying form and subform to new record (using tip from Allen Browne'swebsite)

[RJB]

I'm using code from Allen Browne's website for copying data from a
form and its subform into a new record. I keep getting syntax errors
when running. I've quadruple checked the field names. Any ideas what
is causing the errors?

code>
strSql = "INSERT INTO Tbl_WorkorderEntry (Log#, LogLetter,
Description, OrderType, CoatingType, SourceType, SourcePlatform,
SourceFileType, SourceFileName, FilePrep, Quantity, Originals, Format,
Paper1, Paper2, Paper3, Paper4, Paper5, Paper6, FinishedPieces,
FinishedSize, FinishingServices, PricingNotes, ItemsPricing, Pricing,
Extras, Runtime) " & _
"SELECT " & lngID & " As NewID, LogLetter,
Description, OrderType, CoatingType, SourceType, SourcePlatform,
SourceFileType, SourceFileName, FilePrep, Quantity, Originals, Format,
Paper1, Paper2, Paper3, Paper4, Paper5, Paper6, FinishedPieces,
FinishedSize, FinishingServices, PricingNotes, ItemsPricing, Pricing,
Extras, Runtime " & _
"FROM Tbl_WorkorderEntry WHERE LogID = " &
Me.LogID & ";"
<end code

Any help is greatly appreciated.
Thanks.

 

[Petr Danes]

My guess would be it's because you're using some reserved words like
Description and Format, and maybe the pound sign in Log# is a problem as
well. Try putting all the field names into square brackets and see if that
helps:

"INSERT INTO Tbl_WorkorderEntry ([Log#], [LogLetter],

[Description], [OrderType], [CoatingType], [SourceType], [SourcePlatform],
[SourceFileType]...

Also, your WHERE clause may need quote marks if LogID is a string field. If
it's a numeric field, you're okay the way you are.

"FROM Tbl_WorkorderEntry WHERE LogID = '" & Me.LogID & "';"

Pete

 

[RJB]

Brackets didn't help. I did try changing Log# to LogNum and that
seemed to fix it. The issue then is replacing Log# with LogNum
everywhere it may be used - and will that change affect existing
entries since the variable name is changing?

My guess would be it's because you're using some reserved words like
Description and Format, and maybe the pound sign in Log# is a problem as
well. Try putting all the field names into square brackets and see if that
helps:

"INSERT INTO Tbl_WorkorderEntry ([Log#], [LogLetter],

[Description], [OrderType], [CoatingType], [SourceType], [SourcePlatform],
[SourceFileType]...

Also, your WHERE clause may need quote marks if LogID is a string field. If
it's a numeric field, you're okay the way you are.

 "FROM Tbl_WorkorderEntry WHERE LogID = '" & Me.LogID & "';"

Pete

I'm using code from Allen Browne's website for copying data from a
form and its subform into a new record.  I keep getting syntax errors
when running.  I've quadruple checked the field names.  Any ideas what
is causing the errors?

code>
strSql = "INSERT INTO Tbl_WorkorderEntry (Log#, LogLetter,
Description, OrderType, CoatingType, SourceType, SourcePlatform,
SourceFileType, SourceFileName, FilePrep, Quantity, Originals, Format,
Paper1, Paper2, Paper3, Paper4, Paper5, Paper6, FinishedPieces,
FinishedSize, FinishingServices, PricingNotes, ItemsPricing, Pricing,
Extras, Runtime) " & _
                   "SELECT " & lngID & " As NewID, LogLetter,
Description, OrderType, CoatingType, SourceType, SourcePlatform,
SourceFileType, SourceFileName, FilePrep, Quantity, Originals, Format,
Paper1, Paper2, Paper3, Paper4, Paper5, Paper6, FinishedPieces,
FinishedSize, FinishingServices, PricingNotes, ItemsPricing, Pricing,
Extras, Runtime " & _
                   "FROM Tbl_WorkorderEntry WHERE LogID = " &
Me.LogID & ";"
<end code

Any help is greatly appreciated.
Thanks.

 

[Petr Danes]

No, it will not. Changing a field's name does nothing to the data it
contains. Changing its TYPE, however, can mess things up a lot.

Glad to hear that fixed your problem. In general, you're best off not using
anything odd in field names. No spaces, foreign characters or anything other
than A-Z, a-z and 0-9. If you need flowery, descriptive names, put them on
forms, reports and the like, but not the actual names in the tables.

Pete



"RJB" <[email protected]> píse v diskusním príspevku
Brackets didn't help. I did try changing Log# to LogNum and that
seemed to fix it. The issue then is replacing Log# with LogNum
everywhere it may be used - and will that change affect existing
entries since the variable name is changing?

My guess would be it's because you're using some reserved words like
Description and Format, and maybe the pound sign in Log# is a problem as
well. Try putting all the field names into square brackets and see if that
helps:

"INSERT INTO Tbl_WorkorderEntry ([Log#], [LogLetter],

[Description], [OrderType], [CoatingType], [SourceType],
[SourcePlatform],
[SourceFileType]...

Also, your WHERE clause may need quote marks if LogID is a string field.
If
it's a numeric field, you're okay the way you are.

"FROM Tbl_WorkorderEntry WHERE LogID = '" & Me.LogID & "';"

Pete

I'm using code from Allen Browne's website for copying data from a
form and its subform into a new record. I keep getting syntax errors
when running. I've quadruple checked the field names. Any ideas what
is causing the errors?

code>
strSql = "INSERT INTO Tbl_WorkorderEntry (Log#, LogLetter,
Description, OrderType, CoatingType, SourceType, SourcePlatform,
SourceFileType, SourceFileName, FilePrep, Quantity, Originals, Format,
Paper1, Paper2, Paper3, Paper4, Paper5, Paper6, FinishedPieces,
FinishedSize, FinishingServices, PricingNotes, ItemsPricing, Pricing,
Extras, Runtime) " & _
"SELECT " & lngID & " As NewID, LogLetter,
Description, OrderType, CoatingType, SourceType, SourcePlatform,
SourceFileType, SourceFileName, FilePrep, Quantity, Originals, Format,
Paper1, Paper2, Paper3, Paper4, Paper5, Paper6, FinishedPieces,
FinishedSize, FinishingServices, PricingNotes, ItemsPricing, Pricing,
Extras, Runtime " & _
"FROM Tbl_WorkorderEntry WHERE LogID = " &
Me.LogID & ";"
<end code

Any help is greatly appreciated.
Thanks.