combobox reverting to original value with Not In List event

[adhoustonj]

Hello AWF,
I have a combobox that is reverting back to the old value when I edit it and I can not figure out why. I have a Not In List event that adds the record if it does not exist, but it is inconsistent with keeping the new value that was just edited, or reverting back to the original value, while the NEW value does get added to the table, the record just is not updated. I've tried to requery in after update event but that isn't helping.

In the example picture - I am trying to remove the last word 'engine' from the instruction.

Can anyone shine a light on this for me please?

EDIT: and to include an example of the edit/vs new working, in the picture below - I was trying to remove the last word 'engine'. If i were to add the word 'engine' so that the last two words of the combobox text said 'engine engine', it would work fine, and display immediately. What is the difference?

Not In List:

Private Sub spec_inst_NotInList(NewData As String, Response As Integer)

Call AddNewToList(NewData, "tblSpec_inst", _
                             "spec_inst", "Special Instructions", "")
Response = acDataErrAdded

End Sub

After Update:

Private Sub spec_inst_AfterUpdate()
Dim oldv As String, newv As String

If IsNull(Me.spec_inst.OldValue) Then
    oldv = "blank"
Else
    oldv = Me.spec_inst.OldValue
End If
If IsNull(Me.spec_inst) Then
    newv = "blank"
Else
    newv = Me.spec_inst
End If
If oldv <> newv Then
    Call buaudit("tblTask", Me.task_id, "spec / key point", oldv, newv)
End If

'Me.spec_inst.Requery

End Sub

 

[adhoustonj]

I also do realize that this isn't 'intended' use of combo boxes, but I'm hoping to find a way to make it work.

My initial problem was users did not like typing into a text box on a single form, into a table that was akin to a fact table like orders - that had a comment field, and if the text did not exist, they had to copy what they had typed, paste it into the 'modal edit form add to list' thing, and then go back to the screen that they were on, and paste it there because now it had been added to the list and a valid entry.

Well lets say you have 10,000,000 rows, and the comments are long text, and many are repetitive, lets just store the text distinctly in a table, and reference an id on the main fact table mainly for performance/cost/scalability/db reliability purposes, whatever fits the bill...

So now - it is a combobox because they can select from previous entries, or type in a new one (which works fine 100% in the current setup for NEW) but if editing an existing comment field, it adds it to the table, but the combo doesn't refresh.


I can imagine i may succeed in using a tempvar, global, public variable to transfer this text's ID to where I need to go, and when I get back to my form after whatever not in list event has finished, in my case on after_update, set the combobox to the id of the value that i just created and am storing in one of the variables above.. I could go that route, but it just feels unnatural.

I've referenced a good number of posts, like allen brownes not in list where he says editing is constraint, a few stackoverflow posts, AWF post asking why are you even using a combobox...

So I guess I'm just asking for direction. Am i approaching this in just a backwards way, should I have an add comment button that pops up a new dialog form to enter new comment, and then proceed through events of saving/moving on from that add comment pop up while doing the magic in the background?

I started to go that route with a requery but I open the form with the current combobox setup with openargs, and when it requeries, it goes all the way back to the first record in my recordset.. so I could maybe use recordsetclone to remember where I am, requery, go back to id that i've got in my recordset clone..

 

[tvanstiphout]

I looked at your db and it does not make sense to me. Starting with the relationships:

This means that each Spec is made up of many Tasks.
However, when I read the Spec records, these seem to be very minute instructions to fix something about an engine.
That would mean: Task = Fix engine. Specs = Do these 20 steps.
Did I get that right? If yes, your db design is incorrect.

Second point is about editing a Spec. Your current NotInList code then keeps the previous spec, and creates a new one with the modified text.
Is that the intended behavior? I would have thought that editing a Spec only happens when it is wrong: rather than connecting the blue wire to the red terminal, it should be the green wire. In such case we should Edit not AddNew.
Or is your aim more ambitious to say: we used to do these 20 steps to perform a task, but then we found a better way and we're now doing the same 19 tasks with the modified 20th. We need to keep the old "recipe" as well as the new one. There is slim to no evidence of this thinking in the current db design.

Third: Steps are not ordered. There is no sequence number.
I appreciate you may have tried to abbreviate a lot, thinking we would just focus on a combobox and its NotInList and AfterUpdate events, but I at least cannot do that without context.

 

[adhoustonj]

Thanks Tom
1st point comments -
the spec_inst stands for special instruction for the task. In the actual db there is also a task_text field.
So it is more like Task = Fix Engine, Task_Text = Do these 20 steps, Spec_Inst = (sometimes no spec_inst for the task) but verify/confirm 'x'

Made up example:
task_id: 1
task_text: attach connecting shaft between engine and transmission
spec_inst: verify yoke within +/- 2 splines

now engineering makes some changes, and spec_inst needs to say 'within +/- 3 splines'

2 point comments -
I want to create a new spec_inst with the modified text, and update tblTask with the new spec_id that was just created in tblSpec_inst. I thought that was what was happening, but it is adding the new spec_inst into tblSpec_inst, but keeping the old spec_id in tblTask.
We do need to keep the old recipe. This db has revisions so if the Process Document that is composed of these 20 tasks is on revision #8, and now I'm editing it to make revision #9 - I still need the previous 8 revisions to remain as they were. Maybe the spec was right at the time but the process changed over time.

3rd point -
We do have sequence but I just trimmed this down to bare bones to show the problem

 

[GPGeorge]

Thanks Tom
1st point comments -
the spec_inst stands for special instruction for the task. In the actual db there is also a task_text field.
So it is more like Task = Fix Engine, Task_Text = Do these 20 steps, Spec_Inst = (sometimes no spec_inst for the task) but verify/confirm 'x'

Made up example:
task_id: 1
task_text: attach connecting shaft between engine and transmission
spec_inst: verify yoke within +/- 2 splines

now engineering makes some changes, and spec_inst needs to say 'within +/- 3 splines'

2 point comments -
I want to create a new spec_inst with the modified text, and update tblTask with the new spec_id that was just created in tblSpec_inst. I thought that was what was happening, but it is adding the new spec_inst into tblSpec_inst, but keeping the old spec_id in tblTask.
We do need to keep the old recipe. This db has revisions so if the Process Document that is composed of these 20 tasks is on revision #8, and now I'm editing it to make revision #9 - I still need the previous 8 revisions to remain as they were. Maybe the spec was right at the time but the process changed over time.

3rd point -
We do have sequence but I just trimmed this down to bare bones to show the problem

It has long been my observation that trying to simplify a problem, i.e. trim it down to bare bones, obscures more than it makes clear and tends to be more confusing than simply explaining the situation and context. Context makes so much difference to understanding a problem that without it solving a problem becomes a matter of guesswork.

Please provide an actual version of the sample accdb, and explain as much of it as people need to understand the context.

 

[MajP]

I might be wrong, but I just cannot imagine that user interface could be in any way useable. That would be a horrible user experience IMO.
Maybe if your list of specs was at most 20 things ever, the user would have a chance. There is no way a user trying to assign a spec could pull down that list and find what they were looking for.

I envision something like this.

I add a Category and maybe even a sub category to specs. Looks to me there are things like ("engine assy, adaptor plate, wiring, greasing etc) Then on the right side of the form I envision having a subform in continuous view (instead of a list box). With ways to search and filter. This way I can see complete specs in a readable format. I would have find as you type capability and a way to filter by category and sub category using combos. Then I would have the ability to assign a found spec, create new spec from existing spec, create new spec from scratch.
This way you have an easily readable format, easy to search for an existing spec, easy to create and assign a new spec. You cannot read long text in a combobox.

Example.
Assume I categorize the spec. Maybe there is an "adj nut" Category
From the combo category box above the subform, I pick a category "Adj Nuts" and my subform returns these in a nice readable format

Specification
FIT SPECIAL TOOL TO ENG ADJUSTING NUT 2. VERIFY BIT IS ENGAGED IN NUT. VERIFY NO GAP EXIST BETWEEN ENGINE AND TRANSMISSION SHAFT HOUSING. TIGHTEN UPPER BOLT ENG ADJUSTING 2 FROM OUTSIDE OF ENGINE THROUGH ADAPTER… DRAWING-***-0A512-**SECTION 151
FIT SPECIAL TOOL TO ENG ADJUSTING NUT 2. VERIFY BIT IS ENGAGED IN NUT. TIGHTEN LOWER BOLT ENG ADJUSTING 2 FROM OUTSIDE OF ENGINE THROUGH ADAPTER. VERIFY CONTACT OF BOLT ENG ADJUSTING 2 AND TRANMISSION AND MARK… DRAWING-***-0A512-**SECTION 151
FIT SPECIAL TOOL TO ENG ADJUSTING NUT 2. VERIFY BIT IS ENGAGED IN NUT. QL UPPER BOLT ENG ADJUSTING 2 FROM OUTSIDE OF ENGINE THROUGH ADAPTER. VERIFY CONTACT OF BOLT ENG ADJUSTING 2 AND TRANSMISSION AND MARK… DRAWING-***-0A512-**SECTION 151

Lets assume this list is longer than what is shown. I would also have a textbox where I can type and search for a string.
I type "QL Upper" and it returns

Specification
FIT SPECIAL TOOL TO ENG ADJUSTING NUT 2. VERIFY BIT IS ENGAGED IN NUT. QL UPPER BOLT ENG ADJUSTING 2 FROM OUTSIDE OF ENGINE THROUGH ADAPTER. VERIFY CONTACT OF BOLT ENG ADJUSTING 2 AND TRANSMISSION AND MARK… DRAWING-***-0A512-**SECTION 151

I can then double click this to select and assign to task.
I would have a button to create New spec from current spec presenting a pop up that is editable with this default information. Edit the spec it will create a new spec and assign to task.

If that makes sense I can probably create a demo faster than it took me to type this.

 

[MajP]

If the lists of specs got real big and they do not really fall neatly into categories, it may need to get more complicated. Specs may have a child table of key words. The above specs may not simple by categorized as Adj Nut but are tagged
Adjustment Nut
Special Tool
Transmission
OA512

Then you search by one or more key words.

 

[MajP]

Here is a pretty good start IMO.
I know the existing Spec I am looking for has the word "Nut" in it. I search for existing specs with "Nut". I can double click to assign.

However none of the specs are exactly correct, so I want to create a new spec from existing Spec.

I did not add code to cancel so you would need to do a before update validation.

Hit OK and new Spec is added to the list.

I purposely did not automatically assign the new Spec to the existing task, but simply double click if you want.

I did not add a button to unassign a task which is easy. The FAYT is probably good enough to find existing tasks, but may want to add categories if the spec list gets to big.

This seems like a useable solution, if I was your users I would be hating on you with that combo idea.

If the FAYT by itself is not good enough I would go with a multiple keyword search like this (or both)

Solved - Search all words in field against all words in another field

I kind of disagree in a db because you can probably build a much better search using sql Like search. This is based on a pretty thorough demonstration of a couple of interfaces using (Soundex, Simil, Levenshtein, and just find as you type) Like?? Like won't find similarities in strings with...

www.access-programmers.co.uk

That way you can search for a spec with all or any Engine, Adaptor, OA512.

 

[adhoustonj]

Here is a pretty good start IMO.

I'm checking this out now @MajP, thanks! I like the concept, and I hear and nod my head at the points being made.

The task screen looks like this currently. Users aren't searching much for [special instructions] but it was made a combo so that the text could be reused and referred to by its special_instruction_id versus storing the actual text in the task table.

I was trying to mimic a text box whilst being a combobox with the not in list event.

In this db we have 150,000 tasks in tblTask, 7,500 task text descriptions in tblTask_txt, and 7,000 special instruction texts in tblSpec_inst.
The thought with the combo was avoid storing 150,000 tasks, 150,000 task text descriptions, and 150,000 special instructions all in tblTask.

But I think I should adopt a means similar to what you have presented to improve searchability..

 

[MajP]

150,000 special instructions all in tblTask.

If you went to a list so that many tasks could share the same SI, how many unique SIs would you have? 10s, hundreds, thousands, 10s of thousands? Even if you only have a couple of hundred unique SIs to reuse, there is still no way you could find what you wanted in a simple combox. Especially since they are long text and unreadable in almost any combo. I just can't see that being useable. I would think you need advance search capabilities to pull a spec from existing specs or then determine if you have to create a new one. This is not like searching for last names. It is easy to determine if Smith exists in a combo. It is far harder to determine if a spec already exists if there are 100 specs with some variation of "connect part A to part B"

Where this gets challenging is when X% of you specs get reused and X% are one offs. Lets say that is 50/50 and say 1000 specs. So 500 are used more than once and you have another 500 added to the list just to use it once. Normally what happens is as the list grows you get more one-offs.
This makes picking from the list cumbersome because it is filled with things only to be used once. You could make it easier with the ability to tag a spec in the list as one-off so it does not show up in future searches.

For me the solution depends on that percentage. If 90 percent of the time I reuse a spec then I will add those one-offs to the list and wade through tem. If it is the other way around then I might have a list, but I am not storing the foreign key. I would store the actual spec_inst in a text field. Then I can alos type one offs in the same field. I still could have the feature to create spec from existing, add to list.

I have never done it, but I guess you could have two fields in your task table
Spec_ID (fk to spec table)
Spec_Text (ability to add a one-off)

Then your query either shows the one-off text or the spec_inst if a spec has been selected.

 

[adhoustonj]

If you went to a list so that many tasks could share the same SI, how many unique SIs would you have? 10s, hundreds, thousands, 10s of thousands? Even if you only have a couple of hundred unique SIs to reuse, there is still no way you could find what you wanted in a simple combox

Right now 7,000 unique SI that are being shared among the 150,000 task records.
Of the 150,000 tasks - 10% have no SI, 89% reuse SI, and >1% (639) of tasks have a SI unique.

That >1% (639 tasks) is the user editing the special instruction text. Most SI is automatically handled by the db through revision control, or copying/duplicating tasks..

You've got me thinking outside of my box though, thank you. Hope I haven't been too frustrating.
I'm going to have to implement a better search/view feature for these.

*** Never mind this doesn't work *** For now - I am setting a tempVar in the AddNewToList function that my NotInList sub calls, and then setting my spec_inst as the tempvar once the function has returned to the sub.. I'm just surprised it has to be this way to get the new added value into the list..

Private Sub spec_inst_NotInList(NewData As String, Response As Integer)

Call AddNewToList(NewData, "tblSpec_inst", _
                             "spec_inst", "Special Instructions", "")
'Me.OrganisationCategoryID.Requery
Response = acDataErrAdded

Me.spec_inst = TempVars!tvSpec

End Sub

Option Compare Database
Option Explicit
Dim tvSpec As TempVars
Public Function AddNewToList(NewData As String, stTable As String, _
                             stFieldName As String, strPlural As String, _
                             Optional strNewForm As String) As Integer
    'On Error GoTo err_proc
    'Adds a new record to a drop down box list
    'If form name passed, then open this form to the newly created record

    'Declare variables
    Dim rst As DAO.Recordset
    Dim IntNewID As Long
    Dim strPKField As String
    Dim strMessage As String
   
    ' Display message box asking if user wants to add the new item
    strMessage = "'" & NewData & "' is not in the current list. " & Chr(13) & Chr(13) & _
                 "Do you want to add it to the list of " & strPlural & "?" & Chr(13) & Chr(13) & _
                 "(Please check the entry before proceeding)."

    If MsgBox(strMessage, vbYesNo + vbQuestion + vbDefaultButton2, "Add New Data") = vbYes Then
        Set rst = CurrentDb.OpenRecordset(stTable, , dbAppendOnly)
        rst.AddNew
        rst(stFieldName) = NewData                'Add new data from combo box
        strPKField = rst(0).name                  'Find name of Primary Key (ID) Field
        rst.Update
        rst.Move 0, rst.LastModified
        IntNewID = rst(strPKField)
        TempVars!tvSpec = IntNewID

        'if a form specified, then open the form with the primary key equal to the new record ID as the criteria
        If strNewForm <> "" Then DoCmd.OpenForm strNewForm, , , strPKField & "=" & IntNewID, , acDialog

        AddNewToList = acDataErrAdded                'Set response 'Data added'
        rst.Close
        Set rst = Nothing
    Else
        AddNewToList = acDataErrContinue             'Set response 'Data NOT added'
    End If

exit_proc:
   
    'rst.Close
    'Set rst = Nothing
    Exit Function

err_proc:

    'Call DisplayErrorMessage(err.Number, "AddNewToList")
   
    Resume exit_proc

End Function
'Notes:
'1. The Primary Key field must be numeric (long integer) and must always be the first field in the table.
'2. The 'Limit to List' property of the combo box must be set to 'Yes'
'3. strNewForm is opened in edit mode as the new record is added first and the form then opened to that record. A consequence of this is that other fields in the table must have their 'Required' property set to 'No' or a (valid) default property value set in the table design.
'4. FieldNamePlural is there simply to make the message grammatically correct; in the AddNewToList code the message box string generated as: strMessage = "'" & NewData & "' is not in the current list. " & Chr(13) ..... would result in the warning message (e.g.):
'London' is not in the current list. "
'Do you want to add it to the list of Cities?
'(Please check the entry before proceeding).
'5. If an edit form is opened (strNewForm <> "") then the field that is displayed in the combo box should be in a locked control on the form, alternatively remove the acDialog argument and ensure the combo box is re-queried when the form is closed.
'6. The form 'strNewForm' should have it's 'Allow Additions' and 'Data Entry' properties set to 'No' to prevent users from adding additional entries to the entry requested by the Not In List event.
'Enjoy!
'Edit1: 18/02/2013: Added Note 4
'Edit2: 02/03/2014: Added Notes 5 & 6.

 

[tvanstiphout]

Thanks Tom
1st point comments -
the spec_inst stands for special instruction for the task. In the actual db there is also a task_text field.
So it is more like Task = Fix Engine, Task_Text = Do these 20 steps, Spec_Inst = (sometimes no spec_inst for the task) but verify/confirm 'x'

Made up example:
task_id: 1
task_text: attach connecting shaft between engine and transmission
spec_inst: verify yoke within +/- 2 splines

now engineering makes some changes, and spec_inst needs to say 'within +/- 3 splines'

Thanks for this, but it does not QUITE get to the core of my question: can one task have many specs, or can each spec be part of many tasks, or both.
That would make it a 1:M, M:1, or M:M relationship.

 

[adhoustonj]

Thanks for this, but it does not QUITE get to the core of my question: can one task have many specs, or can each spec be part of many tasks, or both.
That would make it a 1:M, M:1, or M:M relationship.

tblSpec_inst is 1:M to tblTask

Each spec may be part of many tasks.

 

[MajP]

I still do not get how using a combo and not in list could reasonably work with long text and a list of 7k items.
Again, I could see this working with a list of names, but cannot see it being to be useable with long text fields.

Example with Names.
I want to add "Jones"
I type in J and a standardlist box moves to the first J name. Then O and the first JO name. Then N and if there is no JON names the list goes blank.
So I keep typing the name and then hit return triggering not in list. This would be workable even with a huge list.


With 7000 specs, inconsistent formatting, long text, and I assume users do not have 7k entries memorized
I think it is something like "DRWG 0A244-1"

I type DRW and find nothing (create new record)
I type Drawing and find nothing (create new record)
I type [DRWG 0A and find nothing (create new record)

The thing I am looking for is actually in the database starting with (
(DRWG VIEW T-T) [***-0A244-1*

I just cannot see how your are going to find what you want by typing in the text. You are simply going to create multiple versions of the same content written differently. I am kind of amazed how you currently have assigned 89% from the existing list.
I might be missing something, but I think you would have to have robust search capabilities like I demoed.

 

[adhoustonj]

I still do not get how using a combo and not in list could reasonably work with long text and a list of 7k items.

I don't know if I get it either.... Honest.

In your example:
"(DRWG VIEW T-T) [***-0A244-1*"
This would like be updated to some of the following
"(DRWG VIEW T-T)"
"(DRWG VIEW T-T) [***-0A244-10=***]"
"(DRWG VIEW T-T) [***-0A244-20=***]"

But there is no need to go to a different task and select that special instruction from the dropdown.
But if this is revision 1, and 20 revisions down the road, rather than have 20 records that all store the text, I was hoping to store the text once in its own unique distinct table and reference it's FK in my table, and a combobox on a form.

I want a combobox that acts like a text box. People aren't using the dropdown to search, or find anything to select. - select is a bad word for me to use, because I used it in post two - what I should say is something that the combobox will accept, or if it already exists, update to the bound ID column of the combobox.
The only time that this combobox is used is to update an existing value by adding to it, removing some of the text, or create a new text value - through means as if was a text box. If it exists, great, if not, let's get it added, updated, and the present value of the combobox.

But - my most recent issue, that I thought the NotInList event would fix - is 'editing' a text in the combo. Adding words to an existing text works fine, but taking words out of existing text typically doesn't work the 1st time, sometimes it does the 2nd time, but sometimes it is editing the existing text by removing the word you want removed 5 times, and then it finally takes it and stores that value in the combo after the NIL event has finished.

So examples:
existing text: the brown fox jumps over the fence
works: the brown fox jumps over the fence twice
does not work: the brown fox jumps the fence

The main reason this text is reused is due to revisions, or due to model years. Our processes are specific to the year. But the process might not change much.. Like lets say you build a car, 2024 Oldsmobile 442, engineering will need to release 2025 build plans, engineering documents, etc once it comes time to produce the 2025. Many of the tasks/processes remain the same, but a new document is produced. This is where most duplication of special instructions/SI/spec_inst come from, a new set of records being produced. And we can't use the old ones, because we need to always be able to refer back to the previous sets of documents. Similar to below..

process_id	revision	tblTask_task_id_range_used	tblSpec_inst_spec_id_range_used
1	1	1-20	1-20
1	2	21-40	1-20
1	3	41-60	1-19,21
1	4	61-80	1-18,21,22
2	1	81-100	1-18,21,22

Maybe there is a better way to do that with dates and only creating new records, or having a junction table of modified tasks, revision, and always grabbing the original rev 1 records where no records exist in the modified junction table, and all where exist in the modified junction table..

Is there a way to make a combobox that acts like a text box?

 

[MajP]

If I understand, the user is not creating a new task then searching the long list for a specific spec. For the most part the user has all the tasks with exisiting specs assigned. They just need to update the existing spec by actually creating a new spec based on the existing spec and reassigning it to the new spec.

Would something like this work?
You want to create a new spec based on the existing spec and assign it.

Click the button and start editing by adding or removing text

I did not add cancel code at this time but if you select OK

The first image is wrong image. I started with task 4 associated to spec 24 as in image 2. Then I clicked the button and created new spec 25 based off the existing text. Hit OK and reassigned the new spec 25.