Using NotInList to update tabel with two values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.

Another question, I want the user to select a region (a combo box ) on the
main form and when the country is enetered 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
eneter Mlawi 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.
Niels
 
You might try it like this:
strSQL = strSQL & "VALUES('" & NewData & "','" & Region & "');"
 
Back
Top