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

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" wrote:

> 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
>

Thank you KEN! I am going to try this now..

Ken,

This is the part where I am stuck!

"
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.
"

Ken

this is where I am stuck


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.

I'll assume the name of the first list box is lstAvailableSoftware and that
of the second lstInstalledSoftware. Both should have their MultiSelect
property set to either Simple or Extended as preferred.

To move selected items from lstAvailableSoftware to lstInstalledSoftware
the code would be along these lines. Put this in a button's Click event
procedure so that, once items are selected in the list box, the button would
be clicked to execute the code:

Dim varItem As Variant
Dim strSQL As String
Dim ctrl As Control
Dim cmd As ADODB.Command

Set ctrl = Me.lstAvailableSoftware

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO InstalledSoftware(Computer, Title) " & _
"VALUES(""" & Me.Computer & """,""" & _
ctrl.ItemData(varItem) & """)"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If

Me.lstAvailableSoftware.Requery
Me.lstInstalledSoftware.Requery

To remove selected items from lstInstalledSoftware into lstAvailableSoftware
the code for another button's Click event procedure would be:

Dim varItem As Variant
Dim strSQL As String
Dim ctrl As Control
Dim cmd As ADODB.Command

Set ctrl = Me.lstInstalledSoftware

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "DELETE * FROM InstalledSoftware " & _
"WHERE Computer = """ & Me.Computer & """ " & _
"AND Title = """ & ctrl.ItemData(varItem) & """"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If

Me.lstAvailableSoftware.Requery
Me.lstInstalledSoftware.Requery

You might have to adjust the above to reflect the actual names of your table
and its columns of course. I've assumed in the above that Title and Computer
are both text data type columns, i.e. the actual titles and computer names,
rather than numeric identifiers.

In the form's Current and AfterInsert event procedures requery the list
boxes with:

Me.lstAvailableSoftware.Requery
Me.lstInstalledSoftware.Requery

As with all operations which operate on a set of rows be sure to back up
your InstalledSoftware table before testing this until you are absolutely
sure that it is doing what's required.

Ken Sheridan
Stafford, England

"M@" wrote:

> Ken
>
> this is where I am stuck
>
>
> 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.
>

Thank you Ken !!
is there a way to export all of the tables, forms, code to text in access
2007? the more i am working with access 2007 I am loving it! hard to learn
though.. Matt

Hello Ken, I am getting an error and it stops at " Dim cmd As ADODB.Command "
I am not sure where to put the button control name.

here are my database properties, thank you

-----------------------------------------------

Table: CLIENTS
clientID Long Integer 4
memberDate Date/Time 8
fullname Text 255
firstName Text 255
LastName Text 255

Table: MACHINE
machineID Long Integer 4
clientFK Long Integer 4
softwareFK Text 255
productFK Long Integer 4
machineNotes Memo -


Table: MACHINE_SOFTWARE
machineSoftwareID Long Integer 4
machineFK Long Integer 4
softdCat Text 255
softdSubCat Text 255
softdType Text 255
softdSubType Text 255
title Text 255
publisher Text 255
url Text 255
installed Yes/No 1

Table: SOFTWARE
softwareID Long Integer 4
SoftCat Text 255
softSubCat Text 255
type Text 255
subType Text 255
title Text 255
dateUpdated Date/Time 8
publisher Text 255
homepage Text 255
version Text 255
description Memo -

Table: TEMP_SOFTWARE
tempSoftwareID Long Integer 4
machineSoftFK Long Integer 4
SoftCat Text 255
softSubCat Text 255
type Text 255
subType Text 255
title Text 255
dateUpdated Date/Time 8
publisher Text 255
homepage Text 255
version Text 255
description Memo -
installed Yes/No 1


FORMS
(main form) MACHINE
(subform in main form) MACHINEsoft

List Boxes
lstNotInstalled | InstalledSoftware

Add button >> btnAddSoftware
Remove Button << btnRemoveSoft

I don't use Access 2007 myself, but you can export data from tables to a text
file via the File | Export menu item in earlier versions, or by the
TransferText method/action in VBA or a macro. A report's contents can be
exported using the OutputTo method. You'll find further details of these
methods in the Help system. Object definitions can be documented using the
built in Documenter (Tools | Analyze | Documenter menu item in my version).
This actually creates a report which can be exported as a Rich Text Format
document which can be opened in Word.

As regards the error, check that you have a reference to the Microsoft
Active X Data Objects Library (Tools | Refrences on the VBA menu bar)

Your MACHINE_SOFTWARE table contains a lot of redundancy. You only need the
machineID foreign key to identify the software as the SOFTWARE table
contains the other columns representing the attributes of the software.

The MACHINE table should not include a machineFK column as the
MACHINE_SOFTWARE table models the many-to-many relationship between the
machines and software.

The fact that you are using numeric keys means that your list boxes will
each need to have a hidden column for these as their bound columns, and the
SQL statements built in the code will not need to include the delimiting
quotes characters around the values as these are numbers not text.

Finally, having a TEMP_SOFTWARE table is not a good design as its modelling
an attribute of the software as a table name. A single SOFTWARE table with a
column indicating that a row represents a temporary software item would be
better.

If you find the above difficult to follow my recommendation would be to do a
bit more groundwork on the fundamentals of database design in MS Access
before trying to be too ambitious in developing applications. I'm all for
'learning by doing', but it does require a foundation to build on. The
following is my standard response to enquiries about books on Access. You
might like to get you hands on the first two (I imagine John Viescas's book
will available in a 2007 version). While I can hopefully give you advice on
specific problems this will only really make sense when you have a reasonably
firm understanding of the underlying basics.


"Of the general purpose primers on Access I particularly like John L
Viescas's 'Running Microsoft Access' (Microsoft Press).

For an introduction to VBA programming in Access Evan Callahan's 'Microsoft
Access/Visual Basic Step by Step' (Microsoft Press) is easy to follow and,
while not taking things to a very high level, provides a solid basis on which
to build.

At a more advanced level the 'Access Developer's Handbook' by Paul Litwin,
Ken Getz and Mike Gunderloy (Sybex) covers the subject in great detail, and
contains a vast amount of useable code.

A useful and easy to read little book on the theoretical basis of the
database relational model is Mark Whitehorn and Bill Marklyn's 'Inside
Relational Databases With Examples in Access' (Springer).

For a highly authoritative but quite abstract explanation of the relational
model Chris Date's 'An Introduction to Database Systems' (Addison Wesley) has
for many years been regarded as a definitive work on the subject. Its by no
means an easy read, however.

For SQL Joe Celko's 'SQL for Smarties' (Morgan Kaufmann) is a wealth of
information on how to write queries. It deals with standard SQL, however,
and is not Access oriented. In fact Joe's views on Access do not bear
repetition where they might be read by people of a sensitive disposition.
Even so it is worth its weight in gold."

Ken Sheridan
Stafford, England

"mattyv" wrote:

> Thank you Ken !!
> is there a way to export all of the tables, forms, code to text in access
> 2007? the more i am working with access 2007 I am loving it! hard to learn
> though.. Matt

> Hello Ken, I am getting an error and it stops at " Dim cmd As ADODB.Command "
> I am not sure where to put the button control name.
>
> here are my database properties, thank you
>
> -----------------------------------------------
>
> Table: CLIENTS
> clientID Long Integer 4
> memberDate Date/Time 8
> fullname Text 255
> firstName Text 255
> LastName Text 255
>
> Table: MACHINE
> machineID Long Integer 4
> clientFK Long Integer 4
> softwareFK Text 255
> productFK Long Integer 4
> machineNotes Memo -
>
>
> Table: MACHINE_SOFTWARE
> machineSoftwareID Long Integer 4
> machineFK Long Integer 4
> softdCat Text 255
> softdSubCat Text 255
> softdType Text 255
> softdSubType Text 255
> title Text 255
> publisher Text 255
> url Text 255
> installed Yes/No 1
>
> Table: SOFTWARE
> softwareID Long Integer 4
> SoftCat Text 255
> softSubCat Text 255
> type Text 255
> subType Text 255
> title Text 255
> dateUpdated Date/Time 8
> publisher Text 255
> homepage Text 255
> version Text 255
> description Memo -
>
> Table: TEMP_SOFTWARE
> tempSoftwareID Long Integer 4
> machineSoftFK Long Integer 4
> SoftCat Text 255
> softSubCat Text 255
> type Text 255
> subType Text 255
> title Text 255
> dateUpdated Date/Time 8
> publisher Text 255
> homepage Text 255
> version Text 255
> description Memo -
> installed Yes/No 1
>
>
> FORMS
> (main form) MACHINE
> (subform in main form) MACHINEsoft
>
> List Boxes
> lstNotInstalled | InstalledSoftware
>
> Add button >> btnAddSoftware
> Remove Button << btnRemoveSoft
>

Hello Ken,
thank you for your outstanding advice.

I have learned quite a bit, alot from you. can you help me with one little
problem?
one error is keeping me. and that is deleting software from right. learning
about the DoCmd.RunSQL

Am I doing things right? thank you agian.

Private Sub Form_Current()

Me.lstInstalled.RowSource = "SELECT MACHINESOFTWARE.title FROM
MACHINESOFTWARE " & _
"WHERE MACHINESOFTWARE.client =
forms![MACHINE]!cboFullName"


Me.lstSoftware.RowSource = "SELECT tblSOFTWARE.title FROM tblSOFTWARE " & _
"WHERE tblSOFTWARE.title NOT IN " & _
"(SELECT MACHINESOFTWARE.title FROM
MACHINESOFTWARE " & _
"WHERE MACHINESOFTWARE.client =
forms![MACHINE]!cboFullName)"

End Sub

Private Sub lstInstall_Click()
If IsNull(Me.lstInstalled) And Not IsNull(Me.lstSoftware) Then

'Dim MyMessage
'MyMessage = MsgBox("This will add a new installation to this record.
Continue?", vbYesNoCancel)
'If MyMessage = vbYes Then

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone

With rs

.AddNew

!client = Forms!machine!cboFullName
!machineName = Forms!machine!machineName
!title = Me.lstSoftware

.Update

End With

Me.lstInstalled.Requery
Me.lstSoftware.Requery

Me.lstInstalled = Null
Me.lstSoftware = Null

Set rs = Nothing

End If
'Else: MsgBox "Please select only a software to install", vbOKOnly
'End If

End Sub

Private Sub lstRemove_Click()

If IsNull(Me.lstSoftware) And Not IsNull(Me.lstInstalled) Then

Dim MyMessage
MyMessage = MsgBox("This will delete the selected software's record from
this machine.  Continue?", vbYesNoCancel)
If MyMessage = vbYes Then

DoCmd.RunSQL "DELETE * FROM MACHINESOFTWARE WHERE MACHINESOFTWARE.client
=Forms!machine!cboFullName AND " & _
" MACHINESOFTWARE.machineName =Forms!machine!machineName AND " & _
" MACHINESOFTWARE.title =Forms!MACHINEsoft!lstInstalled "

Me.lstInstalled.Requery
Me.lstSoftware.Requery

Me.lstInstalled = Null
Me.lstSoftware = Null

End If
Else: MsgBox "Please select only a software to Uninstall", vbOKOnly
End If

End Sub

Again Thank you