Hello everyone!
I am currently having a small issue whereby my current code will update and overwrite my current record in my access database after i edit and update on my current record.
What i want is when the current record in edited, it should be save as a new record with the same PO as the record that it has been edited on.
Below is my code:
Private Sub cmdAdd_Click()
'when we click on button Add there are two options
'1. for insert
'2. for update
If Me.txtID.Tag & "" = "" Then
'this is for insert new
'add data to table
CurrentDb.Execute "INSERT INTO mxd(ID,Fabrication,Width,FinishedGoods,Colour,LabDipCode,GrossWeight,NettWeight,Lbs,Loss,Yds,Remarks,POType,ComboName,GroundColour)" & _
" VALUES(" & Me.txtID & ",'" & Me.txtFabrication & "','" & Me.txtWidth & "','" & Me.txtFinishedGood & "','" & _
Me.txtColour & "','" & Me.txtLabDipCode & "','" & Me.txtGrossweight & "','" & _
Me.txtNettweight & "','" & Me.txtLbs & "','" & Me.txtLoss & "','" & _
Me.txtYds & "','" & Me.txtRemarks & "','" & Me.cboPoType & "','" & _
Me.txtGroundColour & "',' & Me.txtComboName & " ')"
Else
'otherwise ( Tag of txtID store the id of student to be modified)
CurrentDb.Execute "UPDATE mxd " & _
" SET ID = " & Me.txtID & _
", Fabrication = '" & Me.txtFabrication & "'" & _
", Width = '" & Me.txtWidth & "'" & _
", FinishedGoods = '" & Me.txtFinishedGood & "'" & _
", Colour = '" & Me.txtColour & "'" & _
", LabDipCode = '" & Me.txtLabDipCode & "'" & _
", GrossWeight = '" & Me.txtGrossweight & "'" & _
", NettWeight = '" & Me.txtNettweight & "'" & _
", LBS = '" & Me.txtLbs & "'" & _
", Loss = '" & Me.txtLoss & "'" & _
", Yds = '" & Me.txtYds & "'" & _
", Remarks = '" & Me.txtRemarks & "'" & _
", POType = '" & Me.cboPoType & "'" & _
", ComboName = '" & Me.txtComboName & "'" & _
", GroundColour = '" & Me.txtGroundColour & "'" & _
" WHERE ID = " & Me.txtID.Tag
End If
'clear form
cmdClear_Click
'refresh data in list on form
FormMxdSub.Form.Requery
End Sub
Private Sub cmdClear_Click()
Me.txtID = ""
Me.txtFabrication = ""
Me.txtWidth = ""
Me.txtFinishedGood = ""
Me.txtColour = ""
Me.txtLabDipCode = ""
Me.txtGrossweight = ""
Me.txtNettweight = ""
Me.txtLbs = ""
Me.txtLoss = ""
Me.txtYds = ""
Me.txtRemarks = ""
Me.cboPoType = ""
Me.txtKeywords = ""
Me.txtComboName = ""
Me.txtGroundColour = ""
'focus on ID text box
Me.txtID.SetFocus
'set button edit to enable
Me.cmdEdit.Enabled = True
'change caption of button add to Add
Me.cmdAdd.Caption = "Add"
'clear tag on txtID for reset new
Me.txtID.Tag = ""
End Sub
Private Sub cmdClose_Click()
DoCmd.Close
End Sub
Private Sub cmdDelete_Click()
'delete record
'check existing selected record
If Not (Me.FormMxdSub.Form.Recordset.EOF And Me.FormMxdSub.Form.Recordset.BOF) Then
'confirm delete
If MsgBox("Are you sure you want to delete?", vbYesNo) = vbYes Then
'delete now
CurrentDb.Execute "DELETE FROM mxd " & _
"where ID = " & Me.FormMxdSub.Form.Recordset.Fields("ID")
'refresh data in list
Me.FormMxdSub.Form.Requery
End If
End If
End Sub
Private Sub cmdEdit_Click()
'check whether there is exists data in list
If Not (Me.FormMxdSub.Form.Recordset.EOF And Me.FormMxdSub.Form.Recordset.BOF) Then
'get data to text box control
With Me.FormMxdSub.Form.Recordset
Me.txtID = .Fields("ID")
Me.txtFabrication = .Fields("Fabrication")
Me.txtWidth = .Fields("Width")
Me.txtFinishedGood = .Fields("FinishedGoods")
Me.txtColour = .Fields("Colour")
Me.txtLabDipCode = .Fields("LabDipCode")
Me.txtGrossweight = .Fields("GrossWeight")
Me.txtNettweight = .Fields("NettWeight")
Me.txtLbs = .Fields("Lbs")
Me.txtLoss = .Fields("Loss")
Me.txtYds = .Fields("Yds")
Me.txtRemarks = .Fields("Remarks")
Me.cboPoType = .Fields("POType")
Me.txtComboName = .Fields("ComboName")
Me.txtGroundColour = .Fields("GroundColour")
'store id of student in Tag of txtID in case id is modified
Me.txtID.Tag = .Fields("ID")
'change caption of button add to update
Me.cmdAdd.Caption = "Update"
'disable button edit
Me.cmdEdit.Enabled = False
End With
End If
End Sub
I am currently having a small issue whereby my current code will update and overwrite my current record in my access database after i edit and update on my current record.
What i want is when the current record in edited, it should be save as a new record with the same PO as the record that it has been edited on.
Below is my code:
Private Sub cmdAdd_Click()
'when we click on button Add there are two options
'1. for insert
'2. for update
If Me.txtID.Tag & "" = "" Then
'this is for insert new
'add data to table
CurrentDb.Execute "INSERT INTO mxd(ID,Fabrication,Width,FinishedGoods,Colour,LabDipCode,GrossWeight,NettWeight,Lbs,Loss,Yds,Remarks,POType,ComboName,GroundColour)" & _
" VALUES(" & Me.txtID & ",'" & Me.txtFabrication & "','" & Me.txtWidth & "','" & Me.txtFinishedGood & "','" & _
Me.txtColour & "','" & Me.txtLabDipCode & "','" & Me.txtGrossweight & "','" & _
Me.txtNettweight & "','" & Me.txtLbs & "','" & Me.txtLoss & "','" & _
Me.txtYds & "','" & Me.txtRemarks & "','" & Me.cboPoType & "','" & _
Me.txtGroundColour & "',' & Me.txtComboName & " ')"
Else
'otherwise ( Tag of txtID store the id of student to be modified)
CurrentDb.Execute "UPDATE mxd " & _
" SET ID = " & Me.txtID & _
", Fabrication = '" & Me.txtFabrication & "'" & _
", Width = '" & Me.txtWidth & "'" & _
", FinishedGoods = '" & Me.txtFinishedGood & "'" & _
", Colour = '" & Me.txtColour & "'" & _
", LabDipCode = '" & Me.txtLabDipCode & "'" & _
", GrossWeight = '" & Me.txtGrossweight & "'" & _
", NettWeight = '" & Me.txtNettweight & "'" & _
", LBS = '" & Me.txtLbs & "'" & _
", Loss = '" & Me.txtLoss & "'" & _
", Yds = '" & Me.txtYds & "'" & _
", Remarks = '" & Me.txtRemarks & "'" & _
", POType = '" & Me.cboPoType & "'" & _
", ComboName = '" & Me.txtComboName & "'" & _
", GroundColour = '" & Me.txtGroundColour & "'" & _
" WHERE ID = " & Me.txtID.Tag
End If
'clear form
cmdClear_Click
'refresh data in list on form
FormMxdSub.Form.Requery
End Sub
Private Sub cmdClear_Click()
Me.txtID = ""
Me.txtFabrication = ""
Me.txtWidth = ""
Me.txtFinishedGood = ""
Me.txtColour = ""
Me.txtLabDipCode = ""
Me.txtGrossweight = ""
Me.txtNettweight = ""
Me.txtLbs = ""
Me.txtLoss = ""
Me.txtYds = ""
Me.txtRemarks = ""
Me.cboPoType = ""
Me.txtKeywords = ""
Me.txtComboName = ""
Me.txtGroundColour = ""
'focus on ID text box
Me.txtID.SetFocus
'set button edit to enable
Me.cmdEdit.Enabled = True
'change caption of button add to Add
Me.cmdAdd.Caption = "Add"
'clear tag on txtID for reset new
Me.txtID.Tag = ""
End Sub
Private Sub cmdClose_Click()
DoCmd.Close
End Sub
Private Sub cmdDelete_Click()
'delete record
'check existing selected record
If Not (Me.FormMxdSub.Form.Recordset.EOF And Me.FormMxdSub.Form.Recordset.BOF) Then
'confirm delete
If MsgBox("Are you sure you want to delete?", vbYesNo) = vbYes Then
'delete now
CurrentDb.Execute "DELETE FROM mxd " & _
"where ID = " & Me.FormMxdSub.Form.Recordset.Fields("ID")
'refresh data in list
Me.FormMxdSub.Form.Requery
End If
End If
End Sub
Private Sub cmdEdit_Click()
'check whether there is exists data in list
If Not (Me.FormMxdSub.Form.Recordset.EOF And Me.FormMxdSub.Form.Recordset.BOF) Then
'get data to text box control
With Me.FormMxdSub.Form.Recordset
Me.txtID = .Fields("ID")
Me.txtFabrication = .Fields("Fabrication")
Me.txtWidth = .Fields("Width")
Me.txtFinishedGood = .Fields("FinishedGoods")
Me.txtColour = .Fields("Colour")
Me.txtLabDipCode = .Fields("LabDipCode")
Me.txtGrossweight = .Fields("GrossWeight")
Me.txtNettweight = .Fields("NettWeight")
Me.txtLbs = .Fields("Lbs")
Me.txtLoss = .Fields("Loss")
Me.txtYds = .Fields("Yds")
Me.txtRemarks = .Fields("Remarks")
Me.cboPoType = .Fields("POType")
Me.txtComboName = .Fields("ComboName")
Me.txtGroundColour = .Fields("GroundColour")
'store id of student in Tag of txtID in case id is modified
Me.txtID.Tag = .Fields("ID")
'change caption of button add to update
Me.cmdAdd.Caption = "Update"
'disable button edit
Me.cmdEdit.Enabled = False
End With
End If
End Sub
