Not In List Event to improve UX of Edit List Items

[MS ACCESS PROBZZZ]

Hello!

I need some help reducing the number of clicks it takes to get to the center of the Access pop.

I am using an Edit List Form but find the user experience unsatisfactory. The ghosted button is difficult to see and the location is awkward. I also don't like that once I've entered the new item and return to the original form, I have to then scroll through a long list to find the thing I just typed. Not very efficient. The new data should be passed from the Edit List Form to the original form field to automate the tedium.

I've been trying to solve this with the On Not In List Event. I like this in concept because I can type my new data in the original form and pressing enter will trigger the Not In List event.

However the text that I just worked so hard to type in the field should be passed to the related field in the launched edit form. Right now that's not happening. Additionally, when I return to the original form, the list does not update. (I get an error message that goes to this line: Me!Publication_IDFK = Me!txtNewPublication) It is added to the correct table but I have to close and reopen the form to see the new value appear in the combobox list.

Here's the code I'm using. I can't remember who the original author is. I do know I've tried multiple peoples code for this though.

*I have a hidden text box called txtNewPublication on the original form. I still don't understand the black magic behind why this is necessary though. I don't understand why the model is to set a form control equal to another control on the same form; as in Me!Publication_IDFK = Me!txtNewPublication. Why not something like Me!Publication_IDFK = [Forms]![Frm_PublicationEntry]![Publication_ID]??

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

    '* ask if the user want to add this Publication
    If MsgBox("Do you want to add " & Chr(34) & NewData & Chr(34) & _
        " to Publications?", vbQuestion + vbYesNo) = vbNo Then
        
        '* doesn't want to add this
        Response = acDataErrContinue
        
        '* clear the combo
        Me.Publication_IDFK.Value = ""
        SendKeys "{ESC}"
    Else
    
        '* This is a new Unbound textbox I made.
        '* It will contain the actual Publication_ID
        '* If Frm_PublicationEntry Saved and Closed
        Me!txtNewPublication = Null
        
        '* Yes, open Frm_PublicationEntry form
        DoCmd.OpenForm FormName:="Frm_PublicationEntry", View:=acNormal, WindowMode:=acDialog, OpenArgs:=NewData
        
        '* Frm_PublicationEntry will set [txtUnbound] to True
        '* if it click the Save And Close button on that form
        '* then when this happens we are sure that the NewData
        '* was already added to the Table and the the Combobox
        
        If IsNull(Me!txtNewPublication) = False Then
            Response = acDataErrAdded
            
            '* get the new Publication from Unbound Textbox
            Me!Publication_IDFK = Me!txtNewPublication
        Else
            
            '* user did not save the NewData
            Response = acDataErrContinue
            
            '* clear the combo
            Me.Publication_IDFK.Value = ""
            SendKeys "{ESC}"
        End If
        
    End If
    
    '* set Focus to the combobox
    Me.Publication_IDFK.SetFocus
        
End Sub

 

[GinaWhipp]

I guess I'm not getting why all the extra code and why something like the below won't work...

On Error GoTo Err_Publication_IDFK_NotInList

        Dim intAnswer As Integer

        intAnswer = MsgBox("Do you want to add " & Chr(34) & NewData & Chr(34) & _
        " to Publications?", vbQuestion + vbYesNo)

        If intAnswer = vbYes Then
            DoCmd.RunCommand acCmdUndo
            DoCmd.OpenForm FormName:="Frm_PublicationEntry", View:=acNormal, WindowMode:=acDialog, OpenArgs:=NewData
            Response = acDataErrAdded
        Else
            DoCmd.RunCommand acCmdUndo
            Response = acDataErrContinue
        End If

Exit_Publication_IDFK_NotInList:
        Exit Sub

Err_Publication_IDFK_NotInList:
        MsgBox Err.Description
        Resume Exit_Publication_IDFK_NotInList

 

[MS ACCESS PROBZZZ]

Hi Gina!

Yeah, it seemed to me like there was some unnecessary code in there too.

You're code is very helpful and does update the list. And it passes the entry from the edit form back to the original form!!!

The only thing it's not doing is passing the text entered on the original form to the edit form.

Do you have any suggestions?

Thank you so much, this is a huge improvement so far!

 

[GinaWhipp]

Okay, why? What is the purpose of the Text Box? That said, you should be able to add a line, something like...

Me.YourTextBox = NewData

 

[MS ACCESS PROBZZZ]

You mean instead of OpenArgs:=NewData ? Are you talking about an unbound hidden text box, like I had in the first code I pasted?

I don't fully understand the unbound text box strategy for this particular case. I got that from someone else and it might have been useful in another scenario. It might be useful here too, I just don't understand it.

Why not Me.Publication_IDFK = NewData instead of Me.unboundtextbox = NewData

I found this: http://www.baldyweb.com/wherecondition.htm. I know I'm not trying to open the second form to a specific record, because that record hasn't been created. But it seems like this line would be relevant to what I'm trying to do. DoCmd.OpenForm "SecondFormName", , , "FieldName = '" & Me.ControlName & "'"

Unfortunately VBA doesn't like it when I paste that in. I've tried multiple orders:

of something like this:

DoCmd.OpenForm FormName:="Frm_PublicationEntry", View:=acNormal, , , , "PublicationName = '" & Me.Publication_IDFK & "'", WindowMode:=acDialog, OpenArgs:=NewData

But it isn't liking it.

 

[arnelgp]

the unbound textbox serves ad a flag to determine whether the pop up form's record was saved or not.

 

[MS ACCESS PROBZZZ]

Thanks arnelgp!

I don't understand exactly.

It seems like the pop up record is being saved because it writes the record to the table. And also the main field of the new record passes from the popup back to the original form field. So I can see that it's been saved when the popup closes and the original form is active again.

I'm sure you're right, it's just above my head.

 

[GinaWhipp]

I was talking about the hidden Text Box. Since adjusting the code not sure it's needed.

 

[MS ACCESS PROBZZZ]

I don't know. I think only ever understand up to 75% of what is happening with any given chunk of VBA code.

Everything seems to be working fine to my eyes using the adjusted code, without using an unbound text box.

I have no clue what syntax to try to get it to pass the text entered in the original combo box - the text that triggers the Not In List event - to the edit form.

I thought it should be something like [Forms].[Frm_PublicationEntry].[PublicationName] = Me.Publication_IDFK

or [Forms].[Frm_PublicationEntry].[PublicationName] = NewData

and it seems like that would go just above Response = acDataErrAdded.

Doesn't work though.

 

[GinaWhipp]

If it's working fine without the hidden text box then remove it and don't give it another thought. And after reading arnelgp's post the other code needed it the one I posted does not.

 

[moke123]

I know I'm not trying to open the second form to a specific record, because that record hasn't been created. But it seems like this line would be relevant to what I'm trying to do. DoCmd.OpenForm "SecondFormName", , , "FieldName = '" & Me.ControlName & "'"

I use a public function for NIL events which is very flexible. I dont know who the original author is but it works great.

If you pass a form name in the argument, it adds the record and retrieves the PK of the new record. It then opens the form to the new record.

heres an example I did yesterday for someone else.

 

[MS ACCESS PROBZZZ]

Thanks Gina!

 

[arnelgp]

it will work without the hidden textbx if the pop uo was closed using thr button.
when the user closes the pop yp using the [X] on the form, what then, the record is not save and yiu have
a code in the main form, acDataErrAdded when actually it is not added. get it.

 

[MS ACCESS PROBZZZ]

Hi Moke,

Thanks for the code. If the db has only simple tables, it looks like it would work well. So far I'm only getting compile errors or function errors with it though. There are probably two main underlying causes for this. 1) Unfortunately I have multiple dependent fields of various field types which need to be entered as part of the Not In List record. I'm not sure whether or not your code would only allow text based fields. 2) My Publications table has a compound unique index. Tbl_Publications: AutoID | PublicationName | PublicationType. AutoID is the Primary Key. Name and Type are a unique index. I have it this way, for example, because most publications have a print division and digital division, but different staff for each.

The function error I get says: Error in Function: 'AddNewToList' You must enter a value in the 'Tbl_Publications.PublicationType_IDFK' field. Ie. ^See concern #2 above.

Your code

Option Compare Database
Option Explicit

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

Response = AddNewToList(NewData, "tblColors", "txtColor", "Colors", "pfrmColors")

End Sub

~ It's weird, I don't see an object or control in your db with the name "Colors"; just cboColors and txtColor; your table field is actually also called txtColor.

But anyway this creates compile errors when applied in my case as

Private Sub Publication_IDFK_NotInList(NewData As String, Response As Integer)
Response = AddNewToList(NewData, "Tbl_Publications", "PublicationName", "Publication_IDFK", "Frm_PublicationEntry")

I've also tried:
Response = AddNewToList(NewData, "Tbl_Publications", "PublicationName", "Frm_PublicationEntry")

and:
Response = AddNewToList(NewData, "Tbl_Publications", "PublicationName", "someword", "Frm_PublicationEntry")

I've made sure that in my db, PublicationName is the field name on the table as well as the control name on the form, to match your model.

I do like your idea of having a public function to make the ongoing programming require less code. But I'm not sure if your code can be modified to account for tables with a primary key and also an additional combined unique index. I'm guessing this is why most people seem to use DoCmd.OpenForm FormName: directly inside the control's event. Seems like it gives more flexibility for complex data relationships.

I can see in your model that Access does pass the new text entered in colors to the edit form pfrmColors field called txtColor. And that's really the only behavior I am lacking with my current solution. However I don't see which part of your code is specifically request that behavior from Access. So, I'm wondering if this is something that can actually be controlled with code? Or if it's a program default that works in some cases (simple tables), doesn't in others (multiple fields forming a unique index), and can't specifically be requested?

 

[MS ACCESS PROBZZZ]

Thanks arnelgp!

I just tested that. I added a new record to the popup and closed it with the X. But it is actually adding the new record to the list of the main form and also to the table.

I understand that maybe "X" might be thought of as a way to escape out of the form and not add a record. But seems to only perform as an escape if I haven't added data to each of my required fields.

If I do add data to each of my required fields, it acts just like a save.

I've therefore added a "Clear And Close" button to the popup form to undo any data entry and close the form. Hopefully that provides enough interface options.

 

[moke123]

If you are requiring additional fields in addition to the value of the combo box you would have to alter the code to include those fields.

        rst.AddNew
            rst(stFieldName) = NewData                'Add new data from combo box
            rst("YourAdditionalField") = Somevalue   ' <<< add an additional field here
            rst("YourAdditionalField2") = Somevalue2 '<<<  add an additional field here
            strPKField = rst(0).Name                  'Find name of Primary Key (ID) Field
        rst.Update

 

[arnelgp]

then it shouldnt be that way. if you are the inky user, thats fine. what uf others are also using and they are at first curiius and want to explore and test it. s they enter garbage data to the combo until they get to the pop up...where there is no bailing out.

 

[MS ACCESS PROBZZZ]

Hope everyone had a happy labor day! Thanks for all the suggestions. I will post the final code once I work it out.

I still don't understand the hidden textbox though. I must be missing something. Yes, it's possible for a user to add garbage data to the database if they've filled out all the required fields. Garbage data can be written whether the record is X-ed out of or whether it is saved and closed with the command button. I don't understand how the hidden textbox code would really prevent that.

I could make the form launch an additional message box before closing to check if the user is sure this is real data and not junk.

The only thing I can kind of abstractly grasp about the hidden textbox is that maybe it could somehow prevent two users from adding records to the same form at the same time? Because it's forcing a value into the field and holding it open? But I don't know nearly enough about what's going on under the hood in Access to know if that's necessary or if it would work.

I guess, when I look at this chunk of code here:

'* This is a new Unbound textbox I made.
        '* It will contain the actual Publication_ID
        '* If Frm_PublicationEntry Saved and Closed
        Me!txtNewPublication = Null
        
        '* Yes, open Frm_PublicationEntry form
        DoCmd.OpenForm FormName:="Frm_PublicationEntry", View:=acNormal, WindowMode:=acDialog, OpenArgs:=NewData
        
        '* Frm_PublicationEntry will set [txtUnbound] to True
        '* if it click the Save And Close button on that form
        '* then when this happens we are sure that the NewData
        '* was already added to the Table and the the Combobox
        
        If IsNull(Me!txtNewPublication) = False Then
            Response = acDataErrAdded
            
            '* get the new Publication from Unbound Textbox
            Me!Publication_IDFK = Me!txtNewPublication
        Else

I don't understand how Me!Publication_IDFK = Me!txtNewPublication is supposed to work, when upstream I just have the code If IsNull(Me!txtNewPublication) = False Then Response = acDataErrAdded and further upstream I have Me!txtNewPublication = Null.

In my silly human terms, I interpret the code in the following way:
the textbox is null by rule of code. But IF it's not null then data has been added. - I don't follow where in the code the magic is happening to set the textbox to isnull = false. The code says IF IsNull is false. Which is logic right, not a command?

Do I have this in the correct order? Should I swap the order of If IsNull(Me!txtNewPublication) = False Then and Me!Publication_IDFK = Me!txtNewPublication?

When I run this as part of the first code I posted, I get an error that highlights the line Me!Publication_IDFK = Me!txtNewPublication. And the list would not refresh unless I closed and re-opened the form.

 

[MS ACCESS PROBZZZ]

@moke123 I followed your suggestion with the code

        Set rst = CurrentDb.OpenRecordset(stTable, , dbAppendOnly)
        rst.AddNew
            rst(stFieldName) = NewData                'Add new data from combo box
            rst("PublicationType_IDFK") = somevalue        ' <<< add an additional field here
            strPKField = rst(0).Name                     'Find name of Primary Key (ID) Field
        rst.Update
        rst.Move 0, rst.LastModified
        IntNewID = rst(strPKField)

It's not working so far. I haven't had any luck with finding which "somevalue" will do what I need.

If I replace your placeholder

= Somevalue

with a real value, such as 1, the record gets written immediately bypassing the popup. And since the value will not always be 1, this won't work.

I've also tried

rst("PublicationType_IDFK") = null
rst("PublicationType_IDFK") = " "
rst("PublicationType_IDFK") = NewData

I get a function error: data type conversion error

Is there a way to say rst("PublicationType_IDFK") = temporarily empty, just launch the form and we'll take it from there?

 

[moke123]

Is there a way to say rst("PublicationType_IDFK") = temporarily empty, just launch the form and we'll take it from there?

IIRC since your using compound keys,No. you cant enter a record without entering both values. Without the compound keys the code i originally posted works.