CODEWITHSUNDEEP
vbams-access
user2119980
user2119980

Reputation: 509

Update Button not updating

I have been hammering out the issues of this form for about a week now and have almost come to a solution, but I have hit a brick wall. I have a large form with multiple functions, one of the functions is to edit a subform that houses a list of codes and other various pieces of data. When I click the edit button it auto fills the boxes with the selected data. The function works when I click the update button but when I click the save button it does not actually save the data. The code is:

Private Sub cmdEdit_Click()
'check whether there exists data in list
If Not (Me.TableSub.Form.Recordset.EOF And Me.TableSub.Form.Recordset.BOF) Then
'Get data to text box control
With Me.TableSub.Form.Recordset
    Me.text_key = .Fields("KW")
    Me.txt_code = .Fields("Code")
    Me.combo_source = .Fields("Source")
    'Store id of student in tag of text id in case id is modified
    Me.txt_code.Tag = .Fields("Code")
    'Change caption of button add to Update
    Me.cmdAdd.Caption = "Update"
    'disable button edit
    Me.cmdEdit.Enabled = False
End With
End If
End Sub

This is the code for the save or Add button.

Private Sub cmdAdd_Click()
'when we click on button Add there are two options
'1. For insert
'2. For Update
If Me.txt_code.Tag & "" = "" Then
    'this is for insert new
    'add data to table
    CurrentDb.Execute "INSERT INTO KWTable(KW, Source, Code) " & _
        " VALUES('" & Me.text_key & "','" & Me.combo_source & "','" & _
        Me.txt_code & "')"

Else
'otherwise (Tag of txtID store the id of student to be modified)
CurrentDb.Execute "UPDATE KWTable " & _
" SET KW='" & Me.text_key & "'" & _
", Code='" & Me.txt_code & "'" & _
", Source='" & Me.combo_source & "'" & _
" WHERE KW='" & Me.text_key & "'"
End If
'clear form
cmdClear_Click
'refresh data in list on form
TableSub.Form.Requery

End Sub

Upvotes: 2

Views: 209

Answers (2)

DHW
DHW

Reputation: 1196

It sounds to me like you are looking at forcing the current bound form's data to save.

If Me.Dirty Then Me.Dirty = False

This statement essentially says "If there is unsaved data on this form/report - save it"

You can also reference your subform 

If subform.Form.Dirty Then subform.Form.Dirty = False

It will do the same thing as Fabio's suggestion but I find it a bit more reliable with bound forms than the recordset approach.

Upvotes: 0

Fabio Pereira
Fabio Pereira

Reputation: 348

You're only copying values to the fields. They're not bound to the subform's recordset in any way. So, to save them, just reverse the process:

With Me.TableSub.Form.Recordset
    .Edit
    .Fields("KW") = Me.text_key
    .Fields("Code") = Me.txt_code
    .Fields("Source") = Me.combo_source
    .Fields("Code") = Me.txt_code.Tag
    .Update
End With

Upvotes: 1

Related Questions