Using a combobox to check if record exist then populate form

1.  By synchronize here all that's meant is that the unbound combo box will always show the contact represented by the form's current record.  This doesn't really have anything to do with finding a contact, and if omitted the form would still function in that respect.  It's just an added refinement which means that if the user navigates to a contact by means other than the combo box, e.g. by the built in navigation buttons or by means of the keyboard, the combo box will always show the current contact, and therefore not confuse the user by showing a previously selected contact, which could be the case without this code: Me.cboGotoContact = Me.ContactID

2.  First it checks that the control has a value with: If Not IsNull(ctrl) Then

i.e. that the user has not simply deleted whatever was selected in the control.  If they have done this then none of the following code is executed and nothing happens.  Otherwise if the value of the control is zero, which, by virtue of SELECT 0, "<New Contact>" in the UNION query, means that <New Contact> has been selected, it moves the form to an empty new record.

If a value other than zero has been selected, i.e. a contact, finds the row in an exact copy of the form's recordset, returned by the form's RecordsetClone property.  It then assigns the RecordsetClone's Bookmark property to the form's Bookmark property, which has the effect of moving the form to the record which was found in the RecordsetClone, i.e. the selected contact.

So, as Scott surmised, in the combo box's AfterUpdate event procedure it's the RecordsetClone property which 'identifies the table' by virtue of its returning an exact copy of the form's recordset.  The code does not need to know what table or query this is, the fact that it's working with an exact copy of the form's recordset is sufficient.

In the control's NotInList event procedure it doesn't need to know anything about the recordset at all.  This code executes if the user types in a new value not in the list.  The new value is passed into the procedure as the NewData argument, and, after user confirmation via a message box, the form is then moved to an empty new record and the value typed into the combo box is inserted into the new record.  My demo is a little more complex in this respect than you'd need because the name typed in is actually the concatenated values for two separate fields, FirstName and LastName, so has to be parsed into its constituent values.  If we assume that in my demo the value was only a single field, LastName, it would be analogous to yours as you are concerned with just the one field, and the code would be simplified as follows:

Private Sub cboGotoContact_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 value into last name control

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

        Response = acDataErrContinue

        ctrl.Undo

        DoCmd.GoToRecord acForm, Me.Name, acNewRec

        Me.LastName = NewData

    Else

        Response = acDataErrContinue

        ctrl.Undo

    End If

End Sub

So if I am going to try this code, I can change name to AssessionNumber to check if it exists. If it does populate form with recordsetclone. This should populate my form for editing. If AssessionNumber doesnot exist then add record. I will try to conver this to my form and see where it goes.

KA

I do not understand how table is identified to synchronize name. Where is table identified?

How does this and the next VB Sub cboGoToContact_NotInList identify table and fields to populate new record.

KA

Not Ken, but I can answer those questions. The table is identified by the Recordsource of the form. As soon as you assign a Recordsource to the form, you shouldn't need anything else. I"m not 100% sure but I suspect Ken's code uses the Form's RecordsetClone property to move the record pointer based on the value selected in the combobox. If you are using unbound forms, then all bets are off and you have to write code to populate the controls. But it doesn't sound like you are doing that.

Thank you for your recommendations and assistance. I have reviewed your FindContact dB and attempting to understand its flow and use of VB. Please bear in mind I am fairly new to Access development and coding using VB. I would first of all try to explain your code back to you to see if I am on tract.

VB Code from frmFindContact

 1. Opening the form fires Sub Form_Current and synchronizes the contact combo box Me.cboGoToContact (Form combo box) = Me.ContactID (table field). If the contact is listed in Me.cboGoToContact then

2. Sub cboGoToContact_AfterUpdate() fires and if Me.ActiveControl which should be Me.cboGoToContact focus then Me.RecordsetClone fires in Else clause otherwise new record and move focus to input name.

I do not understand how table is identified to synchronize name. Where is table identified?

How does this and the next VB Sub cboGoToContact_NotInList identify table and fields to populate new record.

Lets first start with this and let me digest it until I understand. I would like to try and convert this to my form and tables.

Thanks for your patients.

KA

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