Using the NotInList event and updating two fields the table

[NielsE]

I have a data entry form where country(ies) (there can be more than one for
each project) is/are entered in a sub-form on a tab control. Countries not
listed are entered by the following:

Private Sub CountryName_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim strMsg As String
Dim ctl As Control
Set ctl = Screen.ActiveControl

strMsg = "Country " & NewData & " Is not listed!" & vbCrLf & "Do you want to
add it?"
If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then
strSQL = "INSERT INTO tblCOUNTRY (CountryName) "
strSQL = strSQL & "VALUES('" & NewData & "');"
CurrentDb.Execute strSQL
Response = acDataErrAdded
Else
ctl.Undo
Response = acDataErrContinue
End If
End Sub

This works fine. However, if I restrict the country names by a region
selection on the main form (for example if region Africa is selected on the
the African countries are preselected) the NotIn List does not work. Any way to work around this?

Another question, I want the user to select a region (a combo box ) on the
main form and when a new country (using the NotInList event) is entered in the subform both values go into
the same record in tblCountry (which has two fields CountryName and Region).
For example, I enter AFRICA for region on the main form and on the sub-form I enter Malawi which is not listed. I have tried the following:

Private Sub CountryName_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim strMsg As String
Dim ctl As Control
Set ctl = Screen.ActiveControl

strMsg = "Country " & NewData & " Is not listed!" & vbCrLf & "Do you want to
add it?"
If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then
strSQL = "INSERT INTO tblCOUNTRY (CountryName, Region) "
strSQL = strSQL & "VALUES('" & NewData & "', FORMS!frmAddPro!Region);"
CurrentDb.Execute strSQL
Response = acDataErrAdded
Else
ctl.Undo
Response = acDataErrContinue
End If
End Sub

But it does not work (Region is the control on the main form named frmAddPro).
Any suggestions would be welcome. Thanks.
Niels

 

[RuralGuy]

Try:
strSQL = strSQL & "VALUES('" & NewData & "', '" & FORMS!frmAddPro!Region & "');"
or:
strSQL = strSQL & "VALUES('" & NewData & "', '" & Me.Parent!Region & "');"

Edit: Fixed my missing "s

 

[NielsE]

Thanks, it worked when I added inverted commas before & FORMS!...

 

[delamerbleu]

the code doesn't work, can u help?

I also have similar problem. I used the code suggested. But it doesn't work.

See my code below:

Private Sub cbo_MediaAffiliation_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim strMsg As String
Dim ctl As Control
Set ctl = Screen.ActiveControl

strMsg = "Media Affiliation '" & NewData & "' is not listed!" & vbCrLf & "Do you want to add it?"
If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then
strSQL = "INSERT INTO Tbl_InfoSource (InfoSource, SourceCate2)"
strSQL = strSQL & "VALUES('" & NewData & "', '" & Forms!frmAddPro!SourceCate2 & "');"
CurrentDb.Execute strSQL
Response = acDataErrAdded
Else
ctl.Undo
Response = acDataErrContinue
End If
End Sub

Can you help? Ideally, I'd like to have the Tbl_InfoSource pop up (with the InfoSource always add the new data) to allow me to key the SourceCate2 (which is the category for the InfoSource). Then, I can select the newly added data in the cbo_MediaAffiliation combo box. But how can I do that?

 

[RuralGuy]

You said it doesn't work. What does it do? Do you get an error? Is the form frmAddPro open?