Create 2-texts box with buttons like the Query Wizard

[Peter via AccessMonster.com]

I have main form. On this main form I need to create 2- list boxes. One
list box will have the list of items and the second list box I will need to
click on a button to have the secon list box populated. This should function
just like the Query Wizard(simple query) where on the left is a list of
fields and you click on the buttons to add or remove fields from the list box
on the right.

Does anyone have the code to add and remove field names to the list box on
the right?
The code is for the 2-buttons,
the add button >>
the remove button <<

This adding and removing of field names will save to the table.

Thank for your help,
Peter

 

[Barry Gilbert]

Presumably, your list boxes are populated from two different tables. The
listbox on the left, the one with available values, should be based on a
query that returns values from the first table that are not stored in the
second table. The listbox on the right can be just the values from the second
table.

The arrow buttons simply add or remove records from the second table. This
can be done a few different ways; with either a ado or dao or by using
DoCmd.RunSQL, which is simpler but less robust. Here's an example of the
latter method:

Dim strSQL As String
strSQL = "INSERT INTO MyDestinationTable(myField) VALUES('" & Me.MyListBox1
& "')
DoCmd.RunSql strSQL

This code assumes your values are text type. If they're numeric, remove the
single quote marks next to each parenthesis.

To remove a value, you simply change the sql string to something like:
strSQL = "DELETE FROM MyDestinationTable WHERE MyField = '" & Me.MyListBox2
& "'"

I hope this is somewhat clear. Don't forget that you also need to requery
both listboxes after making the updates.

Barry

 

[Peter via AccessMonster.com]

Hi,

This goes through an append process. And yes it appends the item to the table,
but it doesn't show up in the Listbox2.

I really just need to copy the value selected from listbox1 to listbox2
without any system prompts. And have listbox2 save that value in the table.

Can you or someone help me with this??

Thanks,
Peter

Presumably, your list boxes are populated from two different tables. The
listbox on the left, the one with available values, should be based on a
query that returns values from the first table that are not stored in the
second table. The listbox on the right can be just the values from the second
table.

The arrow buttons simply add or remove records from the second table. This
can be done a few different ways; with either a ado or dao or by using
DoCmd.RunSQL, which is simpler but less robust. Here's an example of the
latter method:

Dim strSQL As String
strSQL = "INSERT INTO MyDestinationTable(myField) VALUES('" & Me.MyListBox1
& "')
DoCmd.RunSql strSQL

This code assumes your values are text type. If they're numeric, remove the
single quote marks next to each parenthesis.

To remove a value, you simply change the sql string to something like:
strSQL = "DELETE FROM MyDestinationTable WHERE MyField = '" & Me.MyListBox2
& "'"

I hope this is somewhat clear. Don't forget that you also need to requery
both listboxes after making the updates.

Barry

I have main form. On this main form I need to create 2- list boxes. One
list box will have the list of items and the second list box I will need to

[quoted text clipped - 13 lines]

Thank for your help,
Peter

 

[Barry Gilbert]

If it copies the value to the table, that's what you want. If you want to
eliminate the prompt, put this in front of the DoCmd.RunSQL statement:
DoCmd.SetWarnings False

Then after the DoCmd.RunSql, put:

DoCmd.SetWarnings True

After this happens, put Me.Listbox2.Requery. That will refresh the listbox
from the table.

Barry

Hi,

This goes through an append process. And yes it appends the item to the table,
but it doesn't show up in the Listbox2.

I really just need to copy the value selected from listbox1 to listbox2
without any system prompts. And have listbox2 save that value in the table.

Can you or someone help me with this??

Thanks,
Peter

Presumably, your list boxes are populated from two different tables. The
listbox on the left, the one with available values, should be based on a
query that returns values from the first table that are not stored in the
second table. The listbox on the right can be just the values from the second
table.

The arrow buttons simply add or remove records from the second table. This
can be done a few different ways; with either a ado or dao or by using
DoCmd.RunSQL, which is simpler but less robust. Here's an example of the
latter method:

Dim strSQL As String
strSQL = "INSERT INTO MyDestinationTable(myField) VALUES('" & Me.MyListBox1
& "')
DoCmd.RunSql strSQL

This code assumes your values are text type. If they're numeric, remove the
single quote marks next to each parenthesis.

To remove a value, you simply change the sql string to something like:
strSQL = "DELETE FROM MyDestinationTable WHERE MyField = '" & Me.MyListBox2
& "'"

I hope this is somewhat clear. Don't forget that you also need to requery
both listboxes after making the updates.

Barry

I have main form. On this main form I need to create 2- list boxes. One
list box will have the list of items and the second list box I will need to

[quoted text clipped - 13 lines]

Thank for your help,
Peter