combo box update on subform

[Pam Ammond]

I am trying to update a combo box (PartID) on the subform after going to
another form (frmPart) to enter the new value into the table (tblPart) used
as the source for the combo box.

Yes, I HAVE used code from other posts in this forum and spent 3 days trying
to play with the code to get it to work.

It doesn't give any errors when it runs, but the combo box dropdown on the
subform doesn't show the new value, although the value is in the database.
To get the new value in the combo box dropdown I have to escape out of the
subform line, go to the main form, and then come back to the subform.

Here's the relevant code:

Private Sub PartID_GotFocus()

Me![PartID].Undo
DoCmd.RunCommand acCmdSaveRecord
Me![PartID].Form.Requery
Me![PartID].Requery

End Sub

Private Sub PartID_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_PartID_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPart"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

Exit_PartID_NotInList:
Exit Sub

Err_PartID_NotInList:
MsgBox Err.Description
Resume Exit_PartID_NotInList

End Sub

Thanks in advance,
Pam

 

[Ron2005]

Have you tried a requery of the dropdown immediately after the call to
the add form and make the add form a dialog form. (I believe that is
what I ended up having to do one time on something similar.)
Ron

 

[Marshall Barton]

I am trying to update a combo box (PartID) on the subform after going to
another form (frmPart) to enter the new value into the table (tblPart) used
as the source for the combo box.

Yes, I HAVE used code from other posts in this forum and spent 3 days trying
to play with the code to get it to work.

It doesn't give any errors when it runs, but the combo box dropdown on the
subform doesn't show the new value, although the value is in the database.
To get the new value in the combo box dropdown I have to escape out of the
subform line, go to the main form, and then come back to the subform.

Here's the relevant code:

Private Sub PartID_GotFocus()
Me![PartID].Undo
DoCmd.RunCommand acCmdSaveRecord
Me![PartID].Form.Requery
Me![PartID].Requery
End Sub

Private Sub PartID_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_PartID_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPart"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

Exit_PartID_NotInList:
Exit Sub

Err_PartID_NotInList:
MsgBox Err.Description
Resume Exit_PartID_NotInList
End Sub

Your NotInList procedure is missing a couple of key
concepts. First, the OpenForm method must open the form in
dialog mode so your code pauses until frmPart is closed.

Second, you need to set the Response argument according to
how you want Access to deal with the what happened in the
NotInList procedure.

DoCmd.OpenForm stDocName, , , stLinkCriteria, _
acFormAdd, acDialog
Response = acDataErrAdded


I see no reason for the code in the GotFocus event. Try
getting rid of it.

 

[Pam Ammond]

Thank you Marshall ! Your solution worked perfectly. And you were right, I
didn't need the extra code in the GotFocus event.

For all those that look at this:
Even though other answers in this forum are simple, and easy to understand
as a solution to this problem, they wouldn't work for me. Marshall's answer
works well and I highly recommend it.

To summarize:
When a combo box (PartID) on a subform needs a new value added, you can
update the combo box (PartID) on the subform after going to another pop-up
form (frmPart) which enters the new value into the table (tblPart) used as
the source for the combo box.

Private Sub PartID_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_PartID_NotInList

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPart"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog
Response = acDataErrAdded

Exit_PartID_NotInList:
Exit Sub

Err_PartID_NotInList:
MsgBox Err.Description
Resume Exit_PartID_NotInList
End Sub