box showing choices, select multiple choices for each record (1 Viewer)

[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

 

[ajetrumpet]

The Subform shows me the entire list of software titles in one box.
Box 2 shows me which titles have been installed.

What kind of boxes are they?

 

[gcoaster]

Thank you Adam for asking,

ComboBox

 

[ajetrumpet]

I am trying to make a subform linked to each Computer

A subform displaying details about a record in the main form?

The Subform shows me the entire list of software titles in one box. Box 2 shows me which titles have been installed.

You will have to write two rowsources for each combo box for this, assuming the software list is in one table and installation records for each user are in another table.

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.

Update what? The combo box? Update the box whenever something new has been installed?

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

A great way to do this is filtering the first box based on the records that fill the second box. So, like this...

[u]**Second Box Rowsource (Installed Software Names)**[/u]

SELECT FieldThatHoldsInstalledSoftwareNames
    
    FROM TableThatHoldsInstallationRecords

WHERE UserNameField = MainFormUserNameControl

[u]**First Box Rowsource (Software Names Not Installed)**[/u]

SELECT SoftwareListFieldName

    FROM SoftwareListTable (you will probably need an INNER JOIN here too)

  WHERE SoftwareListFieldName NOT IN

(SELECT FieldThatHoldsInstalledSoftwareNames
    
    FROM TableThatHoldsInstallationRecords

WHERE UserNameField = MainFormUserNameControl);

To update the boxes, you will have to have the relevant data in the tables already present. If you have a table that holds installation records (with one software title per record), then you can run an append query (with criteria selected on your form - e.g. software names) on your form from a button of somekind, to get the data into the table.

After that is done, all you have to do to update the Combo Boxes is type "Me.ComboName.Requery" into a form event of your choice to get the new RowSource data.

 

[gcoaster]

Thank you!

Thank you Adam, I am going to give it a shot right now,.. I kinda understand.

Best Regards,
Matt

 

[gcoaster]

Trouble

I almost got it I think.. in too deep now to quite! been working on this for a month.. halp! One thing i have learned.. you have to be a full on programmer to make things really work.. here is my code,

Option Compare Database
Option Explicit
Private Sub btnRight_Click()
If Not IsNull(Me.lbxLeft.Column(0, Me.lbxLeft.ListIndex)) Then
CurrentDb.Execute "UPDATE MACHINE_SOFTWARE SET installed=Yes WHERE machineSoftwareID=" & Me.lbxLeft.Column(0, Me.lbxLeft.ListIndex) & ";"
Me.Refresh
Else
MsgBox "Please select an entry"
End If
End Sub
Private Sub btnRightAll_Click()
CurrentDb.Execute "UPDATE TEMP_SOFTWARE SET installed=Yes;"
Me.Refresh
End Sub
Private Sub btnLeft_Click()
If Not IsNull(Me.lbxRight.Column(0, Me.lbxRight.ListIndex)) Then
CurrentDb.Execute "UPDATE TEMP_SOFTWARE SET installed=Yes WHERE softwareID=" & Me.lbxRight.Column(0, Me.lbxRight.ListIndex) & ";"
Me.Refresh
Else
MsgBox "Please select an entry"
End If
End Sub
Private Sub btnLeftAll_Click()
CurrentDb.Execute "UPDATE TEMP_SOFTWARE SET installed=No;"
Me.Refresh
End Sub
Private Sub Form_Current()
Dim dbs As DAO.Database
Dim sSQL As String
Set dbs = CurrentDb()
' 1) Delete and then update records
sSQL = "DELETE FROM TEMP_SOFTWARE"
dbs.Execute sSQL
sSQL = "INSERT INTO TEMP_SOFTWARE ( title )SELECT SOFTWARE.title FROM SOFTWARE"
dbs.Execute sSQL
Me.Refresh
End Sub

 

[ajetrumpet]

One thing i have learned.. you have to be a full on programmer to make things really work

I am not a programmer, but yet I am helping you with this. What do you say about this now?? Still convinced that you have to be a programmer to do this??

Also, if you can, explain to me (in very short terms) what you want each section of this code of yours to do. It's pretty difficult, and much too time consuming to read many blocks like this that have just been posted without any explanation. I can read this code of yours, but it will save you a heck of a lot of time by describing briefly what each block's outcome should be.

I will put a small sample together for you, which might be able to help you follow what I said. The following is just a copy of your code, posted so I could read it better...

Option Compare Database
Option Explicit
Private Sub btnRight_Click()
    If Not IsNull(Me.lbxLeft.Column(0, Me.lbxLeft.ListIndex)) Then
    CurrentDb.Execute "UPDATE MACHINE_SOFTWARE SET installed=Yes WHERE machineSoftwareID=" & Me.lbxLeft.Column(0, Me.lbxLeft.ListIndex) & ";"
    Me.Refresh
    Else
      MsgBox "Please select an entry"
    End If
End Sub
Private Sub btnRightAll_Click()
    CurrentDb.Execute "UPDATE TEMP_SOFTWARE SET installed=Yes;"
    Me.Refresh
End Sub
Private Sub btnLeft_Click()
    If Not IsNull(Me.lbxRight.Column(0, Me.lbxRight.ListIndex)) Then
    CurrentDb.Execute "UPDATE TEMP_SOFTWARE SET installed=Yes WHERE softwareID=" & Me.lbxRight.Column(0, Me.lbxRight.ListIndex) & ";"
    Me.Refresh
    Else
      MsgBox "Please select an entry"
    End If
End Sub
Private Sub btnLeftAll_Click()
    CurrentDb.Execute "UPDATE TEMP_SOFTWARE SET installed=No;"
    Me.Refresh
End Sub
Private Sub Form_Current()
Dim dbs As DAO.Database
    Dim sSQL As String
    Set dbs = CurrentDb()
    ' 1) Delete and then update records
    sSQL = "DELETE FROM TEMP_SOFTWARE"
    dbs.Execute sSQL
    sSQL = "INSERT INTO TEMP_SOFTWARE ( title )SELECT SOFTWARE.title FROM SOFTWARE"
    dbs.Execute sSQL
    Me.Refresh
End Sub

 

[ajetrumpet]

Coaster,

What I've done is just added a new record based on combo box selections (see attached - A2007 format). I completely dropped the append process because of the difficulty of working with table joins. I have no idea how yours is set up either, but I really can't think of any setup that wouldn't comply with what I have done here. Take a look at the button code, and the code for "OnCurrent" event in the subform.

Hopefully, you can read it, and follow it. I tabbed it out the best I could. Question, just post back, unless of course I've scared you off by now.

BTW, you might want to change the combos to list boxes if you're going to want to multi-select. Were you thinking about that in the beginning??

Also, what I've gathered from your code above is that you have a client table with all the softwares listed and a checkbox for weather or not they have been installed. Does that sound right??

 

[gcoaster]

Hello Adam!
thank you very very much!
I am looking at it now and no matter how much my brain hurts, no way am i giving up! Lord willing of course.. there is a error on the right to left but this looks like IT!!!!
its midnight here, but tomorrow i am so at this. thank you ADAM! you just made a friend.

Best Regards,
Matt

 

[ajetrumpet]

Welcome!

Good luck to ya.

 

[gcoaster]

Hello Adam,
I am kinda understanding this..
What would I have to change to do the fallowing?

[1]
Get items from the RIGHT to the LEFT?
in other words, if I uninstalled something

Attached is the original database.

Thank you!!!!
gcoaster

 

[ajetrumpet]

I looked at the original you posted Coaster, but all of your code is the same as you wrote in your "trouble" post. That's really the reason I just used my sample to show you this instead of your actual database. Are you planning changing it? Do you understand what I did in the first version of my sample? Also, you have listboxes in your form, not combos.

I am assuming you are going to try to put all of this together when you get the complete picture? Anyway, here is the second sample. I inserted a DoCmd.RunSQL command that has the following code...

DELETE * FROM SoftwareTable (CONDITIONS HERE)

So that's how you go about doing that part of it. Why don't you post up again when you get your original file working like this? I'd like to see it.

If you have any questions about the different parts of code that are written, just ask.

BTW, I put the message boxes in to prevent me from experimenting with the combo boxes unconditionally. You can ignore those...

 

[gcoaster]

Dude...
you rock.

I would have NEVER guessed this part ( Well eventually after studying VBA for a year )

DoCmd.RunSQL "DELETE * FROM Installations WHERE Installations.client = Forms!frmclients!client AND " & _
"Installations.computer = Forms!frmclients!computer AND " & _
"Installations.installedsoftware = Forms!frmclients![installations subform].Form!cboinstalled"

 

[ajetrumpet]

thank you thank you thank you..
Thank the Lord for people like you.

Man, I don't know if I can take that many "thank you" notes. But, you're welcome anyway.

 

[gcoaster]

Edit

Man, I don't know if I can take that many "thank you" notes. But, you're welcome anyway.

I turned the COMBO BOXES to LIST BOXES,

What edit to the code would I have to add or remove to multiselect more then one?
and of course delete more then one or clear all.

thank you Adam.
gCoaster

PS

Also learned how to get rid of the warning message on delete from the right by adding setwarnings false

eg

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM Installations WHERE Installations.client = Forms!frmclients!client AND " & _
"Installations.computer = Forms!frmclients!computer AND " & _
"Installations.installedsoftware = Forms!frmclients![installations subform].Form!cboinstalled"

 

[gcoaster]

Problem Deleting from installed software

Hello Adam,
cant quite get this to work. almost

I am getting a popup asking for a parameter value when trying to remove software from lstInstalled. ( right List Box )

I have tried everything , it seems it cannot pick up the value from the main form = Forms![MACHINE]![cboFullName].


here is the code




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

Thank you

 

[ajetrumpet]

I am getting a popup asking for a parameter value when trying to remove software from lstInstalled. ( right List Box )

How many values are you selecting? If it's just one, then most likely, your reference syntax is incorrect. That's the most common cause of the error. It would help if you could post another file, because the first one that you posted didn't have anything done in it. It was in the making, remember?

Also, does the process you're referring to work if you run it the other way around (INSTALLING software)? If it does, then there is definately a syntax error present. Unless of course you've corrupted the file already, which I can't imagine...

 

[gcoaster]

Installing works like a charm. I would post the database but its HUGE!!!
the last one i had to dissect and then post part..

I THINK it may have something to do with the relationship?
how should the MACHINE and MACHINESOFTWARE be related?

tblMACHINE has | machineID Autonumber as primary Key
MACHINESOFTWARE table has | machineSoftID Autonumber as Primary Key

I dont have them linked,

the MACHINE form has a drop down that lists the clients from the CLIENT table
SELECT [CLIENTS].[clientID], [CLIENTS].[fullname] FROM CLIENTS ORDER BY [fullname];

and the form MACHINE source is

SELECT tblMACHINE.*, CLIENTS.fullname FROM CLIENTS INNER JOIN tblMACHINE ON CLIENTS.clientID=tblMACHINE.clientFK;

could this have something to do with it?

All i think i need is VBA that gets value from Forms!machine!cboFullName
and title from MACHINESOFTWARE and then deletes it...

thank you
gcoaster

 

[gcoaster]

Snapshot of issue

here are 3 snapshots of the Issue..

 

[ajetrumpet]

Coaster, what difference do you notice in the following pieces of code...??

DoCmd.RunSQL "DELETE * FROM Installations WHERE 

Installations.client = Forms!frmclients!client AND " & _

"Installations.computer = Forms!frmclients!computer AND " & _

"[COLOR="Red"][B]Installations.installedsoftware = Forms!frmclients![installations subform].Form!cboinstalled[/B][/COLOR]"

DoCmd.RunSQL "DELETE * FROM MACHINESOFTWARE WHERE 

MACHINESOFTWARE.client = Forms![MACHINE]![cboFullName] AND " & _

"MACHINESOFTWARE.machineName = Forms![MACHINE]!machineName AND " & _

"[COLOR="Red"][B]MACHINESOFTWARE.title = Forms![MACHINEsoft]![lstInstalled][/B][/COLOR]"

Is the form that holds your list boxes still a subform? Did you forget to write the correct syntax for this? That would certainly explain a popup...