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

Allen Browne, Where are You??

P: n/a
Mr. Browne's copy code on his web site has saved me. I have been
struggling to copy a record with several related sub-form tables. I
found code on his web site that copies a sub-form table, implemented it
and was successful to make it work. Thank you Mr. Browne!

However, the copy code on his web site only updates one sub-form table.
My database has three sub-forms that need to be updated. Although, I
am not a programmer (I am learning through this excercise), I modified
the code a bit to try to copy a second table and am getting an error
(the story of my life!). If Mr. Browne or anyone reading this can
help, I may be able to keep all my hair on my head.

Here's the error I'm getting (with my modified code following):

Run-time error '3134'
Syntax error in Insert Into statement

When I debug, this is the line highlighted: db.Execute strSq2,
dbFailOnError

Here is the entire code:

Private Sub Command61_Click()
'Adapted from: http://allenbrowne.com

Dim strSql As String
Dim strSq2 As String
Dim db As DAO.Database
Dim lngInvID As Long

Set db = DBEngine(0)(0)

If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record
With Me.RecordsetClone
.AddNew
!OADate = Me.OADate
!AcctMgr = Me.AcctMgr
!ProvType = Me.ProvType
!ProvExpDate = Me.ProvExpDate
!ProvNoExp = Me.ProvNoExp
!ProvOANo = Me.ProvOANo
!OrderType = Me.OrderType
!Plant = Me.Plant
!TypeComments = Me.TypeComments
!EngChange = Me.EngChange
!EngComments = Me.EngComments
!PriceChange = Me.PriceChange
!NewPrice = Me.NewPrice
!NewPriceDate = Me.NewPriceDate
!BaseMetalPrice = Me.BaseMetalPrice
!PriceComments = Me.PriceComments
!BudgetRev = Me.BudgetRev
!OACustID = Me.OACustID
!NewCust = Me.NewCust
!PONo = Me.PONo
!PODate = Me.PODate
!QuoteProjNo = Me.QuoteProjNo
!PODate = Me.PODate
!QuoteProjNo = Me.QuoteProjNo
!ProdNo = Me.ProdNo
![Prod Name] = Me.[Prod Name]
!ToolProjNo = Me.ToolProjNo
!OrderDescription = Me.OrderDescription
!PODate = Me.PODate
!PODate = Me.PODate
!PODate = Me.PODate
!PODate = Me.PODate
!OAPass = Me.OAPass
!Approval = Me.Approval

.Update
.Bookmark = .LastModified
lngID = !OANo
'Duplicate the related records.
If Me.FTool.Form.RecordsetClone.RecordCount 0 Then
strSql = "INSERT INTO TTool (OANo, ToolReplacement,
ReplDescription, [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling,
AmortDescription, EstLuntCost, EstYearlyVolume, Routing ) " & _
"SELECT " & lngID & " As OANo, ToolReplacement,
ReplDescription, [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling,
AmortDescription, EstLuntCost, EstYearlyVolume, Routing " & _
"FROM TTool " & _
"WHERE OANo = " & Me.OANo & ";"

'db.Execute strSql, dbFailOnError

If Me.FBudget.Form.RecordsetClone.RecordCount 0 Then
strSq2 = "INSERT INTO TBudget (OANo, ToolRev,
ToolEstCost, TrimRev, TrimEstCost, MachineRev, MachineEstCost,
AssRevenue, AssEstCost, SampleRev, SampleEstCost, GageRev, GageEstCost,
CompRev, CompEstCost, ENHRRev, ENHREstCost, QAHRRev, QAHREstCost,
DesignRev, DesignEstCost, CaplEquipRev, CaplEquipEstCost, PROTRev,
PROTEstCost, DSHRRev, DSHREstCost, MiscRevenue, MiscEstCost,
PrototypePrice, LotSetupCharge, TtlRevenue, TtlEstCost, TtlRev & Cost,
AddAmtCurBudget, Comments ) " & _
"SELECT " & lngID & " As OANo, ToolRev,
ToolEstCost, TrimRev, TrimEstCost, MachineRev, MachineEstCost,
AssRevenue, AssEstCost, SampleRev, SampleEstCost, GageRev, GageEstCost,
CompRev, CompEstCost, ENHRRev, ENHREstCost, QAHRRev, QAHREstCost,
DesignRev, DesignEstCost, CaplEquipRev, CaplEquipEstCost, PROTRev,
PROTEstCost, DSHRRev, DSHREstCost, MiscRevenue, MiscEstCost,
PrototypePrice, LotSetupCharge, TtlRevenue, TtlEstCost, TtlRev & Cost,
AddAmtCurBudget, Comments " & _
"FROM TBudget " & _
"WHERE OANo = " & Me.OANo & ";"

db.Execute strSql
db.Execute strSq2, dbFailOnError (THIS IS THE LINE THAT
ERRORS WHEN I DEBUG)
Else
MsgBox "Main record duplicated, but there were no
related records."
End If
End If
'Display the duplicate.
Me.Bookmark = .LastModified
End With
End If

Set db = Nothing
End Sub

While I would love to hear from Mr. Browne (at least to say thank you
for his awesome web site!), any and all suggestions are welcome!

Dec 14 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
To help you debug the SQL statement, add the line:
Debug.Print strSq2
immediately above the line:
db.Execute strSq2, dbFailOnError

When it fails, press Ctrl+G to open the Immediate window.
Copy the SQL statement to clipboard.
Create a new query, swtich it to SQL View (View menu), and paste the SQL
statement in. You can now work with it to get it right. Once you know how to
fix it, you can then fix your code.

It could be something as simple as th OANo field in TBudget is defined as a
Text field, where it needs to be a Number field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Swinky" <sw********@lunt.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.com...
Mr. Browne's copy code on his web site has saved me. I have been
struggling to copy a record with several related sub-form tables. I
found code on his web site that copies a sub-form table, implemented it
and was successful to make it work. Thank you Mr. Browne!

However, the copy code on his web site only updates one sub-form table.
My database has three sub-forms that need to be updated. Although, I
am not a programmer (I am learning through this excercise), I modified
the code a bit to try to copy a second table and am getting an error
(the story of my life!). If Mr. Browne or anyone reading this can
help, I may be able to keep all my hair on my head.

Here's the error I'm getting (with my modified code following):

Run-time error '3134'
Syntax error in Insert Into statement

When I debug, this is the line highlighted: db.Execute strSq2,
dbFailOnError

Here is the entire code:

Private Sub Command61_Click()
'Adapted from: http://allenbrowne.com

Dim strSql As String
Dim strSq2 As String
Dim db As DAO.Database
Dim lngInvID As Long

Set db = DBEngine(0)(0)

If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record
With Me.RecordsetClone
.AddNew
!OADate = Me.OADate
!AcctMgr = Me.AcctMgr
!ProvType = Me.ProvType
!ProvExpDate = Me.ProvExpDate
!ProvNoExp = Me.ProvNoExp
!ProvOANo = Me.ProvOANo
!OrderType = Me.OrderType
!Plant = Me.Plant
!TypeComments = Me.TypeComments
!EngChange = Me.EngChange
!EngComments = Me.EngComments
!PriceChange = Me.PriceChange
!NewPrice = Me.NewPrice
!NewPriceDate = Me.NewPriceDate
!BaseMetalPrice = Me.BaseMetalPrice
!PriceComments = Me.PriceComments
!BudgetRev = Me.BudgetRev
!OACustID = Me.OACustID
!NewCust = Me.NewCust
!PONo = Me.PONo
!PODate = Me.PODate
!QuoteProjNo = Me.QuoteProjNo
!PODate = Me.PODate
!QuoteProjNo = Me.QuoteProjNo
!ProdNo = Me.ProdNo
![Prod Name] = Me.[Prod Name]
!ToolProjNo = Me.ToolProjNo
!OrderDescription = Me.OrderDescription
!PODate = Me.PODate
!PODate = Me.PODate
!PODate = Me.PODate
!PODate = Me.PODate
!OAPass = Me.OAPass
!Approval = Me.Approval

.Update
.Bookmark = .LastModified
lngID = !OANo
'Duplicate the related records.
If Me.FTool.Form.RecordsetClone.RecordCount 0 Then
strSql = "INSERT INTO TTool (OANo, ToolReplacement,
ReplDescription, [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling,
AmortDescription, EstLuntCost, EstYearlyVolume, Routing ) " & _
"SELECT " & lngID & " As OANo, ToolReplacement,
ReplDescription, [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling,
AmortDescription, EstLuntCost, EstYearlyVolume, Routing " & _
"FROM TTool " & _
"WHERE OANo = " & Me.OANo & ";"

'db.Execute strSql, dbFailOnError

If Me.FBudget.Form.RecordsetClone.RecordCount 0 Then
strSq2 = "INSERT INTO TBudget (OANo, ToolRev,
ToolEstCost, TrimRev, TrimEstCost, MachineRev, MachineEstCost,
AssRevenue, AssEstCost, SampleRev, SampleEstCost, GageRev, GageEstCost,
CompRev, CompEstCost, ENHRRev, ENHREstCost, QAHRRev, QAHREstCost,
DesignRev, DesignEstCost, CaplEquipRev, CaplEquipEstCost, PROTRev,
PROTEstCost, DSHRRev, DSHREstCost, MiscRevenue, MiscEstCost,
PrototypePrice, LotSetupCharge, TtlRevenue, TtlEstCost, TtlRev & Cost,
AddAmtCurBudget, Comments ) " & _
"SELECT " & lngID & " As OANo, ToolRev,
ToolEstCost, TrimRev, TrimEstCost, MachineRev, MachineEstCost,
AssRevenue, AssEstCost, SampleRev, SampleEstCost, GageRev, GageEstCost,
CompRev, CompEstCost, ENHRRev, ENHREstCost, QAHRRev, QAHREstCost,
DesignRev, DesignEstCost, CaplEquipRev, CaplEquipEstCost, PROTRev,
PROTEstCost, DSHRRev, DSHREstCost, MiscRevenue, MiscEstCost,
PrototypePrice, LotSetupCharge, TtlRevenue, TtlEstCost, TtlRev & Cost,
AddAmtCurBudget, Comments " & _
"FROM TBudget " & _
"WHERE OANo = " & Me.OANo & ";"

db.Execute strSql
db.Execute strSq2, dbFailOnError (THIS IS THE LINE THAT
ERRORS WHEN I DEBUG)
Else
MsgBox "Main record duplicated, but there were no
related records."
End If
End If
'Display the duplicate.
Me.Bookmark = .LastModified
End With
End If

Set db = Nothing
End Sub

While I would love to hear from Mr. Browne (at least to say thank you
for his awesome web site!), any and all suggestions are welcome!

Dec 15 '06 #2

P: n/a
PW
I think I wore him out or scared him away with my "Form can't find
control" post ;-)

-pw
Dec 15 '06 #3

P: n/a
Thank you Allen for repsonding to my SOS. I'm totally grateful for
help to get me this far! Can you (or anyone else) help just a bit
more??

I followed your instructions and found out that one of my fields had a
space in it and the error was removed when adding [] brackets to the
field. Fixing that, I get the query to run.

Now I get a new error: Run-time error: 3061, too few parameters,
expected 1.

When I run the above query, I get a message box that asks me for the
parameter value for my field [TtlRev & Cost]. In my form, this is a
calculated field based on a formula with input from two other fields:
TTLRevenue minus TTLEstCost. So I think what is happening is my code
is not taking the [TtlRev & Cost] value from my original record and
moving it into the new record since it's a calculated field. Am I
correct?

To make it even more confusing, the two fields from the formula are
calculated fields themselves. They are the added sum of other fields
in the form. But I don't get an error on that addition when I run the
query---although it's assigning $0 as the value for the two fields,
even though it should give me an amount other than $0 since there are
numbers to add in the other fields.

If the formula is the problem, is there a way to build the formula into
my "Select" statement so that this will run??

Erg! So close but yet not close enough. I was suppossed to roll out
this little program on Wednesday of this week but this crazy little
problem is keeping me from doing that.

Any suggestions on how to resolve the problem?

Allen Browne wrote:
To help you debug the SQL statement, add the line:
Debug.Print strSq2
immediately above the line:
db.Execute strSq2, dbFailOnError

When it fails, press Ctrl+G to open the Immediate window.
Copy the SQL statement to clipboard.
Create a new query, swtich it to SQL View (View menu), and paste the SQL
statement in. You can now work with it to get it right. Once you know how to
fix it, you can then fix your code.

It could be something as simple as th OANo field in TBudget is defined as a
Text field, where it needs to be a Number field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Swinky" <sw********@lunt.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.com...
Mr. Browne's copy code on his web site has saved me. I have been
struggling to copy a record with several related sub-form tables. I
found code on his web site that copies a sub-form table, implemented it
and was successful to make it work. Thank you Mr. Browne!

However, the copy code on his web site only updates one sub-form table.
My database has three sub-forms that need to be updated. Although, I
am not a programmer (I am learning through this excercise), I modified
the code a bit to try to copy a second table and am getting an error
(the story of my life!). If Mr. Browne or anyone reading this can
help, I may be able to keep all my hair on my head.

Here's the error I'm getting (with my modified code following):

Run-time error '3134'
Syntax error in Insert Into statement

When I debug, this is the line highlighted: db.Execute strSq2,
dbFailOnError

Here is the entire code:

Private Sub Command61_Click()
'Adapted from: http://allenbrowne.com

Dim strSql As String
Dim strSq2 As String
Dim db As DAO.Database
Dim lngInvID As Long

Set db = DBEngine(0)(0)

If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record
With Me.RecordsetClone
.AddNew
!OADate = Me.OADate
!AcctMgr = Me.AcctMgr
!ProvType = Me.ProvType
!ProvExpDate = Me.ProvExpDate
!ProvNoExp = Me.ProvNoExp
!ProvOANo = Me.ProvOANo
!OrderType = Me.OrderType
!Plant = Me.Plant
!TypeComments = Me.TypeComments
!EngChange = Me.EngChange
!EngComments = Me.EngComments
!PriceChange = Me.PriceChange
!NewPrice = Me.NewPrice
!NewPriceDate = Me.NewPriceDate
!BaseMetalPrice = Me.BaseMetalPrice
!PriceComments = Me.PriceComments
!BudgetRev = Me.BudgetRev
!OACustID = Me.OACustID
!NewCust = Me.NewCust
!PONo = Me.PONo
!PODate = Me.PODate
!QuoteProjNo = Me.QuoteProjNo
!PODate = Me.PODate
!QuoteProjNo = Me.QuoteProjNo
!ProdNo = Me.ProdNo
![Prod Name] = Me.[Prod Name]
!ToolProjNo = Me.ToolProjNo
!OrderDescription = Me.OrderDescription
!PODate = Me.PODate
!PODate = Me.PODate
!PODate = Me.PODate
!PODate = Me.PODate
!OAPass = Me.OAPass
!Approval = Me.Approval

.Update
.Bookmark = .LastModified
lngID = !OANo
'Duplicate the related records.
If Me.FTool.Form.RecordsetClone.RecordCount 0 Then
strSql = "INSERT INTO TTool (OANo, ToolReplacement,
ReplDescription, [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling,
AmortDescription, EstLuntCost, EstYearlyVolume, Routing ) " & _
"SELECT " & lngID & " As OANo, ToolReplacement,
ReplDescription, [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling,
AmortDescription, EstLuntCost, EstYearlyVolume, Routing " & _
"FROM TTool " & _
"WHERE OANo = " & Me.OANo & ";"

'db.Execute strSql, dbFailOnError

If Me.FBudget.Form.RecordsetClone.RecordCount 0 Then
strSq2 = "INSERT INTO TBudget (OANo, ToolRev,
ToolEstCost, TrimRev, TrimEstCost, MachineRev, MachineEstCost,
AssRevenue, AssEstCost, SampleRev, SampleEstCost, GageRev, GageEstCost,
CompRev, CompEstCost, ENHRRev, ENHREstCost, QAHRRev, QAHREstCost,
DesignRev, DesignEstCost, CaplEquipRev, CaplEquipEstCost, PROTRev,
PROTEstCost, DSHRRev, DSHREstCost, MiscRevenue, MiscEstCost,
PrototypePrice, LotSetupCharge, TtlRevenue, TtlEstCost, TtlRev & Cost,
AddAmtCurBudget, Comments ) " & _
"SELECT " & lngID & " As OANo, ToolRev,
ToolEstCost, TrimRev, TrimEstCost, MachineRev, MachineEstCost,
AssRevenue, AssEstCost, SampleRev, SampleEstCost, GageRev, GageEstCost,
CompRev, CompEstCost, ENHRRev, ENHREstCost, QAHRRev, QAHREstCost,
DesignRev, DesignEstCost, CaplEquipRev, CaplEquipEstCost, PROTRev,
PROTEstCost, DSHRRev, DSHREstCost, MiscRevenue, MiscEstCost,
PrototypePrice, LotSetupCharge, TtlRevenue, TtlEstCost, TtlRev & Cost,
AddAmtCurBudget, Comments " & _
"FROM TBudget " & _
"WHERE OANo = " & Me.OANo & ";"

db.Execute strSql
db.Execute strSq2, dbFailOnError (THIS IS THE LINE THAT
ERRORS WHEN I DEBUG)
Else
MsgBox "Main record duplicated, but there were no
related records."
End If
End If
'Display the duplicate.
Me.Bookmark = .LastModified
End With
End If

Set db = Nothing
End Sub

While I would love to hear from Mr. Browne (at least to say thank you
for his awesome web site!), any and all suggestions are welcome!
Dec 15 '06 #4

P: n/a
The usual approach in a normalized is that you do not store calculations in
your table. Instead, you put the calcuation expression in a query, so you
can use the calculated field in any form or report, but it can never store
the wrong answer because it isn't stored.

If that's what you are doing, then you need to remove the calculated field
from the SQL statement twice (from the INSERT clause, and the SELECT
clause.) You cannot insert the value into a field that is not in the table.

If you do have the field in the table, the fact that Access is treating it
as a parameter means it's name is spelled incorrectly in one of those
places--either the target (the table), or the source (the form.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Swinky" <sw********@lunt.comwrote in message
news:11*********************@j72g2000cwa.googlegro ups.com...
Thank you Allen for repsonding to my SOS. I'm totally grateful for
help to get me this far! Can you (or anyone else) help just a bit
more??

I followed your instructions and found out that one of my fields had a
space in it and the error was removed when adding [] brackets to the
field. Fixing that, I get the query to run.

Now I get a new error: Run-time error: 3061, too few parameters,
expected 1.

When I run the above query, I get a message box that asks me for the
parameter value for my field [TtlRev & Cost]. In my form, this is a
calculated field based on a formula with input from two other fields:
TTLRevenue minus TTLEstCost. So I think what is happening is my code
is not taking the [TtlRev & Cost] value from my original record and
moving it into the new record since it's a calculated field. Am I
correct?

To make it even more confusing, the two fields from the formula are
calculated fields themselves. They are the added sum of other fields
in the form. But I don't get an error on that addition when I run the
query---although it's assigning $0 as the value for the two fields,
even though it should give me an amount other than $0 since there are
numbers to add in the other fields.

If the formula is the problem, is there a way to build the formula into
my "Select" statement so that this will run??

Erg! So close but yet not close enough. I was suppossed to roll out
this little program on Wednesday of this week but this crazy little
problem is keeping me from doing that.

Any suggestions on how to resolve the problem?

Allen Browne wrote:
>To help you debug the SQL statement, add the line:
Debug.Print strSq2
immediately above the line:
db.Execute strSq2, dbFailOnError

When it fails, press Ctrl+G to open the Immediate window.
Copy the SQL statement to clipboard.
Create a new query, swtich it to SQL View (View menu), and paste the SQL
statement in. You can now work with it to get it right. Once you know how
to
fix it, you can then fix your code.

It could be something as simple as th OANo field in TBudget is defined as
a
Text field, where it needs to be a Number field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Swinky" <sw********@lunt.comwrote in message
news:11**********************@l12g2000cwl.googleg roups.com...
Mr. Browne's copy code on his web site has saved me. I have been
struggling to copy a record with several related sub-form tables. I
found code on his web site that copies a sub-form table, implemented it
and was successful to make it work. Thank you Mr. Browne!

However, the copy code on his web site only updates one sub-form table.
My database has three sub-forms that need to be updated. Although, I
am not a programmer (I am learning through this excercise), I modified
the code a bit to try to copy a second table and am getting an error
(the story of my life!). If Mr. Browne or anyone reading this can
help, I may be able to keep all my hair on my head.

Here's the error I'm getting (with my modified code following):

Run-time error '3134'
Syntax error in Insert Into statement

When I debug, this is the line highlighted: db.Execute strSq2,
dbFailOnError

Here is the entire code:

Private Sub Command61_Click()
'Adapted from: http://allenbrowne.com

Dim strSql As String
Dim strSq2 As String
Dim db As DAO.Database
Dim lngInvID As Long

Set db = DBEngine(0)(0)

If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record
With Me.RecordsetClone
.AddNew
!OADate = Me.OADate
!AcctMgr = Me.AcctMgr
!ProvType = Me.ProvType
!ProvExpDate = Me.ProvExpDate
!ProvNoExp = Me.ProvNoExp
!ProvOANo = Me.ProvOANo
!OrderType = Me.OrderType
!Plant = Me.Plant
!TypeComments = Me.TypeComments
!EngChange = Me.EngChange
!EngComments = Me.EngComments
!PriceChange = Me.PriceChange
!NewPrice = Me.NewPrice
!NewPriceDate = Me.NewPriceDate
!BaseMetalPrice = Me.BaseMetalPrice
!PriceComments = Me.PriceComments
!BudgetRev = Me.BudgetRev
!OACustID = Me.OACustID
!NewCust = Me.NewCust
!PONo = Me.PONo
!PODate = Me.PODate
!QuoteProjNo = Me.QuoteProjNo
!PODate = Me.PODate
!QuoteProjNo = Me.QuoteProjNo
!ProdNo = Me.ProdNo
![Prod Name] = Me.[Prod Name]
!ToolProjNo = Me.ToolProjNo
!OrderDescription = Me.OrderDescription
!PODate = Me.PODate
!PODate = Me.PODate
!PODate = Me.PODate
!PODate = Me.PODate
!OAPass = Me.OAPass
!Approval = Me.Approval

.Update
.Bookmark = .LastModified
lngID = !OANo
'Duplicate the related records.
If Me.FTool.Form.RecordsetClone.RecordCount 0 Then
strSql = "INSERT INTO TTool (OANo, ToolReplacement,
ReplDescription, [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling,
AmortDescription, EstLuntCost, EstYearlyVolume, Routing ) " & _
"SELECT " & lngID & " As OANo, ToolReplacement,
ReplDescription, [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling,
AmortDescription, EstLuntCost, EstYearlyVolume, Routing " & _
"FROM TTool " & _
"WHERE OANo = " & Me.OANo & ";"

'db.Execute strSql, dbFailOnError

If Me.FBudget.Form.RecordsetClone.RecordCount 0 Then
strSq2 = "INSERT INTO TBudget (OANo, ToolRev,
ToolEstCost, TrimRev, TrimEstCost, MachineRev, MachineEstCost,
AssRevenue, AssEstCost, SampleRev, SampleEstCost, GageRev, GageEstCost,
CompRev, CompEstCost, ENHRRev, ENHREstCost, QAHRRev, QAHREstCost,
DesignRev, DesignEstCost, CaplEquipRev, CaplEquipEstCost, PROTRev,
PROTEstCost, DSHRRev, DSHREstCost, MiscRevenue, MiscEstCost,
PrototypePrice, LotSetupCharge, TtlRevenue, TtlEstCost, TtlRev & Cost,
AddAmtCurBudget, Comments ) " & _
"SELECT " & lngID & " As OANo, ToolRev,
ToolEstCost, TrimRev, TrimEstCost, MachineRev, MachineEstCost,
AssRevenue, AssEstCost, SampleRev, SampleEstCost, GageRev, GageEstCost,
CompRev, CompEstCost, ENHRRev, ENHREstCost, QAHRRev, QAHREstCost,
DesignRev, DesignEstCost, CaplEquipRev, CaplEquipEstCost, PROTRev,
PROTEstCost, DSHRRev, DSHREstCost, MiscRevenue, MiscEstCost,
PrototypePrice, LotSetupCharge, TtlRevenue, TtlEstCost, TtlRev & Cost,
AddAmtCurBudget, Comments " & _
"FROM TBudget " & _
"WHERE OANo = " & Me.OANo & ";"

db.Execute strSql
db.Execute strSq2, dbFailOnError (THIS IS THE LINE THAT
ERRORS WHEN I DEBUG)
Else
MsgBox "Main record duplicated, but there were no
related records."
End If
End If
'Display the duplicate.
Me.Bookmark = .LastModified
End With
End If

Set db = Nothing
End Sub

While I would love to hear from Mr. Browne (at least to say thank you
for his awesome web site!), any and all suggestions are welcome!

Dec 15 '06 #5

P: n/a
That did the trick! I removed the calculated field from the SELECT
statement and wa-la, it worked!!

Mr. Allen Browne, Thank you! Thank you! Thank you! Not only for
helping me solve the problem, but your suggestions helped me to work
through it so I now have a better understanding of VB in the Access
environment. Thanks for helping a lady in distress...I am sending you
a big internet ((((hug)))) for your help!

Sandy

Allen Browne wrote:
The usual approach in a normalized is that you do not store calculations in
your table. Instead, you put the calcuation expression in a query, so you
can use the calculated field in any form or report, but it can never store
the wrong answer because it isn't stored.

If that's what you are doing, then you need to remove the calculated field
from the SQL statement twice (from the INSERT clause, and the SELECT
clause.) You cannot insert the value into a field that is not in the table.

If you do have the field in the table, the fact that Access is treating it
as a parameter means it's name is spelled incorrectly in one of those
places--either the target (the table), or the source (the form.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Swinky" <sw********@lunt.comwrote in message
news:11*********************@j72g2000cwa.googlegro ups.com...
Thank you Allen for repsonding to my SOS. I'm totally grateful for
help to get me this far! Can you (or anyone else) help just a bit
more??

I followed your instructions and found out that one of my fields had a
space in it and the error was removed when adding [] brackets to the
field. Fixing that, I get the query to run.

Now I get a new error: Run-time error: 3061, too few parameters,
expected 1.

When I run the above query, I get a message box that asks me for the
parameter value for my field [TtlRev & Cost]. In my form, this is a
calculated field based on a formula with input from two other fields:
TTLRevenue minus TTLEstCost. So I think what is happening is my code
is not taking the [TtlRev & Cost] value from my original record and
moving it into the new record since it's a calculated field. Am I
correct?

To make it even more confusing, the two fields from the formula are
calculated fields themselves. They are the added sum of other fields
in the form. But I don't get an error on that addition when I run the
query---although it's assigning $0 as the value for the two fields,
even though it should give me an amount other than $0 since there are
numbers to add in the other fields.

If the formula is the problem, is there a way to build the formula into
my "Select" statement so that this will run??

Erg! So close but yet not close enough. I was suppossed to roll out
this little program on Wednesday of this week but this crazy little
problem is keeping me from doing that.

Any suggestions on how to resolve the problem?

Allen Browne wrote:
To help you debug the SQL statement, add the line:
Debug.Print strSq2
immediately above the line:
db.Execute strSq2, dbFailOnError

When it fails, press Ctrl+G to open the Immediate window.
Copy the SQL statement to clipboard.
Create a new query, swtich it to SQL View (View menu), and paste the SQL
statement in. You can now work with it to get it right. Once you know how
to
fix it, you can then fix your code.

It could be something as simple as th OANo field in TBudget is defined as
a
Text field, where it needs to be a Number field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Swinky" <sw********@lunt.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.com...
Mr. Browne's copy code on his web site has saved me. I have been
struggling to copy a record with several related sub-form tables. I
found code on his web site that copies a sub-form table, implemented it
and was successful to make it work. Thank you Mr. Browne!

However, the copy code on his web site only updates one sub-form table.
My database has three sub-forms that need to be updated. Although, I
am not a programmer (I am learning through this excercise), I modified
the code a bit to try to copy a second table and am getting an error
(the story of my life!). If Mr. Browne or anyone reading this can
help, I may be able to keep all my hair on my head.

Here's the error I'm getting (with my modified code following):

Run-time error '3134'
Syntax error in Insert Into statement

When I debug, this is the line highlighted: db.Execute strSq2,
dbFailOnError

Here is the entire code:

Private Sub Command61_Click()
'Adapted from: http://allenbrowne.com

Dim strSql As String
Dim strSq2 As String
Dim db As DAO.Database
Dim lngInvID As Long

Set db = DBEngine(0)(0)

If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record
With Me.RecordsetClone
.AddNew
!OADate = Me.OADate
!AcctMgr = Me.AcctMgr
!ProvType = Me.ProvType
!ProvExpDate = Me.ProvExpDate
!ProvNoExp = Me.ProvNoExp
!ProvOANo = Me.ProvOANo
!OrderType = Me.OrderType
!Plant = Me.Plant
!TypeComments = Me.TypeComments
!EngChange = Me.EngChange
!EngComments = Me.EngComments
!PriceChange = Me.PriceChange
!NewPrice = Me.NewPrice
!NewPriceDate = Me.NewPriceDate
!BaseMetalPrice = Me.BaseMetalPrice
!PriceComments = Me.PriceComments
!BudgetRev = Me.BudgetRev
!OACustID = Me.OACustID
!NewCust = Me.NewCust
!PONo = Me.PONo
!PODate = Me.PODate
!QuoteProjNo = Me.QuoteProjNo
!PODate = Me.PODate
!QuoteProjNo = Me.QuoteProjNo
!ProdNo = Me.ProdNo
![Prod Name] = Me.[Prod Name]
!ToolProjNo = Me.ToolProjNo
!OrderDescription = Me.OrderDescription
!PODate = Me.PODate
!PODate = Me.PODate
!PODate = Me.PODate
!PODate = Me.PODate
!OAPass = Me.OAPass
!Approval = Me.Approval

.Update
.Bookmark = .LastModified
lngID = !OANo
'Duplicate the related records.
If Me.FTool.Form.RecordsetClone.RecordCount 0 Then
strSql = "INSERT INTO TTool (OANo, ToolReplacement,
ReplDescription, [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling,
AmortDescription, EstLuntCost, EstYearlyVolume, Routing ) " & _
"SELECT " & lngID & " As OANo, ToolReplacement,
ReplDescription, [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling,
AmortDescription, EstLuntCost, EstYearlyVolume, Routing " & _
"FROM TTool " & _
"WHERE OANo = " & Me.OANo & ";"

'db.Execute strSql, dbFailOnError

If Me.FBudget.Form.RecordsetClone.RecordCount 0 Then
strSq2 = "INSERT INTO TBudget (OANo, ToolRev,
ToolEstCost, TrimRev, TrimEstCost, MachineRev, MachineEstCost,
AssRevenue, AssEstCost, SampleRev, SampleEstCost, GageRev, GageEstCost,
CompRev, CompEstCost, ENHRRev, ENHREstCost, QAHRRev, QAHREstCost,
DesignRev, DesignEstCost, CaplEquipRev, CaplEquipEstCost, PROTRev,
PROTEstCost, DSHRRev, DSHREstCost, MiscRevenue, MiscEstCost,
PrototypePrice, LotSetupCharge, TtlRevenue, TtlEstCost, TtlRev & Cost,
AddAmtCurBudget, Comments ) " & _
"SELECT " & lngID & " As OANo, ToolRev,
ToolEstCost, TrimRev, TrimEstCost, MachineRev, MachineEstCost,
AssRevenue, AssEstCost, SampleRev, SampleEstCost, GageRev, GageEstCost,
CompRev, CompEstCost, ENHRRev, ENHREstCost, QAHRRev, QAHREstCost,
DesignRev, DesignEstCost, CaplEquipRev, CaplEquipEstCost, PROTRev,
PROTEstCost, DSHRRev, DSHREstCost, MiscRevenue, MiscEstCost,
PrototypePrice, LotSetupCharge, TtlRevenue, TtlEstCost, TtlRev & Cost,
AddAmtCurBudget, Comments " & _
"FROM TBudget " & _
"WHERE OANo = " & Me.OANo & ";"

db.Execute strSql
db.Execute strSq2, dbFailOnError (THIS IS THE LINE THAT
ERRORS WHEN I DEBUG)
Else
MsgBox "Main record duplicated, but there were no
related records."
End If
End If
'Display the duplicate.
Me.Bookmark = .LastModified
End With
End If

Set db = Nothing
End Sub

While I would love to hear from Mr. Browne (at least to say thank you
for his awesome web site!), any and all suggestions are welcome!
Dec 15 '06 #6

P: n/a
That did the trick! I removed the calculated field from the SELECT
statement and wa-la, it worked!!

Mr. Allen Browne, Thank you! Thank you! Thank you! Not only for
helping me solve the problem, but your suggestions helped me to work
through it so I now have a better understanding of VB in the Access
environment. Thanks for helping a lady in distress...I am sending you
a big internet ((((hug)))) for your help!

Sandy

Allen Browne wrote:
The usual approach in a normalized is that you do not store calculations in
your table. Instead, you put the calcuation expression in a query, so you
can use the calculated field in any form or report, but it can never store
the wrong answer because it isn't stored.

If that's what you are doing, then you need to remove the calculated field
from the SQL statement twice (from the INSERT clause, and the SELECT
clause.) You cannot insert the value into a field that is not in the table.

If you do have the field in the table, the fact that Access is treating it
as a parameter means it's name is spelled incorrectly in one of those
places--either the target (the table), or the source (the form.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Swinky" <sw********@lunt.comwrote in message
news:11*********************@j72g2000cwa.googlegro ups.com...
Thank you Allen for repsonding to my SOS. I'm totally grateful for
help to get me this far! Can you (or anyone else) help just a bit
more??

I followed your instructions and found out that one of my fields had a
space in it and the error was removed when adding [] brackets to the
field. Fixing that, I get the query to run.

Now I get a new error: Run-time error: 3061, too few parameters,
expected 1.

When I run the above query, I get a message box that asks me for the
parameter value for my field [TtlRev & Cost]. In my form, this is a
calculated field based on a formula with input from two other fields:
TTLRevenue minus TTLEstCost. So I think what is happening is my code
is not taking the [TtlRev & Cost] value from my original record and
moving it into the new record since it's a calculated field. Am I
correct?

To make it even more confusing, the two fields from the formula are
calculated fields themselves. They are the added sum of other fields
in the form. But I don't get an error on that addition when I run the
query---although it's assigning $0 as the value for the two fields,
even though it should give me an amount other than $0 since there are
numbers to add in the other fields.

If the formula is the problem, is there a way to build the formula into
my "Select" statement so that this will run??

Erg! So close but yet not close enough. I was suppossed to roll out
this little program on Wednesday of this week but this crazy little
problem is keeping me from doing that.

Any suggestions on how to resolve the problem?

Allen Browne wrote:
To help you debug the SQL statement, add the line:
Debug.Print strSq2
immediately above the line:
db.Execute strSq2, dbFailOnError

When it fails, press Ctrl+G to open the Immediate window.
Copy the SQL statement to clipboard.
Create a new query, swtich it to SQL View (View menu), and paste the SQL
statement in. You can now work with it to get it right. Once you know how
to
fix it, you can then fix your code.

It could be something as simple as th OANo field in TBudget is defined as
a
Text field, where it needs to be a Number field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Swinky" <sw********@lunt.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.com...
Mr. Browne's copy code on his web site has saved me. I have been
struggling to copy a record with several related sub-form tables. I
found code on his web site that copies a sub-form table, implemented it
and was successful to make it work. Thank you Mr. Browne!

However, the copy code on his web site only updates one sub-form table.
My database has three sub-forms that need to be updated. Although, I
am not a programmer (I am learning through this excercise), I modified
the code a bit to try to copy a second table and am getting an error
(the story of my life!). If Mr. Browne or anyone reading this can
help, I may be able to keep all my hair on my head.

Here's the error I'm getting (with my modified code following):

Run-time error '3134'
Syntax error in Insert Into statement

When I debug, this is the line highlighted: db.Execute strSq2,
dbFailOnError

Here is the entire code:

Private Sub Command61_Click()
'Adapted from: http://allenbrowne.com

Dim strSql As String
Dim strSq2 As String
Dim db As DAO.Database
Dim lngInvID As Long

Set db = DBEngine(0)(0)

If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record
With Me.RecordsetClone
.AddNew
!OADate = Me.OADate
!AcctMgr = Me.AcctMgr
!ProvType = Me.ProvType
!ProvExpDate = Me.ProvExpDate
!ProvNoExp = Me.ProvNoExp
!ProvOANo = Me.ProvOANo
!OrderType = Me.OrderType
!Plant = Me.Plant
!TypeComments = Me.TypeComments
!EngChange = Me.EngChange
!EngComments = Me.EngComments
!PriceChange = Me.PriceChange
!NewPrice = Me.NewPrice
!NewPriceDate = Me.NewPriceDate
!BaseMetalPrice = Me.BaseMetalPrice
!PriceComments = Me.PriceComments
!BudgetRev = Me.BudgetRev
!OACustID = Me.OACustID
!NewCust = Me.NewCust
!PONo = Me.PONo
!PODate = Me.PODate
!QuoteProjNo = Me.QuoteProjNo
!PODate = Me.PODate
!QuoteProjNo = Me.QuoteProjNo
!ProdNo = Me.ProdNo
![Prod Name] = Me.[Prod Name]
!ToolProjNo = Me.ToolProjNo
!OrderDescription = Me.OrderDescription
!PODate = Me.PODate
!PODate = Me.PODate
!PODate = Me.PODate
!PODate = Me.PODate
!OAPass = Me.OAPass
!Approval = Me.Approval

.Update
.Bookmark = .LastModified
lngID = !OANo
'Duplicate the related records.
If Me.FTool.Form.RecordsetClone.RecordCount 0 Then
strSql = "INSERT INTO TTool (OANo, ToolReplacement,
ReplDescription, [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling,
AmortDescription, EstLuntCost, EstYearlyVolume, Routing ) " & _
"SELECT " & lngID & " As OANo, ToolReplacement,
ReplDescription, [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling,
AmortDescription, EstLuntCost, EstYearlyVolume, Routing " & _
"FROM TTool " & _
"WHERE OANo = " & Me.OANo & ";"

'db.Execute strSql, dbFailOnError

If Me.FBudget.Form.RecordsetClone.RecordCount 0 Then
strSq2 = "INSERT INTO TBudget (OANo, ToolRev,
ToolEstCost, TrimRev, TrimEstCost, MachineRev, MachineEstCost,
AssRevenue, AssEstCost, SampleRev, SampleEstCost, GageRev, GageEstCost,
CompRev, CompEstCost, ENHRRev, ENHREstCost, QAHRRev, QAHREstCost,
DesignRev, DesignEstCost, CaplEquipRev, CaplEquipEstCost, PROTRev,
PROTEstCost, DSHRRev, DSHREstCost, MiscRevenue, MiscEstCost,
PrototypePrice, LotSetupCharge, TtlRevenue, TtlEstCost, TtlRev & Cost,
AddAmtCurBudget, Comments ) " & _
"SELECT " & lngID & " As OANo, ToolRev,
ToolEstCost, TrimRev, TrimEstCost, MachineRev, MachineEstCost,
AssRevenue, AssEstCost, SampleRev, SampleEstCost, GageRev, GageEstCost,
CompRev, CompEstCost, ENHRRev, ENHREstCost, QAHRRev, QAHREstCost,
DesignRev, DesignEstCost, CaplEquipRev, CaplEquipEstCost, PROTRev,
PROTEstCost, DSHRRev, DSHREstCost, MiscRevenue, MiscEstCost,
PrototypePrice, LotSetupCharge, TtlRevenue, TtlEstCost, TtlRev & Cost,
AddAmtCurBudget, Comments " & _
"FROM TBudget " & _
"WHERE OANo = " & Me.OANo & ";"

db.Execute strSql
db.Execute strSq2, dbFailOnError (THIS IS THE LINE THAT
ERRORS WHEN I DEBUG)
Else
MsgBox "Main record duplicated, but there were no
related records."
End If
End If
'Display the duplicate.
Me.Bookmark = .LastModified
End With
End If

Set db = Nothing
End Sub

While I would love to hear from Mr. Browne (at least to say thank you
for his awesome web site!), any and all suggestions are welcome!
Dec 15 '06 #7

P: n/a
That did the trick! I removed the calculated field from the SELECT
statement and wa-la, it worked!!

Mr. Allen Browne, Thank you! Thank you! Thank you! Not only for
helping me solve the problem, but your suggestions helped me to work
through it so I now have a better understanding of VB in the Access
environment. Thanks for helping a lady in distress...I am sending you
a big internet ((((hug)))) for your help!

Sandy

Allen Browne wrote:
The usual approach in a normalized is that you do not store calculations in
your table. Instead, you put the calcuation expression in a query, so you
can use the calculated field in any form or report, but it can never store
the wrong answer because it isn't stored.

If that's what you are doing, then you need to remove the calculated field
from the SQL statement twice (from the INSERT clause, and the SELECT
clause.) You cannot insert the value into a field that is not in the table.

If you do have the field in the table, the fact that Access is treating it
as a parameter means it's name is spelled incorrectly in one of those
places--either the target (the table), or the source (the form.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Swinky" <sw********@lunt.comwrote in message
news:11*********************@j72g2000cwa.googlegro ups.com...
Thank you Allen for repsonding to my SOS. I'm totally grateful for
help to get me this far! Can you (or anyone else) help just a bit
more??

I followed your instructions and found out that one of my fields had a
space in it and the error was removed when adding [] brackets to the
field. Fixing that, I get the query to run.

Now I get a new error: Run-time error: 3061, too few parameters,
expected 1.

When I run the above query, I get a message box that asks me for the
parameter value for my field [TtlRev & Cost]. In my form, this is a
calculated field based on a formula with input from two other fields:
TTLRevenue minus TTLEstCost. So I think what is happening is my code
is not taking the [TtlRev & Cost] value from my original record and
moving it into the new record since it's a calculated field. Am I
correct?

To make it even more confusing, the two fields from the formula are
calculated fields themselves. They are the added sum of other fields
in the form. But I don't get an error on that addition when I run the
query---although it's assigning $0 as the value for the two fields,
even though it should give me an amount other than $0 since there are
numbers to add in the other fields.

If the formula is the problem, is there a way to build the formula into
my "Select" statement so that this will run??

Erg! So close but yet not close enough. I was suppossed to roll out
this little program on Wednesday of this week but this crazy little
problem is keeping me from doing that.

Any suggestions on how to resolve the problem?

Allen Browne wrote:
To help you debug the SQL statement, add the line:
Debug.Print strSq2
immediately above the line:
db.Execute strSq2, dbFailOnError

When it fails, press Ctrl+G to open the Immediate window.
Copy the SQL statement to clipboard.
Create a new query, swtich it to SQL View (View menu), and paste the SQL
statement in. You can now work with it to get it right. Once you know how
to
fix it, you can then fix your code.

It could be something as simple as th OANo field in TBudget is defined as
a
Text field, where it needs to be a Number field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Swinky" <sw********@lunt.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.com...
Mr. Browne's copy code on his web site has saved me. I have been
struggling to copy a record with several related sub-form tables. I
found code on his web site that copies a sub-form table, implemented it
and was successful to make it work. Thank you Mr. Browne!

However, the copy code on his web site only updates one sub-form table.
My database has three sub-forms that need to be updated. Although, I
am not a programmer (I am learning through this excercise), I modified
the code a bit to try to copy a second table and am getting an error
(the story of my life!). If Mr. Browne or anyone reading this can
help, I may be able to keep all my hair on my head.

Here's the error I'm getting (with my modified code following):

Run-time error '3134'
Syntax error in Insert Into statement

When I debug, this is the line highlighted: db.Execute strSq2,
dbFailOnError

Here is the entire code:

Private Sub Command61_Click()
'Adapted from: http://allenbrowne.com

Dim strSql As String
Dim strSq2 As String
Dim db As DAO.Database
Dim lngInvID As Long

Set db = DBEngine(0)(0)

If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record
With Me.RecordsetClone
.AddNew
!OADate = Me.OADate
!AcctMgr = Me.AcctMgr
!ProvType = Me.ProvType
!ProvExpDate = Me.ProvExpDate
!ProvNoExp = Me.ProvNoExp
!ProvOANo = Me.ProvOANo
!OrderType = Me.OrderType
!Plant = Me.Plant
!TypeComments = Me.TypeComments
!EngChange = Me.EngChange
!EngComments = Me.EngComments
!PriceChange = Me.PriceChange
!NewPrice = Me.NewPrice
!NewPriceDate = Me.NewPriceDate
!BaseMetalPrice = Me.BaseMetalPrice
!PriceComments = Me.PriceComments
!BudgetRev = Me.BudgetRev
!OACustID = Me.OACustID
!NewCust = Me.NewCust
!PONo = Me.PONo
!PODate = Me.PODate
!QuoteProjNo = Me.QuoteProjNo
!PODate = Me.PODate
!QuoteProjNo = Me.QuoteProjNo
!ProdNo = Me.ProdNo
![Prod Name] = Me.[Prod Name]
!ToolProjNo = Me.ToolProjNo
!OrderDescription = Me.OrderDescription
!PODate = Me.PODate
!PODate = Me.PODate
!PODate = Me.PODate
!PODate = Me.PODate
!OAPass = Me.OAPass
!Approval = Me.Approval

.Update
.Bookmark = .LastModified
lngID = !OANo
'Duplicate the related records.
If Me.FTool.Form.RecordsetClone.RecordCount 0 Then
strSql = "INSERT INTO TTool (OANo, ToolReplacement,
ReplDescription, [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling,
AmortDescription, EstLuntCost, EstYearlyVolume, Routing ) " & _
"SELECT " & lngID & " As OANo, ToolReplacement,
ReplDescription, [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling,
AmortDescription, EstLuntCost, EstYearlyVolume, Routing " & _
"FROM TTool " & _
"WHERE OANo = " & Me.OANo & ";"

'db.Execute strSql, dbFailOnError

If Me.FBudget.Form.RecordsetClone.RecordCount 0 Then
strSq2 = "INSERT INTO TBudget (OANo, ToolRev,
ToolEstCost, TrimRev, TrimEstCost, MachineRev, MachineEstCost,
AssRevenue, AssEstCost, SampleRev, SampleEstCost, GageRev, GageEstCost,
CompRev, CompEstCost, ENHRRev, ENHREstCost, QAHRRev, QAHREstCost,
DesignRev, DesignEstCost, CaplEquipRev, CaplEquipEstCost, PROTRev,
PROTEstCost, DSHRRev, DSHREstCost, MiscRevenue, MiscEstCost,
PrototypePrice, LotSetupCharge, TtlRevenue, TtlEstCost, TtlRev & Cost,
AddAmtCurBudget, Comments ) " & _
"SELECT " & lngID & " As OANo, ToolRev,
ToolEstCost, TrimRev, TrimEstCost, MachineRev, MachineEstCost,
AssRevenue, AssEstCost, SampleRev, SampleEstCost, GageRev, GageEstCost,
CompRev, CompEstCost, ENHRRev, ENHREstCost, QAHRRev, QAHREstCost,
DesignRev, DesignEstCost, CaplEquipRev, CaplEquipEstCost, PROTRev,
PROTEstCost, DSHRRev, DSHREstCost, MiscRevenue, MiscEstCost,
PrototypePrice, LotSetupCharge, TtlRevenue, TtlEstCost, TtlRev & Cost,
AddAmtCurBudget, Comments " & _
"FROM TBudget " & _
"WHERE OANo = " & Me.OANo & ";"

db.Execute strSql
db.Execute strSq2, dbFailOnError (THIS IS THE LINE THAT
ERRORS WHEN I DEBUG)
Else
MsgBox "Main record duplicated, but there were no
related records."
End If
End If
'Display the duplicate.
Me.Bookmark = .LastModified
End With
End If

Set db = Nothing
End Sub

While I would love to hear from Mr. Browne (at least to say thank you
for his awesome web site!), any and all suggestions are welcome!
Dec 15 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.