combo box rowsource requery/refresh after edit problem

[Al]

I have a form (frmProdLineMaint) with a combo box (cmbProdLineID) which is
tied to a subform on the form (this works) and a command button that edits
the underlying table (tblProdLine) which is also the rowsource for the combo
box. The rowsource SQL looks like this and it works:

SELECT DISTINCTROW tblProductLine.idsProdLineID,
tblProductLine.chrProdLineName, tblProductLine.lngProdID FROM tblProductLine
WHERE (((tblProductLine.lngProdID)=Forms!frmProdLineMaint!cmbProdID)) ORDER
BY tblProductLine.lngProdID, tblProductLine.intSortSequence;

My problem is requerying the rowsource of cmbProdLineID when there has been
an edit/addition to tblProdLine File and having it display after the update
form is closed. The new entry (or edit) does not show in the combo box after
the edit.

My vba to open the update form and requery looks like this:

Private Sub cmdProdLine_Click()
DoCmd.OpenForm "frmProductLine"
Me.cmbProdLineID.SetFocus
Me.cmbProdLineID.Requery
end sub

What am I missing?

 

[Ken Snell [MVP]]

I assume that the entry of the new product line is done in the form
frmProductLine? If yes, you need to open that form in Dialog mode so that
your code stops until the form is closed or hidden. As now written, the code
opens the form and then continues on with the other two steps. Try this:

Private Sub cmdProdLine_Click()
DoCmd.OpenForm "frmProductLine", , , , , acDialog
Me.cmbProdLineID.SetFocus
Me.cmbProdLineID.Requery
End Sub

 

[Al]

Ken,

Thankyou!! That worked.

Just added the "acDialog" to the FormOpen.

 

[Ken Snell [MVP]]

You're welcome.
--

Ken Snell
<MS ACCESS MVP>

Ken,

Thankyou!! That worked.

Just added the "acDialog" to the FormOpen.