Hello,
I'm trying to update(create a Change Order) based on a table of Extra work items( Extra Log) for different projects. I use a form bound to (Extra Log) to easily enter items as needed. I Also use a check box to allow only those items that are checked.To print a change order on this table the data needs to be split into 2 tables first ( Change Order) Second(Change Order details) with a common field Change Order Id to relate them. There are multiple line Items for Each Project and then a Change Order is created From this. I have the Following Code but I get an Error message (Wend Without While). I'm also having difficulty creating a (ssql) statement to filter the data to specific Projects that have a yes/no field checked (True).
The first part of this sub works. I create a special change Order Id based on the project id, then a record is created in the (Change Order) Table.It's creating the line Items that causes the errors. At some point I will use a similar sub to create an Invoice on this created Change Order.
Private Sub cmdchangeorderDetail_Click()
Dim Db As DAO.Database
Dim rs As DAO.Recordset2
Dim rsa As DAO.Recordset2
Dim CustomerID As Long
Dim statusID As Long
Dim projectID As String
Dim ChangeOrderID As String
Dim changeDate As Date
Dim changeType As String
Dim Description As String
Dim Quantity As Long
Dim Price As Currency
Dim Num As Integer
Dim Ctr As Integer
Dim ssql As String
Set Db = CurrentDb
' set the Counter variable to zero so
Ctr = 0
' Will use this variable to fill the last part of the Change order ID
Num = 1
'Set the variables for the change order function
statusID = 0
projectID = Me.Project_ID
CustomerID = Me.[Customer ID]
' String the change Order ID :Will Format ( Project ID & CHA & Num)
ChangeOrderID = Me.Project_ID & "-" & "CHA" & "-" & Num
Do Until Not Changeorders.IsChanged(ChangeOrderID)
Num = Num + 1
ChangeOrderID = Me.Project_ID & "-" & "CHA" & "-" & Num
Loop
' Open the Change Ordr Table and Add a record
If Changeorders.CreateChangeOrder(CustomerID, statusID, projectID, ChangeOrderID) Then
End If
' set ssql to criteria string for opentables function
'ssql = "[Customer ID]= " & CustomerID And "[Status ID]= " & statusID And "[Project ID]='" & projectID & "'"
' open the extra log table and fill the variables to update the Change Order
Set rs = Db.OpenRecordset("Extra Log")
With rs
While Not rs.EOF
If (![Change Ordered]) = True And (![Status ID]) = 0 Then
![Extra Date] = changeDate
![Change Type] = changeType
![Description] = Description
![Quantity] = Quantity
![Price] = Price
![Status ID] = statusID
Set rsa = Db.OpenRecordset("Change Order Details")
With rsa
rsa.AddNew
![Change Order ID] = ChangeOrderID
![Change Date] = changeDate
![Change Type] = changeType
![Description] = Description
![Quantity] = Quantity
![Price] = Price
![Status ID] = statusID
rsa.Update
rsa.Close
Set rsa = Nothing
rs.MoveNext
Wend
End If
rs.Close
Set rs = Nothing
End Sub
Thanks for any Help
Steve
I'm trying to update(create a Change Order) based on a table of Extra work items( Extra Log) for different projects. I use a form bound to (Extra Log) to easily enter items as needed. I Also use a check box to allow only those items that are checked.To print a change order on this table the data needs to be split into 2 tables first ( Change Order) Second(Change Order details) with a common field Change Order Id to relate them. There are multiple line Items for Each Project and then a Change Order is created From this. I have the Following Code but I get an Error message (Wend Without While). I'm also having difficulty creating a (ssql) statement to filter the data to specific Projects that have a yes/no field checked (True).
The first part of this sub works. I create a special change Order Id based on the project id, then a record is created in the (Change Order) Table.It's creating the line Items that causes the errors. At some point I will use a similar sub to create an Invoice on this created Change Order.
Private Sub cmdchangeorderDetail_Click()
Dim Db As DAO.Database
Dim rs As DAO.Recordset2
Dim rsa As DAO.Recordset2
Dim CustomerID As Long
Dim statusID As Long
Dim projectID As String
Dim ChangeOrderID As String
Dim changeDate As Date
Dim changeType As String
Dim Description As String
Dim Quantity As Long
Dim Price As Currency
Dim Num As Integer
Dim Ctr As Integer
Dim ssql As String
Set Db = CurrentDb
' set the Counter variable to zero so
Ctr = 0
' Will use this variable to fill the last part of the Change order ID
Num = 1
'Set the variables for the change order function
statusID = 0
projectID = Me.Project_ID
CustomerID = Me.[Customer ID]
' String the change Order ID :Will Format ( Project ID & CHA & Num)
ChangeOrderID = Me.Project_ID & "-" & "CHA" & "-" & Num
Do Until Not Changeorders.IsChanged(ChangeOrderID)
Num = Num + 1
ChangeOrderID = Me.Project_ID & "-" & "CHA" & "-" & Num
Loop
' Open the Change Ordr Table and Add a record
If Changeorders.CreateChangeOrder(CustomerID, statusID, projectID, ChangeOrderID) Then
End If
' set ssql to criteria string for opentables function
'ssql = "[Customer ID]= " & CustomerID And "[Status ID]= " & statusID And "[Project ID]='" & projectID & "'"
' open the extra log table and fill the variables to update the Change Order
Set rs = Db.OpenRecordset("Extra Log")
With rs
While Not rs.EOF
If (![Change Ordered]) = True And (![Status ID]) = 0 Then
![Extra Date] = changeDate
![Change Type] = changeType
![Description] = Description
![Quantity] = Quantity
![Price] = Price
![Status ID] = statusID
Set rsa = Db.OpenRecordset("Change Order Details")
With rsa
rsa.AddNew
![Change Order ID] = ChangeOrderID
![Change Date] = changeDate
![Change Type] = changeType
![Description] = Description
![Quantity] = Quantity
![Price] = Price
![Status ID] = statusID
rsa.Update
rsa.Close
Set rsa = Nothing
rs.MoveNext
Wend
End If
rs.Close
Set rs = Nothing
End Sub
Thanks for any Help
Steve
