Edited data record to be inserted as a new data record under the same PO number in Microsoft Access

Joined
Aug 28, 2017
Messages
2
Reaction score
0
Hello everyone, I am facing a small issue with my Microsoft access form and subform. The issue is when i click on "edit" and selected the row of data on the subform which i want to make edit to and when i am done with editing and click on "update" it will update to the same row of data onto the subform which is not what i want. Instead, what i want is that when i click on "edit" and selected the row of data on the subform which i want to make edit to and when i am done with editing and click on "update" it should be insert into the table as a new row of data onto the subform with the same PO number as the row of the data that i edited on.
Below is the code that i used for the button:

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


Private Sub txtSearch_Click()

Dim sql As String

sql = "SELECT [mxd].[ID], [mxd].[Fabrication], mxd.Width, mxd.FinishedGoods, mxd.Colour, mxd.LabDipCode, mxd.GrossWeight, mxd.NettWeight, mxd.Lbs, mxd.Loss, mxd.Yds, mxd.Remarks, mxd.POType, mxd.ComboName, mxd.GroundColour" _
& " FROM mxd " _
& " WHERE ID LIKE '*" & Me.txtKeywords & "*'"

Me.FormMxdSub.Form.RecordSource = sql
Me.FormMxdSub.Form.Requery
End Sub
 
Back
Top