Personally, I think you should change your table layout a bit... Something like:
Then I'd do as r937 suggested and have each computer, linked through their DepartmentName foreign key to their department... And their software, again linked through their foreign key.
DepartmentName [Primary Key]
SoftwareName [Primary Key]
ID [Primary Key]
DepartmentName [Foreign Key]
SoftwareName [Foreign Key]
You could even go so far as to eliminate Department altogether (provided you didn't miss an 'etc...' off the end of that table too?), and simply use a SELECT statement to retrieve those computers with a 'DepartmentName' that matches 'Administration' (for example).
On the other hand, if you're happy enough with your present database layout, and it does the job you're after, I would suggest:
- Adding a field to your 'Tbl_DepartmentSoftware' table, that is of yes/no (boolean) type, called 'Installed' (or whatever you want), which defaults to 'no' (or 'FALSE').
- Adding a 3 column listbox to your form (which it sounds like you already have... or are you refering to the actual records appearing in the footer [for example]?), and populating it with:
- Setup a button, with a teeny tiny bit of VBA (about 15 words or so), accompanied with a small SQL statement to update the table (about 15 words or so)... The button, when clicked, will detect which item is selected in the listbox, and change the 'Installed' value from 'no' to 'yes', and vice versa. The listbox will also be updated instantly to indicate the change.
To achieve what you're after without using any code whatsoever, isn't really possible.
Add the new field to the table, and create a listbox on your form that contains only the three fields I mentioned above (in that order). Then create your button, go into its properties, and at the bottom you should see, 'On Click', use the dropdown box to input '[Event Procedure]', and then click the little '[...]' button next to it... This will open the VBA editor for you, and will input for you:
Then on the line immediately after that, copy and paste:
Private Sub myButtonName_Click()
Change the bits in blue to whatever name you gave your listbox... Save it, and give it a whirl.
Dim strSQL As String
strSQL = "UPDATE Tbl_DepartmentSoftware " & _
"SET [Installed] = TRUE " & _
"WHERE [DepartmentID] = '" & Me.yourListboxName.Column(0) & "' " & _
"AND [SoftwareID] = '" & Me.yourListboxName.Column(1) & "'"
'Column(2) is based off the order I gave for the 3 columns in the listbox.
'(2) refering to the third, 'Installed' column.
If (Me.yourListboxName.Column(2) = 0) Then
CurrentDb.Execute (Replace(strSQL, "TRUE", "FALSE"))
Should do exactly what you're after.
Any issues, let me know, be here all day.
Last edited by kez1304; 09-01-11 at 06:44.
Looking for the perfect beer...