Trigger open form from Notinlist

[Nick-in-Paris]

I think the title sort of spells it out, but here is the whole question. Simple contacts dbase I have contacts that belong to organisations (usually companies). In my contacts form, the organisation that the contact belongs to comes from the organisation table (drop down). When I have an organisation that isn't in the list therefore Notinlist, I'd like the organisation form to open, so I can add the organisation and update the drop down list. Also be nice if my vba boxes would tell me the org isn't in the list and ask me if I really want to add it.
Apart from that the weathers lovely here today and Bonny Scotland beat the French 1-0 last night

 

[RuralGuy]

So what do you have in the NotInList event so far?

 

[Nick-in-Paris]

Here is the code

The problem is that I am not a programmer and I have just cobbled this together from other stuff I found on the site, so that's probably why it doesn't work - oh and the fact that I don't know what I am doing.

-----------------

Private Sub Organisation_NotInList(NewData As String, Response As Integer)
On Error GoTo cboOrganisation_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Organisation " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Gleeds (France)")
If intAnswer = vbYes Then
strSQL = "Insert Into Organisations([Organisation]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Organisation has been added to the list." _
, vbInformation, "Gleeds (France)"
Response = acDataErrAdded
Else
MsgBox "Please choose an Organisation from the list." _
, vbInformation, "Gleeds (France)"
Response = acDataErrContinue
End If
cboOrganisation_NotInList_Exit:
Exit Sub
cboOrganisation_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cboVille_NotInList_Exit
End Sub

 

[RuralGuy]

Try using the following code:

Private Sub Organisation_NotInList(NewData As String, Response As Integer)
   On Error GoTo cboOrganisation_NotInList_Err
[COLOR="SeaGreen"]'   The following 2 variables are no longer needed
'   Dim intAnswer As Integer
'   Dim strSQL As String[/COLOR]
   If MsgBox("The Organisation " & Chr(34) & NewData & _
                      Chr(34) & " is not currently listed." & vbCrLf & _
                      "Would you like to add it to the list now?" _
                      , vbQuestion + vbYesNo, "Gleeds (France)") = vbYes Then
[COLOR="SeaGreen"]'   If intAnswer = vbYes Then
'      strSQL = "Insert Into Organisations([Organisation]) " & _
'               "VALUES ('" & NewData & "');"
'      DoCmd.SetWarnings False
'      DoCmd.RunSQL strSQL
'      DoCmd.SetWarnings True
'      MsgBox "The new Organisation has been added to the list." _
'             , vbInformation, "Gleeds (France)"[/COLOR]
      [COLOR="Red"]DoCmd.OpenForm "YourOrganisationFormName", , , , acFormAdd, acDialog, NewData[/COLOR]
      Response = acDataErrAdded
   Else
      MsgBox "Please choose an Organisation from the list." _
             , vbInformation, "Gleeds (France)"
      Response = acDataErrContinue
   End If
cboOrganisation_NotInList_Exit:
   Exit Sub
cboOrganisation_NotInList_Err:
   MsgBox Err.Description, vbCritical, "Error"
   Resume [COLOR="Red"]cboOrganisation_NotInList_Exit[/COLOR]
End Sub

...using your YourOrganisationFormName of course.

Then in the OnLoad event of that Organisation form put the following code:

Private Sub Form_Load()

   If Not IsNull(Me.OpenArgs) Then
      '-- Form is being opened from a form passing a New Organisation Name
      Me.OrganisationNameControl = Me.OpenArgs
   End If

End Sub

...using your OrganisationNameControl of course.

By using the acDialog argument in the OpenForm command, the code in the 1st form will halt while the "YourOrganisationFormName" is open and start again when that form closes. BTW, as you can see with my post, using the code tags (the # button) makes code easier to read.

 

[Nick-in-Paris]

Thanks rural guy, I am trying to learn. It's just taking me a while

 

[RuralGuy]

Take the time you need and post back when you get stuck again.