Solved

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

Posted on 2015-01-09
15
320 Views
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?
0
Comment
Question by:David Bigelow
  • 9
  • 5
15 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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
Next
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"
    Cancel=True
    Me(NullCtl).SetFocus
    Exit Sub
End If
end sub
0
 
LVL 1

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.
0
 
LVL 1

Author Comment

by:David Bigelow
ID: 40540969
Either way, that is some valuable code to have. Thank you!
0
 
LVL 1

Author Comment

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

Expert Comment

by:PatHartman
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.
0
 
LVL 1

Author Comment

by:David Bigelow
ID: 40541426
Pat,
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.
0
 
LVL 34

Expert Comment

by:PatHartman
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:

Forms!frmClients!sfrmServiceList.Form.Requery

Sometimes you just have to requery a combobox.  In that case:
Forms!someform!somecombo.Requery

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.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 1

Author Comment

by:David Bigelow
ID: 40545165
Pat,
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.
Training-Database-2015-01-12--2-.accdb
Training-Database-be-2015-01-12--2-.accd
0
 
LVL 34

Accepted Solution

by:
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.
0
 
LVL 1

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.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40561016
You can convert embedded macros to VBA.  There is a button on the ribbon.
0
 
LVL 1

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.
0
 
LVL 34

Expert Comment

by:PatHartman
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".
0
 
LVL 1

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.
0
 
LVL 1

Author Comment

by:David Bigelow
ID: 40573435
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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 …

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now