box showing choices, select multiple choices for each record

[gcoaster]

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

[OTWarrior]

If you mean the user would enter the information, then it would be easy. If you mean for Access to read the computers for the programs, then as far as I know, you are out of luck.

For the previous option, why not have a table containing multiple tickboxes (one for each program) and have the computer as each new row. that way you can use the "2nd box" to select the program via a tickbox.

[Larry Larsen]

Hi
Your table structure is the important key in capturing the items of software associated with each PC/Unit..
I can see using some simple sql/vba/Update query to update your table with the appropriate data..

Structure Example:
tblSoftware
SoftwareID - Autonumber (pk)
SoftWareName - Text
Etc..

tblHardWare
HardwareID - Autonumber (pk)
HardwareNum - Text/Numeric
Etc..

jtblSoftHardware
SoftHardwareID - Autonumber (pk)
SoftwareID - Numeric (fk)
HardwareID - Numeric (fk)

This junction table will enable you to store any amount of software application to any PC unit..
Remember each software entry is a single record.

Here is a small example I have used/demo where I loop through the items of a list box and populate my junction table with my SF/HW id's..

On Error GoTo Err_cmdClose_Click
Dim db As DAO.Database, rs As DAO.Recordset
Dim strSQL As String, Criteria As String
Dim i As Integer
strSQL = "Delete * from Associated where HardwareID =" & [Forms]![frmSoftware]!txtHardwareID
'MsgBox strSQL

Set db = CurrentDb
db.Execute strSQL
strSQL = "Select * from Associated"
Set rs = db.OpenRecordset(strSQL)
For i = 0 To lstSoftwareRef.ListCount - 1
If lstSoftwareRef.Selected(i) = True Then
rs.AddNew
rs!HardwareID = txtHardwareID
rs!SoftwareID = lstSoftwareRef.Column(0, i)
rs.Update
End If
Next i

DoCmd.Close
Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click


Hope that help..
:hi:

[OTWarrior]

on your form, lets say that you have a set number of drop down boxes, when you choose an option, you change the value of the drop down

if me.dropdown1.text = "AVG" then
me.boolAVG = true


obviously this is very basic (a better way would be to use a select case, and to have both sets of values in an array), but should get you started.