To do what you describe would not require a subform, but merely two unbound
list boxes in a form based on the Computers table. The first list box's
RowSource would be query which lists all software where there is no row for
the current computer in the InstalledSoftware table, e.g.
SELECT Title
FROM Software
WHERE NOT EXISTS
(SELECT *
FROM InstalledSoftware
WHERE InstalledSoftware.Title = Software.Title
AND Computer = Form!Computer)
ORDER BY Title;
The second list box would have as its RowSource property a query which lists
all titles from the InstalledSoftware table where the Computer matches the
current computer, e.g.
SELECT Title
FROM InstalledSoftware
WHERE Computer = Form!Computer
ORDER BY Title;
Both list boxes should be multiselect (either simple or extended according
to your own preference) and should be requeried in the form's current event
procedure and in its AfterInsert event procedure for when a new computer
record is added.
To add selected software titles from the first list box to the second would
need code which iterates through the first list box's ItemsSelected
collection and for each selected item executes an SQL statement to insert a
row into the InstalledSoftware table. The code would then requery both the
first and second list boxes to remove the items from the former and show them
in the latter.
To remove items from the second list box would be a reversal of the above,
iterating through the second list box's ItemsSelected collection and
executing SQL statements to delete rows from InstalledSoftware, again
followed by requerying both list boxes.
The above is not too difficult to achieve but would require a fairly good
knowledge of writing VBA procedures in Access, which your post suggests might
not be the case. I could give you more detailed help if you post back the
actual names of your tables, their columns and the two list boxes, or you
could opt for a much simpler solution as follows:
Create a form based on the Computers table and a continuous form view
subform based on a query on the SoftwareInstalled table such as:
SELECT *
FROM SoftwareInstalled
ORDER BY Title;
Link the parent form and subform on the Computer columns (i.e. the primary
key of the Computers table and the foreign key in SoftwareInstalled which
references it). In the subform add a combo box bound to the Title column
which lists all software with a RowSource of:
SELECT Title
FROM Software
ORDER BY Title;
The SoftwareInstalled table should have the Computer and Title columns as
its composite primary key, or at least a unique index on those columns. This
prevents a user selecting the same title twice for any one computer.
This second solution merely requires a row to be added to the subform to add
a title to the software installed on the current machine by selecting an item
from the combo box in a new row. Similarly to remove a title simply requires
a row to be deleted in the subform.
Ken Sheridan
Stafford, England
mattyv said:
Hello,
* Access 2007
* Little Experience with VBA
I will try and make this as clear as I can.
ISSUE
I have a database that has information for Clients and their Computers.
I am trying to make a subform linked to each Computer
The Subform shows me the entire list of software titles in one box.
Box 2 shows me which titles have been installed.
I would like to pick which title(s) that are installed on each machine
listed in box 2.
For instance
Matts Computer has AVG installed, Office 2007 installed and Foxit.
when I go back to Matts Computer Record, I would like to see which titles
have been installed and easily update on the fly.
Box 1 showing all software titles and filtering out
( AVG installed, Office 2007 installed and Foxit. )
Box 2 showing which titles are already installed [3]
( AVG installed, Office 2007 installed and Foxit. )
From Box 1 I can select, multiselect titles and update BOX 2.
Please help
thank you
Gcoaster
