I set up a hazardous material table named tblReceivals with the following fields: MaterialName, QtyReceived, QtyDisposed, QtyRemained, etc.
I also set up a data entry form named frmReceivals where the user can populate the QtyReceived field for adding a new record. The QtyRemained field is not on this form.
How can I assign the input QtyReceived value to the QtyRemained value (these two fields should have the same values) in a new added record, using a frmReceivals event procedure?
Using Access you have 2 ways to populate a table by using a form.
The first is by using bounded controls (this means that the fields of the table are assigned to for example a textbox).
I used this way in the beginning, but I didn't like it.
Suppose you are entering data into textboxes on the form, but they're not all completed yet. When you swith to a subform, Access will already add the 'incomplete' record to the table because of autocommit.
Therefore I prefer the second way.
I do not assign my controls to a field (unbound controls)
I fill in my data and only when pressing a buttun I will add the record to the table.
Your table is like this :
MaterialName, QtyReceived, QtyDisposed, QtyRemained
Your form contains 3 textboxes :
txtName, txtReceived, txtDisposed
You can use this code :
public sub button_Click()
Dim strSQL as String
"INSERT INTO tblReceivals (MaterialName, QtyReceived, QtyDisposed, QtyRemained) " & _
"VALUES (" & txtName.text & "," & val(txtReceived.text) & "," & val( txtDisposed.text) & "," & val(txtReceived.text) & ")"
If you really want to use the first method you can put an invisible textbox on the form for QtyRemained.
In the QtyReceived_Change - event you write