multiselect listbox -> populates subform in a form in a form! ;-)

[gcoaster]

Hello!!
Love this forum. by the way, FishVal and

NeoPa 

are geniuses.

I have a question regarding = comboxes to multiple listbox selection populates subform. My goal is NOT to use macros, very little VBA if possible, and check boxes would be ideal or multiple selecting! eg crtl+click

Fictional Scenario:

lets say I am a computer guy, I go to clients house and install software.
I went to johns house and installed the fallowing
Avg | firefox | foxit
I uninstalled
Norton | adobe pdf

I have already figured out or was rather taught on here to
cascade Combobox categories filter to last box..

What I would LOVE to do is, that last box ( lstSOFTWARE),
is select software in lstSOFTWARE and what is selected entered in new record below in subform which is linked to tblSOFTWAREDETAILS.
and tblSOFTWAREDETAILS is linked to TBLMACHINE so when i
look at clients machine, i can see which software is installed.

FORMS
==MACHINE==
(Embedded )====SOFTWAREDETAILS

==SOFTWAREDETAILS
(Embedded )====SOFTWAREINSTALLED

SOFTWAREDETAILS has listboxes at the top
=lstCategory=lstSubCategory=lstType=lstSoftware
When items in lstSoftware are selected or checked ,
subform SOFTWAREINSTALLED with default view = DATASHEET
Adds what is checked in lstType!
Then linked to tblMACHINE


TABLE INFO
====tblSOFTWARE====

tblsoftwareID
softCat
softSubCat
softType
title
publisher
url
 

====tblSOFTWAREDETAILS====

softDetailsID
machineFK
softwareFK
Installed
softCat
softSubCat
softType
title
publisher
Url

===tblMACHINE==

tblMachine
clientFK
softwareFK
machineNotes
 

Thank you in advanced!
M@

[nico5038]

No complete solution, but you might check:
http://www.geocities.com/nico5038/x...Up-Down2000.zip
Just check the form "frmMoveLeftRight" and see that the listbox is based on a table with a YesNo field ("LeftRight") to determine in which listbox the data needs to appear.
Could be used for your software selection and has only little VBA code :-)

Nic;o)

[gcoaster]

Hello Nico,
Link not working
http://www.geocities.com/nico5038/xS...p-Down2000.zip

I found it, let me check it out.. THANK YOU!

HERE

[gcoaster]

No complete solution, but you might check:
http://www.geocities.com/nico5038/x...Up-Down2000.zip
Just check the form "frmMoveLeftRight" and see that the listbox is based on a table with a YesNo field ("LeftRight") to determine in which listbox the data needs to appear.
Could be used for your software selection and has only little VBA code :-)

Nic;o)

Nic, That is COOLEST feature I have seen so far!

and I got it to work!
only thing, I am trying to figure out how to apply what i select to EACH record.
in softwaredetails record for each client. and getting it to copy over to the right when double clicked or ctrl+click multiple and then > copy over

Here is the Code.

Private Sub btnRight_Click()
    If Not IsNull(Me.lbxLeft.Column(0, Me.lbxLeft.ListIndex)) Then
      CurrentDb.Execute "UPDATE SOFTWAREDETAILS SET LeftRight = True WHERE softDetailsID=" & 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 SOFTWAREDETAILS SET LeftRight = True;"
    Me.Refresh
End Sub
Private Sub btnLeft_Click()
    If Not IsNull(Me.lbxRight.Column(0, Me.lbxRight.ListIndex)) Then
      CurrentDb.Execute "UPDATE SOFTWAREDETAILS SET LeftRight = False WHERE softDetailsID=" & 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 SOFTWAREDETAILS SET LeftRight = False;"
    Me.Refresh
End Sub

LEFT BOX rowsource

SELECT SOFTWAREDETAILS.softDetailsID, SOFTWAREDETAILS.softdCat, SOFTWAREDETAILS.softdSubCat, SOFTWAREDETAILS.softdType, SOFTWAREDETAILS.title, SOFTWAREDETAILS.LeftRight FROM SOFTWAREDETAILS WHERE (((SOFTWAREDETAILS.LeftRight)=False)); 
 

RIGHT BOX rowsource

SELECT SOFTWAREDETAILS.softDetailsID, SOFTWAREDETAILS.softdCat, SOFTWAREDETAILS.softdSubCat, SOFTWAREDETAILS.softdType, SOFTWAREDETAILS.title, SOFTWAREDETAILS.LeftRight FROM SOFTWAREDETAILS WHERE (((SOFTWAREDETAILS.LeftRight)=True)); 

nothing is bound to anything right now

SOFTWAREDETAILS is going to sit in MACHINE form.
what is LeftRight Y/N is then applied to NEW record in other table. i think!
i am understanding this slowly..
oh, and Nico5038 you are uber genius!

[nico5038]

Yep, wrong tags used for the link, but corrected.

The left listbox uses the same table as the right, but filters for the YN field to be "True", while the right listbox filters for the YN field to be "False".
Switching the YN field's value with the UPDATE query will "move" the row from one listbox to the other.

Guess you'll now also understand how moving all is just a simple UPDATE without limiting the value to one row. In your case however (as it's related to one machine), in both Updates (row and all) you'll need to add the machineFK in the criteria! Else all rows of all machines are moved.

As you have the [Installed] field, I guess this can be used instead of the [LeftRight].

Nic;o)

[gcoaster]

OMG, my brain hurts..

[nico5038]

First determine or you want to record all software for each machine. (Also the not used).
When that's the case the application will be "easy", but require a lot of space.

When just the installed software will be recorded, then you'll need a "trick" to manage the MachineDetails. You'll have to create a temp table for the subform with all available software. You fill the temptable with all software from the tblSoftware and next update the "Installed" field in the temptable from the MachineDetails.

When the user indicates (s)he is ready, then first delete all MachineDetails and next append the Machinedetails with the temptable rows that are "True".
When the user Cancels the update, you can just close the form without any further rocessing.

Hmmm, guess you need a big vacation now <LOL>

Nic;o)

[gcoaster]

Here is what I have so far. trying to get the software titles in the left box now, to the right!

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 LeftRight = Yes WHERE softDetailsID=" & Me.lbxLeft.Column(0, Me.lbxLeft.ListIndex) & ";"
      Me.Refresh
    Else
      MsgBox "Please select an entry MATT!"
    End If
End Sub
 
Private Sub btnRightAll_Click()
    CurrentDb.Execute "UPDATE MACHINE_SOFTWARE SET LeftRight = Yes;"
    Me.Refresh
End Sub
Private Sub btnLeft_Click()
    If Not IsNull(Me.lbxRight.Column(0, Me.lbxRight.ListIndex)) Then
      CurrentDb.Execute "UPDATE MACHINE_SOFTWARE SET LeftRight = No WHERE softDetailsID=" & Me.lbxRight.Column(0, Me.lbxRight.ListIndex) & ";"
      Me.Refresh
    Else
      MsgBox "Please select an entry From the Right.. MATT!"
    End If
End Sub
 
Private Sub btnLeftAll_Click()
    CurrentDb.Execute "UPDATE MACHINE_SOFTWARE SET LeftRight = No;"
    Me.Refresh
End Sub
 
Private Sub lstCat_AfterUpdate()
   With Me![lstSubCat]
        If IsNull(Me!lstCat) Then
        .RowSource = ""
        Else
        .RowSource = "SELECT DISTINCT softSubCat " & _
                "FROM SOFTWARECAT " & _
                "WHERE SOFTWARECAT.softCat= '" & Me!lstCat & "' " & _
                "ORDER BY SOFTWARECAT.softSubCat;"
        End If
    Call .Requery
    End With
End Sub
 
Private Sub lstSubCat_AfterUpdate()
   With Me![lstType]
        If IsNull(Me!lstSubCat) Then
        .RowSource = ""
        Else
        .RowSource = "SELECT DISTINCT softType " & _
                "FROM SOFTWARECAT " & _
                "WHERE SOFTWARECAT.softSubCat= '" & Me!lstSubCat & "' " & _
                "ORDER BY SOFTWARECAT.softType;"
        End If
    Call .Requery
    End With
End Sub
 
Private Sub lstType_AfterUpdate()
  With Me![lbxLeft]
        If IsNull(Me!lstType) Then
        .RowSource = ""
        Else
        .RowSource = "SELECT DISTINCT title " & _
                "FROM SOFTWARE " & _
                "WHERE SOFTWARE.type= '" & Me!lstType & "' " & _
                "ORDER BY SOFTWARE.title;"
        End If
    Call .Requery
    End With
End Sub

If I can figure out how to move them now to lbxRight
then its done!
but how do I bind this? link to table?
I have been using VBA for a week now, its starting to make sense!

thank you

[nico5038]

First determine or you want to record all software for each machine. (Also the not used).
When that's the case the application will be "easy", but require a lot of space.

When just the installed software will be recorded, then you'll need a "trick" to manage the MachineDetails. You'll have to create a temp table for the subform with all available software. You fill the temptable with all software from the tblSoftware and next update the "Installed" field in the temptable from the MachineDetails.

When the user indicates (s)he is ready, then first delete all MachineDetails and next append the Machinedetails with the temptable rows that are "True".
When the user Cancels the update, you can just close the form without any further rocessing.

Hmmm, guess you need a big vacation now <LOL>

Nic;o)

You're learning VBA the "fast way" I see, but before coding you'll need to grasp the idea posted in my previous comment.
I would like to propose to work with the temptable approach, but the choice is up to you :-)

Nic;o)

[gcoaster]

Hello Nico,
Thank you for your help and time, I appreciate it.
yea its the fast way, i hear you.

I am just trying to get things to work in minimal time.

I didn't know entering this 2 weeks ago i was going to run into heavy vba programing to get functionality.

the reason for all of this is right now everything is in outlook + bcm,
I was trying to move to access.

and if you have experience with outlook + bcm you know what im talking about!

[gcoaster]

First determine or you want to record all software for each machine. (Also the not used).
When that's the case the application will be "easy", but require a lot of space.

Right now all software is in tblSOFTWARE
there are about 30 items

When just the installed software will be recorded, then you'll need a "trick" to manage the MachineDetails. You'll have to create a temp table for the subform with all available software. You fill the temptable with all software from the tblSoftware and next update the "Installed" field in the temptable from the MachineDetails.

i think i tried that, i used a query 

When the user indicates (s)he is ready, then first delete all MachineDetails and next append the Machinedetails with the temptable rows that are "True".
When the user Cancels the update, you can just close the form without any further rocessing.
Hmmm, guess you need a big vacation now <LOL>
Nic;o)

I just spent my vacation trying to set this up! LOL
i think i am just going to go with MySQL and PHP
soooo much easier and free! we like free. 
thank you NICO! 
Matt
 

[nico5038]

Guess to get this in PHP will require also some effort, but at least you can run it "webbased" :-)

Let me know when you need more Access assistance !

Nic;o)

[gcoaster]

Guess to get this in PHP will require also some effort, but at least you can run it "webbased" :-)

Let me know when you need more Access assistance !

Nic;o)

Hello! your on!
naw, im in this too deep to just quit! just grumpy this morning.
i read what you posted and it took about 5 minutes to sink in, now i understand more!

ok, could you tell me how many tables i need?
i have

tbleSOFTWARE | lists all software softwareID,cat,subcat,type,title
tbleMACHINE | clients machine with machineID,clientFK,softwareFK
tbleMACHINE_SOFTWARE | machinesoftwareID,  machineFK, softwareFK,cat,subcat,title

Yea i know what you are saying when you say it will be huge! now i understand!
could tbleMACHINE_SOFTWARE just have installed yes/no? instead of

tbleMACHINE_SOFTWARE | machinesoftwareID,  machineFK, softwareFK,cat,subcat,title

like so?

machinesoftwareID,  machineFK, softwareFK,installed

I need to figure out how to easily go to clients machine
and add each software title they have installed. with check boxes or your left to right cool solution. halp!
thank you
Matt

[nico5038]

No thanks needed, we're here to help.

OK, we'll adapt the "temptable" solution. The nice part of this is the fact that you don't have to change the tbleMACHINE_SOFTWARE. Basically because when there's a row that indicates "Yes".

We'll have to start with building the temptable with all SoftwareID's from your available software table.
As this tblTempSoftware is used and re-used we start with a delete * from tblTempSoftware to make sure it's empty and then we fill it by using an append query.
The table needs to hold all Software information needed plus the additional SelectedYN field, that will be filled with "False" by default.

Next we need to use an update query using the MachineID and the SoftwareID from tbleMACHINE_SOFTWARE to set the proper SelectedYN fields to "True".

Now we're in business and are able to show the Machine and the two listboxes based on my sample .mdb.

Just try to create this and use the above description to build the table, the needed queries and the form with the "left-right" buttons.

Let me know where you get stuck, or where my description isn't clear..

Nic;o)

[gcoaster]

Hello Nic;o)

QUERIES

As this tblTempSoftware is used and re-used we start with a delete * from tblTempSoftware to make sure it's empty and then we fill it by using an append query.

Where do I add this?

delete * from tblTempSoftware

Create a update query using the MachineID and the SoftwareID from tbleMACHINE_SOFTWARE
to set the proper SelectedYN fields to "True".

I kind of understand.

[nico5038]

Easiest is to code in the procedure:

currentdb.execute ("delete * from tblTempSoftware")

Thus the query is executed directly.

Nic;o)

[gcoaster]

Easiest is to code in the procedure:
currentdb.execute ("delete * from tblTempSoftware")
Thus the query is executed directly.
Nic;o)

Do you have any examples?

[nico5038]

Start to create the form and the two listboxes based on the temptable.
In the OnCurrent event of the form this code can be added.

Nic;o)

[gcoaster]

Hello nico5038,
I am back at it,
I am having a time getting this to work, here is what I understand so far.

FORM= MACHINE_soft
On Form Load
Delete all contents of TEMP_SOFTWARE

LeftBox
SOURCE = update query called qLeftBoxUpdate
using the MachineID and the SoftwareID from tbleMACHINE_SOFTWARE to set the proper SelectedYN fields to "True".


When in doubt ask!

Thank you
Best Regards,
gcoaster

[nico5038]

Small addition/change to your pseudo code:

FORM= MACHINE_soft

On Form Load
Delete all contents of TEMP_SOFTWARE
Fill TEMP_SOFTWARE with all software from your Software table
TEMP_SOFTWARE <= update query called qLeftBoxUpdate

LeftListBox
Source is TEMP_SOFTWARE with SelectedYN fields to "False".

RightListBox
Source is TEMP_SOFTWARE with SelectedYN fields to "True".

Create buttons like in my samle to manipulate the SelectedYN field

Create [Save] and [Cancel] button.

Just try to describe what needs to be done in the button's code...

Nic;o)

[gcoaster]

Thank you Nico,
Now we are talking the same language !
Awesome, I think the language you are using should replace VBA
how about we call it NBA

I figured out the Delete all from temp_table On Current

Private Sub Form_Current()
CurrentDb.Execute ("delete * from TEMP_SOFTWARE")
End Sub

I need to figure out where to place this

Fill TEMP_SOFTWARE with all software from your Software table
TEMP_SOFTWARE <= update query called qLeftBoxUpdate

onload in event tab on the right?
After update in Event?
Fill TEMP_SOFTWARE with all software from your Software table
TEMP_SOFTWARE <= update query called qLeftBoxUpdate

Or do these two go in Data tab in row source on the right tab in design view?

Almost there, thanks to you!
gcoaster

[nico5038]

When you allow the users to navigate through the forms, the OnCurrent location would have to be used.
Personally I offer one form and a user needs to [Save] or [Cancel] the changes before he can select another MainObject to manipulate. In that case it can be in the OnOpen event. This principle is called "Object/Action" and is teh same as you work with the Access database window: 1) Select a form 2) Act (Update/Delete/etc.)
Check my sample at:
http://www.geocities.com/nico5038/xS...tAction-97.zip

Nic;o)

[gcoaster]

Hello Nico,

So the code goes here?

Private Sub Form_Open(Cancel As Integer)
End Sub

what does the code look like? it is calling the update query right?

[nico5038]

Use the On Current event, not the On Open..
Thus even when moving through the records the data will be refreshed.

Nic;o)

[gcoaster]

On Current is doing this

Private Sub Form_Current()
CurrentDb.Execute ("delete * from TEMP_SOFTWARE")
End Sub

Add another line in the code right?

Private Sub Form_Current()
CurrentDb.Execute ("delete * from TEMP_SOFTWARE")
HERE
End Sub

Why did you attach the 97 database? there was nothing relevant to this in the code, the only thing near to "on current" or load was this.

Private Sub Form_Activate()
Me.Refresh
End Sub

How do I add this to the code?

Fill TEMP_SOFTWARE with all software from your Software table
TEMP_SOFTWARE <= update query called qLeftBoxUpdate

[nico5038]

The HERE is indeed the location to aad the code.

=> Fill TEMP_SOFTWARE with all software from your Software table
is done with an Append query you can create the query and execute is with a "DoCmd" or "Currentdb.Exceute" (Check the helpfile (F1) for the syntax)

=> TEMP_SOFTWARE <= update query called qLeftBoxUpdate
is also done with a query and see above for the options.

Nic;o)

[gcoaster]

NO WAY!!!!!
I created the Query to update and then right clicked in design view.
I noticed the view SQL option.. so I took a look and saw the code!
I copied the SQL code into VBA souce view and worked it till it worked!!!

Here is the code, this code deletes all of the data form the table and then populates it from another table, that table is SOFTWARE

Private Sub Form_Current()
 
Dim dbs As DAO.Database
Dim sSQL As String
Set dbs = CurrentDb()
' 1) Delete and then update records
sSQL = "INSERT INTO TEMP_SOFTWARE ( title )SELECT SOFTWARE.title FROM SOFTWARE"
dbs.Execute sSQL
End Sub

I got the idea from here.

http://www.thescripts.com/forum/thread436933.html

[gcoaster]

Nico,
What do I do with this?

=> TEMP_SOFTWARE <= update query called qLeftBoxUpdate
is also done with a query and see above for the options.

Do I add it to what I have?

Where is it loaded?

[gcoaster]

Almost got it working,
need to figure out how to update machine_software by what is selected in TEMP_SOFTWARE

Here is what I have already.

TABLES
[1] TEMP_SOFTWARE
[2] MACHINE_SOFTWARE ( Table that holds software installed on machine, "title" and "installed" if I can get the data there! )

FORM
[1] MACHINEsoft ( subform of MACHINE )

here is the VBA

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= True 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 = False 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

[nico5038]

You're close, but you can "shrink" the code when you set the Installed field to "FAlse" by default in the INSERT, then only the found Software needs to be UPDATEd to "True" like:

currentdb.execute ("UPDATE TEMP_SOFTWARE SET installed = True WHERE softwareID IN (Select SoftwareID from MACHINE_SOFTWARE where MachineID=" & Me.MachineID & ");")
 

Getting the idea ?

Nic;o)

[gcoaster]

oh man,, after 4 hours i give...

currentdb.execute ("UPDATE TEMP_SOFTWARE SET installed = True WHERE softwareID IN (Select SoftwareID from MACHINE_SOFTWARE where MachineID=" & Me.MachineID & ");")

Where does it go?

Private Sub btnRight_Click()
Private Sub btnRightAll_Click()
Private Sub btnLeft_Click()
Private Sub btnLeftAll_Click()
Private Sub Form_Current()

[gcoaster]

You're close, but you can "shrink" the code.. in the INSERT

Here?

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

[nico5038]

Yep, after line 8 as first the tables needs to be filled and than the appropriate software needs to be checked :-)

Nic;o)

[gcoaster]

I am getting an error on the last part with Me.machineID

Private Sub Form_Current()
    CurrentDb.Execute "DELETE FROM TEMP_SOFTWARE"
    CurrentDb.Execute ("UPDATE TEMP_SOFTWARE SET installed = True WHERE softwareID IN (Select softwareID from MACHINE_SOFTWARE where machineID=" & Me.machineID & " ) ; ")
    Me.Refresh
End Sub

[nico5038]

Change Me.machineID into Me.machineSoftwareID.

Always make sure that the used names are the names as used in your tables and forms !

Nic;o)

[gcoaster]

Hello Nicco,
Is there anything I can post to you that would clear up where I am messing up?
the place I am stuck is trying to figure out how to link what is in the left box to MACHINE_SOFTWARE. thank you

Tried
MACHINE_SOFTWARE where machineID=" & Me.machineSoftwareID & ");")
thank you Nicco.

[nico5038]

Sure that the field is the problem ?
Access will change the background color of the error line and the query looks OK...

Nic;o)

[gcoaster]

Sure that the field is the problem ?
Access will change the background color of the error line and the query looks OK...

Yea I know that, using a small screen capture when taking snapshot code focus toggles off.

[nico5038]

It's a problem because your subform selects only two fields and not the needed MACHINE_SOFTWARE_ID.
Best to set the controlsource of the form to the table: MACHINE_SOFTWARE instead of using the default query Access creates.

Nic;o)

[gcoaster]

It's a problem because your subform selects only two fields and not the needed MACHINE_SOFTWARE_ID.

Should I go ahead and add the " MACHINE_SOFTWARE_ID "?

Best to set the controlsource of the form to the table: MACHINE_SOFTWARE instead of using the default query Access creates.

The controlsource of which form? Are you still talking about the subform? controlsource is not a option, I see Record Source of the subform form properties.

How does

currentdb.execute ("UPDATE TEMP_SOFTWARE SET installed = True WHERE softwareID IN (Select SoftwareID from MACHINE_SOFTWARE where MachineID=" & Me.MachineID & ");")

Replace

sSQL = "INSERT INTO TEMP_SOFTWARE ( title )SELECT SOFTWARE.title FROM SOFTWARE"
dbs.Execute sSQL
Call .Requery

Thank you

[nico5038]

It should be the recordsource of the subform.

Both code samples will achieve the same. The last one will however trigger a warning message, as the first will suppress this....

Nic;o)

[gcoaster]

I don't understand!
I really dont

[nico5038]

Just try the sample code and give feedback when (and where) stuck with a copy/paste of the used code and the problem you ran into.

Nic;o)

[gcoaster]

Hello Nico,
I copped out and took a simpler route. now I am stuck again! haha
( think i may go back to waiting on tables after all of this )

here is the code. the problem I get is removing from the right box but EVERYTHING else works!!! amazing.. almost there.

I changed the name of

SOFTWARE table to tblSOFTWARE
MACHINE_SOFTWARE to MACHINESOFTWARE

 
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