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

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
Fred FisherPhotographerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BitsqueezerCommented:
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
0
Fred FisherPhotographerAuthor 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.
0
BitsqueezerCommented:
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
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Fred FisherPhotographerAuthor Commented:
Me.ctlComposerID.Requery" does not work.  Combo box list is not updated.
0
BitsqueezerCommented:
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
0
Fred FisherPhotographerAuthor 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.
0
BitsqueezerCommented:
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
0
Fred FisherPhotographerAuthor 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.
0
BitsqueezerCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fred FisherPhotographerAuthor Commented:
Thanks for the assistance.  It took a bit of work but we got it to work!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.