By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,583 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

Form used to send/edit data from table is creating blank lines

P: 22
Dear Bytes community,

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
  1. Option Compare Database
  2. Option Explicit
  3. Private SaveButton As Boolean
  5. Private Sub Edit_Click()
  6. NewValve.Enabled = False
  7. End Sub
  9. Private Sub NewValve_Click()
  10. Edit.Enabled = False
  11. End Sub
  13. Private Sub cbTag_AfterUpdate() 'associated to the "Choose the Tag:" field
  14.     Dim Fltr As String
  15.     Fltr = "ValveID = " & cbTag
  16.     Me.Filter = Fltr
  17.     Me.FilterOn = True
  18. End Sub
  20. Private Sub Gravar_Click()
  21. '-----------------Check if Null--------------
  22. If IsNull(Me![Tag]) Then
  23.     MsgBox "Tag is mandatory."
  24.     Cancel = True: Me![Tag].SetFocus
  25. Exit Sub
  26. End If
  27. '------------------------Add information to table-------------------------------
  28. SaveButton = True
  29. Call DoCmd.RunCommand(acCmdSaveRecord)
  30. DoCmd.GoToRecord , , acNewRec
  31. SaveButton = False
  32. Clean_Click
  33. End Sub
  35. Private Sub Form_BeforeUpdate(Cancel As Integer)
  36. If NewValve.Enabled = True Then 'This line is set in order to be able to update the information via the Private Sub cbTag_AfterUpdate
  37.     If Not SaveButton Then
  38.         Cancel = True 'If button not activated, form doesn't send information to the table
  39.     End If
  40. End If
  41. End Sub
  43. Private Sub Clean_Click() '------------------All fields go null and buttons are enabled---------------
  44. On Error Resume Next
  45. Dim ctl As Control
  46. For Each ctl In Me.Controls
  47.   ctl.Value = Null
  48. Next
  49. Edit.Enabled = True
  50. NewValve.Enabled = True
  51. End Sub
Jul 22 '17 #1
Share this Question
Share on Google+
5 Replies

Expert 100+
P: 1,430
We need to get back to basics.

Firstly the word "Tag" is bad as there is a property called Tag on the "Other tab" of a form in form Design.

Secondly in your table, is "Tag" Unique and Required?
If it is NOT Unique, the routine I suggested before will find all records in the form that match the CboTag, but the form is set up to display only 1 record, so it will only show the first one that matches.

Thirdly, the routine only FINDS the records, it doesn't set a value to Tag, which is why you are getting a blank record. So I deduce that "Tag" is not set to Required.

What I would suggest is that you change Tag back to a bound Textbox on your form (Suitably renamed), and put the existing Combo Box in the form's footer to "Choose the TAG".

Back to the table, you need to decide what fields are required, and I repeat my point, that if you use the same manufacturer for a number of different valves, there should be a TblManufacturers. That way you can immediately see all the valves supplied by each manufacturer.

Your way, if you enter one manufacturer as J. Smith, one as J Smith and one as John Smith, and try get any information, Access will regard that as 3 separate manufacturers.

Jul 22 '17 #2

P: 22

Thank you for your answer.

I have changed the field name to ValveTag.
This field is a required field but not Unique since, for example, one refinary can have two symetrical stations where the names of the valves (Tag) will be exactly the same.
The piece of code I introduced at the save button routine is to ensure that the TAG is introduced:
Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me![Tag]) Then
  2.     MsgBox "Tag is mandatory."
  3.     Cancel = True: Me![Tag].SetFocus
  4. Exit Sub
  5. End If
I have tried putting at the ValveTag requirement parameter the option "Yes". But this is not possible because it will give error 3314 (Field required), which is not surprising since at the second edit attemp, demonstrated at the first post, Access will try to put the said blank line.

ValveTag have always been bound to the table. The combobox is the only that isn't.

I haven't already created the TblManufacturers because I am still trying to solve this problems.

I hope my lay description is enough to explain the problem.
I think the way choosen will simply not work at all, which is a pity since this was almost working.

Best regards.
Jul 22 '17 #3

Expert 100+
P: 1,430
Good that you have got the ValveTag sorted out.

I think, in a way, you have answered your own question. Because ValveTag is not Unique, when you search for it using the Combo Box, there will be a number of records that will match. Your form on the Format Tab has Navigation Buttons set to "No", so you will only see one record (which may or may not be the one you are looking for).

So set the Navigation Buttons to Yes and see if that works better. You will then have to scroll through the Navigation Buttons to find the Model you are looking for.

Incidentally call the control ValveTag on your form as well (and in your code).

I am assuming you know you can set these properties in the Table Design, and you may also want to consider setting "Allow Zero Length" to "No" which ensures you don't just enter a space.

Jul 22 '17 #4

P: 22

Thank you for your reply.
Well, having the ValveTag without unique values it not a problem since in the combobox selection there is two columns (ValveTag + Localization) and when the user chooses the value there is only 1ID selected.

The problem of the blank line 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.

I have to give up this idea and look after other pratical solutions to this.

Nevertheless, thank you for your contribution.
Jul 24 '17 #5

Expert 100+
P: 1,430
I assumed ValveID was an AutoNumber.

Jul 24 '17 #6

Post your reply

Sign in to post your reply or Sign up for a free account.