New lookup value not showing up until main form is closed and reopened...

Fred Fisher
Fred Fisher used Ask the Experts™
on
I have a subform (frmMMSongComposer) located in the footer of another subform  (subfrmSongs) which is a continuous form located on the main form (frmTitles).

Subform frmMMSongComposer is based on a query qryFRMMMSongsComposers.  The form frmMMSongComposer contains a combo box that when selected pulls up a list of composers sorted by ComposerLastName, ComposerFirstName.  The not in list is set to yes.  I have written code for the not in list event that pulls up the form frmMaintComposers where I add the new name and then close the form by clicking the "x" in the upper right corner of the dialog box BUT when I close the form the new Composer is not added to the table tblComposers and the qryFRMMMSongsComposers is not update UNLESS I close the main form (frmTitles) and then reopen it.

I have tried all weekend to get this to work by requerying this and refreshing that but nothing works.  

The code for the not in list event is:

Private Function IsLoaded(strName As String, Optional lngType As AcObjectType = acForm) As Boolean
    IsLoaded = SysCmd(acSysCmdGetObjectState, lngType, strName) <> 0
End Function


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

Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String
strMsg = NewData & " Composer is not in the database.  " & "Would you like to add a new Composer?"
mbrResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "Invalid entry")

Select Case mbrResponse

    Case vbYes
   
        DoCmd.OpenForm "frmMaintComposers", DataMode:=acFormAdd, WindowMode:=acDialog, OpenArgs:=NewData

        If IsLoaded("frmMaintComposers") Then
              Response = acDataErrAdded
              DoCmd.Close acForm, "frmMaintComposers"
        Else
              Response = acDataErrContinue
        End If
   
    Case vbNo
   
        Response = acDataErrContinue

End Select
End Sub


Private Sub Form_Load()

If Len(Me.OpenArgs) > 0 Then
   Me.ComposerID = Me.OpenArgs
End If
End Sub



Private Sub cmdCancel_Click()
   
    Me.Undo
    DoCmd.Close acForm, Me.Name
End Sub


Private Sub cmdOK_Click()
     Me.Visible = False
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,

beginning with Access 2010 you can define a data entry form directly in the property of the combobox. On this way you do not need the "Not in List" event and Access automatically requeries the combobox after closing the data entry form. You should also set the property of this form to "Data Entry=Yes" so that it automatically only shows a new blank record.

Access automatically also shows a transparent "Add" button below the combobox when it is opened, you can also enter a new value and Access will ask if a new value should be entered, clicking "Yes" will show the defined data entry form.

If you want to stay with the "classic" approach you need to requery the combobox after closing the data entry form by yourself.

Cheers,

Christian
Fred FisherPhotographer

Author

Commented:
"beginning with Access 2010 you can define a data entry form directly in the property of the combobox. On this way you do not need the "Not in List" event and Access automatically requeries the combobox after closing the data entry form. You should also set the property of this form to "Data Entry=Yes" so that it automatically only shows a new blank record."

Ok so how, giving my situation, do I do that with my combo box that uses this query as the Record Source?

SELECT tblComposers.ComposerID, ([ComposerLastName] & (", "+[ComposerFirstName])) AS Display, tblComposers.ComposerLastName, tblComposers.ComposerFirstName
FROM tblComposers
ORDER BY tblComposers.ComposerLastName, tblComposers.ComposerFirstName;

As far as the classic approach went I tried to requery the combo box using the On Exit event and I used .update, Me!Refresh and nothing worked.
Hi,

if your combobox has the name "ctlComposerID" for example then it would be "Me.ctlComposerID.Requery" because you want to requery the contents of the combobox dropdown list only. "Me.Refresh" would reload the records of the current form instead.

By the way, you do not need the "IsLoaded" function as this is already existing in Access:
CurrentProject.AllForms("FormName").IsLoaded

Open in new window


Cheers,

Christian
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Fred FisherPhotographer

Author

Commented:
Me.ctlComposerID.Requery" does not work.  Combo box list is not updated.
Hi,

the question is, where did you enter this codeline? And is it ever executed? Did you add a breakpoint to see if it is executed?

"Does not work" is not helpful, I cannot see what you made. For deeper analyzation you would need to upload a demo database.

Cheers,

Christian
Fred FisherPhotographer

Author

Commented:
Where should it go?  It does not work in:

        If IsLoaded("frmMaintComposers") Then
              Response = acDataErrAdded
              DoCmd.Close acForm, "frmMaintComposers"
        Else
              Response = acDataErrContinue
        End If
       Me.ComposerID.Requery

Or

    Case vbNo
   
        Response = acDataErrContinue
         Me.ComposerID.Requery

End Select

Or

 If IsLoaded("frmMaintComposers") Then
              Response = acDataErrAdded
              DoCmd.Close acForm, "frmMaintComposers"
        Else
              Response = acDataErrContinue
               Me.ComposerID.Requery
        End If
     

Using breakpoints I determined that it is skipping the IsLoaded and executing the Else statement, none of these three examples worked.

I would upload a demo database but even if I remove all of the data except for a couple of records it is over 50mb's in size.
Hi,

the problem is that you open the entry form as acDialog (which is OK):
 DoCmd.OpenForm "frmMaintComposers", DataMode:=acFormAdd, WindowMode:=acDialog, OpenArgs:=NewData

But that stops the code in the calling form until the frmMaintComposers is closed - so your If will never see a loaded form.

As you do not know after closing the form if the user really has added a new record simply write "Me.ComposerID.Requery" after the "DoCmd.OpenForm..." command so it will be requeried in all cases.

Why should "acDataErrAdded" be the right response if the opened form is still loaded? And why should it be "acDataErrContinue" if it is not? That makes no sense. Set the Response to "acDataErrAdded" if you directly add the data to the records with the VBA code.

Here is an example how to use the "NotInList" event:

If MsgBox(NewData & " Composer is not in the database.  " & vbcrlf & "Would you like to add a new Composer?", vbYesNo) = vbYes Then
    Response = acDataErrContinue
    DoCmd.OpenForm "frmMaintComposers", DataMode:=acFormAdd, WindowMode:=acDialog, OpenArgs:=NewData
    Me.ComposerID.Requery
Else 
    Response = acDataErrContinue
    Me.ComposerID.Undo
End If

Open in new window


Cheers,

Christian
Fred FisherPhotographer

Author

Commented:
I have replaced the code with yours but when it gets to the line Me.ComposerID.Requery I get the error message:  Runtime error 2118. You  must save the current field before you run the requery action.
Hi,

sorry, my fault. The "NotInList" event doesn't allow to requery the combobox, instead it requeries it automatically if the response is "acDataErrAdded". So change the code above to:

If MsgBox(NewData & " Composer is not in the database.  " & vbcrlf & "Would you like to add a new Composer?", vbYesNo) = vbYes Then
    DoCmd.OpenForm "frmMaintComposers", DataMode:=acFormAdd, WindowMode:=acDialog, OpenArgs:=NewData
    Response = acDataErrAdded
Else 
    Response = acDataErrContinue
    Me.ComposerID.Undo
End If

Open in new window


After that it should work.

Cheers,

Christian
Fred FisherPhotographer

Author

Commented:
Thanks for the assistance.  It took a bit of work but we got it to work!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial