New record using form

[nepotist]

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 ??

[CreganTur]

I noticed that you're creating a DAO connection to the current database. Since the table you're working with is in the same database as this Form, why don't you use use a SQL Update Query instead of trying to do the update via DAO?

It would be a simple process- hardcode the SQL statement as a string in your code and set the values of your fields as references to the Form's object's values. Just remember to wrap the object's values with the correct data symbol- no symbol needed if it's a number data type; single quotes for strings; pound signs(#) for dates.

[nepotist]

I am sorry if I got you wrong.

I am actually creating a new record witha different primary key and I would input the key in the form. I noticed that I dont need to write any sorts of code to update the table, I just need to close the form and i can view the new record in the table.

One other thing is that as this is a new record, a few fields would be considered as zero and change when other table is updated.

I might have not able to see your point, could you please give an example of the syntax so that I can understand how would that work.

Appreciate it
Thanks

[CreganTur]

Another thought:

If the record source feeding your data entry Form is updateable, and you have the Form set to a Data Entry form (Form Properties -> Data Tab) then whatever you write into the Form will automatically be populated to the Table when you move to the next record (int he case of a Data Entry Form, the next New record).

You could have a 'Save' command button that would use 'DoCmd.GoToRecord acNext' (wrote this from memory- syntax may be incorrect) to move you to the next record, which would save the data to the table. You could have all of your error checking behind this button as well, so that it will only move to the New record if everything is kosher.

[nepotist]

Awesome I like that Idea and the data be saved and then update the rest. I figured how to save it, and i did add one more cmdbutton to update the rest of the fields of the latest record.

i could refer to refer to the last record and update it ... (SAY PRIMARY KEY BEING THE NEXT HIGHER NUMBER WHICH WOULD WORK) but what if i have a primary key which falls between two other primary keys.

is there any syntax for me to find which was the last add new record and just update that particular record. ??

[nepotist]

Cregan Thanks a ton... this is the first time that my problem has been ever solved in this forum..
I am so happy..
This is what i did i included the docmd.gotorecord,, acnext after assigning the variable the values from the form and just one command button work..
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.GoToRecord , , acNext



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
!TotalPeak = TotalPeak
!Permitted = Permitted
!Encumbered = Encumbered
!Reserved = Reserved
.Update
End With

rstLink.Close

MsgBox "New Roadway Created", vbOKOnly
'Loop
End Sub

Thank you once again

[CreganTur]

It depends on what your primary key is.

If it is a number, then you can can build a query that looks only at the primary key field. In query design view- after you select your field, click on the Totals button. It will add in a new row right under Table. This is the Total row. Click on the drop-down box and select Max. This will get the greatest value in that field. Then you can increase it by 1 to get your next primary key.

[nepotist]

I encountered a strange behaviour
the above code was when i intend to add a new roadway. (form name newroadway)
I have two forms one is edit1 and edit the edit1 form allows you to select the roadway through a ocmbobox and then clicking the button , it would open the form edit, will all the info about the selected roadway that needs to be edited.

the whole programm works fine but the thing is that the name of the form edit changes to newroadway. (I made sure that it is not actually opening newform and also the two forms have different designs)
Strange .,...
ANY CLUE ABOUT WHAT IS HAPPENING??

[CreganTur]

the whole programm works fine but the thing is that the name of the form edit changes to newroadway.

...:confused:

are you talking about the Form's caption?

[nepotist]

Yes

[CreganTur]

Yes

If you want to change the caption then just open your form in Design View. Open the Form's properties sheet and change the value of the Caption property so that it shows whatever you want.