• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

Main form table won't always update from subform entries. (yes, sometimes it does)

My database is split into front and bank ends.

I have two sets of forms, each form with a subform.

In the first form, I have a Class form with a Class table combo box.
The subform, Class Skills, for the Class Skills table, is linked by a Class ID primary key and foreign key in the Master/Child fields link property.

Adding Skills to the Class Skills form updates the Class Skills table with the name of the class and the skills taught.

That works.

In the second form, I have a Class form with a Class table combo box.
The subform, Class Roster, for the Class Roster table, is linked by a Class ID primary key and foreign key in the Master/Child fields link property.

Adding students to the Class Roster form "sometimes," not all the time, updates the Class Roster with both the Class name and the Student names, sometimes it just adds the student names and leaves the Class name blank.

Not to disuade from checking these again for something I may not have thought of, but I've checked the table relationships, form properties for Data Entry and Allow Edits, and Master/Child link field. I've created a new form.

I don't know where to go from here. Any ideas?
David Bigelow
David Bigelow
  • 9
  • 5
1 Solution
Rey Obrero (Capricorn1)Commented:
first try doing a Compact and Repair...

see if this change the behavior of adding records.

do you have a  routine to check for required fields?

here is a short checking code that i use.
* for all the fields that are "required" place a value of REQ in the tag property
* you can use the codes for any number of controls in your form

Private Sub Form_BeforeUpdate(Cancel as integer)
Dim strCtlName As String, NullCtl As String, Msg As String, ctl As Control
strCtlName = ""
For Each ctl In Me.Controls
    If ctl.Tag = "REQ" Then
        If Len(Me(ctl.Name) & "") = 0 Then
            strCtlName = strCtlName & ctl.Name & ";"
        End If
    End If
If Len(strCtlName) > 0 Then
    NullCtl = Mid(strCtlName, 1, InStr(strCtlName, ";") - 1)
    Msg = "Please fill out the required fields!" & vbCr & vbCr
    Msg = Msg & Left(strCtlName, Len(strCtlName) - 1)
    MsgBox Msg, vbCritical, "Required Fields"
    Exit Sub
End If
end sub
David BigelowStaff Operations SpecialistAuthor Commented:
I probably don't understand the full impact of adding the routine to check for required fields.

If on the subform I have input the student names for the selected class on the main form combo box, how would adding the routine to check for required fields help? To me, that would help if I wasn't sure whether another user had, in fact, input the student names.

But, maybe I'm misunderstanding.
David BigelowStaff Operations SpecialistAuthor Commented:
Either way, that is some valuable code to have. Thank you!
7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

David BigelowStaff Operations SpecialistAuthor Commented:
Update: Compact and Repair did not help.
When you add data to a table is a process separate from a form that has that table open already, you don't see the changes immediately.  Remember, when you run a query, you select data from a table or tables and copy it into a temporary work area.  If the original source is changed, your temporary copy isn't necessarily updated immediately or even ever depending on the circumstances.  There is a default refresh interval set for the database/form.  At that interval, Access will "refresh" the data.  Refresh picks up changes in column contents and it recognizes deleted records.  It does NOT recognize added records.  This may be why you think it works sometimes but not every time.

To ensure that other open forms pick up the added/changed values, you must force them to requery their RecordSource.  Remember that Requery will reposition a recordset back to the first record if it is showing multiple records.
David BigelowStaff Operations SpecialistAuthor Commented:
I'd like to delve a little deeper with you. I've isolated that the Class Roster subform, linked to the Class combo box will update the Class Roster table with the Student AND the Class Name
IF the form with the Class combo box and Class Skills is closed. I seem to recall reading something about one form not being able to update when another is open. Perhaps this is in reference to the Class combo box on both forms, which both pull their data from the same Class table.
As I mentioned, the Class Skills always updates, but I'm seeing that when the Class Skills form is closed, only then will the Class Roster form update the Class Roster table.
I'm thinking:
1. Force a requery of the Class table when I open the Class combo form/Class Roster subform. But that doesn't really make sense because the new class is in the combobox list of classes when I open the "Class combo/Class Roster subform."

Here's a point that might clarify, or at least help you help me. When I input the data into the Class Skills form, it is updating the Class Skills table. I see that when I switch from the form to the table. Then, with that same form open, but having also opened the Class Roster form, the Class Roster table is updated with the names I enter, but not the Class name. Only when I close the Class Skills form will the Class Roster table update the Class name AND student. I don't mean to say it is delayed until then; but I mean to say it doesn't happen at all unless the Class Skills form was first closed.

2. Maybe I should/could have the button from the "Class combo/Class Skills subform" form close the form when it is clicked to open the "Class combo/Class Roster" subform. This would set up the success scenario that I'm seeing with the first form being closed, which I've verified in three separate tests that the information then updates correctly.

Thank you for helping with this and I look forward to your reply.
To avoid this type of confusion, I rarely allow more than one form to be open at one time.  Occassionally I have to use a popup form that is going to modify something visible on the main form.  In that case, the popup form forces the main form to requery.  In one situation I have a main form with a number of tabs that contain subforms.  One of the subforms lists all services for a particular client.  The datasheet style subform doesn't lend itself to adding/updating a service dur to the amount of data required so I open a single record form to add a service or change an existing service.  When I do this, I want the DS view subform to show the new record or reflect the changes to an existing record.  The code to do that is:


Sometimes you just have to requery a combobox.  In that case:

When running code that explicitly references another form, remember that the other form MUST be OPEN for the code to work.  In my case, I only open the Service Order form from the client form so I know that the client form is open.  If you have a situation where your other form might not be open, you have to determine if it is open before you attempt to requery it.

but I mean to say it doesn't happen at all unless the Class Skills form was first closed.
Access automatically saves data under certain conditions.  Primarily when the current record loses focus due to scrolling, clicking into a parent or subform, or closing a form.  You are not seeing the update immediately because the record you are updating isn't losing focus until the form is closed.  You can add a "save" button to the popup form that will force a save.
If Me.Dirty Then
    Docmd.RunCommand acCmdSaveRecord
End If

Open in new window

Go back and reread my original response and see if you can relate that explanation to your current situation.
David BigelowStaff Operations SpecialistAuthor Commented:
If I may ask you to look at my database...

I made a backup of my database, stripped out usual records, and am uploading it for your review. It is in two parts, front and back.
Usage is a rather topdown flow.
From Main Menu, Add Class - just make up info, trainers not needed. Some generic skills are input.

Save Class is just for cosmetics, since a lot of people think you need a save button. You gave code to save on dirty. I tried that On Click for the command Save button of the next form, the Class Roster form, but am not certain I did so properly, because it didn't make a difference in saving the class name to the class roster table, which I am trying to fix.

Add Class Roster opens the form to add students to the class you just made, or another class. (To add students, Alt + down arrow for student list)
It is this Class Roster form that is not updating the Class Roster table with the class name.
I tried having the Add Class Roster button close the "Add Class" form when it opens the Add Class Roster form, but the data was still not updating to the Class Roster table when I selected the class name and input the students.
The Class Roster Name isn't in the ClassRosters table.  Only the ID is there.  If you want to see the name, you need to join to the classes table.

I think you mean that the Class_ID is not being placed in the class_Rosters table.  I can see that it is not.  You have to put it there yourself.  You need code in the BeforeInsert event of the sfm_Class_Rosters subform to copy the ID from frm_Add_Class.  This may do it:
me.Class_ID_fk = Forms!frm_Add_Class!Class_ID_pk

A better solution would be to pass in the class id using the OpenArgs of the OpenForm method.  You have that working with a macro and I don't touch macros so you are on your own.  But if you change the macro then the code in the class roster form would be
me.Class_ID_fk = Me.OpenArgs

The OpenArgs is better because it doesn't require you to leave the calling form open.  

I rarely allow multiple forms to be open at one time.  I think it leads to confusion because if you go back to the original form and open the second one again, comes back to the top of the stack but it has old information rather than the new information.  When I do allow multiple forms to be open, usually the second one of them is a popup.
David BigelowStaff Operations SpecialistAuthor Commented:
The solution in the 2nd paragraph worked like a charm.

To test the "better solution," how do I open the form with expression or code builder or another way besides a macro? I tried using the button wizard, but even that says embedded macro.
You can convert embedded macros to VBA.  There is a button on the ribbon.
David BigelowStaff Operations SpecialistAuthor Commented:
Not knowing where to put the better solution code,  I tried in the subform, "sf_Class_Rosters_subform."

Private Sub Form_Load()
    Me.Class_ID_fk.ColumnHidden = True
    Me.Class_ID_fk = Me.OpenArgs

End Sub

Where should I be placing the code? Or, what do you see that I should be changing? Would I add a second occurence of code, with a divider line and End Sub, to the Sub Form_Load Event for the OpenArgs code?

I have the Me.Class_ID_fk column hidden because I just need it for linking to the main form; it would be distracting on the subform.
In the Form's BeforeInsert event -
Me.Class_ID_fk = Me.OpenArgs

You don't need to hid the column in code.  Just set its visible property to no on the property sheet and it will stay hidden.

The reason to use the BeforeInsert event is because you don't want to dirty the form with your own code.  You want the user to dirty it first by typing something, then your code in the BeforeInsert event will run.  The Load event runs only once when the form is opened so it is not the appropriate event to use for something like this.  It would only work for the first record you add.  Subsequent records would not have the FK field populated.  You could use the BeforeUpdate or Dirty event, but those run whether you are updating or adding, so that leaves us with the BeforeInsert event which runs when the first character is typed in a form.  So if the form is capable of adding multiple records, each record will have the FK populated "just in time".
David BigelowStaff Operations SpecialistAuthor Commented:
This worked perfectly, "You need code in the BeforeInsert event of the sfm_Class_Rosters subform to copy the ID from frm_Add_Class.  This may do it:
 me.Class_ID_fk = Forms!frm_Add_Class!Class_ID_pk"

I couldn't get the OpenArgs to work, but I'm not too versed with code builder.

But, I have two solutions.
1. Close the form and then open the other.
2. Use the code above.
David BigelowStaff Operations SpecialistAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now