NotInList needs a little tweak

[Tarnish]

Hi all.

I have a notInList event on my main form that opens a popup form and parses the user's entry into the appropriate controls on that popup form. All works well EXCEPT:

IF the user makes changes to what was parsed into the popup form, usually that causes an error message when the "ok" button on the popup is clicked. The error message indicates that the name is 'not in the list', the text part of the combo box is blank, BUT the actual entry based on the modified values typed into the controls on the popup form IS available as a choice in the combobox.

Sometimes, depending on exactly what changes were made, these modified values are reflected BOTH in the dropdown list of the combo box AND in the text area of that combo box. I want the changes to ALWAYS be reflected like that so the error message doesn't come up and the user doesn't have to find the entry in the dropdown (or type it again).

Here's the notInList event code on my main form:

Private Sub Requesting_Party_NotInList(NewData As String, Response As Integer)
    Dim mbrResponse As VbMsgBoxResult
    Dim strMsg As String

    On Error GoTo Requesting_Party_NotInList_Error
        
    strMsg = "Add " & NewData & " as a new Party?"
    mbrResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "Invalid Party Name")
    Select Case mbrResponse
    Case vbYes
        DoCmd.OpenForm "PopupContacts", DataMode:=acFormAdd, WindowMode:=acDialog, OpenArgs:=NewData
        Me.Requesting_Party.Undo
              
        If IsLoaded("PopupContacts") Then
            Response = acDataErrAdded
            DoCmd.Close acForm, "PopupContacts"
        Else
            Response = acDataErrContinue
        End If
    Case vbNo
        Response = acDataErrContinue
    End Select

Exit_Requesting_Party_NotInList:
    Exit Sub

Requesting_Party_NotInList_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Requesting_Party_NotInList of VBA Document Form_Issues"

End Sub

The popup form basically just has code behind the 'ok' command button 'on click' that makes the popup form invisible.

Can anyone see where the code is messed up and not properly getting ALL the changes made on the popup form to the original data typed in the main form combo box?

Changes that ARE properly reflected include: adding a middle initial (MI) when there wasn't one originally and adding letters to the first name when there is NOT a middle initial. Changes that are NOT reflected include ANY change made to the last name on the popup form, or additions to the first name when there's a pre-existing entry for MI.

I'm just stuck here. Any help would be greatly appreciated.

Thanks,
T

 

[TheAceMan1]

How are ya Tarnish . . .

[blue]IF the user makes changes to what was parsed into the popup form, usually that causes an error message when the "ok" button on the popup is clicked.[/blue]

In the PopUp form [blue]disable the control(s)[/blue] after the data is parsed:

[blue]   Me![purple][b][i]TextboxName[/i][/b][/purple].Enabled = False[/blue]

This will stop user intervention of the parsed data.

Next you need too leave the [blue]Textbox portion[/blue] of the combo intact! Reason being [blue]acDataErrAdded[/blue] performs a final comparsion and update of the combo. If the [blue]Textbox portion[/blue] is empty or doesn't match an item in the updated list . . . you've already seen the error. So in your code disable this line:

[blue]   [green]'Me.Requesting_Party.Undo[/green][/blue]

One other Item . . . you need to be able to apprise the NotInList event wether data was actually added to the table by the popup (suppose a user aborts and closes the popup)!

Finally:

[blue]The format of data in the combo list dictates the format for data entry in the combo![/blue]

[blue]Your thoughts? . . .[/blue]

See Ya! . . . . . .

 

[Tarnish]

Thanks for the reply, TheAceMan,

I do have a 'cancel' button on my popup form. It just didn't seem relevent given it's use would negate the situation I was trying to address in my post.

Two other points:

If you mean lock the controls on the popup form where the data from the main form combo is parsed to, I hadn't really thought about that though my initial reaction is that I don't want to do that.

It would allow users a second chance to view (but not change) the data they typed in on the main form, and then cancel out of it if it's wrong. That's a benefit. But all in all I'd prefer them to be able to go ahead and make a correction if they notice a mistake on the popup form. That likely will result in occasinal errors (duplicate entries due to mispelling on the main form), but I suspect those won't happen often and the db isn't going to be adding 100 new records a day or anything like that, so I think a small amount of maintenance would be worth the convenience of trying to get it working the way I described in my first post.

On the Me.requesting_party.undo line, I think having that line allows me to make some changes on the popup form that actually reflect back in the text portion of the combo box on the main form, whereas not having it doesn't allow any corrections to pass through properly. Either way, if the wrong kind of correction is made I'm still going to get that error message. So, it's really just a benefit in some limited situations at no cost that I can see by testing it.

Anyway, thanks a lot for the input.

T

 

[Remou]

I can't quite see how the code can continue without some sort of a close button on the pop-up dialog. After that, if the user is allowed to change the information in the pop-up, acDataErrAdded will not work. How about:

    Case vbYes
        DoCmd.OpenForm "PopupContacts", DataMode:=acFormAdd, WindowMode:=acDialog, OpenArgs:=NewData
        Me.Requesting_Party.Undo
              
        'If isloaded("PopupContacts") Then
            Response = acDataErrContinue
            Me.Requesting_Party.Requery
            Me.Requesting_Party = Forms!popupcontacts.Institution
            'Response = acDataErrAdded
            DoCmd.Close acForm, "PopupContacts"
        'Else
        'End If
    Case vbNo
        Response = acDataErrContinue
    End Select

With a close button on the pop-up that hides the form until the data is processed.

 

[Tarnish]

Thanks Remou,

I'll give a look at what you have there.

I can't quite see how the code can continue without some sort of a close button on the pop-up dialog.

There is. I said as much here:

The popup form basically just has code behind the 'ok' command button 'on click' that makes the popup form invisible.

which appears just below the code in my first post.

Again, I'll check out your suggestion and thanks for taking the time to respond.

T

 

[Remou]

Sorry, missed that bit, well, the rest of the idea should fit that nicely.

 

[Tarnish]

Remou,

I've finally had a chance to look at and try that code...sorta.

I don't really understand where this line comes from:
Me.Requesting_Party = Forms!popupcontacts.Institution

There is no 'Institution' object in my project. Can you explain what that line is suppose to accomplish?

Thanks again,
T

 

[Remou]

I tested with my own data, so control names are imaginary. Change 'Insitution' to whatever you have called the control on the pop-up with the Requesting_Party.

 

[Tarnish]

Remou,

Why are the lines commented out in the code you supplied?

T

 

[Remou]

They should not be becessary, but I left them in to show the changes I had made.

 

[Tarnish]

Thanks for the quick reply,

The code I have to use to replace that line (Me.requesting_party = ...) is this:

Me.Requesting_Party.Text = Trim(Forms!PopupContacts.txtLastName & ", " & Forms!PopupContacts.txtFirstName & " " & Forms!PopupContacts.txtMI)

The result, however, is that while the correct entry DOES appear on the main form in the text part of the combo box (after clicking on 'ok' on the popupcontacts form), having that line in the code somehow interferes with the combo box requery in terms of the drop down choices. With that line in the code, the 'new' choice isn't in the drop down list and doesn't appear until I completely close and reopen the database.

Sigh...

Again, thanks for the help.
T

 

[Remou]

Did you requery as I suggested?
[tt]Me.Requesting_Party.Requery[/tt]

Has the pop-up record been saved before being hidden?

 

[Tarnish]

Yeah, it's in there just like in your code. I don't understand it either.

Before I was positive about what your commenting meant in your code, I messed around with all kinds of things trying to figure it out and the closest I've come is by taking that line of code (the Me.requesting_party = ....) and moving it to the "on_click" event of the popupcontacts 'ok' button. That produced an error with the "me.invisible" line already in that procedure, so I commented it (me.invis) out.

The result of those changes (your code, minus that line which was moved as explained) was that I got both the modified name entry in the text area AND the drop down, but I STILL got the error message.

So I'm back to your code and trying to figure out why the new name isn't appearing in the list...

Thanks again,
T

 

[Tarnish]

Rem said:

Has the pop-up record been saved before hidden

Bingo!

I tried a public procedure I wrote for saving but that didn't seem to work. Then I used an IF statement:

If me.dirty = true then
me.dirty = false
end if

And it works like a charm!

Thanks a ton for the help.
T