How to save a manually entered "custom text" in a dropdown to the list items for future choice?

[DGodfrey90]

I have searched with no luck on this. In Access, you can add a manually entered text in a combo box to the associated table containing the list items. When the form is opened in the future, that list item that is newly created is now an available option.

Here is what I'm trying to do: My Location1,2,etc. dropdown boxes have one item below the blank space default item. When a user clicks and enters a location (the "Let users enter custom text" option is checked), after that option is displayed in the form, it is then saved to the list as a new list item. The next time the form is opened, there would be two locations listed, instead of just the one.

As an example, the box has Zoom Meeting only. The user attended a meeting at the mall. They type in "Mall" as the custom text. Next month, they have another meeting at the mall, but the form is blank because it is a new month. They click on the arrow in the dropdown, and "Mall" is now and available option to select.

I figure this is an easier and more efficient way than entering the same location month after month using "other" as an option and populating a text field or some other method of showing a location not in the list.

[try67]

You can do it using this code as the field's custom Validation script:

if (event.value) {
	var f = event.target;
	var found = false;
	for (var i=0; i<f.numItems; i++) {
		if (f.getItemAt(i, false)==event.value) {found = true; break;}
	}
	if (!found) f.insertItemAt({nIdx: -1, cName: event.value});
}

The new item will be added to the list when the user exits the field.

[DGodfrey90]

That works, except that the custom text becomes the new default, rather than the blank space default. My form reset button does not clear the box either, using the reset form action. The box is checked in the select list for form reset action.

Thanks.

[try67]

Interesting, I did not anticipate that to happen... To fix it, change this line:

if (!found) f.insertItemAt({nIdx: -1, cName: event.value});

To:

if (!found) {
	f.insertItemAt({nIdx: -1, cName: event.value});
	f.defaultValue = f.getItemAt(0, false);
}

[DGodfrey90]

I got that par working I think, but I have run into another problem or two.

1. This only works in the first row dropdown. Since I have 18 rows in this column, I need any custom text entered in any row (box) to be available from the list in any other row.  Currently, I have the first box coded with default instructional text, but I only want that to show in the first row and not the rest (Not a deal breaker, but would be really nice for aesthetic reasons). The first time the user opens the form, they have "Virtual" as an item, the blank space and the default text, regardless of row. So that needs to be in a "Master" Table/List. I do not know how to call that master list into the dropdown (Step 2) after creating said master list (Step 1). Step 3 will be:as the user fills in the custom list items in any row, they are added to the master list to be available in any row at any time for future months.

Example:

Joe Smith opens the form and completes all of the columns on Row 1. In the "Location1" dropdown (Row 1), he sees the istructional text and the "Virtual" Item. He then adds "Mall" as in the original post example.  He fills out several other rows and then he needs to use "Mall" again in say, Row 5. He should now see "Mall" as an option in the Row 5 Location dropdown (Location5), or any other row for that matter.  How do I do this?

2. The other issue is I noticed the custom options are not saved in the "Master form" if it is closed or the file is saved as a new form under a different filename, as is the desired workflow, because we want the fields to be blank every month when the master form is opened again. I do have it coded so two of the fields do not get cleared with the clear form button or clear form action. So I'm not sure how to fix this bit either.

I hope what I am asking is clear about the workflow. I'm guessing the master list may need to be a document level script, but I'm not sure how to code it. I have not been able to get a suitabe answer in another thread I posted this to. I can't seem to search for the setItem method in the API, it comes back with no results. So I don't have the full syntax to even start with there.

For reference, here is teh current code:

Custom Format script:

(event.value !== " " || event.value =="") ?

([

event.value = event.target.value,

event.target.display = display.visible

]) : ([

event.value = "Please Select or Enter a Location.",

event.target.display = display.noPrint,

]);

Custom Validation script:

if (event.value) {
var f = event.target;
var found = false;
for (var i=0; i<f.numItems; i++) {
if (f.getItemAt(i, false)==event.value) {found = true; break;}
}
if (!found) {
f.insertItemAt({nIdx: -1, cName: event.value});
f.defaultValue = f.getItemAt(0, false);
}
}

Thank you for your help again.

[try67]

1. You can use a loop to add the new item into the other fields. In order to do that you will need to skip the current field's name, though. What are the names of all the fields?

2. That should not happen. If the file is saved when closed all items that were added to the list(s) should be saved as well. Do you maybe have another script that sets the items in the list when the file is opened?

[DGodfrey90]

1. I'm not sure how to do that. The fields are named Location1, Location2,etc.-Location18.

2. Currently, the items are not set when the file is opened. Do I need to actually code it to save on close somehow?

[DGodfrey90]

2. I do have a load script that resets the form, but didn't think that would affect it this way?

[try67]

Depends. Post the code, please.

[DGodfrey90]

var fieldsNotToReset = ["ChaplainName", "CompanyName",];

var fieldsToReset = []; for (var i=0; i<this.numFields; i++) { var fname = this.getNthFieldName(i); var f = this.getField(fname); if (f==null) continue; if (fieldsNotToReset.indexOf(fname)!=-1) continue; fieldsToReset.push(fname); } this.resetForm(fieldsToReset);
for (var i=0; i<this.numFields; i++) {
var f = this.getField(this.getNthFieldName(i));
if (f==null) continue;
f.readonly = false;
}

this.getField("CommunityServiceTotal").readonly = true;
this.getField("CounselingTotal").readonly = true;
this.getField("CrisisResponseTotal").readonly = true;
this.getField("FirstResponderTotal").readonly = true;
this.getField("MeetingsTotal").readonly = true;
this.getField("MissionsTotal").readonly = true;
this.getField("RapidResponseTotal").readonly = true;
this.getField("TrainingTotal").readonly = true;
this.getField("VeteransTotal").readonly = true;
this.getField("TotalHoursServed").readonly = true;
this.getField("TotalTravelHours").readonly = true;

for (var i=0; i<this.numFields; i++) {

var f = this.getField(this.getNthFieldName(i));

if (f==null) continue;

if (f.type=="button") f.display = display.visible;

}

this.getField("MonthYear").setFocus();

[try67]

This code should have no effect on that. Can you share the file, please?

[DGodfrey90]

Here is the current version of the form. Note it is not saving the custom entry on the original form, only on the form saved with Save As (as the original form is not actually saved by the user).

When it is saved, it is available in the Row it was entered on, but not available to other rows.

Thanks.

[try67]

Works fine for me. The custom entry I entered exists when I re-open the file, but of course it is no longer selected, since you're resetting the fields when the file is opened.

[DGodfrey90]

Odd that it is not saving it when I test it.

I'm still trying to figure out how to do the master list part though. I'm thinking I'll have to have a hidden combo box with the master list and modify the code that saves the custom entry to save it in the master box/list? How exactly do I then call that list into each combo box on Rows 1-18?

I'm still learning and the setItem sample code in the API doesn't tell me a whole lot of how to do all of this.

[try67]

What kind of field would this "master list" be, exactly?

[DGodfrey90]

I was experimenting with it as a combo box. I have not found any examples online though that would do exactly what I want in the first place. I was going to also experiment with a regular list field, but not sure that will work.