nepotist
09-19-2008, 06:59 AM
Hi,
I am creating a data entry form that is based on the table. I want to limit the inputs on the form as the other fields in the record can be calculated based on the other fileds provided by the user.
The below is the code that i used to a command button to update. I noticed that it does update the user input in to the tables once i close the form.
The code:
Option Compare Database
Option Explicit
Private Sub cmdNewRoadway_Click()
Dim stationid As Single
Dim SegmentID As Single
Dim LosA As Integer
Dim LosB As Integer
Dim LosC As Integer
Dim LosD As Integer
Dim LosE As Integer
Dim Permitted As Integer
Dim Encumbered As Integer
Dim Reserved As Integer
Dim TotalPeak As Integer
Dim Los As String
Dim PkVol As Integer
Dim PkDir As String
Dim RemCap As Integer
Dim AvaCap As Integer
Dim BackLOS As String
Dim FinalLOS As String
Dim BackVC As Single
Dim FinalVC As Single
SegmentID = Me!SegmentID
PkVol = Me!PkHourPkDirVol
Los = Me!cmblos
LosA = Me!LosAServiceVolume
LosB = Me!LosBServiceVolume
LosC = Me!LosCServiceVolume
LosD = Me!LosDServiceVolume
LosE = Me!LosEServiceVolume
'DoCmd.Close
Dim db As Database
Dim rstLink As Recordset
Set db = CurrentDb()
Set rstLink = db.OpenRecordset("tblconcurrencysegments", dbOpenTable)
rstLink.MoveFirst
'Do Until rstLink.EOF = True
Permitted = 0
Encumbered = 0
Reserved = 0
TotalPeak = PkVol + Permitted + Encumbered + Reserved
If Los = "A" Then
Capacity = LosA
ElseIf Los = "B" Then
Capacity = LosB
ElseIf Los = "C" Then
Capacity = LosC
ElseIf Los = "D" Then
Capacity = LosD
Else
Capacity = LosE
End If
RemCap = Capacity - PkVol
AvaCap = Capacity - TotalPeak
If PkVol <= LosA Then
BackLOS = "A"
ElseIf PkVol <= LosB Then
BackLOS = "B"
ElseIf PkVol <= LosC Then
BackLOS = "C"
ElseIf PkVol <= LosD Then
BackLOS = "D"
ElseIf PkVol <= LosE Then
BackLOS = "E"
Else
BackLOS = "F"
End If
If TotalPeak <= LosA Then
FinalLOS = "A"
ElseIf TotalPeak <= LosB Then
FinalLOS = "B"
ElseIf TotalPeak <= LosC Then
FinalLOS = "C"
ElseIf TotalPeak <= LosD Then
FinalLOS = "D"
ElseIf TotalPeak <= LosE Then
FinalLOS = "E"
Else
FinalLOS = "F"
End If
BackVC = Format((PkVol / Capacity), "#.00")
FinalVC = Format((TotalPeak / Capacity), "#.00")
rstLink.Index = "SegmentID"
rstLink.Seek "=", SegmentID
With rstLink
.Edit
!Capacity = Capacity
!RemainCapacity = RemCap
!AvailCapacity = AvaCap
!BackLOS = BackLOS
!FinalLOS = FinalLOS
!BackLOS = BackLOS
!FinalLOS = FinalLOS
!Back_vC = BackVC
!Final_vC = FinalVC
.Update
End With
rstLink.Close
MsgBox "New Roadway Created", vbOKOnly
'Loop
End Sub
When i run this it says that it is not able to find the record with the link number.(Note linkID or segmentID is my primary key in the table)
can some one help me ??
I am creating a data entry form that is based on the table. I want to limit the inputs on the form as the other fields in the record can be calculated based on the other fileds provided by the user.
The below is the code that i used to a command button to update. I noticed that it does update the user input in to the tables once i close the form.
The code:
Option Compare Database
Option Explicit
Private Sub cmdNewRoadway_Click()
Dim stationid As Single
Dim SegmentID As Single
Dim LosA As Integer
Dim LosB As Integer
Dim LosC As Integer
Dim LosD As Integer
Dim LosE As Integer
Dim Permitted As Integer
Dim Encumbered As Integer
Dim Reserved As Integer
Dim TotalPeak As Integer
Dim Los As String
Dim PkVol As Integer
Dim PkDir As String
Dim RemCap As Integer
Dim AvaCap As Integer
Dim BackLOS As String
Dim FinalLOS As String
Dim BackVC As Single
Dim FinalVC As Single
SegmentID = Me!SegmentID
PkVol = Me!PkHourPkDirVol
Los = Me!cmblos
LosA = Me!LosAServiceVolume
LosB = Me!LosBServiceVolume
LosC = Me!LosCServiceVolume
LosD = Me!LosDServiceVolume
LosE = Me!LosEServiceVolume
'DoCmd.Close
Dim db As Database
Dim rstLink As Recordset
Set db = CurrentDb()
Set rstLink = db.OpenRecordset("tblconcurrencysegments", dbOpenTable)
rstLink.MoveFirst
'Do Until rstLink.EOF = True
Permitted = 0
Encumbered = 0
Reserved = 0
TotalPeak = PkVol + Permitted + Encumbered + Reserved
If Los = "A" Then
Capacity = LosA
ElseIf Los = "B" Then
Capacity = LosB
ElseIf Los = "C" Then
Capacity = LosC
ElseIf Los = "D" Then
Capacity = LosD
Else
Capacity = LosE
End If
RemCap = Capacity - PkVol
AvaCap = Capacity - TotalPeak
If PkVol <= LosA Then
BackLOS = "A"
ElseIf PkVol <= LosB Then
BackLOS = "B"
ElseIf PkVol <= LosC Then
BackLOS = "C"
ElseIf PkVol <= LosD Then
BackLOS = "D"
ElseIf PkVol <= LosE Then
BackLOS = "E"
Else
BackLOS = "F"
End If
If TotalPeak <= LosA Then
FinalLOS = "A"
ElseIf TotalPeak <= LosB Then
FinalLOS = "B"
ElseIf TotalPeak <= LosC Then
FinalLOS = "C"
ElseIf TotalPeak <= LosD Then
FinalLOS = "D"
ElseIf TotalPeak <= LosE Then
FinalLOS = "E"
Else
FinalLOS = "F"
End If
BackVC = Format((PkVol / Capacity), "#.00")
FinalVC = Format((TotalPeak / Capacity), "#.00")
rstLink.Index = "SegmentID"
rstLink.Seek "=", SegmentID
With rstLink
.Edit
!Capacity = Capacity
!RemainCapacity = RemCap
!AvailCapacity = AvaCap
!BackLOS = BackLOS
!FinalLOS = FinalLOS
!BackLOS = BackLOS
!FinalLOS = FinalLOS
!Back_vC = BackVC
!Final_vC = FinalVC
.Update
End With
rstLink.Close
MsgBox "New Roadway Created", vbOKOnly
'Loop
End Sub
When i run this it says that it is not able to find the record with the link number.(Note linkID or segmentID is my primary key in the table)
can some one help me ??