Tom:
I understand however, it has been requested that the original quote be
available after having been changed to a WorkOrder.
It's more about the methodology and how to get around the problem of not
being able to add a record to the detail side without the one side having a
record. Although, I'm sure that my code could use help as well.
Currently I am using recordsets as opposed to INSERT INTO but that can be
changed. An example is below:
On Error GoTo errorTransaction
Dim myWrk As DAO.Workspace,db as DAO.database
Dim SQLQuote As String, SQLQuotePart As String, SQLQuoteLabor As String
Dim rstQuote As DAO.Recordset, rstQuotePart As DAO.Recordset,
rstQuoteLabor As DAO.Recordset
Dim rstWO As DAO.Recordset, rstWOPart As DAO.Recordset, rstWOLabor As
DAO.Recordset
Dim frm As Form
Set frm = Forms!frmQuotes
Set myWrk = DBEngine.Workspaces(0)
Set db = CurrentDb
myWrk.BeginTrans
SQLQuote = "Select * " & vbCrLf & _
"FROM tblQuotes " & vbCrLf & _
"WHERE " & "[tblQuotes].[QuoteID] = " & Forms!frmQuotes![QuoteID]
Set rstQuote = db.OpenRecordset(SQLQuote, dbReadOnly)
Set rstWO = db.OpenRecordset("tblWorkOrders", dbOpenDynaset)
If Not rstQuote.NoMatch Then
If rstQuote!ChangeToWorkOrder = False Then
rstWO.AddNew
rstWO!CustomerID = rstQuote!CustomerID
rstWO!ProblemDescription = rstQuote!ProblemDescription
rstWO!CarID = rstQuote!CarID
rstWO!SalesTaxRate = rstQuote!SalesTaxRate
rstWO!QuoteID = rstQuote!QuoteID
rstWO.Update
End If
Else
GoTo errorTransaction
End If
SQLQuotePart = "Select * " & vbCrLf & _
"FROM tblQuoteParts " & vbCrLf & _
"WHERE " & "[tblQuoteParts.QuoteID] = " & frm!QuoteID
Set rstQuotePart = db.OpenRecordset(SQLQuotePart, dbReadOnly)
Set rstWOPart = db.OpenRecordset("tblWorkOrderParts", dbOpenDynaset)
If Not rstQuotePart.NoMatch Then
If rstQuote!ChangeToWorkOrder = False Then
Do Until rstQuotePart.EOF
rstWOPart.AddNew
rstWOPart!PartCategoryID = rstQuotePart!CategoryID
rstWOPart!PartID = rstQuotePart!PartID
rstWOPart!Qty = rstQuotePart!Qty
rstWOPart!Price = rstQuotePart!Price
rstWOPart.Update
Loop
Else
GoTo errorTransaction
End If
End If
myWrk.CommitTrans
errorTransaction:
myWrk.Rollback
MsgBox Err.Number & " " & Err.Description
MsgBox "Transaction was not completed successfully", vbCritical, "Problem
with batch run"
GoTo exitTransaction
exitTransaction:
db.Close
myWrk.Close
Well, it's late here and I will check for responses in the AM. Thanks so
much.
Barry
Tom van Stiphout said:
On Thu, 4 Mar 2010 13:30:01 -0800, Barry
Show us some code. How are you copying hte data from the Quote* tables
to the WorkOrder* tables?
Also consider if this is really a good idea. I remember making this
mistake once myself. When I came to my senses I simply added a Status
field that would turn the Quote status to a Workorder status.
-Tom.
Microsoft Access MVP
Hello:
I am trying to add new records from a Quotes table and associated
QuoteDetails table to a WorkOrder table and associated WorkOrderDetails
table. I thought transactions but, perhaps I am wrong. The WorkOrderDetails
table will not allow the addition of records unless there is an associated
redord in WorkOrders. So, the transaction fails. If I commit the single
record from WorkOrders and there is some error after in the WorkOrderDetails,
how would I then recover from this situation? I hope that I have adequately
explained the problem and I look forward to some discussion about the method
to better implement this type of coding.
Thanks,
Barry
.
