Unbound form with command button updates a table based on the current User_Logs_ID where the logoff

[Oblio]

Attached is a database used for logging users in and importing files scanned.


Currently, the Computers_ID is updated in t_Runs.Computers_ID, thereby showing what machine was used for each run.
I would like more details recorded when the runs are undertaken.

My preference is to swap out Computers_ID with t_User_Logs.User_Logs_ID as it also shows the WIN User, and current logged in user in addition to the computer name.

The relationships table would be revised to show t_User_Logs as a one to many with t_Runs.

The existing relationship between t_Runs and t_Computers would be deleted. Instead, we would create a one_to_one relationship with t_User_Logs.User_Logs_ID joined to t_Runs.User_logs_ID in a one to many relationship. t_Computers.Computers_ID would be in a one to one relationship with t_User_Logs.txt_Machine_Name.

It is still required to check and see if the computer name is already entered or not in t_Computers.

1. After changing the relationships so that User_Logs_ID is where t_Computers was, how do I add the User_Logs_ID to the t_Runs when the Command240 "Get Scan Details" button is pressed on the Import Files Form?

2. Fix the relationship change in the VBA for the t_Computers ?

Thanks for your help !

Bill

[ssanfu]

These are the relationships you have



These are the relationships I changed

You can see the new relationship link and the red underline are the two fields I renamed.



I added a function to the modUserLog Mudule; the functio is named "GetUserLogID"
I also modified bas_ListFiles_080217. To find both of these, in the IDE, do a Find on " '#-#- " (no double quotes)

Then I had to fix a couple of saved queries......

Test and see if this is close to what you want....

[Oblio]

WOW !!! Thank you so much for this, Steve!

It works perfectly and I cannot thank you enough for your help ... I do not know what we would do without teachers like you... just be endlessly frustrated I suppose, lol...

Is there a way we can separate t_Computers from t_Runs ? It seems to me that it is redundant since hat information is already recorded in t_User_Logs.txt_Machine_Name which is written to the table in the LogOn function...

What I envision is t_Computers.Computers_ID in t_Runs would be replaced by t_User_Logs.User_Logs_ID.

We would only update t_Computers if there was a new computer name to add, and I would replace t_User_Logs.txt_Machine_Name with t_Computers_ID...

Below is how I am thinking about the relationships... Does this make sense ?



Bill

These are the relationships you have



These are the relationships I changed

You can see the new relationship link and the red underline are the two fields I renamed.



I added a function to the modUserLog Mudule; the functio is named "GetUserLogID"
I also modified bas_ListFiles_080217. To find both of these, in the IDE, do a Find on " '#-#- " (no double quotes)

Then I had to fix a couple of saved queries......

Test and see if this is close to what you want....

[ssanfu]

Is there a way we can separate t_Computers from t_Runs ? It seems to me that it is redundant since hat information is already recorded in t_User_Logs.txt_Machine_Name which is written to the table in the LogOn function...

What I envision is t_Computers.Computers_ID in t_Runs would be replaced by t_User_Logs.User_Logs_ID.

We would only update t_Computers if there was a new computer name to add, and I would replace t_User_Logs.txt_Machine_Name with t_Computers_ID...

Below is how I am thinking about the relationships... Does this make sense ?

Yes, It does and can do.

Something like this?



Consider:
In fact, you could/should move "t_User_Log.Computer_ID_FK" TO "t_Users".


Of course, you DO realize you will have to check/edit the queries and code...


One other point. I add suffixes to the PK/FK fields. It makes it easier (for me) to know which field is the PK or FK.

In a query, you have fields Runs_ID, Computer_ID and Computer_ID.
I would have RunsID_PK, ComputerID_FK and ComputerID_PK. Which is easier to understand??

[Oblio]

Yes, I see your point about moving it to the Users table. It really amazes me how many ways you can model these relationships... I believe I am going to adopt your PK and FK into my future projects as I agree it is really helpful especially when you are trying to keep complicated relationships in mind VBA to not make a giant mess of it

In my humble opinion (and I mean HUMBLE, lol) I still feel that keeping the Computers_ID in the t_Users_Log is more natural ...it is like a snapshot of the Person, The Computer and the logon with a date and time, and what time and results occurred in the run.... tomato/tomawto...

But that is now and I have been at it all day and the head is pounding so let's hope and pray tomorrow some light will shine in this dark cavern of ignorance tomorrow lol !

Any chance you are interested in helping me remove the weave of Computer_ID from the VBA ??? Maybe even create it's own little separate function???

Ok well thanks again for your generous and thoughtful and detailed help, Steve, and wherever you are I hope life is good to you and the people around you will know how lucky they are to be around someone as kind and thoughtful as you have been to me, a complete stranger... I really hope you have a great day and awesome weekend !!!! Thanks again ,
Bill

[ssanfu]

In my humble opinion (and I mean HUMBLE, lol) I still feel that keeping the Computers_ID in the t_Users_Log is more natural ...it is like a snapshot of the Person, The Computer and the logon with a date and time, and what time and results occurred in the run.... tomato/tomawto...

After thinking about it for a while, I have to agree with you. I wasn't thinking about a user could log in from a different computer or use a different log on name.

So you are entirely correct, the fields Windows_User_Name and Computers_ID_FK should be in "t_Users_Log".

[Oblio]

Thank you ! You have been a great support and I really appreciate the help, Steve... have a great weekend!

Bill