I am copying and pasting a record using the code after a user updates list boxes.
Private Sub cmdStart_Click()
InstanceEnd = Now
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
' Clear fields for new values
InstanceStart = Null
InstanceEnd = Null
TotalUnits = 0
InstanceStart = Now
UniqueShiftCounter = NextCounter
mpgLineRunning.SetFocus
'DTReason = Null
End Sub
Private Sub cmdStop_Click()
InstanceEnd = Now
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
' Clear fields for new values
InstanceStart = Null
InstanceEnd = Null
TotalUnits = 0
InstanceStart = Now
UniqueShiftCounter = NextCounter
Select Case lstDTReason
Case "Cleaning at end of day"
Dim Config As Integer
Dim Ans As Integer
Config = vbYesNo + vbQuestion + vbDefaultButton2
Ans = MsgBox("Are you sure the shift is ending?", Config)
If Ans = vbYes Then mpgDayEnd.SetFocus
AssignTimerValue = "720000"
Case "Break"
mpgLineStopped.SetFocus
AssignTimerValue = "1200000"
Case "Start new product"
mpg3Customer.SetFocus
'may not need to copy and paste new record
InstanceEnd = Now
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
InstanceStart = Now
UniqueShiftCounter = NextCounter
' Clear fields for new values
Description.Value = Null
TotalUnits.Value = 0
CrewSize.Value = Null
Customer.Value = Null
DTReason.Value = Null
AssignTimerValue = "60000"
Case Else
mpgLineStopped.SetFocus
AssignTimerValue = "0"
End Select
'DTReason = Null
TimerInterval = AssignTimerValue
End Sub
However i want to set the DTReason field in the record to Null when i am copying it and try as i might i can't get it to work (could be an issue with the record being bound to the form rather than through a query but i don't know how to do this yet and i have to demo a prototype in 2 days time so i haven't got time to learn and rewrite it all (i will if my boss likes the protype)).
I think i could solve my immediate problem by identifying the fields to copy into the new record rather than copying the entire record but i need help with some syntax. I think i can do it using SQL which i have a limited understanding of but i don't know how to reference fields or "start SQL running" within Access.
My thoughts on the SQL would be something like this
SELECT UniqueShiftCounter, Description, LineNumber, UnitCount, TotalUnits, CrewSize, DataInputter, Customer
FROM tblProductionDetails
I hope somebody can help.
Regards
Adrian
Private Sub cmdStart_Click()
InstanceEnd = Now
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
' Clear fields for new values
InstanceStart = Null
InstanceEnd = Null
TotalUnits = 0
InstanceStart = Now
UniqueShiftCounter = NextCounter
mpgLineRunning.SetFocus
'DTReason = Null
End Sub
Private Sub cmdStop_Click()
InstanceEnd = Now
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
' Clear fields for new values
InstanceStart = Null
InstanceEnd = Null
TotalUnits = 0
InstanceStart = Now
UniqueShiftCounter = NextCounter
Select Case lstDTReason
Case "Cleaning at end of day"
Dim Config As Integer
Dim Ans As Integer
Config = vbYesNo + vbQuestion + vbDefaultButton2
Ans = MsgBox("Are you sure the shift is ending?", Config)
If Ans = vbYes Then mpgDayEnd.SetFocus
AssignTimerValue = "720000"
Case "Break"
mpgLineStopped.SetFocus
AssignTimerValue = "1200000"
Case "Start new product"
mpg3Customer.SetFocus
'may not need to copy and paste new record
InstanceEnd = Now
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
InstanceStart = Now
UniqueShiftCounter = NextCounter
' Clear fields for new values
Description.Value = Null
TotalUnits.Value = 0
CrewSize.Value = Null
Customer.Value = Null
DTReason.Value = Null
AssignTimerValue = "60000"
Case Else
mpgLineStopped.SetFocus
AssignTimerValue = "0"
End Select
'DTReason = Null
TimerInterval = AssignTimerValue
End Sub
However i want to set the DTReason field in the record to Null when i am copying it and try as i might i can't get it to work (could be an issue with the record being bound to the form rather than through a query but i don't know how to do this yet and i have to demo a prototype in 2 days time so i haven't got time to learn and rewrite it all (i will if my boss likes the protype)).
I think i could solve my immediate problem by identifying the fields to copy into the new record rather than copying the entire record but i need help with some syntax. I think i can do it using SQL which i have a limited understanding of but i don't know how to reference fields or "start SQL running" within Access.
My thoughts on the SQL would be something like this
SELECT UniqueShiftCounter, Description, LineNumber, UnitCount, TotalUnits, CrewSize, DataInputter, Customer
FROM tblProductionDetails
I hope somebody can help.
Regards
Adrian