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

Posted on 2015-01-09
Last Modified: 2015-01-27
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?
Question by:David Bigelow
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40540926
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

Author Comment

by:David Bigelow
ID: 40540946
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.

Author Comment

by:David Bigelow
ID: 40540969
Either way, that is some valuable code to have. Thank you!
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

by:David Bigelow
ID: 40541044
Update: Compact and Repair did not help.
LVL 37

Expert Comment

ID: 40541380
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.

Author Comment

by:David Bigelow
ID: 40541426
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.
LVL 37

Expert Comment

ID: 40541464
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.

Author Comment

by:David Bigelow
ID: 40545165
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.
LVL 37

Accepted Solution

PatHartman earned 500 total points
ID: 40545745
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.

Author Comment

by:David Bigelow
ID: 40560879
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.
LVL 37

Expert Comment

ID: 40561016
You can convert embedded macros to VBA.  There is a button on the ribbon.

Author Comment

by:David Bigelow
ID: 40562803
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.
LVL 37

Expert Comment

ID: 40562861
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".

Author Closing Comment

by:David Bigelow
ID: 40562949
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.

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question