Framework: I have a form where it is possible to add new information (via the New valve button) or to Edit the data that already exists at the table (via the Edit data button).
All fields are bound to the table with exception to the "Choose Tag:"
When new valves are introduced, or edited, it is necessary to click on the save button in order to save the information.
Problem: Along with the explanation, it will be easier to understood the problem if the image is analysed
Image 1 - Form is opened and the edit button is clicked in order to be able to select a TAG in which we'll change the field values (notice the that valveID, which is the autonumber of the table, is set to New)
Image 2 - After choosing the TAG, all fields are updated using the Private Sub cbTag_AfterUpdate() [thanks to PhilOfWalton]
Image 3 - After saving the changed information at image 2, a new valve ID is presented. If the Edit data button is again clicked and a Tag is choosen at the "Choose TAG:", a new blank line is Immediately displayed at the table.
Image 4 - New blank line displayed at the table. This happens because when the form is updated, there is an avaiable ValveID in order to store the blank values. Private Sub Form_BeforeUpdate cannot be used in here since information would not be updated.
Question: Is there any way to solve this problem? Thank you very much.
Expand|Select|Wrap|Line Numbers
- Option Compare Database
- Option Explicit
- Private SaveButton As Boolean
- Private Sub Edit_Click()
- NewValve.Enabled = False
- End Sub
- Private Sub NewValve_Click()
- Edit.Enabled = False
- End Sub
- Private Sub cbTag_AfterUpdate() 'associated to the "Choose the Tag:" field
- Dim Fltr As String
- Fltr = "ValveID = " & cbTag
- Me.Filter = Fltr
- Me.FilterOn = True
- End Sub
- Private Sub Gravar_Click()
- '-----------------Check if Null--------------
- If IsNull(Me![Tag]) Then
- MsgBox "Tag is mandatory."
- Cancel = True: Me![Tag].SetFocus
- Exit Sub
- End If
- '------------------------Add information to table-------------------------------
- SaveButton = True
- Call DoCmd.RunCommand(acCmdSaveRecord)
- DoCmd.GoToRecord , , acNewRec
- SaveButton = False
- Clean_Click
- End Sub
- Private Sub Form_BeforeUpdate(Cancel As Integer)
- If NewValve.Enabled = True Then 'This line is set in order to be able to update the information via the Private Sub cbTag_AfterUpdate
- If Not SaveButton Then
- Cancel = True 'If button not activated, form doesn't send information to the table
- End If
- End If
- End Sub
- Private Sub Clean_Click() '------------------All fields go null and buttons are enabled---------------
- On Error Resume Next
- Dim ctl As Control
- For Each ctl In Me.Controls
- ctl.Value = Null
- Next
- Edit.Enabled = True
- NewValve.Enabled = True
- End Sub