Using a Listbox to populate a Textbox.

[IncidentalProgrammer]

Hi! I have a small form that exists for the sole purpose of adding/editing/deleting short descriptive names (such as "Add Privacy Notice") that are to be used on another form for the sole purpose of generating names that are used on files.



The plan for this form is to have an unbound listbox who's source is either the table (a 2 column--auto# key, and name) or a query that takes just the information from that table (whichever I need to use). I want the user to be able to click an entry in the list box, and have it appear in a textbox below, which would allow it to be edited. So far, I've left that textbox unbound as well, because it needs to be able to accept existing names for editing, and take new entries and add to the table.

I have scoured Google, and tried every combination of VBA and setting sources that I can find, and the best I can get in the textbox is "#NAME?". What is the best way to enable this textbox both take the value the user selects from the listbox, OR take completely new data the user enters, check it against the table to make sure it doesn't already exist, and if not, add it?


A couple of other things I'm trying to do with this as well, are to enable the user to delete these records (I was thinking select item from listbox, hit delete button), and to alphabetize the entries in the listbox. I've tried alphabetizing the table, and using a query, but I just can't get the entries to appear in order in the listbox. Any suggestions?

[June7]

Code in listbox AfterUpdate event:

Me.textboxname = Me.listboxname.Column(x)

What do you want to be done with the edited value?

If you want to use the listbox (or a combobox) to locate existing records, review http://www.datapigtechnologies.com/f...tomfilter.html

Why delete records?

What is the listbox RowSource? What criteria is used for sorting?

[IncidentalProgrammer]

Tried it, and still can't get the value from the listbox to move into textbox. And the listbox is pulling records from the table, it just won't alphabetize them.

When a user edits a value, I want it to overwrite that record. Basically, it's for making amendments and corrections. And deleting would be for removing items (records) that are no longer in use, or if somehow duplicate entires make it in (via typo, or slight variation of name).

I'll clarify a bit, because this is kind of a weird item. The form I'm needing help with is merely a means for users to add/delete/edit tiny descriptive bits that are used in a "File Name Generator" form. The name generator exists for the sole purpose of enforcing the boss' rigid naming convention for PDF'd insurance policy documents that are saved in the client folders on the server. The names that are generated are not saved anywhere; they are meant only to be made, copied, and pasted onto the document. Below is the generator form; I highlighted the little bits of text that I want to be able to edit/add/delete through the other form (this one is working perfectly).


And this is the form I'm working on now. It's accessible via the "Edit Descriptives" button on the name generator. The listbox pulls the "descriptives" from a table that houses only them and an auto# key.


The three things I want the user to be able to do from this form are: 1) Select an item in the listbox and hit the "Delete" button to erase the selected record, 2) Select an item in the listbox, have it populate the textbox, and edit/save it there to change the record as it appears in the table/form, 3) Enter new records into the textbox, hit the "Save" button, and add them to the table.

Does that make a bit more sense?

[burrina]

If you are saying you want to Select All and DeSelect All then maybe this will help.

Dim intCounter As Integer ' create a loop counter
    
    If cmdSelectAll.Caption = "Select All" Then
        For intCounter = 0 To lstlist.ListCount
            lstlist.Selected(intCounter) = True
        Next intCounter
        cmdSelectAll.Caption = "Deselect All"
    Else
        For intCounter = 0 To lstlist.ListCount
            lstlist.Selected(intCounter) = False
        Next intCounter
        cmdSelectAll.Caption = "Deselect All"
    End If


Dim varItem As Variant

    If lstlist.MultiSelect = 0 Then  'Clears Selected Items,i.e DeHighLights Them
        lstlist = Null
    Else
        For Each varItem In lstlist.ItemsSelected
            lstlist.Selected(varItem) = False
        Next
    End If

Of course replace the List Box name with yours.

[June7]

This is to allow users to add/delete/edit descriptors in the Descriptors table (Why edit/delete?)? Or do you just want the listbox list to be dynamic (again, Why?)?

For a dynamic listbox RowSource based on a table (not a value list), removing an item from the list requires an SQL statement with a filter. One way to filter is with a yes/no field in the Descriptors table. Set the yes/no field then requery the listbox RowSource:

SELECT * FROM Descriptors WHERE [Selected] = True;

If the listbox has columns for the descriptor ID (hidden) as well as the text, then populate the textbox by referencing column index. Index begins with 0:

Me.textbox = Me.listbox.Column(1)

If you want to allow users to change the Descriptors table records, why not use a bound form instead of listbox? Simpler than running action SQLs.

[IncidentalProgrammer]

Thanks, Burrina, but I'm really wanting to work with just one piece of data at a time, rather than selecting all.



I need the edit/delete capabilities for users, so they don't go playing "Operation" with my tables to try to figure out how to do it themselves. These bits of data are primarily the names of endorsements that insurance companies may add to the policies, and over time, they may fall out of use, or a name might change. To keep the list tidier and the results of the name generator more accurate (for instance, someone new may try to use an outdated name, if its still available), I want to give the users (mostly my boss) the ability to make the appropriate changes to the list. Since these aren't actually saved anywhere in the database except for their own table, I don't see why outdated ones would need to be kept (tell me if I'm wrong though, but these snipits of info only save in the one table, and names generated using them do not save anywhere in the database).

I have been using the index (1) because of the auto# key in 0, but that SQL statement sounds like what I need to get that listbox working as I want it too. Can you elaborate on the Yes/No field, though? Would that be "yes" for data in use, and "no" for ones that users have "deleted"?

I tried both, and ended up on unbound, but that was more about me just experimenting to find what would work, than anything. With a bound form, could I still get the listbox to populate the textbox?

[June7]

With a bound form for editing the Descriptors table records, why would you even still need listbox and textbox?

[IncidentalProgrammer]

You know what...that's a good question.

I think I may be a little too hung up on the aesthetics.