Add records to one table and update records in another table (1 Viewer)

[gemadan96]

I have three tables: Members, Promotions, Ranks.

Members has all the data specific to a member and contains just one record per member.
Promotions contains information on member promotions and contains multiple records per member.
Ranks is a table of ranks and data specific to each rank such as name, description, title, fee.

I have a Promotion Detail form used for adding new promotions. I use some VBA that auto completes other fields on the form with information from the Ranks table based on the rank selected. The user has the ability to modify any of the data.

What I would like to do is update the Member's title (in the Members table) when a new promotion is added to the Promotions table. Their title is determined by the information from their latest promotion.

Any suggestions on how to accomplish this are greatly appreciated. I'm trying to automate as many processes as possible.

 

[burrina]

How are you entering your data, is it via Main form / Sub form and if so, are you using all of the tables as the data source ?

 

[gemadan96]

At the moment I have a Promotion Details form used to add the new promotion. It is linked only to the promotion table. I use a combo box to select the rank that has been earned. Using VBA on an after update event it auto completes a few other fields. I've been trying to figure out how to use a subform to update the members RankTitle in the Members table.

Presently in my MembersExt query I use a Dlookup function to get the RankTitle, however my concern is that this will slow things down if I have a couple thousand members.

In the Member Details form there is a tab that has a subform that lists all the promotions for the member. Each record has a link that opens the promotion detail form.

 

[JHB]

Use an update query which is run in the after update event for the rank combo box.

 

[gemadan96]

Any suggestion on how to do that would be appreciated.

 

[JHB]

What exactly do you have problem with?
In post #3 you write:

Using VBA on an after update event it auto completes a few other fields.

So I would run the update query here.