Create new related record for every record in another table.

[TraciAnn via AccessMonster.com]

My front end is used to track information separate from which the SQL backend
was created. Therefore, I am very limited to changes I can make to the BE.

Case in point:
User Table (BE)
UserID
FirstName
LastName
Status (FK to dboStatus (BE))

The "main application" for the BE has an entirely different "Status"
structure than the requirements for my FE app. Therefore, I added two FE
tables to handle this:

Status table
StatusID (PK)
Status

UserStatus table
UserStatusID (PK)
UserID (FK)
StatusID (FK)

Now I can assign the statuses appropriate to the purpose of the application
without touching the backend table.

The problem:
How can I automatically create the initial userstatus record after a user is
added to the BE through another app?

I assume that I need a logic that runs either AutoExec or a form On Load that
queries the User table that meets the project filter (not all Users appear in
my FE) and compares the UserIDs to the existing UserIDs in UserStatus. Then,
where UserID doesn't exist in UserStatus to create a record using the default
StatusID (=1).

That's my assumption but I don't know where to begin to implement this
thought. I've also learned there are far simpler ways of accomplishing tasks
than what I dream up

Thanks in advance for your help!

 

[golfinray]

Should not refreshing your table add the update? Then you can edit it however
you want.

 

[TraciAnn via AccessMonster.com]

Should not refreshing your table add the update? Then you can edit it however

you want.

I'm sorry, I don't know what you mean "refreshing your table".
- What is done to "Refresh"?
- Which table (dboUser, Status, UserStatus)?

Of course, the purpose of automatically creating a related record in the
UserStatus table for all Users meeting query criteria is to avoid having to
do it manually. Anytime a user is added to the db (through a different app
than mine) it is assumed the status is "Active". The only time a user of my
FE should need to edit the UserStatus is if/when the status changes for this
specific project (not the status of the user in the BE table).

Thanks for your input!