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

DON'T go to new record?

P: 493
Hi all, newbie here, back again with more questions.

I'm sure I'm not the first to ask this but I'm not sure how to search for this one. Nothing turned up with the parameters I searched under.

Anyway, I have a form with a combo box. On the combo box is a NotInList event that opens a new form to add an item to the combo box. This works great. However, when the user closes the form that adds new data it advances the main form to a new record. This is not good if the user hasn't finished filling out all the info for the original record.

Is there any way to keep Access from advancing to a new record until the user clicks on the button to go to a new record?

Mar 1 '07 #1
Share this Question
Share on Google+
12 Replies

Expert 100+
P: 159
If I had to guess, I would say you are requerying the entire form and not just the combobox after the record is added.
Of course, if you post the code I wouldn't have to guess.
Mar 2 '07 #2

Expert Mod 15k+
P: 31,307
Interesting one.
As MSeda says, it all depends on how the new form is opened and which events happen in which order.
Please post the code for us to look at for you.
BTW any .Requery calls are particularly interesting so should be included.
Mar 5 '07 #3

P: 493
Hey thanks for looking!

I have lots of code on this form for various things but this is the code that refers to the new record when the form is launched:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     DoCmd.OpenForm "frm_Specimen", , , , acNewRec
  3.     DoCmd.GoToRecord , , acNewRec
  4. End Sub
and this is the code for the NotInList:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboSpecies_NotInList(NewData As String, Response As Integer)
  2. Dim Result
  3. Dim Msg As String
  4. Dim CR As String
  6.     CR = Chr$(13)
  8.     ' Exit this subroutine if the combo box was cleared.
  9.     If NewData = "" Then Exit Sub
  11.     Msg = "'" & NewData & "' is not in the list." & CR & CR
  12.     Msg = Msg & "Do you want to add it?"
  13.     If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
  14.        DoCmd.OpenForm "frm_Species", , , , acFormAdd, acDialog, NewData
  15.     End If
  17.     Result = DLookup("[Species_Code]", "tbl_Species", _
  18.              "[Species_Code]='" & NewData & "'")
  19.     If IsNull(Result) Then
  21.        Response = acDataErrContinue
  22.        ' Display a customized message.
  23.        MsgBox "Please try again!"
  24.     Else
  25.        Response = acDataErrAdded
  26.     End If
  27. End Sub
I don't think I have any .Requery commands anywhere.

Here's another thing though - this happens twice, once on the original form and again on the form that opens in response to the NotInList. Let me explain . . .

frm_Specimen form has combo box called cboSpecies. cboSpecies has a NotInList event that opens a new form (frm_Species) to add a new species. frmSpecies has a combo box called cboGenus. cboGenus has a NotInlist event that adds a new genus to the combo box, but without opening a new form. The code for that (cboGenus) is here:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboGenus_NotInList(NewData As String, Response As Integer)
  2.     Dim strSQL As String
  3.     Dim i As Integer
  4.     Dim Msg As String
  6.     'Exit this sub if the combo box is cleared
  7.     If NewData = "" Then Exit Sub
  9.     Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
  10.     Msg = Msg & "Do you want to add it?"
  12.     i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Genus Category...")
  13.     If i = vbYes Then
  14.         strSQL = "Insert Into tbl_Genus ([Genus]) " & _
  15.                  "values ('" & NewData & "');"
  16.         CurrentDb.Execute strSQL, dbFailOnError
  17.         Response = acDataErrAdded
  18.     Else
  19.         Response = acDataErrContinue
  20.     End If
  22. End Sub
So Access goes to a new record if you add a new Genus AND if you add a new Species. I don't want to go to a new record unless the user specifically presses the button to do so. Is this possible?

And finally, I don't know if this helps or not but on the frm_Species I have this code
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     If Not IsNull(Me.OpenArgs) Then
  3.       Me![Species_Code] = Me.OpenArgs
  4.    End If
  5. End Sub
Thanks for any help!
Mar 5 '07 #4

Expert Mod 15k+
P: 31,307
Your post just caught me going out the door.
I'll get to it later from home.
Mar 5 '07 #5

P: 493
thanks I really appreciate it!!
Mar 5 '07 #6

Expert 100+
P: 159
I do have some comments on the code you have posted that I am working on. however, I do not see any code associated with a command button that enters the newly created species from frm_Species into frm_specimen's cboSpecies. This command button or however you get the new species to frm_specimen is the most suspect in the problem you are having. Please this post this code.
Mar 5 '07 #7

P: 493

I'm not sure I follow. What other code do I need to post for you?

Basically, on the Specimen form, the user can type a new species into the combo box if they don't see it in the list. This triggers the NotInList event which automatically opens the Species form. On the Species form you can enter in a bunch of data about the new species. When the user closes the form Access advances to a new record on the Specimen form, though the user may not have finished filling out the first record.

Thank you!
Mar 5 '07 #8

Expert 100+
P: 159
i'm sorry I see you have Response = acDataErrAdded to add the value to the combobox which should do the trick. This differs from my usual MO for not in list so I'll have to tinker with it a little more. Off hand I do not see an obvious reason that frm_specimen is advancing to a new record.
the first bit of code you listed (the Form_Load event) which form is that from?
Mar 5 '07 #9

Expert 100+
P: 159
Now I'm stumpped.
I've duplicated your forms as closely as possible using some forms of my own but I cannot duplicate the error; the code performs as expected. The data entry form opens as expected on the NotinList Event and when closed the combo box displays the new record and the main for does not advance to a new record.
Is there any other code in your forms that you would have assumed not to be involved in this error and thus not included.
Mar 5 '07 #10

P: 493
You know what? I'm stumped too . . . because now it seems to be working just fine. I don't get it - the only thing that changed was that I imported all the forms and tables into a brand new database to try to fix an autonumber increment problem.

I guess that solved this issue as well?

I'm sorry to have been such a bother. Thanks so much for trying to help me through this.

Just out of curiosity - what is your usual MO for the NotInList event? I'm always curious what works for people.

cheers, and thanks to everyone for their help,
Mar 5 '07 #11

Expert 100+
P: 159
Truthfully, the method you use is probably the "right" way but I'm old and set in my ways.

this is what I usually do.

Expand|Select|Wrap|Line Numbers
  1. Private Sub QU_Client_Code_NotInList(NewData As String, Response As Integer)
  2. Dim Result As Integer
  4. Response = acDataErrContinue
  5. NewData = UCase(NewData)
  6.   Me.QU_Client_Code = Null
  8. Result = MsgBox(NewData & " is not associated with an existing client." & vbCr & "Press OK to enter information for a new client." & vbCr & "Press cancel to choose a different code for an existing client.", vbOKCancel, "New Client Code")
  9.     If Result = 1 Then
  10.     DoCmd.OpenForm "Client Information Entry"
  11.     Forms![Client Information Entry]![Client Code] = NewData
  12.     Forms![Client Information Entry]!TabCtl2.Enabled = True
  13.     DoCmd.GoToControl "Client Company"
  14.     Forms![Client Information Entry].Form.Modal = True
  15.     Exit Sub
  16.     End If
  18. End Sub
I just don't normally use the acDataErrAdded I just open the form and have a command button on the form that requeries the combo and passes the value back. Again probably not the best way so keep doing what you're doing.
Mar 5 '07 #12

P: 493
Well, seeing as I'm a newbie and don't understand most of the code I'm sort of forced to stick with whatever works!

Thanks for sharing that though, always good to see alternative ways of doing things. I'm convinced there is no "right" way of doing anything!

Mar 5 '07 #13

Post your reply

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