Using a combobox to check if record exist then populate form

One question to clarify inGoToContact_AfterUpdate() in the DoCmd.GoToRecord what is the Me.Name. Because of my lack of knowledge I cannot find what Me.Name does for the code.

Me.Name simply returns the name of the current form.  It's probably not absolutely necessary here, but I always include it in situations like this so there can be no ambiguity whatsoever over which form's new record is to be moved to.   NB, it won't work with a subform, though, because a subform cannot be referenced by its name, only via the parent form's subform control's Form property.

I am currently addressing the findrecord code you sent me. I have my combobox listing all my Assession Numbers including the <Add Record>. I have changes the name of my combobox to cboGoToAssession. So fisrt thing would be to change cboGoToContact_AfterUpdate() to cboGoToAssession_AfterUpdate(). Next I tried to change Me.FirstName.SetFocus to Me.txtMRN.SetFocus. I then changed .FindFirst "ContactID = " & ctrl to "ID = " & ctrl.

With these changes I get a message when I put a number into the combobox which states "The text you entered isn't an item in the list. Select an iten from the list, or enter text that matcheson of the listed items. Not sure where this message was derived from.

What I need to do is type in combobox an AssessionNumber, if not in list GoToAssession_NotInList. I am having trouble changing your code to meet my needs. Any help is greatly appreciated.

Thank,

Kerry

Private Sub cboGotoContact_AfterUpdate()

    Const MESSAGETEXT = "No matching record"

    Dim ctrl As Control

    Set ctrl = Me.ActiveControl

    If Not IsNull(ctrl) Then

        If ctrl = 0 Then

            ' go to new record and move focus to FirstName control

            DoCmd.GoToRecord acForm, Me.Name, acNewRec

            Me.FirstName.SetFocus

        Else

            With Me.RecordsetClone

                .FindFirst "ContactID = " & ctrl

                If Not .NoMatch Then

                    ' go to record by synchronizing bookmarks

                    Me.Bookmark = .Bookmark

                Else

                    MsgBox MESSAGETEXT, vbInformation, "Warning"

                End If

            End With

        End If

    End If

End Sub

Private Sub cboGotoContact_NotInList(NewData As String, Response As Integer)

    Dim ctrl As Control

    Dim strMessage As String

    Dim strFirstName As String, strLastName As String

    Set ctrl = Me.ActiveControl

    strMessage = "Add " & NewData & " to list?"

    strFirstName = Left(NewData, InStr(NewData, " ") - 1)

    strLastName = Mid(NewData, InStr(NewData, " ") + 1)

    'if user confirms insert move form to new record

    ' and insert values into first and last name controls

    If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then

        Response = acDataErrContinue

        ctrl.Undo

        DoCmd.GoToRecord acForm, Me.Name, acNewRec

        Me.FirstName = strFirstName

        Me.LastName = strLastName

    Else

        Response = acDataErrContinue

        ctrl.Undo

    End If

End Sub

I's really just a question of changing the relevant object names:

Private Sub cboGotoAssession_AfterUpdate()

    Const MESSAGETEXT = "No matching record"

    Dim ctrl As Control

    Set ctrl = Me.ActiveControl

    If Not IsNull(ctrl) Then

        If ctrl = 0 Then

            ' go to new record and move focus to Assession control

            DoCmd.GoToRecord acForm, Me.Name, acNewRec

            Me.Assession.SetFocus

        Else

            With Me.RecordsetClone

                .FindFirst "ID = " & ctrl

                If Not .NoMatch Then

                    ' go to record by synchronizing bookmarks

                    Me.Bookmark = .Bookmark

                Else

                    MsgBox MESSAGETEXT, vbInformation, "Warning"

                End If

            End With

        End If

    End If

End Sub

Private Sub cboGotoAssession_NotInList(NewData As String, Response As Integer)

    Dim ctrl As Control

    Dim strMessage As String

    Set ctrl = Me.ActiveControl

    strMessage = "Add " & NewData & " to list?"

    'if user confirms insert, move form to new record

    ' and insert values into Assession control

    If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then

        Response = acDataErrContinue

        ctrl.Undo

        DoCmd.GoToRecord acForm, Me.Name, acNewRec

        Me.Assession = NewData

    Else

        Response = acDataErrContinue

        ctrl.Undo

    End If

End Sub

NB, you need to create the event procedures from the properties sheet in the usual way, and then paste in the body text of each from the above.

BTW is Assession the real name, rather than Accession?

I made the recommended changes including adding code to the event procedures. When I select <New Record> I get a Run-time error 2493 This action requires an Object Name argument and it highlights DoCmd.GoToRecord acForm, , acNewRec in the procedure. If I select a Assession Number from the combobox I get a message No matching record. I I type in an Assession Number I get message Add xxxx to list. If I select Yes, I get the run-time error 2493.

You mentioned yesterday that might be an issue with navigation page subform. Should I get rid of Navigation page and just use regular forms?

Thanks for all your assistance. I really appreciate experts like yourself giving of your time.

Thanks again.

Kerry

1.  You misunderstood what I said.  You can't just leave out the one argument, if you specify the object type you must name it.  You can omit all bar the last argument and it will usually default to the current object, but I'd stick with what I posted, DoCmd.GoToRecord acForm,Me.Name , acNewRec

2.  I didn't say a navigation page subform, I said a subform per se.  The point is that a subform is not a member of the Forms collection, so referencing it with Me.Name won't work.    If you want to move to a new record in a subform with code in the parent form's module then first set focus to the subform control, and then call the GoToRecord method without specifying the form by name:

    Me.MySubformControl.SetFocus

    DoCmd.GoToRecord , , acNewRec