[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2012-09-20
Medium Priority
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
  • 3
  • 3
LVL 85
ID: 38420310
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

by:Helen Feddema
ID: 38422324
Some Office components support checked items on listboxes, but not Access listboxes.  Do you mean selecting the item in the listbox?

Author Comment

ID: 38468930
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.....

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Author Comment

ID: 38530324
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 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 38533355
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

ID: 38536943
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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

872 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