473,324 Members | 2,214 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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

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
  4.  
  5. Private Sub Edit_Click()
  6. NewValve.Enabled = False
  7. End Sub
  8.  
  9. Private Sub NewValve_Click()
  10. Edit.Enabled = False
  11. End Sub
  12.  
  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
  19.  
  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
  34.  
  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
  42.  
  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
  52.  
Jul 22 '17 #1
5 1377
PhilOfWalton
1,430 Expert 1GB
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.

Phil
Jul 22 '17 #2
PhilOfWalton,

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
PhilOfWalton
1,430 Expert 1GB
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.

Phil
Jul 22 '17 #4
PhilOfWalton,

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
PhilOfWalton
1,430 Expert 1GB
I assumed ValveID was an AutoNumber.

Phil
Jul 24 '17 #6

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

Similar topics

6
by: Ruben | last post by:
Hello. I am trying to read a small text file using the readline statement. I can only read the first 2 records from the file. It stops at the blank lines or at lines with only spaces. I have a...
3
by: Jeff Calico | last post by:
Hello everyone I am transforming an XML document to text, basically only outputting a small portion of it. When I run the following XSLT via Xalan's processor, I get a bunch of unwanted blank...
1
by: DAnne | last post by:
Hi, I have checked your archives but have not been able to find anything that works for my situation. I have a for loop that brings back a list of unique responses for each section in a report....
1
by: ortega.rad | last post by:
I have a form which allows you to select a record. That record has other records asscociated with it via a table. The asscociated records of the record selected on the main form are shown in a...
4
by: Andreas Bauer | last post by:
Hi, I have to audit some c# code. I know in the options I can adjust how the code should be formatted while entering it. But is there any way to apply afterwards a code template to the classes...
3
by: Steed | last post by:
Evening all, I'm very new to VBA and am struggling badly with this one. I'll explain what I need to do as the title is a little unclear! I'm making an insurance calculator for classic cars, and...
0
by: goldxr | last post by:
Hi, I'm new to MS Access and currently using the 2003 version. I have had very little VBA coding experience and need to set up a database which will enable to track staffing needs as projects...
3
by: lisles | last post by:
i need to edit data from the databse through a form.my code is below <?php require_once "../inc/functions.php"; require_once "../inc/vars.inc.php"; sessionCheck(); session_start(); ...
2
by: Amian | last post by:
So I'll explain to you everything that I'm doing and then let you know what I need help with b/c maybe I should be doing this a different way. I am trying to create a database for all patient...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.