Using a combobox to check if record exist then populate form

So I am giving it an attempt to convert this over to my table and form. I started by creating an unbound combobox in my header. In the Property Sheet under Data, Row Source I have put this SQL:

SELECT tblDelay.ID,AssessionNumber, 1 As SortColumn, AssessionNumber

FROM tblDelay Union SELECT 0, "<New Record>", 0, "", "" FROM tblDelay

ORDER BY SortColumn, AssessionNumber

When I open the form and select the combobox I get the following error.

"The number of Columns in the two selected tables or queries of a union do not match."

How do I resolve this error.

Thanks, Kerry

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