By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,903 Members | 2,075 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,903 IT Pros & Developers. It's quick & easy.

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

gcoaster
P: 91
Hello!!
Love this forum. by the way, FishVal and
Expand|Select|Wrap|Line Numbers
  1. 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====
Expand|Select|Wrap|Line Numbers
  1. tblsoftwareID
  2. softCat
  3. softSubCat
  4. softType
  5. title
  6. publisher
  7. url
  8.  
====tblSOFTWAREDETAILS====
Expand|Select|Wrap|Line Numbers
  1. softDetailsID
  2. machineFK
  3. softwareFK
  4. Installed
  5. softCat
  6. softSubCat
  7. softType
  8. title
  9. publisher
  10. Url
===tblMACHINE==
Expand|Select|Wrap|Line Numbers
  1. tblMachine
  2. clientFK
  3. softwareFK
  4. machineNotes
  5.  
Thank you in advanced!
M@
Oct 12 '07 #1
Share this Question
Share on Google+
43 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Oct 12 '07 #2

gcoaster
P: 91
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
Oct 12 '07 #3

gcoaster
P: 91
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnRight_Click()
  2.     If Not IsNull(Me.lbxLeft.Column(0, Me.lbxLeft.ListIndex)) Then
  3.       CurrentDb.Execute "UPDATE SOFTWAREDETAILS SET LeftRight = True WHERE softDetailsID=" & Me.lbxLeft.Column(0, Me.lbxLeft.ListIndex) & ";"
  4.       Me.Refresh
  5.     Else
  6.       MsgBox "Please select an entry"
  7.     End If
  8. End Sub
  9.  
  10. Private Sub btnRightAll_Click()
  11.     CurrentDb.Execute "UPDATE SOFTWAREDETAILS SET LeftRight = True;"
  12.     Me.Refresh
  13. End Sub
  14. Private Sub btnLeft_Click()
  15.     If Not IsNull(Me.lbxRight.Column(0, Me.lbxRight.ListIndex)) Then
  16.       CurrentDb.Execute "UPDATE SOFTWAREDETAILS SET LeftRight = False WHERE softDetailsID=" & Me.lbxRight.Column(0, Me.lbxRight.ListIndex) & ";"
  17.       Me.Refresh
  18.     Else
  19.       MsgBox "Please select an entry"
  20.     End If
  21. End Sub
  22.  
  23. Private Sub btnLeftAll_Click()
  24.     CurrentDb.Execute "UPDATE SOFTWAREDETAILS SET LeftRight = False;"
  25.     Me.Refresh
  26. End Sub
LEFT BOX rowsource
Expand|Select|Wrap|Line Numbers
  1. SELECT SOFTWAREDETAILS.softDetailsID, SOFTWAREDETAILS.softdCat, SOFTWAREDETAILS.softdSubCat, SOFTWAREDETAILS.softdType, SOFTWAREDETAILS.title, SOFTWAREDETAILS.LeftRight FROM SOFTWAREDETAILS WHERE (((SOFTWAREDETAILS.LeftRight)=False)); 
  2.  
RIGHT BOX rowsource
Expand|Select|Wrap|Line Numbers
  1. 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!
Oct 12 '07 #4

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 12 '07 #5

gcoaster
P: 91
OMG, my brain hurts..
Oct 12 '07 #6

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 12 '07 #7

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

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub btnRight_Click()
  5.     If Not IsNull(Me.lbxLeft.Column(0, Me.lbxLeft.ListIndex)) Then
  6.       CurrentDb.Execute "UPDATE MACHINE_SOFTWARE SET LeftRight = Yes WHERE softDetailsID=" & Me.lbxLeft.Column(0, Me.lbxLeft.ListIndex) & ";"
  7.       Me.Refresh
  8.     Else
  9.       MsgBox "Please select an entry MATT!"
  10.     End If
  11. End Sub
  12.  
  13. Private Sub btnRightAll_Click()
  14.     CurrentDb.Execute "UPDATE MACHINE_SOFTWARE SET LeftRight = Yes;"
  15.     Me.Refresh
  16. End Sub
  17. Private Sub btnLeft_Click()
  18.     If Not IsNull(Me.lbxRight.Column(0, Me.lbxRight.ListIndex)) Then
  19.       CurrentDb.Execute "UPDATE MACHINE_SOFTWARE SET LeftRight = No WHERE softDetailsID=" & Me.lbxRight.Column(0, Me.lbxRight.ListIndex) & ";"
  20.       Me.Refresh
  21.     Else
  22.       MsgBox "Please select an entry From the Right.. MATT!"
  23.     End If
  24. End Sub
  25.  
  26. Private Sub btnLeftAll_Click()
  27.     CurrentDb.Execute "UPDATE MACHINE_SOFTWARE SET LeftRight = No;"
  28.     Me.Refresh
  29. End Sub
  30.  
  31. Private Sub lstCat_AfterUpdate()
  32.    With Me![lstSubCat]
  33.         If IsNull(Me!lstCat) Then
  34.         .RowSource = ""
  35.         Else
  36.         .RowSource = "SELECT DISTINCT softSubCat " & _
  37.                 "FROM SOFTWARECAT " & _
  38.                 "WHERE SOFTWARECAT.softCat= '" & Me!lstCat & "' " & _
  39.                 "ORDER BY SOFTWARECAT.softSubCat;"
  40.         End If
  41.     Call .Requery
  42.     End With
  43. End Sub
  44.  
  45. Private Sub lstSubCat_AfterUpdate()
  46.    With Me![lstType]
  47.         If IsNull(Me!lstSubCat) Then
  48.         .RowSource = ""
  49.         Else
  50.         .RowSource = "SELECT DISTINCT softType " & _
  51.                 "FROM SOFTWARECAT " & _
  52.                 "WHERE SOFTWARECAT.softSubCat= '" & Me!lstSubCat & "' " & _
  53.                 "ORDER BY SOFTWARECAT.softType;"
  54.         End If
  55.     Call .Requery
  56.     End With
  57. End Sub
  58.  
  59. Private Sub lstType_AfterUpdate()
  60.   With Me![lbxLeft]
  61.         If IsNull(Me!lstType) Then
  62.         .RowSource = ""
  63.         Else
  64.         .RowSource = "SELECT DISTINCT title " & _
  65.                 "FROM SOFTWARE " & _
  66.                 "WHERE SOFTWARE.type= '" & Me!lstType & "' " & _
  67.                 "ORDER BY SOFTWARE.title;"
  68.         End If
  69.     Call .Requery
  70.     End With
  71. 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
Oct 12 '07 #8

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 12 '07 #9

gcoaster
P: 91
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!
Oct 12 '07 #10

gcoaster
P: 91
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.

Expand|Select|Wrap|Line Numbers
  1. Right now all software is in tblSOFTWARE
  2. 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.

Expand|Select|Wrap|Line Numbers
  1. 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)
Expand|Select|Wrap|Line Numbers
  1. I just spent my vacation trying to set this up! LOL
  2. i think i am just going to go with MySQL and PHP
  3. soooo much easier and free! we like free. 
  4. thank you NICO! 
  5. Matt
  6.  
Oct 12 '07 #11

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 12 '07 #12

gcoaster
P: 91
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
Expand|Select|Wrap|Line Numbers
  1. tbleSOFTWARE | lists all software softwareID,cat,subcat,type,title
  2. tbleMACHINE | clients machine with machineID,clientFK,softwareFK
  3. 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
Expand|Select|Wrap|Line Numbers
  1. tbleMACHINE_SOFTWARE | machinesoftwareID,  machineFK, softwareFK,cat,subcat,title
like so?
Expand|Select|Wrap|Line Numbers
  1. 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
Oct 12 '07 #13

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 13 '07 #14

gcoaster
P: 91
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?
Expand|Select|Wrap|Line Numbers
  1. 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.
Oct 14 '07 #15

nico5038
Expert 2.5K+
P: 3,072
Easiest is to code in the procedure:

currentdb.execute ("delete * from tblTempSoftware")

Thus the query is executed directly.

Nic;o)
Oct 14 '07 #16

gcoaster
P: 91
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?
Oct 14 '07 #17

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 14 '07 #18

gcoaster
P: 91
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
Oct 31 '07 #19

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 31 '07 #20

gcoaster
P: 91
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. CurrentDb.Execute ("delete * from TEMP_SOFTWARE")
  3. End Sub
I need to figure out where to place this

Expand|Select|Wrap|Line Numbers
  1. Fill TEMP_SOFTWARE with all software from your Software table
  2. 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
Nov 2 '07 #21

nico5038
Expert 2.5K+
P: 3,072
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)
Nov 2 '07 #22

gcoaster
P: 91
Hello Nico,

So the code goes here?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. End Sub
what does the code look like? it is calling the update query right?
Attached Images
File Type: jpg onOpen.jpg (9.9 KB, 155 views)
Nov 3 '07 #23

nico5038
Expert 2.5K+
P: 3,072
Use the On Current event, not the On Open..
Thus even when moving through the records the data will be refreshed.

Nic;o)
Nov 3 '07 #24

gcoaster
P: 91
On Current is doing this

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. CurrentDb.Execute ("delete * from TEMP_SOFTWARE")
  3. End Sub
Add another line in the code right?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. CurrentDb.Execute ("delete * from TEMP_SOFTWARE")
  3. HERE
  4. 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Activate()
  2. Me.Refresh
  3. End Sub
How do I add this to the code?

Expand|Select|Wrap|Line Numbers
  1. Fill TEMP_SOFTWARE with all software from your Software table
  2. TEMP_SOFTWARE <= update query called qLeftBoxUpdate
Nov 5 '07 #25

nico5038
Expert 2.5K+
P: 3,072
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)
Nov 5 '07 #26

gcoaster
P: 91
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3. Dim dbs As DAO.Database
  4. Dim sSQL As String
  5. Set dbs = CurrentDb()
  6. ' 1) Delete and then update records
  7. sSQL = "INSERT INTO TEMP_SOFTWARE ( title )SELECT SOFTWARE.title FROM SOFTWARE"
  8. dbs.Execute sSQL
  9. End Sub
I got the idea from here.

http://www.thescripts.com/forum/thread436933.html
Nov 7 '07 #27

gcoaster
P: 91
Nico,
What do I do with this?

Expand|Select|Wrap|Line Numbers
  1. => TEMP_SOFTWARE <= update query called qLeftBoxUpdate
  2. is also done with a query and see above for the options.
Do I add it to what I have?

Where is it loaded?
Nov 7 '07 #28

gcoaster
P: 91
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

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Private Sub btnRight_Click()
  4.     If Not IsNull(Me.lbxLeft.Column(0, Me.lbxLeft.ListIndex)) Then
  5.     CurrentDb.Execute "UPDATE MACHINE_SOFTWARE SET installed= True WHERE machineSoftwareID=" & Me.lbxLeft.Column(0, Me.lbxLeft.ListIndex) & ";"
  6.       Me.Refresh
  7.     Else
  8.       MsgBox "Please select an entry"
  9.     End If
  10. End Sub
  11. Private Sub btnRightAll_Click()
  12.     CurrentDb.Execute "UPDATE TEMP_SOFTWARE SET installed = Yes;"
  13.     Me.Refresh
  14. End Sub
  15.  
  16. Private Sub btnLeft_Click()
  17.     If Not IsNull(Me.lbxRight.Column(0, Me.lbxRight.ListIndex)) Then
  18.       CurrentDb.Execute "UPDATE TEMP_SOFTWARE SET installed = False WHERE softwareID=" & Me.lbxRight.Column(0, Me.lbxRight.ListIndex) & ";"
  19.       Me.Refresh
  20.     Else
  21.       MsgBox "Please select an entry"
  22.     End If
  23. End Sub
  24. Private Sub btnLeftAll_Click()
  25.     CurrentDb.Execute "UPDATE TEMP_SOFTWARE SET installed = No;"
  26.     Me.Refresh
  27. End Sub
  28. Private Sub Form_Current()
  29. Dim dbs As DAO.Database
  30.     Dim sSQL As String
  31.     Set dbs = CurrentDb()
  32.     ' 1) Delete and then update records
  33.     sSQL = "DELETE FROM TEMP_SOFTWARE"
  34.     dbs.Execute sSQL
  35.     sSQL = "INSERT INTO TEMP_SOFTWARE ( title )SELECT SOFTWARE.title FROM SOFTWARE"
  36.     dbs.Execute sSQL
  37.     Me.Refresh
  38. End Sub
Nov 7 '07 #29

nico5038
Expert 2.5K+
P: 3,072
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:
Expand|Select|Wrap|Line Numbers
  1. currentdb.execute ("UPDATE TEMP_SOFTWARE SET installed = True WHERE softwareID IN (Select SoftwareID from MACHINE_SOFTWARE where MachineID=" & Me.MachineID & ");")
  2.  
Getting the idea ?

Nic;o)
Nov 7 '07 #30

gcoaster
P: 91
oh man,, after 4 hours i give...

Expand|Select|Wrap|Line Numbers
  1. currentdb.execute ("UPDATE TEMP_SOFTWARE SET installed = True WHERE softwareID IN (Select SoftwareID from MACHINE_SOFTWARE where MachineID=" & Me.MachineID & ");")
Where does it go?

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnRight_Click()
  2. Private Sub btnRightAll_Click()
  3. Private Sub btnLeft_Click()
  4. Private Sub btnLeftAll_Click()
  5. Private Sub Form_Current()
Nov 11 '07 #31

gcoaster
P: 91
You're close, but you can "shrink" the code.. in the INSERT
Here?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Dim dbs As DAO.Database
  3.     Dim sSQL As String
  4.     Set dbs = CurrentDb()
  5.     ' 1) Delete and then update records
  6.     sSQL = "DELETE FROM TEMP_SOFTWARE"
  7.     dbs.Execute sSQL
  8.     sSQL = "INSERT INTO TEMP_SOFTWARE ( title )SELECT SOFTWARE.title FROM SOFTWARE"
  9.     dbs.Execute sSQL
  10.     Me.Refresh
  11. End Sub
Nov 11 '07 #32

nico5038
Expert 2.5K+
P: 3,072
Yep, after line 8 as first the tables needs to be filled and than the appropriate software needs to be checked :-)

Nic;o)
Nov 11 '07 #33

gcoaster
P: 91
I am getting an error on the last part with Me.machineID

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     CurrentDb.Execute "DELETE FROM TEMP_SOFTWARE"
  3.     CurrentDb.Execute ("UPDATE TEMP_SOFTWARE SET installed = True WHERE softwareID IN (Select softwareID from MACHINE_SOFTWARE where machineID=" & Me.machineID & " ) ; ")
  4.     Me.Refresh
  5. End Sub
Attached Images
File Type: jpg Relationship.jpg (15.3 KB, 137 views)
File Type: jpg compileError.jpg (24.1 KB, 136 views)
Nov 11 '07 #34

nico5038
Expert 2.5K+
P: 3,072
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)
Nov 11 '07 #35

gcoaster
P: 91
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.
Attached Files
File Type: zip gcoaster2003.part01.zip (48.8 KB, 93 views)
File Type: zip gcoaster2003.part02.zip (47.4 KB, 62 views)
Nov 11 '07 #36

nico5038
Expert 2.5K+
P: 3,072
Sure that the field is the problem ?
Access will change the background color of the error line and the query looks OK...

Nic;o)
Nov 11 '07 #37

gcoaster
P: 91
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.
Nov 11 '07 #38

nico5038
Expert 2.5K+
P: 3,072
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)
Nov 11 '07 #39

gcoaster
P: 91
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
Expand|Select|Wrap|Line Numbers
  1. currentdb.execute ("UPDATE TEMP_SOFTWARE SET installed = True WHERE softwareID IN (Select SoftwareID from MACHINE_SOFTWARE where MachineID=" & Me.MachineID & ");")
Replace
Expand|Select|Wrap|Line Numbers
  1. sSQL = "INSERT INTO TEMP_SOFTWARE ( title )SELECT SOFTWARE.title FROM SOFTWARE"
  2. dbs.Execute sSQL
  3. Call .Requery

Thank you
Nov 13 '07 #40

nico5038
Expert 2.5K+
P: 3,072
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)
Nov 13 '07 #41

gcoaster
P: 91
I don't understand!
I really dont
Nov 16 '07 #42

nico5038
Expert 2.5K+
P: 3,072
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)
Nov 16 '07 #43

gcoaster
P: 91
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


Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_Current()
  3.  
  4.   Me.lstInstalled.RowSource = "SELECT MACHINESOFTWARE.title FROM MACHINESOFTWARE " & _
  5.                               "WHERE MACHINESOFTWARE.client = forms![MACHINE]!cboFullName"
  6.  
  7.  
  8.   Me.lstSoftware.RowSource = "SELECT tblSOFTWARE.title FROM tblSOFTWARE " & _
  9.                                  "WHERE tblSOFTWARE.title NOT IN " & _
  10.                                  "(SELECT MACHINESOFTWARE.title FROM MACHINESOFTWARE " & _
  11.                                  "WHERE MACHINESOFTWARE.client = forms![MACHINE]!cboFullName)"
  12.  
  13. End Sub
  14.  
  15. Private Sub lstInstall_Click()
  16.   If IsNull(Me.lstInstalled) And Not IsNull(Me.lstSoftware) Then
  17.  
  18.     'Dim MyMessage
  19.     'MyMessage = MsgBox("This will add a new installation to this record.  Continue?", vbYesNoCancel)
  20.   'If MyMessage = vbYes Then
  21.  
  22. Dim db As DAO.Database
  23. Dim rs As DAO.Recordset
  24.  
  25. Set rs = Me.RecordsetClone
  26.  
  27.   With rs
  28.  
  29.     .AddNew
  30.  
  31.     !client = Forms!machine!cboFullName
  32.     !machineName = Forms!machine!machineName
  33.     !title = Me.lstSoftware
  34.  
  35.     .Update
  36.  
  37.   End With
  38.  
  39.     Me.lstInstalled.Requery
  40.     Me.lstSoftware.Requery
  41.  
  42.     Me.lstInstalled = Null
  43.     Me.lstSoftware = Null
  44.  
  45. Set rs = Nothing
  46.  
  47.   End If
  48.     'Else: MsgBox "Please select only a software to install", vbOKOnly
  49.   'End If
  50.  
  51. End Sub
  52.  
  53. Private Sub lstRemove_Click()
  54.  
  55.   If IsNull(Me.lstSoftware) And Not IsNull(Me.lstInstalled) Then
  56.  
  57.     Dim MyMessage
  58.     MyMessage = MsgBox("This will delete the selected software's record from this machine.  Continue?", vbYesNoCancel)
  59.   If MyMessage = vbYes Then
  60.  
  61.   DoCmd.RunSQL "DELETE * FROM MACHINESOFTWARE WHERE MACHINESOFTWARE.client =Forms!machine!cboFullName AND " & _
  62.     " MACHINESOFTWARE.machineName =Forms!machine!machineName AND " & _
  63.     " MACHINESOFTWARE.title =Forms!MACHINEsoft!lstInstalled "
  64.  
  65.     Me.lstInstalled.Requery
  66.     Me.lstSoftware.Requery
  67.  
  68.     Me.lstInstalled = Null
  69.     Me.lstSoftware = Null
  70.  
  71.   End If
  72.   Else: MsgBox "Please select only a software to Uninstall", vbOKOnly
  73.   End If
  74.  
  75. End Sub
Nov 16 '07 #44

Post your reply

Sign in to post your reply or Sign up for a free account.