Using a combobox to check if record exist then populate form

1.  How do I get the MessageText "No Matching Record" and "Warning" to fire? Can't seem to see that message with data input.

2.   Also, Is it possible to leave the form blank until a Go To Contact has been selected.

3.   Can we add a message for edit/update to ask if user really wants to change the data in a record.

1.   That's really only in there as a backstop.  Normally that message will never be displayed, only in the unlikely circumstance that the combo box lists more distinct values than are actually represented in the form's recordset.  If a user enters a new value then the form's NotInList event procedure (q.v.) will execute and prompt the user for confirmation of whether they wish to add the new item to the list or not.  If they confirm then the form is moved to a new record, and the new value assigned to the control (actually two controls in my case as the name is parsed, but it would be one in yours).

2.  You could move to an empty new record when the form opens by putting this in its Open event procedure:

        DoCmd.GoToRecord acForm, Me.Name, acNewRec

3.  the simple way is to put the following in the form's BeforeUpdate event procedure:

    Const MESSAGETEXT = "Do you wish to save the changes to the record?"

    If MsgBox(MESSAGETEXT, vbQuestion + vbYesNo, "Confirm") = vbNo Then

        Cancel = True

        Me.Undo

    End If

However, the fact that a record is updated does not per se mean the values of its data have been changed.  The above also is clumsy if the user attempts to save the record by closing the form.  For a solution which only prompts for confirmation only where the data has actually changed see the ChangedRecordDemo file in my same OneDrive folder.  The code for the form's module in this case is rather more complex, as follows:

Option Compare Database

Option Explicit

Private Sub Form_BeforeInsert(Cancel As Integer)

    Me.DateTimeStamp = Now()

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

 On Error GoTo Err_Handler

    Const MESSAGETEXT = "Data has changed.  Save record?"

    If Not Me.NewRecord Then

        ' store unsaved values of bound controls in array

        StoreProposedVals Me

        ' if data in controls has changed get user

        ' confirmation to save record

        If RecordWillChange() Then

            If MsgBox(MESSAGETEXT, vbQuestion + vbYesNo, "Confirm") = vbNo Then

                Cancel = True

                Me.Undo

            End If

        End If

    Else

        If MsgBox("Save new record", vbQuestion + vbYesNo, "Confirm") = vbNo Then

            Cancel = True

            Me.Undo

        Else

            ' timestamp record

            Me.DateTimeStamp = Now()

            Me.UpdatedBy = GetUser()

        End If

    End If

Exit_Here:

   Exit Sub

Err_Handler:

   MsgBox Err.Description, vbExclamation, "Error"

   Resume Exit_Here

End Sub

Private Sub Form_Current()

    On Error GoTo Err_Handler

    If Not Me.NewRecord Then

        ' store current values of bound controls in array

        StoreCurVals Me

    End If

Exit_Here:

    Exit Sub

Err_Handler:

    MsgBox Err.Description, vbExclamation, "Error"

    Resume Exit_Here

End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)

    Const IS_DIRTY = 2169

    ' suppress system error message if form

    ' is closed while record is unsaved,

    ' NB: changes to current record will be lost

    If DataErr = IS_DIRTY Then

         Response = acDataErrContinue

    End If

End Sub

The above code calls the following function to determine if the data in the record will in fact be changed if the record is saved:

Public Function RecordWillChange() As Boolean

   Dim n As Integer, intlast As Integer

   Dim var As Variant

   Dim aOld(), aNew()

   intlast = UBound(aOldVals) - 1

   ' loop through array of original values

   ' and store in new array

   ReDim Preserve aOld(UBound(aOldVals))

   For Each var In aOldVals()

       aOld(n) = var

       n = n + 1

   Next var

   n = 0

   ' loop through array of edited values

   ' and store in new array

   ReDim Preserve aNew(UBound(aOld))

   For Each var In aNewVals()

       aNew(n) = var

       ' if any value has changed then return True

       If (IsNull(aNew(n)) And Not IsNull(aOld(n))) _

           Or (Not IsNull(aNew(n)) And IsNull(aOld(n))) _

           Or aNew(n) <> aOld(n) Then

           RecordWillChange = True

           Exit For

       End If

       n = n + 1

   Next var

End Function

I use the search combo frequently. The only kicker here is that you are suing a Navigation form. I have not worked greatly with Navigation forms but as I understand them, they are an overall form with tabs, that you place embedded subforms on. So as you select a tab, you are seeing a subform on that tab. For a search combo to work, it needs to be within the same form that you want to position the record to. If its not it won't work properly. 

I suspect, Ken's example uses the Not In List event to fire messages that there are no matching records. 

As for ensuring that users don't "accidentally edit a record", this is a concern with Access. What I do, is use the function found here: http://allenbrowne.com/ser-56.html to lock my controls until the user presses a button to unlock them. This still allows your search controls to work (since they need to be unbound), but prevents the user from unconsciously editing data.

Scott, thanks for the reply,

I tried this yesterday. I was able to populate the combobox with my AssessionNumber but unable to get the record into the form for editing. Any suggestions?

Kerry

Ken, Thank you for the examples in FindRecord. I am reviewing the VB right now and have a question regarding cboGoToContact_AfterUpdate(). Please bear in mind I am fairly new at this. I am playing with the form and then reviewing the VB. How do I get the MessageText "No Matching Record" and "Warning" to fire? Can't seem to see that message with data input.

Also, Is it possible to leave the form blank until a Go To Contact has been selected. That way for my situation the users don't accidentally edit a record. Can we add a message for edit/update to ask if user really wants to change the data in a record. If yes then save edited record as update and save modified date/time.

Thank you,

Kerry

The Combobox wizard will create a search combo for you. Its the third option on the wizard.