MS Access update item in list box connected to a multi-value field in a table

Posted on 2012-09-20
Last Modified: 2012-10-25
I have a multi-value field in a table that controls the events in ordering furniture from a manufacturer through to subsequent shipping to UK and fianally delivery to the customer. The multi-field control on an 'Orders' form is displayed as a list box and each event is displayed with a check box. When the check box next to an event is checked code takes over and the check box is checked to indicate that this event has been completed. For example, if the event 'Invoice Paid By Customer' is completed the the associated check box is checked and payment recorded.

What I now need to achieve is to undo a checked list event; that is remove the check mark against an event.

The code to identify the events is as follows:
eventid = Me!Order_Status_Display.ListIndex

The 'eventid' tells me the index (from 0-14) of the checked items and allows me to 'dlookup' a table to find the textual event as follows:
stat = DLookup("returnedvalue", "lookupvalues", "searchvalue='order status' AND returnedvalueranking=" & eventid)
I then process other code accordingly.
How do I change the listbox event from checked to unchecked?

Question by:Rayshka
    LVL 84
    I'm not sure what you mean ...

    Your DLookup would give you the EventID, and you could then process whatever event is needed at that point. A Listbox doesn't have a "Checked" or "Unchecked" event - in fact, it doesn't even have checkboxes (unless you're referring to a multivalued field, which are evil, by the way :) ).

    If you ARE referring to a MultiValued field, then see this post:
    LVL 31

    Expert Comment

    Some Office components support checked items on listboxes, but not Access listboxes.  Do you mean selecting the item in the listbox?

    Author Comment

    Your answers have not helped me but I have found a way of achieving what I want.

    FYI I am using a multi-valued field and the items available for the field are listed in a list box and each item has a check box adjacent to it. When checked the item is placed in the multi-value field and likewise when unchecked it is removed from the field.

    What I wanted was to automate the un-checking of the item and I have found the sql below works. [status_monitor] is the multi-value field, strStatus is the string that identifies the field value to delete. [Please confirm the Order ID (top right of form)] is a cheat to obtain the order id (from the user) of the record from which the field value is to be deleted otherwise the value will be deleted from the multi-value field in ALL records!

    "DELETE [status_monitor].value FROM Orders WHERE [status_monitor].value='" & strStatus & "' AND [Please confirm the Order ID (top right of form)]=" & dblOrderID

    When the sql is run the user is prompted with the 'Please confirm the Order ID......'. Although dblOrderID is a valid value I have to use the cheat because I am not allowed to list other fields with a multi-value field in the SQL Statement, e.g. DELETE [status_monitor].value, [another_field] FROM.

    Hope this helps and thanks for trying to help me.....

    LVL 84
    Your answers have not helped me
    That's because you never responded. We can't read your mind.

    Author Comment

    That's a little unfair as the questions you left me had already been answered in my original question; I am using a multivalue field in a list box and each value does have a check mark in the list box as shown in the attached file.

    The question is how can I undo or remove a value from the multi-value field from just one record in the table. The SQL I documented in my previous reply still has to have user input to identify the record in the table from which the value is to be removed from the multi value field. If the record id is not supplied the value is removed from thee field in every record in the table......not what I want.

    I appreciate your view that MVFs are bad news  but I have to stick with them for now....once bitten.....

    Many thanks
    LVL 84

    Accepted Solution

    My point still stands - if we ask questions of you, the we obviously have trouble understanding your question. It's easy for you to see exactly what you're doing; it's nearly impossible for us to do so unless you respond to our questions. At the very least, you should respond to our comments.

    FWIW, you can't use a MultiValued field with a Listbox. You may have meant that you had a Multivalued and you were simply displaying it to the end user, or you may have meant that you have a Listbox where the user can choose multipe values, but it was not clear (and your code suggests that you don't have a Multivalued field, since that's not the right way to work with multivalued field data).

    Author Closing Comment

    I cannot rate your answer because we seem to have hit an impasse. I DO have a list box bound to a MULTI_VALUE field, viz. the control source. The list of items in the listbox is a sql list of events taken from a table of parameters, viz the Row Source. Each event in the list has a check box associated with it and once an item is checked the associated event is added to the multi-value field.<br /><br />Thus when you scroll through records in the form the list box displays the events that have occurred for that particular record by showing a check mark against the associated item(s).<br /><br />I believe now I have a solution to my question of how do I remove an associated event from a multi-value field, which appears to be by simply un-checking the event in the list. By simply checking or unchecking an item in the list box the associated event is automatically added or removed from the multi-value field. Thus code is only required for the user interface.<br /><br />Thank you for your efforts.<br />Rayshka

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Many companies are making the switch from Microsoft to Google Apps ( Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
    No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now