Hi. In a database to track Contacts, I'd like to add a Contact to the Database if it's not on a drop-down list of last names, and AUTOMATICALLY GO TO THAT CONTACT in the form. So if the types a name in the combo box that's not there, the form reopens as a new form with only the last name filled in. In the On Not in List event, I've tried the following code, but I get an error when it runs the line that closes the form. If someone could please tweak my code, I would really appreciate it.

Many Thanks,
Adam


Public Sub Combo115_NotInList(NewData As String, Response As Integer)
'Suppress the default error message.
Response = acDataErrContinue
' Prompt user to verify if they wish to add a new value.
If MsgBox("An entry for " & NewData & " is not in list. Add it?", vbYesNo) = vbYes Then
' Set Response argument to indicate that data is being added.
'Open a recordset of the Cities Table.
Dim db As Database
Dim rstLastName As Recordset
Dim sqlContacts As String
Set db = CurrentDb()
sqlContacts = "Select * From Contacts"
Set rstLastName = db.OpenRecordset(sqlContacts, dbOpenDynaset)
'Add a new City with the value that is stored in the variable NewData.
rstLastName.AddNew
rstLastName![LastName] = NewData
rstLastName.Update
'Inform the combo box that the desired item has been added to the list.
Response = acDataErrAdded
rstLastName.Close 'Close the recordset
DoCmd.Close
DoCmd.OpenForm "Main Form", acNormal, "", "", , acNormal
DoCmd.GoToRecord , "", acLast

End If

End Sub

Name what you want the DoCmd.Close to act on using either its name, or a variable. What form do you want the code to close? It will normally close the form that is running the code if nothing is named.

Did that help?


mmcdonal

Well, I want to close the form that is running the code, so I think leaving it blank should work. Is there simpler code to open up the form to the entry that was just entered. For example, my form records conversations with various people. For On Not in List, I'd liek to open up a new page in the form, with the last name already filled in from the NewData Variable. Thanks, Adam

I would still name the form that you are closing, just to be rigorous.

To take the name from the list, do this:

Public sName As String

...

sName = Me.cboName.Value ' or Me.cboName.Columns(1), or whatever column it is.

This will create a public variable that you can snag when you reopen the form.

I am thinking you may want to create a duplicate copy of your data entry form just for new entries. Then you can have it take the name value from the Public variable when it opens. Then you can also put this code on its On Load event:

RunCommand acCmdRecordsGoToNew
Me.Name = sName

Remember to set the form to tab within the current record only. Cycle = Current Record



mmcdonal

Yes! That worked. Apologies for not getting back to you sooner. Thank you very much for the help, this was a lifesaver!

Best,
Adam