Subforms question

[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

 

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

 

[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.

 

[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.

 

[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.

 

[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.