473,287 Members | 1,827 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Allen Browne, Where are You??

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
7 3355
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
PW
I think I wore him out or scared him away with my "Form can't find
control" post ;-)

-pw
Dec 15 '06 #3
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Allen Browne | last post by:
If you have bookmarked or linked to the Access tips at this site, please ensure you are using the domain name: http://allenbrowne.com/ If your link contains a reference to the server that...
1
by: Don Leverton | last post by:
Hi Folks, I'm still in the process of rewriting my Parts Inventory application, and still using good old Access97 to do it. I'm attempting to modify Allen Browne's code from:...
2
by: Sunil Korah | last post by:
Hi, I want to update the data in the master table with data from another table. For example the following tables. Table1 - Account_No Name Last_Transaction_Date Table2 - Account_No
1
by: Steven Britton via AccessMonster.com | last post by:
Follow Up question to the below posted by Allen Browne on 02/06/2005 - Could something like this work for a form that has a subform and the subform is a datasheet? ...
15
by: Jerry Alexander | last post by:
The Northwind Order Entry Application database is great! ----------------------------------------- But one thing is lacking: Real-time Stock Qty calculation!...
3
by: Photobug | last post by:
I have downloaded Allen Browne's function TableInfo() and am getting a ByRef Type Mismatch error when I try to execute it. I don't know if it is a reference problem or not, but my references for...
2
by: sara | last post by:
I use Allen Browne's Audit Trail code in everything I do and I love it. Recently, I've run into a problem I can't figure out. I have a database with about 35 lookup tables. I am creating an...
6
by: OzairKhalid | last post by:
Hi, I have uploaded "ClientMultiSample2k.zip" at ... http://tech.groups.yahoo.com/group/MS_Access_Professionals/files/2_AssistanceNeeded/ClientMultiSample2k.zip The file is basically Allen...
6
by: babamc4 | last post by:
I have a main form (mainformlung) with 5 subforms (followupacute, followuplate, biochemresults, haemresults and pftresults). I have copied Allen Browne's Audit Trail code (thanks to Allen Browne)...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.