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

[epb]

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

[June7]

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.

[epb]

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??

[epb]

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?

[June7]

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.

[Missinglinq]

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:

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)>

[epb]

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.

[epb]

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:

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.

[Missinglinq]

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)>