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 tblSOFTWAREDETA ILS.
and tblSOFTWAREDETA ILS is linked to TBLMACHINE so when i
look at clients machine, i can see which software is installed.

FORMS
==MACHINE==
(Embedded )====SOFTWAREDE TAILS

==SOFTWAREDETAI LS
(Embedded )====SOFTWAREIN STALLED

SOFTWAREDETAILS has listboxes at the top
=lstCategory=ls tSubCategory=ls tType=lstSoftwa re
When items in lstSoftware are selected or checked ,
subform SOFTWAREINSTALL ED with default view = DATASHEET
Adds what is checked in lstType!
Then linked to tblMACHINE


TABLE INFO
====tblSOFTWARE ====

tblsoftwareID
softCat
softSubCat
softType
title
publisher
url

====tblSOFTWARE DETAILS====

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.c om/nico5038/x...Up-Down2000.zip
Just check the form "frmMoveLeftRig ht" 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/xSample-Listbox-Datasheet-Up-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 "frmMoveLeftRig ht" 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_SOF TWARE 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_SOF TWARE. 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_SOF TWARE 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_SOF TWARE
to set the proper SelectedYN fields to "True".

I kind of understand.