By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,940 Members | 611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,940 IT Pros & Developers. It's quick & easy.

Open Form and Update List on Double-Click

P: 1
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.
Jun 6 '17 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 1,430
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub MissingDataID_DblClick(Cancel As Integer)
  3.     If Not CurrentProject.AllForms("FrmMissingData").IsLoaded Then
  4.         DoCmd.OpenForm "FrmMissingData"
  5.     End If
  7.     DoCmd.SelectObject acForm, "FrmMissingData"
  8.     DoCmd.Restore
  9.     If Nz(MissingDataID) = 0 Then
  10.         DoCmd.GoToRecord acForm, "FrmMissingData", acNewRec
  11.     Else
  12.         DoCmd.GoToControl "MissingDataID"
  13.         DoCmd.FindRecord MissingDataID
  14.     End If
  16. 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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  3.     With Forms(FrmExpenses)
  4.         If !MissingDataID <> MissingDataID Then
  5.             !MissingDataID = 0
  6.             !MissingDataID.Requery
  7.             !MissingDataID = MissingDataID
  8.         End If
  9.     End With
  11. EndSub
That should load the new value back to the FrmExpenses.

Jun 6 '17 #2

Expert 100+
P: 1,107
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.
Jun 7 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.