A Dirty NotInList

[TeeSee]

In the following code fpkCompany is a ComboBox on a

subform. I am tabbing out of the subform but NOT going to a

new record or closing the form. I get error(s) pertaining

to the WHERE part of my OpenForm statement presumable

because the SubForm data has not yet been saved. I have

tried a Me.Dirty=False both at the form Current event level

as well as adding it into the following code. Neither

solved my problem. tblCompany DOES update with the NewData.

So here are the questions.

1) Can I open the frmCompany form from the combo without

first closing out the record?
2) Can you force a save (Me.Dirty=False) from the control

level as well as the form level
3) Is there different syntax for the WHERE part when a

subform is involved.
4) What is the correct to do this?

Private Sub fpkCompanyID_NotInList(NewData As String,

Response As Integer)
Dim db As DAO.Database
Dim strSQL As String
If vbYes = MsgBox("'" & NewData & "' is not a current

Company." & vbCrLf & "Do you wish to add it?", vbQuestion +

vbYesNo, " ") Then
Set db = DBEngine(0)(0)
strSQL = "INSERT INTO [tblCompany]

([txtCompanyName]) VALUES('" & NewData & "');"
db.Execute strSQL

Response = acDataErrAdded
Set db = Nothing
Else
Response = acDataErrContinue
End If
DoCmd.OpenForm "frmCompany", acNormal, , "[fpkCompanyID] ="

& Me.pkCompanyID, acFormEdit, acDialog
End Sub

Thanks as always.

 

[Arvin Meyer MVP]

Moving between form and subform, or visa-versa, saves the record being
edited. If there is anything which denies that save, like an unmet
validation rule, an error occurs.