Tough Stuff 2

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

This is an shorter/easier to understand version of
previously posted message. Does anyone know how to move a
value in a list box to another list box using a command
button??? Thank you...
 
There are a couple of ways depending on what exactly you're trying to do.

1) Create a field in the table that supplies the data for the listboxes' Row
Sources that will indicate which listbox the value should be in. For
example, 1 or 2 for listbox 1 or 2. Change this value for the selected
item(s) and requery the listboxes when you click the button. Use a query for
each Row Source that has the appropriate restriction (1 or 2) in its WHERE
clause.

2) Use two tables for the Row Sources of the listboxes. Copy the selected
record(s) from one table to the other then delete them from the previous
table. Requery the listboxes.
 
Both list boxes need to be populated by queries based on the same table. Go
back to that table and add a field named Selected and make it Yes/No.
Include the Selected Field in both queries. In the query for the first
listbox, set the criteria for Selected False. In the query for the second
listbox, set the criteria for Selected True. Put the following code in the
AfterUpdate event of the first listbox:
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE NameOfYourTable SET NameOfYourTable.Selected = True _
Where NameOfYourTable.NameOfPKField = " & Me!NameOfFirstListBox & ";"

DoCmd.SetWarnings True
Me!NameOfFirstListBox.Requery
Me!NameOfSecondListBox.Requery

Put the following code in the AfterUpdate event of the second listbox:
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE NameOfYourTable SET NameOfYourTable.Selected = False _
Where NameOfYourTable.NameOfPKField = " & Me!NameOfSecondListBox & ";"

DoCmd.SetWarnings True
Me!NameOfSecondListBox.Requery
Me!NameOfFirstListBox.Requery

Note: Both listboxes must be designed so that the value of each listbox is
the PK of the table.

With the above, all the records from the table will first appear in the
first listbox. When you select a record in the first listbox, that record
will disappear from the first listbox and appear in the second listbox.
Selecting a record in the second listbox will make it disappear from the
second listbox and reappear in the first listbox.
 
Thank you for the details and code...I need all the help I
can get! :)
-----Original Message-----
Both list boxes need to be populated by queries based on the same table. Go
back to that table and add a field named Selected and make it Yes/No.
Include the Selected Field in both queries. In the query for the first
listbox, set the criteria for Selected False. In the query for the second
listbox, set the criteria for Selected True. Put the following code in the
AfterUpdate event of the first listbox:
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE NameOfYourTable SET
Click to expand...
NameOfYourTable.Selected = True _
Where NameOfYourTable.NameOfPKField = " & Me! NameOfFirstListBox & ";"

DoCmd.SetWarnings True
Me!NameOfFirstListBox.Requery
Me!NameOfSecondListBox.Requery

Put the following code in the AfterUpdate event of the second listbox:
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE NameOfYourTable SET
Click to expand...
NameOfYourTable.Selected = False _
 
I edited and entered the code for both list boxes and got
the following error when I click on the firt value in list
box 1 to send to list box 2: "The macro (or its macro
group) doesn't exist or the macro is new but hasn't been
saved. Note that when you enter the
macrogroupname.macroname syntax in an argument, you must
specify the name the macro's macro group was last saved
under." Am I missing a macro??? Here's the code I put
in:
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE FSCa SET FSCa.Selected = True _
Where FSCa.SSAN = " & Me!List0 & ";"

DoCmd.SetWarnings True
Me!List0.Requery
Me!List2.Requery

Put the following code in the AfterUpdate event of the
second listbox:
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE FSCa SET FSCa.Selected = False _
Where FSCa.NameOfPKField = " & Me!List2 & ";"

DoCmd.SetWarnings True
Me!List2.Requery
Me!List0.Requery

I named the field "Selected" and made it to a yes/no. Any
ideas??? Thanks.
-----Original Message-----
Both list boxes need to be populated by queries based on the same table. Go
back to that table and add a field named Selected and make it Yes/No.
Include the Selected Field in both queries. In the query for the first
listbox, set the criteria for Selected False. In the query for the second
listbox, set the criteria for Selected True. Put the following code in the
AfterUpdate event of the first listbox:
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE NameOfYourTable SET
Click to expand...
NameOfYourTable.Selected = True _
Where NameOfYourTable.NameOfPKField = " & Me! NameOfFirstListBox & ";"

DoCmd.SetWarnings True
Me!NameOfFirstListBox.Requery
Me!NameOfSecondListBox.Requery

Put the following code in the AfterUpdate event of the second listbox:
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE NameOfYourTable SET
Click to expand...
NameOfYourTable.Selected = False _
 
Back
Top