How to assign a textbox a value from a junction table

[easyrider]

Hello all,

Mgmt has asked me to add a module to our existing database to keep track of computer assets (desktops/laptops). Because Microsoft uses the same Windows Version (21H2, 22H2, etc) for different operating systems, I had to build a junction table to handle the many-to many relationships - which I've done. So far so good.

I have a continuous sub form on the Main Menu form that correctly displays the information that I want, based on a query ComputerList_Q. When I call the Add New Computer form, I have three text boxes on it that I would like to display the OS Edition, OS version & End of Life date *after* the operating system has been selected from the ChooseOS_F which is called from the SelectOS button. I have tried different approaches: select statements in the textbox Control Source, different queries, etc. but I cannot get the three text boxes to display the data that I want.

Some specs:
1. Due to performance issues, coupled with the fact that this thing will eventually be split, I do not want to use DLookup.
2. The AddComputer_F uses parameterized querydefs to write to the table.



Attached is a concise version of the DB with the pertinent tables, forms & queries.

Start with the MainMenu_F -> click on the Add New Computer button -> Click on the Select OS button -> Choose an OS then click the Assign to computer button. When this returns to to the AddComputer_F the junction table ID is correctly passed.

How do I get the three text boxes to display the information that I want?

Thanks in advance!
-Bill

[moke123]

Here's one way.

This uses withevents.

EDIT: on your ChooseOS_F form ignore or delete the AssignComputerOS_BTN button as I removed the code in the form. I forgot to delete the button.
Just highlight your selection in the list and close the form.

[moke123]

Here's another example using a different event.

[easyrider]

Whoa, that's too cool. I'll have to dig into the withevents some more. Thank you!

[easyrider]

I like version 2 better since the user only has to click once to select an OS - more efficient, eh?

And BTW, thanks for adding the check to make sure the computer name isn't null. I have that check in the real database, just didn't include it in the example I submitted, lol.

[moke123]

I use something similar to the 2nd example.
I have a generic unbound form with just a listbox and a cancel button. No code in the actual form.
I set the all the properties of the list, ie. rowsource column count, column widths, etc, in code along with all the events.
This way I can reuse the form for several different procedures.

Good luck with your project.

[ArviLaanemets]

My advice is, instead of button to select OS, use a combo box instead - with rowsource having columns for [OS_ID] (in case you have it), OS_Name, OS_Version and OS_EndOfLife, and displaying as selection a string composed from OS_Name, OS_Version, [and maybe OS_EndOfLife too, in case there may be rows with same OS_Name and OS_Version, but with different OS_EndOfLife in your OS registry table]. Selecting the combination from combo sets either OS_ID or OS_Name as the value of combo box, and the AfterUpdate event of combo set's values of 2 or 3 text boxes equal with rest of matching values of combo's current rowsource values array.

Another question is, why you store in your tables data, which can be always read from source.

[easyrider]

My advice is, instead of button to select OS, use a combo box instead - with rowsource having columns for [OS_ID] (in case you have it), OS_Name, OS_Version and OS_EndOfLife, and displaying as selection a string composed from OS_Name, OS_Version, [and maybe OS_EndOfLife too, in case there may be rows with same OS_Name and OS_Version, but with different OS_EndOfLife in your OS registry table]. Selecting the combination from combo sets either OS_ID or OS_Name as the value of combo box, and the AfterUpdate event of combo set's values of 2 or 3 text boxes equal with rest of matching values of combo's current rowsource values array.

Another question is, why you store in your tables data, which can be always read from source.

I actually thought about using a combobox instead of the Select OS form. The issue that I was running into is that the Add Asset form is pretty busy already. The current list of active Windows OS's that are in use is 9 rows long and I haven't even started with the handful of Apple/Mac devices yet. Here's a screenshot of the actual form currently in development:



Also, I wanted to give the user a quick solution to add/edit an OS Edition, Version and/or EOL without having to call me to add it to a table, or have them back out and go someplace else to add it. I thought it made sense to put those functions on one form. Perhaps this is not the best way to approach this and I'm willing to contemplate alternative suggestions.






Maybe I'm having another senior moment, but I don't understand your 2nd question. Would you mind elaborating?

Thanks,
-Bill

[ArviLaanemets]

Maybe I'm having another senior moment, but I don't understand your 2nd question. Would you mind elaborating?

Looking at added picture of your form, it looks like you want in same place to register new operation systems, their versions, and manage operation systems assigned to devices. With this setup, especially when you store the EOL date as device info, there will be a risk for integrity of data. Let's assume for some reason Microsoft changes EOL date for some edition. When the EOL is stored with device info, then you have to edit it for all such devices. When only OS version is saved for device, and EOL is read from OS versions table in case it is needed, you have to edit it only in OS versions table, and this change is immediately effective everywhere through your DB (in queries, forms, and reports)

I would have instead:
1. A table where all OS are registered. Like tblOS: EditionID, Edition;
2. A table where all OS versions are registered. Like tblOSVersions: OSVersionID, EditionID, Version, EOL;
3. A form for registering new OS editions and their versions.
A Main form (fOS) with a subform (sfOSVersions).
The Main form is a single form with tblOS as source. The form has unbound navigation combo to activate any of previously registered edition. Adding a new row into this form starts the process of registering of new OS edition. The new record can't be saved when Edition control is empty.
The subform is a continous form with tblOSVersions as source, is linked to Main form by field EditionID, and displays all versions of currently active OS edition in Main form. The subform has [hidden] textbox with EditionID as source and adding a new record into subform fills it with EditionID value from Main form - i.e. the new version record will be linked to active edition in Main form. The subform also has visible textbox for EOL, available for user for editing;
4. To assign a specifix OS version to device, I'd prefer to use devices form. Simply having a combo on devices form to select current OS version from tblOSVersions (in case you want to keep the history of all OS versions assigned to given device, the solution will get much more difficult).
In case you anyway want to assign OS version to assets directly from OS registration for described above:
4.1 You add into Main form a hidden textbox (e.g. txtCurrVersion), and create for subform an OnCurrent event, which writes into this hidden textbox in Main form the value of active OSVersionID in subform;
4.2 You add into Main form another continuous subform (sfDeviceOsVersions), based on form (fDeviceOSVersions) with query from devices table which reads from there DeviceID and OSVersionID as Source, and link it to hidden textbox in Main Form. The OsVersionID willhave combo as control, where the version from tblOSVersions is displayed. The subform displays all devices, having currently active OS Version from 1st subform assigned. It is possible to design this subform so you can register currently OS version for device not listed (updating Devices table), but this will not be easy.

[easyrider]

ArviLaanemets, thank you for taking the time to provide a detailed response. Here is a simplified graphic of my current schema:



As you can see, I am only storing the OSWV_ID -- from the junction table -- in a corresponding field in the Computer/Asset table. If Microsoft changes an EOL for a given Edition/Version then I only need to edit the junction table. On The Add Asset form, the textbox to the right of the Operating System label gets this value from the SelectOS form, then written to the computer table when the Add button is clicked. In production, this text box will be hidden

I'll contemplate some of your other suggestions, but again, thank you for your response!

-Bill