Delete a field in a record (1 Viewer)

[Adrian510]

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

 

[Adrian510]

For anybody who looked at the example i attached to the initial post the reason i want to Null DTReason is that there should not be a down time reason when the line is running only when it is not running.

Regards

Adrian