Add records to one table and update records in another table

[gemadan96]

I have three tables in a database to manage members: Members, Promotions, Ranks

When ever a member earns a promotion to a new rank I want to add new records to the Promotions table with information from the Ranks table, at the same time I want to update the members's records with information in the Ranks table.

When a promotion is added it is linked to a member in the Members table. The Promotion table will include the member ID, the date of the promotion, and the fee for the promotion (the fee comes from the Rank table. The member record in the Members table needs to have the Title and Rank fields updated to the correct values from the Ranks table.

At this time I have a Promotion Detail form where I select the member with a combo box then I use another combo box to select the rank and auto complete the remaining fields on the form with data in the Ranks table. The data is presently saved to the Promotions table. This is done with VBA to populate the fields.

Private Sub Rank_AfterUpdate()
Description = Rank.Column(2)


Title = Rank.Column(3)
PromotionFee = Rank.Column(4)
End Sub

I do also have two queries that find the information about promotions. The first finds the most recent promotion for each member. The second one links the first query with the PromotionsExtended query to give details just about the most recent promotion.

Is it possible to add to the VBA of the form to have it update the information in the member records and not have to create extra steps for the end users?

[June7]

Certainly possible but maybe not necessary.

If you already have query that finds the most recent promotion then why bother duplicating rank info into Members? Also, why save all the related Rank info into Promotions and not just RankID?

[gemadan96]

The data in the Rank table is default information for the various ranks. On a case by case bases it might be modified. Also the fees may change from year to year and we don't want that to impact previous records. The rank and title may also be used in reports for each member.

[June7]

Options for rank data.

1. save the info into Promotions (your current process)

2. new record in Ranks for changes and set records as active/inactive

Either way, I don't see need to duplicate rank and title in Members table. But if you must, that is just more code to populate fields. Maybe in the Promotions form AfterUpdate event. Is Promotions form used as a subform and main form bound to Members?

[gemadan96]

To simplify this what I need to do is when a member is promoted is add a record to the Promotions table with the required information from the ranks table, which may or may not be modified. At the same time the Title field in the members table needs to be updated with the Title value in the ranks table based on the new rank. The Promotions table will be used to prepare certain documentation while the Members table is used for other functions and requires the correct title for the member. The title is not required in the Promotions table. The Ranks table for the most part is static data and will grow very little if at all. The only thing there that will change are the fees. At this time there are only 18 records in Rank.

[June7]

My comments in previous post still apply. And didn't really answer my question.

As you must already be aware, saving that data will require code in some event. The trick is figuring out what event. The event and code depend on the form arrangement.

[gemadan96]

When a promotion is added using the promotion detail form a rank is selected from the Rank table. Other fields in the form are then populated with information from the rank table. No new records are added to the rank table. The promotions table is used to print diplomas and reports. Some of the default data may be modified in the promotions table. Kind of like modifying the unit price on a product order without impacting other orders. What I want to do is also update the title field in the Members table for that member with the value entered in the Promotions table. Each member will have many records in the Promotions table and only one record in the Members table. Their title in the members table needs to be equal to the title associated with their last promotion. The title will be used for generating a full member name on mailing labels, correspondence etc and that is why it needs to be stored in the Members table.

I understand this will probably require some more code and I'm fine with that I'm looking for assistance in writing that code. I want to make this as simple as possible.

[June7]

As I said, it depends on the form arrangement. Is this a form/subform? Are the fields that need updating on one of the forms? If the table with the fields are not included on any open form, then probably need to run and SQL UPDATE action, like:

CurrentDb.Execute "UPDATE tablename SET fieldname='" & Me.somecontrolname & "'"

The real trick is figuring out what event to use, maybe the form AfterUpdate.

Again, saving the title into Members table is not really necessary. The member's current title can be retrieved from the promotions table and used in mailing labels, correspondence, etc.

[gemadan96]

I've reworked this to that the title is not stored with the member and kept in the promotions table. I've linked the Members query (used for the Members List and Member Details forms) with the CurrentPromotions query. The draw back to this is that a new student won't be visible in the Member List until they are promoted. So therefore what I would like to do is that when a new student is entered and saved I would like to also create a new record for them in the Promotions table at the beginner rank, without having to click the New Promotions button. If I can do this then I will achieve my desired results.

Any thoughts?

Basically when a new student is added a record needs to be created in the Members table and the Promotions table.

[June7]

Options are manual entry of those records or VBA code that runs an SQL INSERT action. Again, the real trick is figuring out what event to put code in. Again, maybe the form BeforeUpdate or AfterUpdate.