Open Form and Update List on Double-Click

[poggid]

I have a database where I have a form "Expense Table" it has a number of pull downs for other tables. I have a not in list error message already. But I would like to set up a double click event procedure. That would open the form for the not in list data. Add the data to it then close the from. This then up dates the table and makes the data available to select.
Don't even know where to begin.

[PhilOfWalton]

First thing is may I strongly advise not having spaces in the names of forms, tables, queries, controls or field.

Equally Expenses Table is a very misleading name for a form, something like FrmExpenses would be much clearer.

That said, we have two separate processes.

Let us assume you have a field in your FrmExpenses called MissingDataID, and the form you want to open to enter the required information is called FrmMissingData.

This is the code you need on the double click.

Private Sub MissingDataID_DblClick(Cancel As Integer)
 
    If Not CurrentProject.AllForms("FrmMissingData").IsLoaded Then
        DoCmd.OpenForm "FrmMissingData"
    End If
 
    DoCmd.SelectObject acForm, "FrmMissingData"
    DoCmd.Restore
    If Nz(MissingDataID) = 0 Then
        DoCmd.GoToRecord acForm, "FrmMissingData", acNewRec
    Else
        DoCmd.GoToControl "MissingDataID"
        DoCmd.FindRecord MissingDataID
    End If
 
End Sub
 

It opens the form and optionally restores it. If there is a value in the MissingDataID field in the main form, it opens the new form at that record so that it can be edited.
If there is no value in the MissingDataID field in the main form, it opens the new form at a new record record so that you can add the new data.

Then after you update or add the new record you use this code

Private Sub Form_AfterUpdate()
 
    With Forms(FrmExpenses)
        If !MissingDataID <> MissingDataID Then
            !MissingDataID = 0
            !MissingDataID.Requery
            !MissingDataID = MissingDataID
        End If
    End With
 
EndSub
 

That should load the new value back to the FrmExpenses.

Phil

[jforbes]

You may want to consider setting .AllowValueListEdits to True to bring up an editor when a value is entered that isn't in the list. You could also consider the .ListItemEditForm property if you want to supply the Form to perform the addition to the List.