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.RecordsetClo ne
.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.BaseMetalPri ce
!PriceComments = Me.PriceComment s
!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
!OrderDescripti on = Me.OrderDescrip tion
!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.R ecordsetClone.R ecordCount 0 Then
strSql = "INSERT INTO TTool (OANo, ToolReplacement ,
ReplDescription , [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling ,
AmortDescriptio n, EstLuntCost, EstYearlyVolume , Routing ) " & _
"SELECT " & lngID & " As OANo, ToolReplacement ,
ReplDescription , [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling ,
AmortDescriptio n, 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, CaplEquipEstCos t, 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, CaplEquipEstCos t, 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! 7  3387 
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********@lun t.comwrote in message
news:11******** **************@ l12g2000cwl.goo glegroups.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.RecordsetClo ne
.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.BaseMetalPri ce
!PriceComments = Me.PriceComment s
!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
!OrderDescripti on = Me.OrderDescrip tion
!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.R ecordsetClone.R ecordCount 0 Then
strSql = "INSERT INTO TTool (OANo, ToolReplacement ,
ReplDescription , [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling ,
AmortDescriptio n, EstLuntCost, EstYearlyVolume , Routing ) " & _
"SELECT " & lngID & " As OANo, ToolReplacement ,
ReplDescription , [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling ,
AmortDescriptio n, 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, CaplEquipEstCos t, 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, CaplEquipEstCos t, 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!
I think I wore him out or scared him away with my "Form can't find
control" post ;-)
-pw
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********@lun t.comwrote in message
news:11******** **************@ l12g2000cwl.goo glegroups.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.RecordsetClo ne
.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.BaseMetalPri ce
!PriceComments = Me.PriceComment s
!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
!OrderDescripti on = Me.OrderDescrip tion
!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.R ecordsetClone.R ecordCount 0 Then
strSql = "INSERT INTO TTool (OANo, ToolReplacement ,
ReplDescription , [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling ,
AmortDescriptio n, EstLuntCost, EstYearlyVolume , Routing ) " & _
"SELECT " & lngID & " As OANo, ToolReplacement ,
ReplDescription , [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling ,
AmortDescriptio n, 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, CaplEquipEstCos t, 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, CaplEquipEstCos t, 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!
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********@lun t.comwrote in message
news:11******** *************@j 72g2000cwa.goog legroups.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********@lun t.comwrote in message
news:11******* *************** @l12g2000cwl.go oglegroups.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.RecordsetClo ne
.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.BaseMetalPri ce
!PriceComments = Me.PriceComment s
!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
!OrderDescripti on = Me.OrderDescrip tion
!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.R ecordsetClone.R ecordCount 0 Then
strSql = "INSERT INTO TTool (OANo, ToolReplacement ,
ReplDescription , [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling ,
AmortDescriptio n, EstLuntCost, EstYearlyVolume , Routing ) " & _
"SELECT " & lngID & " As OANo, ToolReplacement ,
ReplDescription , [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling ,
AmortDescriptio n, 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, CaplEquipEstCos t, 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, CaplEquipEstCos t, 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!
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********@lun t.comwrote in message
news:11******** *************@j 72g2000cwa.goog legroups.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********@lun t.comwrote in message
news:11******** **************@ l12g2000cwl.goo glegroups.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.RecordsetClo ne
.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.BaseMetalPri ce
!PriceComments = Me.PriceComment s
!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
!OrderDescripti on = Me.OrderDescrip tion
!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.R ecordsetClone.R ecordCount 0 Then
strSql = "INSERT INTO TTool (OANo, ToolReplacement ,
ReplDescription , [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling ,
AmortDescriptio n, EstLuntCost, EstYearlyVolume , Routing ) " & _
"SELECT " & lngID & " As OANo, ToolReplacement ,
ReplDescription , [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling ,
AmortDescriptio n, 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, CaplEquipEstCos t, 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, CaplEquipEstCos t, 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!
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********@lun t.comwrote in message
news:11******** *************@j 72g2000cwa.goog legroups.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********@lun t.comwrote in message
news:11******** **************@ l12g2000cwl.goo glegroups.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.RecordsetClo ne
.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.BaseMetalPri ce
!PriceComments = Me.PriceComment s
!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
!OrderDescripti on = Me.OrderDescrip tion
!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.R ecordsetClone.R ecordCount 0 Then
strSql = "INSERT INTO TTool (OANo, ToolReplacement ,
ReplDescription , [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling ,
AmortDescriptio n, EstLuntCost, EstYearlyVolume , Routing ) " & _
"SELECT " & lngID & " As OANo, ToolReplacement ,
ReplDescription , [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling ,
AmortDescriptio n, 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, CaplEquipEstCos t, 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, CaplEquipEstCos t, 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!
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********@lun t.comwrote in message
news:11******** *************@j 72g2000cwa.goog legroups.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********@lun t.comwrote in message
news:11******** **************@ l12g2000cwl.goo glegroups.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.RecordsetClo ne
.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.BaseMetalPri ce
!PriceComments = Me.PriceComment s
!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
!OrderDescripti on = Me.OrderDescrip tion
!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.R ecordsetClone.R ecordCount 0 Then
strSql = "INSERT INTO TTool (OANo, ToolReplacement ,
ReplDescription , [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling ,
AmortDescriptio n, EstLuntCost, EstYearlyVolume , Routing ) " & _
"SELECT " & lngID & " As OANo, ToolReplacement ,
ReplDescription , [Es ReplaceCost], ShotLife, CavityNo, AmortizeTooling ,
AmortDescriptio n, 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, CaplEquipEstCos t, 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, CaplEquipEstCos t, 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!
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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 previously hosted the
site (Bigpond), it will cease to work soon.
This move follows a frustrating time of outages, emails not delivered for
days, and ftp broken for weeks. More information on the ISP's problems:
http://whirlpool.net.au/
|
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:
http://members.iinet.net.au/~allenbrowne/AppInventory.html
to suit a few ideas of my own.
I have thought that I might "simplify" Allen's table structure a little,
|
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
|
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?
_______________________________________________________________________
Re: Multiple Sort in a Continuous Form - Similiar to AutoFilter
06 Feb 2005 06:21
Allen Browne
|
by: Jerry Alexander |
last post by:
The Northwind Order Entry Application database is great!
-----------------------------------------
But one thing is lacking: Real-time Stock Qty calculation!
-----------------------------------------
I know that this topic has been discussed over & over in forums, but I
have yet to find anyone that has shown the following:
-----------------
1) Linking Allen Browne's code to Northwind? then an
2) Inventory report to show Products &...
| | |
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 Access 2000 are set at:
VB for Applications
MS Access 9.0 Object Library
OLE Automation
MS ActiveX DataObjects 2.5 Library
MS VB for Apps Extensibility 5.3
|
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
"Admin" screen to allow the head social worker to maintain items in
the list. (This is a volunteer project for a non-profit that is trying
to help elders "age successfully" in their own homes, and I'm trying
to help them do some tracking for...
|
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 Browne's sample. I have tried to get it
customized for me.
frmClient:
|
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) and this is working great, edit, insert etc is working bar when I try to delete a record in one of my subforms (I'm in test stage at the mo) I get a run time error 3022 'The changes you requested to the table where not successful because they would...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |
