NotInList not working in some forms, okay in others

[fire2ice]

The following example code works fine in one of my forms but does not work correctly in two others. My issue arrives when I enter the new data in frmAddSponser and return to the form that called this form. The Response = acDataErrAdded is supposed to requery the combobox and allow you to exit it without retriggering the NotInList even. However, two of my forms still trigger this even while the other does not. I have checked multiple times to verify syntax.

The only difference between the two nonfunctioning events and the one that acts appropriately is that the two nonfuncting are on subforms being called from a form whereas the working event is directly on the form. However, the event does not work properly when I'm on the forms used as subforms either.

This has consumed an inordinant amount of my life and I'm going to put my head through the comp screen soon. Any help is greatly appreciated. Thanks in advance.

--------------------------------------------------------------------------

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

Dim Reply As String

If MsgBox("This Sponsor is not currently part of the Sponsor list. Do you want to add this Sponsor?", vbOKCancel, "Confirm add new Staff Member") = vbOK Then

DoCmd.OpenForm "frmAddSponsor", acNormal, , , acFormAdd, acDialog, NewData
Response = acDataErrAdded

Else
Me!cboSponsorName.Value = Null
Response = acDataErrContinue

End If

Response = acDataErrContinue

Exit Sub

End Sub

 

[fire2ice]

Bad gets worse

Now the code is failing in all 3 forms and I haven't changed ANYTHING! I'm using Access 2007 (in compatibility mode for Access 2002 and 2003) on Windows Vista. Is there some instability that can cause code to malfunction?

 

[RuralGuy]

If this is a direct copy and Paste then you have an extra
Response = acDataErrContinue
just before you exit the sub.

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

   Dim Reply As String

   If MsgBox("This Sponsor is not currently part of the Sponsor list. Do you want to add this Sponsor?", vbOKCancel, "Confirm add new Staff Member") = vbOK Then

      DoCmd.OpenForm "frmAddSponsor", acNormal, , , acFormAdd, acDialog, NewData
      Response = acDataErrAdded

   Else
      Me!cboSponsorName.Value = Null
      Response = acDataErrContinue

   End If

   [COLOR="Red"]Response = acDataErrContinue[/COLOR]

   Exit Sub

End Sub

 

[fire2ice]

RG,

That fixed it. I put that in there earlier in the process to fix a different issue, but that's what I get for thinking. In my attempts to make my database "easy" for the end user, I am trying to automate as much as possible. However, I'm trying to build in 2nd chances as well. Even though they have to say OK to move to the frmAddSponsor, I wanted to have a cancel button in that form as well.

The reason I put the 2nd acDataErrContinue in was so that if the user cancelled the called form frmAddSponsor, it would Null cboSponsorName and let them try again. Without the 2nd acDataErrContinue, it would kick off the NotInList event again before setting cboSponsorName to Null. The code for cancelling the add once they got to the frmAddSponsor is below. Please tell me if I'm being too cautious or if it is possible to make both things work.

-----------------------------------------------------------------------------------------------------------------------------------

Private Sub cmdCancel_Click()

On Error GoTo Err_cmdCancel_Click

DoCmd.RunCommand acCmdUndo
DoCmd.Close
Forms!frmEvents.sfrmEventSponsors!cboSponsorName = Null

Exit_cmdCancel_Click:
Exit Sub

Err_cmdCancel_Click:
MsgBox Err.Description
Resume Exit_cmdCancel_Click

End Sub

-----------------------------------------------------------------------------------------------------------------------------------

As always, you are the go to guy.

Thanks.

 

[RuralGuy]

If the ComboBox is on a SubForm then the syntax is:

Private Sub cmdCancel_Click()

   On Error GoTo Err_cmdCancel_Click

'   DoCmd.RunCommand acCmdUndo
'   DoCmd.Close
   Forms!frmEvents.sfrmEventSponsors[COLOR="Red"][b].Form[/b][/COLOR]!cboSponsorName = Null
   Me.Undo
   DoCmd.Close acForm, Me.Name, acSaveNo
   
Exit_cmdCancel_Click:
   Exit Sub

Err_cmdCancel_Click:
   MsgBox Err.Description
   Resume Exit_cmdCancel_Click

End Sub

Refer to Form and Subform properties and controls

 

[fire2ice]

I appreciate the syntax correction. I've only been working with Access for a week and some of the intracacies are a bit overwhelming. The code set the combobox to Null with the code as it was. With your code I'm still running into the same issue (but I'll keep your code as I'm sure it's more efficient). It's not the NotInList code that I wrote that is kicking off; it's the built in NotInList event. My message box does not trigger; it is the standard message box built into access when something isn't in a list when the property is set to "Limit to List" = Yes.

Basically, my code is skipped but Access's built in function takes its place. This may be something that can be fixed with turning off error messages, but I'd need to turn them right back on. I'm not sure how or if that would work.

Thanks again.

 

[RuralGuy]

Try:
Forms!frmEvents.sfrmEventSponsors.Form!cboSponsorName.UnDo

 

[fire2ice]

Nope. That didn't change anything. The issue persists.

However, I chose = Null instead of Undo for a reason. Instead of emptying the field with a Null value, your way would perform an Undo which would undo the last entry. This means that if they typed something in and then deleted it and typed something else, then the undo would retype the deleted info instead of setting the field to Null.

 

[fire2ice]

Database attached as Zip file

RG,

Here is a copy of my database (cleaned of data). Since this is my first week using Access, I'm sure things aren't as clean as they should be. However, if you would like to take a look, I would be much obliged.

Thanks,
Dean

 

[RuralGuy]

You can trap that error in the Form's OnError event.

 

[fire2ice]

That worked great! However, I'm being left with an empty record in tblEventSponsors when I cancel. I thought the record wasn't supposed to be saved if I canecelled.

All of the orphaned records can be cleaned up with a delete query, but I'd rather not have them created in the first place. Any suggestions?

Once again, I cannot thank you enough. Not just for me, but for everyone you so selflessly help on this forum.

 

[RuralGuy]

You have a hidden control on the sfrmEventSponsers form that is bound to the EventID field. Simply delete the control from the form and the orphan records will stop. The LinkChild/MasterFields of a SubFormControl work as long as the field is in the RecordSource of the SubForm. It is not necessary to have a control bound to the Child field.

 

[fire2ice]

Okay. So I eliminate the hidden control and the relationship still exists. However, if I choose to cancel after I've said OK to the event triggered by the NotInList, it still creates a record in tblEventSponsors. Something in the click event on the frmAddSponsor is not undoing the record and still allows it to be created when I return to frmEvents.

 

[RuralGuy]

Try this one.

 

[fire2ice]

Okay oh wise one. It seems that this resolved the issue. It was a simple matter of an Exit Sub? I am just amazed at how I can mess things up so much with just a couple of words. I don't see that you did anything else, did you?

Thanks again. By the way, your helpfulness is like crack...once people get a taste, they will keep coming back for more. You are wonderful!

 

[RuralGuy]

The Exit Sub was just unnecessary so I deleted it. I think the solution was to UnDo the ComboBox rather than setting it to a Null.