Unbound Field Move to List box 1

[Delano29]

Hi, I am using Access 2003. I would like to know the easiest way of moving the contents of an unbound field to a listbox? I have a text field called 'newdat1' and a listbox field called 'lstselecteddat' on the same form. I would like to use code in a command button to move the item.

Is this possible?

Thanks.

 

[fneily]

I have a table (name Mailinglist) with a field (name FirstName).
I created a form (name Listbox_test) with a listbox (name List0) and a textbox (text2). On the RowSource of the listbox, I placed the following UNION query:

SELECT DISTINCT [FirstName] FROM Mailinglist WHERE [FirstName] Is Not Null UNION Select Forms![Listbox_test]![text2] From MailingList
ORDER BY [FirstName];

I then have a command button with the following on the OnClick command:

Me![List0].RowSource = "SELECT DISTINCT [FirstName] FROM Mailinglist WHERE [FirstName] Is Not Null UNION Select Forms![Listbox_test]![text2] From MailingList ORDER BY [FirstName];"

The listbox is updated with whatever is typed in the textbox. BUT IT IS NOT STORED ANYWHERE!!

HOWEVER, this does not save the new entry to anything. Are you sure that's what you want or do you want to save the new entry to some table???

 

[Delano29]

Hi fneily,

That's pretty much what I wanted to do, Thanks. I inherited this database that had a paired listbox but my boss wanted me to change it to allow additions to the listbox while in the form.

There is another command button on the form that saves the contents of the list to the appropriate table (although it doesn't work all the time and I trying to troubleshoot).

I figured if I am able to move the contents of a field with the new info.(listbox item)to the listbox that is used to store the selections that this might be a better way of solving this problem.

Do you think this is a good idea or would you suggest something better?

Thanks again.

 

[fneily]

It sounds like there's a table that stores the listbox values and ONLY those values. I would have to see the code for the command button to see what it's doing. Or, you could put on the command button's OnClick event:

Dim DB As DAO.Database
Dim RS As DAO.Recordset
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("ListboxTableName", dbOpenDynaset)
RS.AddNew
RS![FieldNameinTable] = Me![Text2]
RS.Update
RS.Close
DB.Close
Set RS = Nothing
Set DB = Nothing
Me![List0].Requery

This opens the table that stores the listbox values, adds the new value from the textbox, then requeries (refreshes) the combobox.

 

[Delano29]

fneily,

The info. for the list isn't in a table. All the choices for the listbox was placed in the 'Row Source' properties. It might be worthwhile for me to create a table to store the info. instead of through the listbox properties.

The following is the code for the command button that saves the record.

Private Sub Command64_Click()
On Error GoTo Err_Command64_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Call Save_Databases
Err_Command64_Click:
MsgBox "Record Saved"
End Sub

I think the above code is insufficient in saving the record to the right tables (especially the items in the listbox). I think thats why it's not functioning properly. It seems to save the record fine when its first created (listbox items included). However, when I go in to do an edit of the record using the same form it winds up deleting the previous records entered through the listbox and fails to enter any new information at all in the table. If you can point me in the right direction on this as well it would be greatly appreciated.

In any event, I am going to use your idea (code) of opening the table and add the new value from the textbox using a command button.

Thanks again.

 

[fneily]

You can use the Listbox Rowsource property, but for alot of values that would just be a pain to type in. Also, let's say you have to change or delete a value - then you have to search, etc. A table is easier. But either way, it'll work.
On the code, he first saves the record, then the second DoCmd refreshes the form, I quess to pick up the new values for the listbox. So it does the same thing I do in a different way. The Call Save_Databases is a function. You can click on the Modules tab, and then open the modules listed until you find it.
The first DoCmd, by the way save info to the table the form is bound on.
You probably could just change the button to a straight save if you're going to use my code to save to a list table. I don't know what's in Save_Databases.