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

[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.machineSoftw areID.

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_SOFTWAR E. thank you

Tried
MACHINE_SOFTWAR E where machineID=" & Me.machineSoftw areID & ");")
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_SOFTWAR E_ID.
Best to set the controlsource of the form to the table: MACHINE_SOFTWAR E 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_SOFTWAR E_ID.

Should I go ahead and add the " MACHINE_SOFTWAR E_ID "?

Best to set the controlsource of the form to the table: MACHINE_SOFTWAR E 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_SOFTWAR E 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