E
EAB1977
Hello everyone,
Can someone help me here? I am working on a database that will save the
record, take some information from the existing record and use it on
the new record being recorded. This is to cut down on data entry.
I am expieriencing difficulty with the DoCmd.GoToRecord,,acNewRec line.
It states that it is already at the end of the recordset, but I do not
see in my code that I have made a new record for it. Any help would be
greatly appreciated. The code is below:
Private Sub cmdAdd_Click()
Dim msg1 As Integer, strMachine As String, strProduct As String,
strTest As String
Dim datAnalysisDate As Date, datDateProduced As Date
On Error GoTo Err_cmdAdd_Click
cmdSaveRecord_Click
msg1 = msgbox("Do you wish to reuse the information for the next
inspection?", vbYesNo)
If msg1 = vbYes Then
'Grab The information and reuse on the form
strMachine = Me.cboMachineId.Value
strProduct = Me.cboProduct.Value
strTest = Me.TestID.Value
datAnalysisDate = Me.AnalysisDate.Value
datDateProduced = Me.DateProduced.Value
Me.Time.SetFocus
DoCmd.GoToRecord , , acNewRec
Me.SampleID.Value = ""
Me.Shift.Value = ""
Me.Time.Value = ""
Me.cboMachineId.Value = ""
Me.Operator.Value = ""
Me.cboProduct.Value = ""
Me.DateProduced.Value = ""
Me.Load.Value = ""
Me.Case.Value = ""
Me.OverallScore.Value = ""
Me.ID.Value = ""
Me.UserID.Value = [Forms]![frmLogin]![cboEmployee]
Me.DateProduced.Value = datDateProduced
Me.AnalysisDate.Value = datAnalysisDate
Me.MachineID.Value = strMachine
Me.cboProduct.Value = strProduct
If Me.cboProdLine.Value = 1 Then
Me.TargetWgtOPS.Value = GetSpecWgt(strProduct)
Else
Me.TargetWgtFDIN.Value = GetSpecWgt(strProduct)
End If
Me.TestID.Value = strTest
Me.Time.SetFocus
Else
DoCmd.GoToRecord , , acNewRec
Me.UserID.Value = [Forms]![frmLogin]![cboEmployee]
Me.DateProduced.Value = Me.AnalysisDate.Value
Me.Time.SetFocus
End If
Exit_cmdAdd_Click:
Exit Sub
Err_cmdAdd_Click:
msgbox Err.Description
Resume Exit_cmdAdd_Click
End Sub
------------------------------------------------------------------------
Private Sub cmdSaveRecord_Click()
Dim db As Database, rst As Recordset, blnFlag As Boolean
On Error GoTo Err_cmdSaveRecord_Click
If IsNull(Time.Value) Then
msgbox "A time must be entered to complete the save process",
vbInformation
Time.SetFocus
Exit Sub
End If
If IsNull(cboMachineId.Value) Then
msgbox "A Machine ID must be entered to complete the save
process", vbInformation
cboMachineId.SetFocus
Exit Sub
End If
If IsNull(cboProduct.Value) Then
msgbox "A Product ID must be entered to complete the save
process", vbInformation
cboProduct.SetFocus
Exit Sub
End If
If IsNull(Operator.Value) Then
msgbox "A operator must be entered to complete the save
process.", vbInformation
Operator.SetFocus
Exit Sub
End If
If IsNull(TestID.Value) Then
msgbox "A test must be selected to complete the save process.",
vbInformation
Me.TestID.SetFocus
Exit Sub
End If
If IsNull(Me.Case.Value) Then
msgbox "A roll number must be entered to complete the save
process.", vbInformation
Me.Case.SetFocus
Exit Sub
End If
If Me.ID.Value = 0 Then
msgbox "Please select your name from the drop down list.",
vbInformation
Me.ID.SetFocus
Exit Sub
End If
Set db = CurrentDb
Set rst = db.OpenRecordset("tblProdTesting")
If rst.RecordCount > 0 Then
rst.MoveFirst
End If
blnFlag = False
If IsNull(Me.SampleID.Value) Then
'Do Nothing
Else
Do Until rst.EOF
If rst!SampleID = Me.SampleID.Value Then
blnFlag = True
Exit Do
End If
rst.MoveNext
Loop
End If
If blnFlag = True Then
With rst
.Edit
!UserID = Me.UserID.Value
!ProdLine = Me.cboProdLine.Value
!DateProduced = Me.DateProduced.Value
!AnalysisDate = Me.AnalysisDate.Value
!Time = Me.Time.Value
!TestID = Me.TestID.Value
!Product = Me.cboProduct.Value
!Shift = Me.Shift.Value
!MachineOperator = Me.MachineOperator.Value
!MachineID = Me.MachineID.Value
!Load = Me.Load.Value
!Case = Me.Case.Value
.Update
.Close
End With
If Me.cboProdLine.Value = 1 Then 'OPS
Set rst = db.OpenRecordset("tblOPS")
With rst
.Edit
!ProdTestID = Me.SampleID.Value
!SampleWeight = Me.SampleWeightOPS.Value
!TargetWeight = Me.TargetWgtOPS.Value
!Trim = Me.TrimOPS.Value
!Contamination = Me.ContaminationOPS.Value
!Clarity = Me.Clarity.Value
!CaseCode = Me.CaseCodeOPS.Value
!Carton = Me.Carton.Value
!Forming = Me.Forming.Value
!LidFit = Me.LidFit.Value
!LatchSecurity = Me.LatchSecurityOPS.Value
!VentHoles = Me.VentHoles.Value
!Imperfection = Me.Imperfection.Value
!Gauge = Me.Gauge.Value
!ClarityEXT = Me.ClarityEXT.Value
!Silicone = Me.Silicone.Value
!Stress = Me.Stress.Value
!Other = Me.OtherOPS.Value
!Comments = Me.Comments.Value
!OverallScore = Me.OverallScore.Value
.Update
End With
rst.Close
Else
Set rst = db.OpenRecordset("tblFDIN")
With rst
.Edit
!ProdTestID = Me.SampleID.Value
!AverageWeight = Me.SampleWeightFDIN.Value
!TargetWeight = Me.TargetWgtFDIN.Value
!Trim = Me.Trim.Value
!Contamination = Me.ContaminationFDIN.Value
!Perforation = Me.Perforation.Value
!Blisters = Me.Blisters.Value
!ColdCracks = Me.ColdCracks.Value
!PatternRing = Me.PatternRing.Value
!RoundedBot = Me.RoundedBot.Value
!LatchSecurity = Me.LatchSecurityFDIN.Value
!SurfaceImperfections = Me.SurfaceImperfections.Value
!Imperfections = Me.Imperfection.Value
!UnsealedSleeves = Me.UnsealedSleeves.Value
!CaseCode = Me.CaseCodeFDIN.Value
!Width = Me.Dimension.Value
!OilBath = Me.OilBath.Value
!Gauge = Me.Gauge.Value
!Pressure = Me.Pressure.Value
!Laminate = Me.Laminate.Value
!Other = Me.OtherFDIN.Value
!Comments = Me.Comments.Value
!OverallScore = Me.OverallScore.Value
.Update
End With
End If
rst.Close
Else
With rst
.AddNew
Me.SampleID.Value = !SampleID
!UserID = Me.UserID.Value
!ProdLine = Me.cboProdLine.Value
!DateProduced = Me.DateProduced.Value
!AnalysisDate = Me.AnalysisDate.Value
!Time = Me.Time.Value
!TestID = Me.TestID.Value
!Product = Me.cboProduct.Value
!Shift = Me.Shift.Value
!MachineOperator = Me.MachineOperator.Value
!MachineID = Me.MachineID.Value
!Load = Me.Load.Value
!Case = Me.Case.Value
.Update
.Close
End With
If Me.cboProdLine.Value = 1 Then 'OPS
Set rst = db.OpenRecordset("tblOPS")
With rst
.AddNew
!ProdTestID = Me.SampleID.Value
!SampleWeight = Me.SampleWeightOPS.Value
!TargetWeight = Me.TargetWgtOPS.Value
!Trim = Me.TrimOPS.Value
!Contamination = Me.ContaminationOPS.Value
!Clarity = Me.Clarity.Value
!CaseCode = Me.CaseCodeOPS.Value
!Carton = Me.Carton.Value
!Forming = Me.Forming.Value
!LidFit = Me.LidFit.Value
!LatchSecurity = Me.LatchSecurityOPS.Value
!VentHoles = Me.VentHoles.Value
!Imperfection = Me.Imperfection.Value
!Gauge = Me.Gauge.Value
!ClarityEXT = Me.ClarityEXT.Value
!Silicone = Me.Silicone.Value
!Stress = Me.Stress.Value
!Other = Me.OtherOPS.Value
!Comments = Me.Comments.Value
!OverallScore = Me.OverallScore.Value
.Update
End With
rst.Close
Else
Set rst = db.OpenRecordset("tblFDIN")
With rst
.AddNew
!ProdTestID = Me.SampleID.Value
!AverageWeight = Me.SampleWeightFDIN.Value
!TargetWeight = Me.TargetWgtFDIN.Value
!Trim = Me.Trim.Value
!Contamination = Me.ContaminationFDIN.Value
!Perforation = Me.Perforation.Value
!Blisters = Me.Blisters.Value
!ColdCracks = Me.ColdCracks.Value
!PatternRing = Me.PatternRing.Value
!RoundedBot = Me.RoundedBot.Value
!LatchSecurity = Me.LatchSecurityFDIN.Value
!SurfaceImperfections = Me.SurfaceImperfections.Value
!Imperfections = Me.Imperfection.Value
!UnsealedSleeves = Me.UnsealedSleeves.Value
!CaseCode = Me.CaseCodeFDIN.Value
!Width = Me.Dimension.Value
!OilBath = Me.OilBath.Value
!Gauge = Me.Gauge.Value
!Pressure = Me.Pressure.Value
!Laminate = Me.Laminate.Value
!Other = Me.OtherFDIN.Value
!Comments = Me.Comments.Value
!OverallScore = Me.OverallScore.Value
.Update
rst.Close
End With
End If
End If
msgbox "Record Saved"
Exit_cmdSaveRecord_Click:
Exit Sub
Err_cmdSaveRecord_Click:
msgbox Err.Description
Resume Exit_cmdSaveRecord_Click
End Sub
---------------------------------------------------------------------------
In a separeate module:
Public Function GetSpecWgt(ProductCode As String)
Dim db As Database, rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblProdSpecs")
'Find the correct spec weight for the given product code.
rst.MoveFirst
Do Until rst.EOF
If rst![product code] = ProductCode Then
GetSpecWgt = rst!specwt
Exit Do
End If
rst.MoveNext
Loop
Set db = Nothing
End Function
Can someone help me here? I am working on a database that will save the
record, take some information from the existing record and use it on
the new record being recorded. This is to cut down on data entry.
I am expieriencing difficulty with the DoCmd.GoToRecord,,acNewRec line.
It states that it is already at the end of the recordset, but I do not
see in my code that I have made a new record for it. Any help would be
greatly appreciated. The code is below:
Private Sub cmdAdd_Click()
Dim msg1 As Integer, strMachine As String, strProduct As String,
strTest As String
Dim datAnalysisDate As Date, datDateProduced As Date
On Error GoTo Err_cmdAdd_Click
cmdSaveRecord_Click
msg1 = msgbox("Do you wish to reuse the information for the next
inspection?", vbYesNo)
If msg1 = vbYes Then
'Grab The information and reuse on the form
strMachine = Me.cboMachineId.Value
strProduct = Me.cboProduct.Value
strTest = Me.TestID.Value
datAnalysisDate = Me.AnalysisDate.Value
datDateProduced = Me.DateProduced.Value
Me.Time.SetFocus
DoCmd.GoToRecord , , acNewRec
Me.SampleID.Value = ""
Me.Shift.Value = ""
Me.Time.Value = ""
Me.cboMachineId.Value = ""
Me.Operator.Value = ""
Me.cboProduct.Value = ""
Me.DateProduced.Value = ""
Me.Load.Value = ""
Me.Case.Value = ""
Me.OverallScore.Value = ""
Me.ID.Value = ""
Me.UserID.Value = [Forms]![frmLogin]![cboEmployee]
Me.DateProduced.Value = datDateProduced
Me.AnalysisDate.Value = datAnalysisDate
Me.MachineID.Value = strMachine
Me.cboProduct.Value = strProduct
If Me.cboProdLine.Value = 1 Then
Me.TargetWgtOPS.Value = GetSpecWgt(strProduct)
Else
Me.TargetWgtFDIN.Value = GetSpecWgt(strProduct)
End If
Me.TestID.Value = strTest
Me.Time.SetFocus
Else
DoCmd.GoToRecord , , acNewRec
Me.UserID.Value = [Forms]![frmLogin]![cboEmployee]
Me.DateProduced.Value = Me.AnalysisDate.Value
Me.Time.SetFocus
End If
Exit_cmdAdd_Click:
Exit Sub
Err_cmdAdd_Click:
msgbox Err.Description
Resume Exit_cmdAdd_Click
End Sub
------------------------------------------------------------------------
Private Sub cmdSaveRecord_Click()
Dim db As Database, rst As Recordset, blnFlag As Boolean
On Error GoTo Err_cmdSaveRecord_Click
If IsNull(Time.Value) Then
msgbox "A time must be entered to complete the save process",
vbInformation
Time.SetFocus
Exit Sub
End If
If IsNull(cboMachineId.Value) Then
msgbox "A Machine ID must be entered to complete the save
process", vbInformation
cboMachineId.SetFocus
Exit Sub
End If
If IsNull(cboProduct.Value) Then
msgbox "A Product ID must be entered to complete the save
process", vbInformation
cboProduct.SetFocus
Exit Sub
End If
If IsNull(Operator.Value) Then
msgbox "A operator must be entered to complete the save
process.", vbInformation
Operator.SetFocus
Exit Sub
End If
If IsNull(TestID.Value) Then
msgbox "A test must be selected to complete the save process.",
vbInformation
Me.TestID.SetFocus
Exit Sub
End If
If IsNull(Me.Case.Value) Then
msgbox "A roll number must be entered to complete the save
process.", vbInformation
Me.Case.SetFocus
Exit Sub
End If
If Me.ID.Value = 0 Then
msgbox "Please select your name from the drop down list.",
vbInformation
Me.ID.SetFocus
Exit Sub
End If
Set db = CurrentDb
Set rst = db.OpenRecordset("tblProdTesting")
If rst.RecordCount > 0 Then
rst.MoveFirst
End If
blnFlag = False
If IsNull(Me.SampleID.Value) Then
'Do Nothing
Else
Do Until rst.EOF
If rst!SampleID = Me.SampleID.Value Then
blnFlag = True
Exit Do
End If
rst.MoveNext
Loop
End If
If blnFlag = True Then
With rst
.Edit
!UserID = Me.UserID.Value
!ProdLine = Me.cboProdLine.Value
!DateProduced = Me.DateProduced.Value
!AnalysisDate = Me.AnalysisDate.Value
!Time = Me.Time.Value
!TestID = Me.TestID.Value
!Product = Me.cboProduct.Value
!Shift = Me.Shift.Value
!MachineOperator = Me.MachineOperator.Value
!MachineID = Me.MachineID.Value
!Load = Me.Load.Value
!Case = Me.Case.Value
.Update
.Close
End With
If Me.cboProdLine.Value = 1 Then 'OPS
Set rst = db.OpenRecordset("tblOPS")
With rst
.Edit
!ProdTestID = Me.SampleID.Value
!SampleWeight = Me.SampleWeightOPS.Value
!TargetWeight = Me.TargetWgtOPS.Value
!Trim = Me.TrimOPS.Value
!Contamination = Me.ContaminationOPS.Value
!Clarity = Me.Clarity.Value
!CaseCode = Me.CaseCodeOPS.Value
!Carton = Me.Carton.Value
!Forming = Me.Forming.Value
!LidFit = Me.LidFit.Value
!LatchSecurity = Me.LatchSecurityOPS.Value
!VentHoles = Me.VentHoles.Value
!Imperfection = Me.Imperfection.Value
!Gauge = Me.Gauge.Value
!ClarityEXT = Me.ClarityEXT.Value
!Silicone = Me.Silicone.Value
!Stress = Me.Stress.Value
!Other = Me.OtherOPS.Value
!Comments = Me.Comments.Value
!OverallScore = Me.OverallScore.Value
.Update
End With
rst.Close
Else
Set rst = db.OpenRecordset("tblFDIN")
With rst
.Edit
!ProdTestID = Me.SampleID.Value
!AverageWeight = Me.SampleWeightFDIN.Value
!TargetWeight = Me.TargetWgtFDIN.Value
!Trim = Me.Trim.Value
!Contamination = Me.ContaminationFDIN.Value
!Perforation = Me.Perforation.Value
!Blisters = Me.Blisters.Value
!ColdCracks = Me.ColdCracks.Value
!PatternRing = Me.PatternRing.Value
!RoundedBot = Me.RoundedBot.Value
!LatchSecurity = Me.LatchSecurityFDIN.Value
!SurfaceImperfections = Me.SurfaceImperfections.Value
!Imperfections = Me.Imperfection.Value
!UnsealedSleeves = Me.UnsealedSleeves.Value
!CaseCode = Me.CaseCodeFDIN.Value
!Width = Me.Dimension.Value
!OilBath = Me.OilBath.Value
!Gauge = Me.Gauge.Value
!Pressure = Me.Pressure.Value
!Laminate = Me.Laminate.Value
!Other = Me.OtherFDIN.Value
!Comments = Me.Comments.Value
!OverallScore = Me.OverallScore.Value
.Update
End With
End If
rst.Close
Else
With rst
.AddNew
Me.SampleID.Value = !SampleID
!UserID = Me.UserID.Value
!ProdLine = Me.cboProdLine.Value
!DateProduced = Me.DateProduced.Value
!AnalysisDate = Me.AnalysisDate.Value
!Time = Me.Time.Value
!TestID = Me.TestID.Value
!Product = Me.cboProduct.Value
!Shift = Me.Shift.Value
!MachineOperator = Me.MachineOperator.Value
!MachineID = Me.MachineID.Value
!Load = Me.Load.Value
!Case = Me.Case.Value
.Update
.Close
End With
If Me.cboProdLine.Value = 1 Then 'OPS
Set rst = db.OpenRecordset("tblOPS")
With rst
.AddNew
!ProdTestID = Me.SampleID.Value
!SampleWeight = Me.SampleWeightOPS.Value
!TargetWeight = Me.TargetWgtOPS.Value
!Trim = Me.TrimOPS.Value
!Contamination = Me.ContaminationOPS.Value
!Clarity = Me.Clarity.Value
!CaseCode = Me.CaseCodeOPS.Value
!Carton = Me.Carton.Value
!Forming = Me.Forming.Value
!LidFit = Me.LidFit.Value
!LatchSecurity = Me.LatchSecurityOPS.Value
!VentHoles = Me.VentHoles.Value
!Imperfection = Me.Imperfection.Value
!Gauge = Me.Gauge.Value
!ClarityEXT = Me.ClarityEXT.Value
!Silicone = Me.Silicone.Value
!Stress = Me.Stress.Value
!Other = Me.OtherOPS.Value
!Comments = Me.Comments.Value
!OverallScore = Me.OverallScore.Value
.Update
End With
rst.Close
Else
Set rst = db.OpenRecordset("tblFDIN")
With rst
.AddNew
!ProdTestID = Me.SampleID.Value
!AverageWeight = Me.SampleWeightFDIN.Value
!TargetWeight = Me.TargetWgtFDIN.Value
!Trim = Me.Trim.Value
!Contamination = Me.ContaminationFDIN.Value
!Perforation = Me.Perforation.Value
!Blisters = Me.Blisters.Value
!ColdCracks = Me.ColdCracks.Value
!PatternRing = Me.PatternRing.Value
!RoundedBot = Me.RoundedBot.Value
!LatchSecurity = Me.LatchSecurityFDIN.Value
!SurfaceImperfections = Me.SurfaceImperfections.Value
!Imperfections = Me.Imperfection.Value
!UnsealedSleeves = Me.UnsealedSleeves.Value
!CaseCode = Me.CaseCodeFDIN.Value
!Width = Me.Dimension.Value
!OilBath = Me.OilBath.Value
!Gauge = Me.Gauge.Value
!Pressure = Me.Pressure.Value
!Laminate = Me.Laminate.Value
!Other = Me.OtherFDIN.Value
!Comments = Me.Comments.Value
!OverallScore = Me.OverallScore.Value
.Update
rst.Close
End With
End If
End If
msgbox "Record Saved"
Exit_cmdSaveRecord_Click:
Exit Sub
Err_cmdSaveRecord_Click:
msgbox Err.Description
Resume Exit_cmdSaveRecord_Click
End Sub
---------------------------------------------------------------------------
In a separeate module:
Public Function GetSpecWgt(ProductCode As String)
Dim db As Database, rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblProdSpecs")
'Find the correct spec weight for the given product code.
rst.MoveFirst
Do Until rst.EOF
If rst![product code] = ProductCode Then
GetSpecWgt = rst!specwt
Exit Do
End If
rst.MoveNext
Loop
Set db = Nothing
End Function