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

[nico5038]

Easiest is to code in the procedure:

currentdb.execu te ("delete * from tblTempSoftware ")

Thus the query is executed directly.

Nic;o)

[gcoaster]

Easiest is to code in the procedure:
currentdb.execu te ("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_SOF TWARE 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/xSample-Datasheet-Filter-ObjectAction-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.Exce ute" (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_softwar e by what is selected in TEMP_SOFTWARE

Here is what I have already.

TABLES
[1] TEMP_SOFTWARE
[2] MACHINE_SOFTWAR E ( 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:
[code=vb]
currentdb.execu te ("UPDATE TEMP_SOFTWARE SET installed = True WHERE softwareID IN (Select SoftwareID from MACHINE_SOFTWAR E where MachineID=" & Me.MachineID & ");")
[/code]

Getting the idea ?

Nic;o)