update table from list using recordsets

[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

[Rabbit]

You need to create a VBA function to loop through a recordset. The following tutorial gives you the basics even though it's not like your situation. Basic DAO recordset loop using two recordsets

[gcoaster]

I think I understand.

Function yourFunctionName() 
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
 
  Set db = CurrentDb() 
  Set rs1 = db.OpenRecordset("Query1") 
  Set rs2 = db.OpenRecordset("Query2")
 
  If rs1.RecordCount=0 Then Exit Sub
    
  rs1.MoveFirst 
  ' loop through each record in the first recordset
  Do Until rs1.EOF
    ' If matching record is found then update field in 
    ' second recordset to value you determine
   If rs2.RecordCount=0 Then Exit Sub
    rs2.MoveFirst
    Do Until rs2.EOF
      If rs1![FieldName] = rs2!FieldName Then
        rs2.Edit
        rs2![FieldName] = 'Your Value'
        rs2.Update
      End If
      rs2.MoveNext
    Loop
    rs1.MoveNext
  Loop
 
  rs1.Close 
  rs2.Close 
  Set rs1 = Nothing
  Set rs2 = Nothing
  Set db = Nothing
 
End Function

[gcoaster]

Hello Rabbit,
Have read some of your posts, you are a master! cool..
this is how far i have gotten. left to right is not working. please help

thank you

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

[Rabbit]

I think I misunderstood your original intention. I originally thought you had multiple records for each computer and wanted to create a single string. However, that does not seem to be the case. What is the structure of your software table?

[gcoaster]

Thank you Rabbit!
here is the structure of Software, and other tables just in case.
( trying to upload it but... File Too Large. Limit for this filetype is 97.7 KB. my file is 225.5 KB.) I managed to, split file in 3 parts.


Table: MACHINE_SOFTWAR E
machineSoftware ID Long Integer 4
machineFK Long Integer 4
softdCat Text 255
softdSubCat Text 255
softdType Text 255
softdSubType Text 255
title Text 255
publisher Text 255
url Text 255
installed Yes/No 1

Table: SOFTWARE
softwareID Long Integer 4
SoftCat Text 255
softSubCat Text 255
type Text 255
subType Text 255
title Text 255
dateUpdated Date/Time 8
publisher Text 255
homepage Text 255
version Text 255
description Memo

Table: TEMP_SOFTWARE
tempSoftwareID Long Integer
machineSoftFK Long Integer
SoftCat Text 255
softSubCat Text 255
type Text 255
subType Text 255
title Text 255
dateUpdated Date/Time 8
publisher Text 255
homepage Text 255
version Text 255
description Memo -
installed Yes/No 1

Table: MACHINE
machineID Long Integer 4
clientFK Long Integer 4
softwareFK Text 255
productFK Long Integer 4
machineNotes Memo

[Rabbit]

If I understand correctly you have one record for each piece of software on each machine. And what you want to do is have 2 list boxes, one lists all software that the chosen machine does NOT have and the other lists the software it does have. Then the user can select one or more software from either listbox, click a button, and either create new records or delete records and requery the listboxes?

[gcoaster]

If I understand correctly you have one record for each piece of software on each machine. And what you want to do is have 2 list boxes, one lists all software that the chosen machine does NOT have and the other lists the software it does have. Then the user can select one or more software from either listbox, click a button, and either create new records or delete records and requery the listboxes?

Thank you Rabbit,
Yes EXACTLY!!!
by the way,
I am the user, Only I will be using this. I am trying to get away from outlook2007 +bcm.

[Rabbit]

Displaying the items for the listboxes is easy. For the listbox with uninstalled software, you use an unmatched query, for the other, a regular select query will suffice. Just make sure you take into account the computer in question. You'll also want to rebuild the control source string for each listbox in the Current event of the form.

As for moving software back and forth, you'll have two command buttons, one to add and one to delete. If you have a multi-select listbox then you'll have to loop a DoCmd.RunSQL. Then you want to requery both listboxes

[gcoaster]

Hello Rabbit,
thank you for your on going support.

I took a different route this time, things I changed where some of the table and form names to make it esier, learned its important working with VBA NOT to name the form and table the same. it gets confusing! .

almost there now, one error is keeping me. and that is deleting software from right. learning about the DoCmd.RunSQL

Am I doing things right? thank you agian.

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

[Rabbit]

I don't see anything explicitly wrong with the code. What's the problem exactly? What's not working the way it should or what is it doing that it shouldn't be doing?

[gcoaster]

The problem is here somewhere ( well at least i think! )

DoCmd.RunSQL "DELETE * FROM MACHINESOFTWARE WHERE MACHINESOFTWARE.client =Forms!machine!cboFullName AND " & _
    " MACHINESOFTWARE.machineName =Forms!machine!machineName AND " & _
    " MACHINESOFTWARE.title =Forms!MACHINEsoft!lstInstalled "

The error is this when cmdRemove is clicked

Here is what pops up

| Enter Parameter Value |
[ Forms!MACHINEso ft!lstInstalled ]

[Rabbit]

That error usually means you misspelled something or referenced the control incorrectly. If the control's on a subform, then the syntax is a bit different. Also, the form needs to be opened.

[gcoaster]

That error usually means you misspelled something or referenced the control incorrectly. If the control's on a subform, then the syntax is a bit different. Also, the form needs to be opened.

Fixed it Rabbit, Thank you! you where right..

[Rabbit]

Not a problem, good luck.