Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664

    Unusual response to saving a record in a db

    In the zipped db that I have attached, the db when I open frmNewTelework and put values in each box (combo or otherwise) and move on down to the save button. It says that "there is already a record in the db ....".



    That is false, there is no record in the db like the one I just created and then tried to put in. In fact there are no records in the db at all when I tru to enter this record. The db is empty, this is its first record.

    I am unsure why this is happening, but I believe that it must have something to do with the VBA code shown in the second pdf screenshot.

    I am not sure what that line with the <> means. The whole line is somewhat ambiguous to me. What is it doing and why is it not just putting th record in the db?

    Any help appreciated. Thanks in advance.

    This is another macro to VBA translation. So take it with a grain of salt.

    Respectfully,

    Lou Reed
    Attached Files Attached Files

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    The error message you are getting has nothing to do with the actual error. The actual error is that all the references to Forms!sfrmTelework1 are wrong. That form is not open, and is not part of the Forms! collection, so those statements fail (or should fail).

    So, obviously the question is "why did you see that error message?" It is due to this line: On Error Resume Next

    What that line effectively does is tell Access to ignore any runtime errors and just keep going to the the next line. So, Access ingores the reference to the "non-existent" form and "keeps right on truckin' " It is IMO very dangerous programming, and should be used only in exception circumstances (which this is not).

    In addition to all that, the "type" is a combo box (the control name is TelworkType) with only two selections, neither of which is "N/A", so If Forms!sfrmTelework1.Type <> "N/A" is always true (even when it does reference the proper form). (The "<>" means "not equal to")

    There are a number of other error in that code - some are code errors, some are logic errors. Determine what the validations you want to make on the form data are, and then look at the code to see if it does them right (it doesn't).

  3. #3
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Quote Originally Posted by John_G View Post
    The error message you are getting has nothing to do with the actual error. The actual error is that all the references to Forms!sfrmTelework1 are wrong. That form is not open, and is not part of the Forms! collection, so those statements fail (or should fail).

    So, obviously the question is "why did you see that error message?" It is due to this line: On Error Resume Next
    So I have to open Forms!sfrmTelework1 ?


    Quote Originally Posted by John_G View Post
    In addition to all that, the "type" is a combo box (the control name is TelworkType) with only two selections, neither of which is "N/A", so If Forms!sfrmTelework1.Type <> "N/A" is always true (even when it does reference the proper form). (The "<>" means "not equal to")

    There are a number of other error in that code - some are code errors, some are logic errors. Determine what the validations you want to make on the form data are, and then look at the code to see if it does them right (it doesn't).
    You will have to explain the logic in adding an N/A to my current selections of current and situational. What is this line saying. it is obviously a conditional, but please explain.

    Respectfully,

    Lou Reed

  4. #4
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I am including a snippet shot of the part of the code. I just do not understand what it is doing and why is it wrong?

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed
    Attached Thumbnails Attached Thumbnails Capture1.GIF  

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    So I have to open Forms!sfrmTelework1 ?
    No, you have to change the references to refer to the proper form, in this case the one the code is in.

    All occurances of Forms!sfrmTelework1 should be changed to Forms!frmNewTelework in the code module for frmNewTelework. (Me! will also work)

    It looks to me as if that code was ported from somewhere else (the formsfrmTelework1 ?). There is nothing wrong with doing that - it can save a lot of time - but when you do, you have to look through the code to make sure the references (forms, controls, other subs, etc) will still be valid, and to make sure the code logic still applies in the new location.

    You will have to explain the logic in adding an N/A to my current selections of current and situational. What is this line saying. it is obviously a conditional, but please explain.
    It's not adding "N/A" to anything, it is testing for it. the line If Me!TelWorkType <> "N/A" then says "If the contents of the form control 'TelWorkType' are not equal to 'N/A', then do...whatever is in the If-Endif block"

    TelWorkType happens to be a combo box in this case, but it could be a textbox.
    The string "N/A" has no particular meaning to Access - the code can check for any string you choose.

    It is this bit that has me thinking that the code was ported from somewhere else, where "N/A" could actually be a value that was in a text box or combo box. In this form however, the combo TelWorkType will never contain "N/A".

  6. #6
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Okay, what is this snippet of code doing. It is right below where the IF-THEN VBA code that had the N/A line I asked about earlier.

    Code:
    'Attempt to save form.  If incomplete, catch error, resume next
            DoCmd.RunCommand acCmdSaveRecord
            
            If Err.Number <> 0 Then
                errorBox = MsgBox("Please Complete the form.  No changes made.", vbOKOnly, "Form Not Completed")
                Exit Sub
            End If

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    OK. After some searching, I was able to figure at least some of this out. I'll start again.

    The form frmNewTelework is referenced only from sfrmTelework1, which is a subform on frmPersonnel. frmNewTelework references controls on the subform sfrmTelework1, which in turn references controls on frmPersonnel. You may already do this, but when you are developing frmNewTelework, you must always test it through frmPersonnel - Telework - New. Testing it standalone from the navigation pane will not work, because of those references.

    So now (Ahem, mea culpa) - forget what I wrote above, and I'll start again.

    Looking at the form frmNewTelework, and its code.


    1. The form has a dropdown combo for Employee Name. It's OK to have the name there (it's not bound to anything), but don't select it from a dropdown again. You already know the employee concerned - you selected it in frmPersonnel, and you can get and display the First and Last names from there.
    2. If you look at tblTelework, you will see that ApproverID is a required field (cannot be Null). However on your form, the Approver textbox control is not bound to the ApproverID field in the table, so even though you can select a name from the combo, it will never be saved, and the save command will always fail. Just bind the control to the ApproverID field.
    3. In the code, all references to Forms!sfrmTelework1 will fail. Because sfrmTelework1 is a subform, it cannot be referenced as part of the Forms! collection. To refer to a control on the subform, you need: Forms!frmPersonnel!sfrmTeleWork1.form!txtTeleworkType. In that reference, sfrmTeleWork1 is NOT the name of the subform - it is the name of the form control that contains the subform. In your case here, the two names just happen to be the same, so be aware of which one you have to use.
    4. Remember that line "On Error Resume Next" ? Here's a case where you can use it. Move it down to immediately before DoCmd.RunCommand acCmdSaveRecord. The effect of this is that if the save generates an error (which it always will at the moment - see point #2 above), the code will continue in spite of the error. Here the code checks for a non-zero error code (doesn't matter which one), and exits with a warning message if the error code is non-zero.


    That's a lot to chew on, I know - but give it a shot and see how it goes.

  8. #8
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Okay, I have it. I am including a zipped db. The frmNewProperty is very similar to frmNewTelework except that it works. It works perfectly.

    Try entries on frmNewProperty and you will see it does what it is supposed to do. This has quite different code behind the form. This VBA code works.

    No <> N/A in code and it is much simpler.

    It works. Try it.

    Respectfully,

    Lou Reed
    Attached Files Attached Files

  9. #9
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    It only appears to be working perfectly, but in fact it is not.

    These three lines:

    Forms!sfrmPropertyPass1.Requery
    Forms!sfrmPropertyPass.Requery
    Forms!sfrmPropertyPass1.updateButtons

    will all generate errors (due to improper reference to a subform), errors which you get no message for because of the On Error Resume Next statement.
    You should only use On Error Resume Next if you know exactly what the consequences of using it are.

  10. #10
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Quote Originally Posted by John_G View Post
    It only appears to be working perfectly, but in fact it is not.

    These three lines:

    Forms!sfrmPropertyPass1.Requery
    Forms!sfrmPropertyPass.Requery
    Forms!sfrmPropertyPass1.updateButtons

    will all generate errors (due to improper reference to a subform), errors which you get no message for because of the On Error Resume Next statement.
    You should only use On Error Resume Next if you know exactly what the consequences of using it are.

    On this then what should I do with:

    Code:
    Forms!sfrmPropertyPass1.Requery
    Forms!sfrmPropertyPass.Requery
    Forms!sfrmPropertyPass1.updateButtons
    I know that you have pointed this out before, but how should I change these lines to make it work properly?

    My main concern on this post yesterday was to get was to get info in that form into related tables an ind some working VBA code. I believe that I did.

    Any help appreciate.

    Thanks in advance.

    respectfully,

    Lou Reed

  11. #11
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Okay, after reading your posit immediately I think that I understand what you are saying.

    I need to know about the relationship between frmNewTelework, sfrmTelework1 and frmPersonnel. I also need to know how you figured this out.
    I put a lot of questions on the forum asking how to find the parent of a subform. When there were no answers or responses I did some detective work and
    discovered that sfrmTelework1 is a subform of frmNewTelwork (or so I thought).

    Now it seems the relationship is much more complicated involving frmPersonnel. I just need to know how you arrived at that conclusion.

    Then I can go from there. There must be an easy way to determine this. I nee to know it.

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed

  12. #12
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Now it seems the relationship is much more complicated involving frmPersonnel. I just need to know how you arrived at that conclusion.

    Then I can go from there. There must be an easy way to determine this. I nee to know it.
    The is not automated way (that I know of) to determine these linkages. It is one of the big (and time-consuming) issues that arise when you inherit a database and code from someone else. (I know from painful experience).

    So the first thing I did was to see where the form frmNewTelwork was referenced in VBA (for the whole database).

    You can do that by:

    1. opening any code module
    2. opening the "Find" screen (Ctrl-F)
    3. Entering frmNewTelework in the "Find What" box"
    4. Selecting "Current Project" in the search range
    5. Clicking "Find Next" until all occurances are found (you get a message after the last one)


    It turns out that the form is only referenced once, like this:

    DoCmd.OpenForm "frmNewTelework", acNormal, "", "", , acNormal

    which is in the code for sfrmTelework1

    It doesn't explicitly say this is a sub form (!), but since it has been named "sfrm..." it seems a pretty good assumption.

    Now, the only way (again, that I know of) to find where sfrmTelework1 is used as a subform is to look for it, but since we know we are working with frmPersonnel, that is in fact where it is, on the "teleWork" tab.

    So, what we have is is frmPersonnel has the subform sfrmTelework1 on it, and the code in sfrmTelework1 opens the form frmNewTelework (not as a subform).

    This is important to know as examination of the properties and code in frmNewTelework shows it refers to controls on sfrmTelework1, and it means that frmNewTelwork cannot be tested or used unless frmPersonnel with sfrmTelework1 is open, with an employee selected.

  13. #13
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I know that you have pointed this out before, but how should I change these lines to make it work properly?
    Look at post#7, point 3 in the list: Forms!sfrmPropertyPass1.Requery is not the correct way to reference a property, control, or method on a subform.

    And please review my comments regarding the use of On Error Resume Next.

  14. #14
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Quote Originally Posted by Lou_Reed View Post
    On this then what should I do with:

    Code:
    Forms!sfrmPropertyPass1.Requery
    Forms!sfrmPropertyPass.Requery
    Forms!sfrmPropertyPass1.updateButtons
    I know that you have pointed this out before, but how should I change these lines to make it work properly?

    My main concern on this post yesterday was to get was to get info in that form into related tables an ind some working VBA code. I believe that I did.

    Any help appreciate.

    Thanks in advance.

    respectfully,

    Lou Reed

    In light of what you said in Post #7 above, and Post 13 how do I change the following lines :


    Code:
    Forms!sfrmPropertyPass1.Requery
    Forms!sfrmPropertyPass.Requery
    Forms!sfrmPropertyPass1.updateButtons

    Also, where do I put the

    On Error Resume Next

    statement?

    Respectfully,

    Lou Reed

  15. #15
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Also, where do I put the On Error Resume Next statement?
    My suggestion: Get Rid of it. Completely. Unless you know exactly how it works, and how to deal with it, it will only confuse you, and make debugging very much more difficult. Stick with the more conventional On Error Go To ... construct.

    In light of what you said in Post #7 above, and Post 13 how do I change the following lines :
    Copied from Post #7:

    In the code, all references to Forms!sfrmTelework1 will fail. Because sfrmTelework1 is a subform, it cannot be referenced as part of the Forms! collection. To refer to a control on the subform, you need: Forms!frmPersonnel!sfrmTeleWork1.form!txtTeleworkType. In that reference, sfrmTeleWork1 is NOT the name of the subform - it is the name of the form control that contains the subform. In your case here, the two names just happen to be the same, so be aware of which one you have to use.


    It's the same idea with those three lines, except that you have a different subform container control - sfrmPropertyPass1

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. A most unusual query
    By Lou_Reed in forum Access
    Replies: 3
    Last Post: 04-27-2017, 03:13 PM
  2. An unusual(?) parent-child form
    By bnorton in forum Access
    Replies: 10
    Last Post: 08-19-2014, 04:55 PM
  3. Replies: 3
    Last Post: 04-10-2013, 01:20 PM
  4. Unusual string
    By Yap in forum Access
    Replies: 6
    Last Post: 12-13-2012, 08:35 PM
  5. Percent textbox unusual behaviour
    By Deutz in forum Forms
    Replies: 4
    Last Post: 06-05-2011, 06:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums