Sorry I forgot to mention this: the actual update of data occurs in my add function.
Code:
Private Sub butEdit_Click()
'check for data existence, store component in tag in case of change
'change Add button caption, disable Edit button
If Not (Me.frmEntrySub.Form.Recordset.EOF And Me.frmEntrySub.Form.Recordset.BOF) Then
With Me.frmEntrySub
Me.txtAssembly = !Assembly
Me.txtComponent = !Component
Me.txtDescription = !Description
Me.numAssemblyQty = !AssemblyQty
Me.txtUOM = !UOM
Me.numQtyA = !QtyA
Me.numQtyB = !QtyB
Me.numQtyC = !QtyC
Me.dbPriceA = !PriceA
Me.dbPriceB = !PriceB
Me.dbPriceC = !PriceC
Me.txtComponent.Tag = .Fields("Component")
Me.butAdd.Caption = "Update"
Me.butEdit.Enabled = False
End With
End If
End Sub
THEN:
Code:
Private Sub butAdd_Click()
'Insert vs Update options
If Me.txtComponent.Tag & "" = "" Then
'add, clear form, and refresh
CurrentDb.Execute "INSERT INTO EntryFormTable(Assembly, Component, Description, AssemblyQty, UOM, QtyA, QtyB, QtyC, PriceA, PriceB, PriceC) " & _
" VALUES('" & Me.txtAssembly & "','" & Me.txtComponent & "','" & Me.txtDescription & "'," & Me.numAssemblyQty & ",'" & Me.txtUOM & "'," & Me.numQtyA & "," & _
Me.numQtyB & "," & Me.numQtyC & "," & Me.dbPriceA & "," & Me.dbPriceB & "," & Me.dbPriceC & ")"
Else
CurrentDb.Execute "UPDATE EntryFormTable " & _
" SET Assembly=" & Me.txtAssembly & _
", Component='" & Me.txtComponent & "'" & _
", Description='" & Me.txtDescription & "'" & _
", AssemblyQty='" & Me.numAssemblyQty & "'" & _
", UOM='" & Me.txtUOM & "'" & _
", QtyA='" & Me.numQtyA & "'" & _
", QtyB='" & Me.numQtyB & "'" & _
", QtyC='" & Me.numQtyC & "'" & _
", PriceA='" & Me.dbPriceA & "'" & _
", PriceB='" & Me.dbPriceB & "'" & _
", PriceC='" & Me.dbPriceC & "'" & _
" WHERE Component=" & Me.txtComponent.Tag
End If
butClear_Click
frmEntrySub.Form.Requery
End Sub
I don't know vba syntax too well :\ I've coded in other languages but not this one.