Re: Form will not requery no matter what

The problem is when you use the not-in-list, you are in the middle of a
controls event.....

the normal approach to add data is:

The easy way to do this is have ms-access do ALL of the work for you. So,
given that new data is the actually text you typed into the combo, then you
can do the following:

Private Sub Distributee_NotInList(NewData As String, Response As Integer)

if MsgBox("Do you want to add this value to the list?", _
vbYesNo) then
DoCmd.OpenForm "frmAddClient", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

The above is ALL YOU need. You can see it is not much code.

Note that by setting response = acDataErrAdded, then ms-access does a
re-load, and a re-query of the comb box list for you. Also, ms-access will
ALSO re-load your form. In other words, quite
a bit of stuff happens to ensue that the combo box is re-loaded, and re-set.

The problem you have is that the combo box is operating on the CURRENT
form.

However, we can use our new found knowlege of the HUGE number of
things that setting acDataErrContinue does for us.

In your case, you HAVE to add the record INSIDE of this event, since the
AFTER UPDATE EVENT IS going to try and MOVE to this new record.

So, try the following:

If MsgBox("Not found, would you like to add this new reocrd?" & NewData,
vbYesNo) = vbYes Then
Me.RecordsetClone.AddNew
Me.RecordsetClone!Description = NewData
Me.RecordsetClone.Update
Response = acDataErrAdded
End If

note that you have to replace "Description" with the actually column that
your combo box does a search on. Remember, the value of
newdata IS NOT the bound column, but the column that you have for display
(they might be different). Useally you have your combo box return a id
value, but display/search on text values...so, keep this in mind...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx


.

Relevant Pages

  • Re: Lookup Logic and Display Values
    ... So, I would use a combo box to select the customer, and it would save the ... Response As Integer) ... Response = acDataErrContinue ... example note how I am passing the newdata as a openargs. ...
    (microsoft.public.access.formscoding)
  • RE: NotInList Code
    ... Response As Integer) ... Dim strMsgText As String ... 'Exit out if for some reason NewData is zero-length, ... Response = acDataErrContinue ...
    (microsoft.public.access.modulesdaovba)
  • RE: NotInList Code
    ... Response As Integer) ... Dim strMsgText As String ... 'Exit out if for some reason NewData is zero-length, ... Response = acDataErrContinue ...
    (microsoft.public.access.modulesdaovba)
  • Re: NotInList Firing Problem
    ... Response As ... DoCmd.OpenForm cFormName, _ ... If cboDefendantsName.Value NewData Then ... Response = acDataErrContinue ...
    (microsoft.public.access.formscoding)
  • Re: Not In List, saving data
    ... Response As Integer) ... should put in the NewData value into the correct field so the user does not ... heck out of the user (this is along the same lines as the tab key setting ... ' Add client stored in NewData argument to the client table. ...
    (microsoft.public.access.formscoding)
Loading