Ok, so, we are doing this from a not in list of a comb box.....

The problem we are having is that we got two forms, and a combo box ALL
BASED on the same table.

Further, when you do a re-query on the form, the combo box gets re-loaded,
but you are in the middle of a not in list event, and that will cause an
error.

Normally, there is little problem in launching a form to add a new record to
a combo box, but your combo box is used to navigate to new records, and also
add records, and the form that the combo box is on is also based on this
table (that 3 guys on the same table!!).

Normally, the not in list code to add a new value to the comb box is:


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

End Sub

The open args passes the new data, and in the frmAddCleint, the on-load
event would go:


me!Lastname = me.OpenArgs

The above is actually ALL THE code you need to do this. Note the use of
acDialog to Halt THE CODE. If you don't use acdialog, then the code above
will continue to run, and NOT WAIT for the frmAddClent to finish. Note that
setting Response = acDataErrAdded does a re-load, and re-query of the combo
box.

However, the above is for the case when adding to a table for the combo box,
not the form we are actually on.

We need a way to re-load the form, and re-load the combo box. We CAN NOT do
that while in the combo box is in the middle of the not in list code. So,
the trick, or solution is to move the requery code to the AFTER UPDATE event
of the combo box

So, we going have to tell the form to requery, but we can't do it in the
middle of the not in list event (that also means you forms close event can't
do the requery and move to the correct record).

So, lets create a forms module level var


Option Compare Database
Option Explicit

Dim bolAdd As Boolean

Now, change our not in list code to:

If MsgBox("Do you want to add this value to the list?", _
vbYesNo) = vbYes Then
DoCmd.OpenForm "ContactsE", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
bolAdd = True
Else
Response = acDataErrContinue
End If


Now, in our comb box after update event which moves to the new record, we
go:

' Find the record that matches the control.

If bolAdd = True Then
bolAdd = False
Me.Requery
End If

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![Combo39], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Note the code to requery the form.

So, really, at the end of the day here, is that we can NOT requery the form
while in the middle of a not in list event. (and, it simply re-fires the not
in list code again and again). And, I betting you got code in the combo
AFTER UPDATE event anyway. So, the beauty of this code approach is that we
don't have to repeat the "after update" event code to "move" to the correct
record in your "setup" forms close event. So, we cleaned up things a lot
here, and got rid of a lot of code.

You can certainly modify the above not in list code to "add" the new record,
and launch form setup to that record if you wish.

Something like:

Dim rst As DAO.Recordset
Dim lngNewID As Long

If MsgBox("Do you want to add this value to the list?", _
vbYesNo) = vbYes Then

Set rst = CurrentDb.OpenRecordset("contacts")
rst.AddNew
rst!LastName = NewData
lngNewID = rst!ContactID
rst.Update
rst.Close
Set rst = Nothing

DoCmd.OpenForm "ContactsE", , , "ContactID = " & lngNewID, , acDialog
Response = acDataErrAdded
bolAdd = True
Else
Response = acDataErrContinue
End If

Note how in the above we don't open the form in add mode, and also do NOT
send the openargs to the record.
(this would also mean that we REMOVE any code in the setup form to set
things via open args DONT forget this...as this messed me up more then once)

So, you can see how this cleans up a ton of code.

The bottom line is that we can't re-query the combo box (or form) while we
are in the not in list code....

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

http://www.members.shaw.ca/AlbertKallal