Results 1 to 9 of 9
  1. #1
    epb is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Location
    Near Chicago
    Posts
    36

    Problems with saving the record - some fields don't save until another edit and save

    Having some problems with a form. I have code in command buttons and code in the form to handle saving a dirty record but there are some issues:

    1) saving some fields at time of record entry shows "Null" until the record is saved and another edit is done then the fields show related data.

    2) Saving dialog box sometimes triggers before all fields are entered.



    Most of this form was created by someone before me. I am trying to get things working with some protection on data entry but it is a struggle. Keep in mind that there are a few related fields that are there for testing purposes. Those are marked "REF"

    I am attaching the form and some of the necessary table info for review.

    Thanks...

    Form db.zip

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,669
    You have ID field in ENGDATA1 set as primary key but save PrintNo value as foreign key in CustomerOrdersTable1. Relationships should be on PK/FK fields. Is PrintNo a unique value in ENGDATA1? You have it set to allow duplicates. If should be unique, suggest you change the Indexed property and set PrintNo as PK. If it is not unique then should be saving ID as foreign key in number field in CustomerOrdersTable1.

    Recommend no spaces in names. Better would be ITEM_NO or ItemNo. Also, all caps is harder to read.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    epb is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Location
    Near Chicago
    Posts
    36
    Hello June,

    Thank you for the information. I know that naming and other things are not as they should be. I have a very large db where I am trying to fix a number of issues. The db was created by another user some time ago. Many problems and wrong thinking. I totally agree with the naming issues and ALL CAPS. It drives me CRAZY! All caps should only be used for Emphasis and not db naming. lol

    The [PRINT NO] field in ENGDATA1 is unique but I don't like the idea of using that field as the PK. I was going to convert the PK to [ID] field and FK to EngDataID field. I would also like to change the field names to take out spaces and change to Lower/upper case names...is there an easy way to do this across a number of related tables, forms and controls??

  4. #4
    epb is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Location
    Near Chicago
    Posts
    36
    I updated the table properties and PK. I still have the problem with the Before_Update of the form not having reference to combobox data form the ENGDATA1 table. After the record is initially saved and I go to make an edit then the related table fields appear to be available to save. I also moved the information to be saved to the combobox After_Update event and still not working.

    Why are the comnbobox colunmn references coming back as "Null" when they are not null?

    Once I select a ENGDATA1.[PART NO] in the combobox when does the underlying CUSTORDERSTABLE1.[PART] actually have a relation with the ENGDATA1 table? Do I need to do a requery before saving information?

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,669
    Access doesn't have built-in global search/replace for design edits. Need an add-in. I use Rick Fisher's Find & Replace (Google it) for global renaming (not for data edits). Costs about $50.

    I will have to take another look at your db later and try to figure how the behavior issue.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,914
    To be honest, Iím totally at sea as to what youíre trying to do, here, based on your explanation! It would appear that you want to make a selection from the Combobox named cboPrintNo and then, if the user confirms that they want to save the changes, assign the various Fields to Columns of Combobox selection. Is that correct? The problem with this approach is that at this point the Record has not been changed! You're only 'changing' it after the Form_BeforUpdate event fires, which it won't if no changes have been made!

    This type of assignment is normally done the the AferUpdate event of the Combobox itself, not in the Form_BeforeUpdate event. You would then ask the user, in the Form_BeforeUpdate, whether to Save the changes or to dump them.

    But in looking at the code in the cboPrintNo_AfterUpdate event, you've apparently set up the Combobox with the purpose of using it to retrieve a matching Record:

    Code:
    Private Sub cboPrintNo_AfterUpdate()
        ' Find the record that matches the control After an update.
        Dim rs As Object
        Dim strVar As String
        
        'Set rs variable to a clone of the current recordset
        Set rs = Me.Recordset.Clone
        
        'Find the correct record based on the combobox selection
        rs.FindFirst "[Part] = '" & Me![cboPrintNo].Column(0) & "'" '[Print No] is bound to column 1
        
        'Set the recordset for the form to the current record
        'Me.Bookmark = rs.Bookmark
        
    End Sub


    The Combobox cannot do this, though, because you have the line:

    'Me.Bookmark = rs.Bookmark

    commented out!

    In point of fact, the Combobox cannot be used for anything, because you have the Allow Edits Property for the Form set to No, and this setting prevents selections from being made from any Combobox!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    epb is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Location
    Near Chicago
    Posts
    36
    Hello Linq,

    You are correct about a number of things. The creator of the database had the combobox saving in the Before_Update event. I saw this after writing the last reply (above) and put this into the combobox After_Update event. I think that is where it belongs - as you have said.

    I am trying to fix something created by someone else. Some things I see after investigation and some things I don't. Although, I did add the allow edits property in an effort to keep users from inadvertently changing record data. They need to click the "Edit Record" button to edit and then are asked to save or dump in the Before_Update event. Which should be correct now that I moved items to be saved to the combobox After_Update event.

    I guess I should ask a couple questions...

    1) Are the things mentioned by you above reasons why the combobox columns are returning "Null"?? I noticed that the displayed column(x) data fields work in some cases and some cases do not. Why? I know some fields are actually empty but the ones that have data should return something by Me.cboPrintColumn(x).

    2) The combobox is used to lookup related part data from the ENGDATA1 table. I think that the code referred to was used when the combobox was unbound at some point. Now it is bound to CustomerOrdersTable.[Part]. Let me know if I am wrong in my thinking about this.

    3) Here is what I need the form to do...I need to not allow users to edit the record unless they specifically click "Edit Record". I also need the combobox to lookup ENG table information for display and in some cases to save.

    I had gone through much of the form code previoulsy and there were many things not connected to any control or had various problems. The entire database is much the same and this is the second form of a number I will be working on.

    I appreciate your input, thanks.

  8. #8
    epb is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Location
    Near Chicago
    Posts
    36
    I believe I fixed the problem...

    The column count was set at seven and not 21. I had added the other fields later. The previous database owner used Dlookup for these fields. That fixes the combobox returning "Null" for columns 8 to 21.

    I also removed the rest of the code in the cbobox AfterUpdate event that was associated with record look up used when the cbobox was unbound.

    Now the Afterupdate event looks like this:

    Code:
    Private Sub cboPrintNo_AfterUpdate()
        
        'Save this info
         Me.EngDataID = Me!cboPrintNo.Column(9)
         Me.PENETRANT = Me!cboPrintNo.Column(13)
         Me.[HEAT TREAT] = Me!cboPrintNo.Column(20)
         Me.Annealingfld = Me!cboPrintNo.Column(19)
         Me.[PASSIVATION] = Me!cboPrintNo.Column(16)
         Me.ANODIZE = Me!cboPrintNo.Column(17)
         Me.[CAD PLATING] = Me!cboPrintNo.Column(15)
         Me.[ELECTROPOLISH] = Me!cboPrintNo.Column(18)
         Me.[MAG PARTICLE] = Me!cboPrintNo.Column(14)
         Me.[OTHER] = Me!cboPrintNo.Column(21)
         
         If Nz(Me!cboPrintNo.Column(4), "") = "" Then
            Me.[CLASS] = "N/A"
         Else
            Me.[CLASS] = Me!cboPrintNo.Column(4)
         End If
         If Nz(Me!cboPrintNo.Column(5), "") = "" Then
            Me.[CLASS 2] = "N/A"
         Else
            Me.[CLASS 2] = Me!cboPrintNo.Column(5)
         End If
         Me.[BLASTING] = Me!cboPrintNo.Column(12)
         Me.[MATERIAL] = Me!cboPrintNo.Column(11)
        
    End Sub
    Everything appears to be working. Thank you June and Linq! I do appreciate the help.

  9. #9
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,914
    As one of our Presidents used to say "I feel your pain!" Following another developer is not fun, even in the best of times!

    Glad we could help!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 06-20-2012, 09:50 AM
  2. mdb save problems with Access 2010
    By pacnwWill in forum Programming
    Replies: 3
    Last Post: 09-27-2011, 11:52 AM
  3. Replies: 9
    Last Post: 01-20-2011, 02:22 PM
  4. VB coding for saving when click on save button in form
    By cwwaicw311 in forum Programming
    Replies: 1
    Last Post: 02-04-2010, 11:11 PM
  5. Save only 3 of 4 fields
    By Schwagr in forum Forms
    Replies: 0
    Last Post: 03-24-2006, 05:20 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
  •  
Tech Forums: Microsoft Office Forums