Re: Using NotInList to update tabel with two values

You might try it like this:
strSQL = strSQL & "VALUES('" & NewData & "','" & Region & "');"


"NielsE" <NielsE@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:53851A24-C3FF-41DD-81C2-70B9FA59BDC8@xxxxxxxxxxxxxxxx
> 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


.

Relevant Pages