Subforms question (1 Viewer)

[hammerva]

First of all sorry for all the questions. Trying to figure out this process that they want quickly. Anyway:

Is there a way to prevent a form from hitting the 'AFTERUPDATE' event when switching to a subform?

I want to create a meeting screens and one of the things I need is a list of topics for the meetings. So I created another table called Meeting Topics. What I want to happen is that user enters the meeting, enters all the topics and then hits the "Add Meeting" Button which adds records to both tables.

Well after I am finished with the fields on the Meeting table and go to the subform for Topics; it activates the AfterUpdate event set all those fields to #delete. How can until the processing of the subform because the main form update and insert event are processing? Is that possible.


Now you know why I didn't want to use subforms.

Thanks for the help

 

[Pat Hartman]

This is a simple, "no code required" process if you use bound forms. Access saves the current record on the parent form when you move the focus to the subform. This executes the Before and AfterUpdate events if the main form was dirty. It is necessary to save the current record because it might be new or you might be changing its primary key. The subform is bound to the mainform with the master/child link fields. This causes Access to "push" the primary key of the mainform record to the foreign key field of the subform record. You don't need to code this. In order for 1-to-many relationships to work, the 1-side record MUST exist before any many-side records are added.

Is there some reason that you don't want to use bound forms? That's the whole reason for using Access - what it does for you behind the scenes. If you want to do it all yourself, you are better off with VB or C++.

You can do what you are asking, you simply have to use an unbound form and you won't be able to use a subform. That will limit the number of many-side records to what can be displayed on the main form. The alternative is to write the subform records to a temporary table and then append them to theire real table once the primary key of the parent record is known. This technique is very tricky in shared databases because of the possibility of two people needing to use the holding table at the same time.

 

[hammerva]

The reason I prefer the "Add Button" format is that it is familiar with our account's system. A record isn't added until you say you want it added. Yeah I understand that it isn't the way Access.

I am using bounded forms. The bounded forms is tied to the Meeting table. The subform is bounded to Meeting Topics

I can live with the subform process (although I need to move my edit checks in the AFTER update event). But how do I get rid of the #deleted message after going to the sub form? How can I keep the records on the form?

 

[Pat Hartman]

although I need to move my edit checks in the AFTER update event

- NO, you need to move your edit checks to the BEFOREUpdate event of the MAINFORM. That way you can cancel the update if any errors are found.

To make this seem a little more "normal" to your users, hide the subform when your form moves to a new record and only unhide it after they push the Add button that adds the mainform record.

If you are getting a #deleted message, there is something wrong with your subform/mainform linkage.

BTW, the verb is To Bind and it is irregular. The past tense is Bound, not Bounded.

 

[hammerva]

I need more smilies to describe my feeling on this right now

I am still getting the #deleted on the fields in the main form when I tab to the subform. I am not sure what I am doing wrong here.

I created a relationship between Meetings and Meeting Topics with links to the Status Number, Scheduled_Meeting_Date, and Meeting_Type fields. I am using the same in the Link Master and Link Child Fields (Status_Number;Scheduled_Meeting_date; Meeting_Type). I made sure that the fields were the same on both tables.

I did everything according to the Subform Wizard and when I enter go into the subform I get the #deleted value. It seems that it is sort of working because when I enter a status number in the main form it is populating it in the Status_Number subform field. But only when I have still focus on the main form.

 

[Pat Hartman]

You could try posting a stripped down version of your db. Most people prefer an A97 version.

 

[hammerva]

I finally figured out the #delete message. I had code in the AFTER update event from the previous version that was screwing with my data. But I still had some questions:

1. Once you are in the subform, is there a way to allow the user to tab back to the main form? It looks like the only other option is for the user to click on a main form field?

2. If go back from the subform and make changes to the main form data (mainly the keys); it is acting like a new record is being created on the Meetings tables. I guess in Access logic it makes sense because you created the record before you went to the subform. But how do I stay on the that record instead of trying to create a new one. I got a feeling that is going to be impossible in Access 97.

I am including a copy of my code for review in Access 97. The main menu button doesn't work because it is connected to other forms in my system that for security reason I couldn't send out. Also my edit checks need to be moved in the BEFORE update event.

Thanks for all the help. Hell you should be getting a freakin paycheck on my account with all this help.

 

[Rich]

You have a missing field called Time on the form. I'm sure Time is a reserved word in access so it's a bad choice for a field/control name.

This part can be replaced with

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
DoCmd.Close acForm, "Add Misc Meeting", acSaveYes
DoCmd.OpenForm "Add Misc Meeting", , , , acFormAdd
Else
stDocName = "Main Menu"
DoCmd.OpenForm stDocName
DoCmd.Close acForm, "Add Misc Meeting", acSaveNo
End If

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
DoCmd. GoToRecord ,,AcNewRec
Else
Me.Undo
stDocName = "Main Menu"
DoCmd.OpenForm stDocName
DoCmd.Close acForm, Me.Name
End If

 

[hammerva]

I am totally confused. What or where would this "time" field be used for?

And for the block of code, are you talking about the processing in the Add button when I say 'Do you want to add another record?" If so then an Undo command wouldn't work because I want to save that records even if the user doesn't want to add another record. If you mean somewhere else please let me know.

Right now my main problem is that if I update the subform data (for table Misc Meetings Topics) and then go back to the main form and update either the Status Number, scheduled Meeting Date, or Meeting type; it tries to create a new Misc Meeting Topics record instead updated the ones just created. Would the code you provided solve that problem?

But thanks for helping.

 

[Rich]

I am totally confused. What or where would this "time" field be used for?

I've no idea, it's in your module.
It now comes up as Time_Updated. The Me.Undo replaces acSaveNo
I'm sorry but I don't understand your structure, you have three primary keys in one table and four in another, you've linked the form/subform with three.

 

[Pat Hartman]

If you have primary keys that may change, you are much better off replacing them with an autonumber. Then you can make a unique index that includes all of your original primary key fields. That will enforce the business rules and will avoid the issue of having to propagate key changes.

Jet does support cascade update. You need to create a relationship between the tables in the relationship window, check the enforce referential integrity check box and then you will have the option of selecting cascade update and cascade delete. It is still better to use an autonumber than cascading your pk changes.

 

[hammerva]

Sorry for the constant questions but I have another one:

If I change my keys for the Meetings and Meeting topics table to an autonumber field; how do I connect the Status Number, Scheduled_Meeting_Date, and Meeting_Type from the main form to the subform? I still want (or think I still should) pull those 3 fields from the Meetings table and add them to the Meeting Topics table as well.

Do I create a relationship of the key field or of the 3 fields I need to connect?

Thanks for all the help guys.

 

[Pat Hartman]

The only field from the parent record that must be duplicated in the child records is the parent key. That is how the tables are joined together. So your tables would be:

tblMeeting:
MeetingID (autonumber primary key)
MeetingDate
MeetingType
StatusNumber
etc.

tblMeetingTopics:
MeetingTopicsID (autonumber primary key)
MeetingID (foreign key to tblMeeting)
Topic
TopicDesc
etc.

In order to obtain any data from the Meetings table and the Topics table in the same query, join the two tables on MeetingID.

 

[hammerva]

More issues. Here was the test I have a problem with:


User starts entering information. As soon the first field is populated the AutoNumber key is created. If the user clicks the Exit PCS button or the Return to Main Menu button instead of the Add Meeting button that record shouldn't be added to the table. Makes logicial sense to me. But the problem is that it goes to the BeforeUpdate event every single time. It just seems that Access allows too many ways to add records to tables that are either incomplete or wasn't supposed to be there to begin with.

Is there a way to prevent the process from even going into the BeforeUpdate event? I guess my other option is to include delete statements in the afterupdate event to remove the records. But that seems like backwards coding.

Of course maybe my whole system is backwards already.

 

[Pat Hartman]

There is only one path that all records follow on their way to being added/updated and the last stop on that path is the BeforeUpdate event. That is the event you use to prevent bad data from being entered into the table.

Edit your data in the BeforeUpdate event and if errors are found, cancel the event with -
Cancel = True

The autonumber will be used even when the record is not added to the table. That is the way autonumbers work. Gaps will occur when someone starts adding a new record and then cancels the add. This is perfectly normal and does not cause any problems.