469,270 Members | 1,117 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,270 developers. It's quick & easy.

MS Access not in list event

I'm new to Access programming. I followed Microsoft online instructions for the notinlist event. The code goes from an order form combobox client name to a data entry client form when the user selects a client not in the list. After entering the new client info and closing that form, the focus(?) returns to the client-name order form combobox. The new client information does not appear on the list. I have to manually clear the combobox, close and then reopen the order form. The new client information then appears.

When attempting to requery the combo box, an error message appears that the "field must be saved before requerying."

Thanks
Nov 19 '10 #1

✓ answered by TheSmileyCoder

I don't know what the Microsoft example looks like but this is some of my own code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmb_Document_NotInList(NewData As String, Response As Integer)
  2.     If vbYes = MsgBox("The document [" & Me.cmb_Document.Text & "] could not be found." & vbNewLine & "Would you like to register it now?", vbYesNo + vbQuestion) Then
  3.         DoCmd.OpenForm "frm_DocumentRegistration", acNormal, , , acFormAdd, acDialog, Me.cmb_Document.Text & ""
  4.         If IsNull(Me.cmb_Document) Then
  5.             Response = acDataErrContinue
  6.         Else
  7.             Response = acDataErrAdded
  8.         End If
  9.         Else
  10.         Me.cmb_Document = Null
  11.         Response = acDataErrContinue
  12.     End If
  13.  
  14. End Sub
Notice that the form is opened as dialog (Which means that the code following on the next line is not executed until after the dialog is closed!

This is the code from the 2 buttons on the dialog form:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btn_Cancel_Click()
  2.     Me.Undo
  3.     DoCmd.Close
  4.     Screen.ActiveControl.Undo
  5.     Screen.ActiveControl = Null
  6.  
  7. End Sub
If user doesn't want to register document after all, the last 2 lines will clear the field so user doesn't get another not-in-list event.

Expand|Select|Wrap|Line Numbers
  1. Private Sub btn_Save_Click()
  2.     Me.Dirty = False 'Save record
  3.     Dim intKey As Integer
  4.     intKey = Me.tb_KEY_Document 'Record the autonumbered key of the document
  5.     DoCmd.Close 
  6.     Screen.ActiveControl.Undo 'Clear so we dont get errors
  7.     Screen.ActiveControl.Requery 'Refresh list
  8.     Screen.ActiveControl = intKey 'Set to the newly created document
  9. End Sub
The Screen.ActiveControl.Undo will stop the error message you are receiving.


This example is based on a combobox where a primary key is used.For example:
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_Documents.KEY_Document, tbl_Documents.tx_Document_D3_ID, tbl_Documents.tx_DocumentName FROM tbl_Documents;

3 4849
TheSmileyCoder
2,321 Expert Mod 2GB
I don't know what the Microsoft example looks like but this is some of my own code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmb_Document_NotInList(NewData As String, Response As Integer)
  2.     If vbYes = MsgBox("The document [" & Me.cmb_Document.Text & "] could not be found." & vbNewLine & "Would you like to register it now?", vbYesNo + vbQuestion) Then
  3.         DoCmd.OpenForm "frm_DocumentRegistration", acNormal, , , acFormAdd, acDialog, Me.cmb_Document.Text & ""
  4.         If IsNull(Me.cmb_Document) Then
  5.             Response = acDataErrContinue
  6.         Else
  7.             Response = acDataErrAdded
  8.         End If
  9.         Else
  10.         Me.cmb_Document = Null
  11.         Response = acDataErrContinue
  12.     End If
  13.  
  14. End Sub
Notice that the form is opened as dialog (Which means that the code following on the next line is not executed until after the dialog is closed!

This is the code from the 2 buttons on the dialog form:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btn_Cancel_Click()
  2.     Me.Undo
  3.     DoCmd.Close
  4.     Screen.ActiveControl.Undo
  5.     Screen.ActiveControl = Null
  6.  
  7. End Sub
If user doesn't want to register document after all, the last 2 lines will clear the field so user doesn't get another not-in-list event.

Expand|Select|Wrap|Line Numbers
  1. Private Sub btn_Save_Click()
  2.     Me.Dirty = False 'Save record
  3.     Dim intKey As Integer
  4.     intKey = Me.tb_KEY_Document 'Record the autonumbered key of the document
  5.     DoCmd.Close 
  6.     Screen.ActiveControl.Undo 'Clear so we dont get errors
  7.     Screen.ActiveControl.Requery 'Refresh list
  8.     Screen.ActiveControl = intKey 'Set to the newly created document
  9. End Sub
The Screen.ActiveControl.Undo will stop the error message you are receiving.


This example is based on a combobox where a primary key is used.For example:
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_Documents.KEY_Document, tbl_Documents.tx_Document_D3_ID, tbl_Documents.tx_DocumentName FROM tbl_Documents;
Nov 19 '10 #2
Thank you very much!! It was the "undo" action that did the trick. Once the combobox was cleared, the order-entry form could be requeried with the updated client information. I'm learning that Access is very powerful but also very quirky. Thanks again.
Nov 19 '10 #3
TheSmileyCoder
2,321 Expert Mod 2GB
Happy to help. Welcome to Bytes
Nov 19 '10 #4

Post your reply

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

Similar topics

29 posts views Thread by Patrick | last post: by
reply views Thread by BeccyBoo | last post: by
3 posts views Thread by Brian Graham | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.