Requery combo box problem (error 2118)

[gd]

This is a question about an Access 2000 database.

I'm going to ask the question in the way that I'm trying to get it to
work, but if there seems like there is a better design to avoid the
problem altogether that I haven't thought of, please mention it.

I have a form (lets say Form1) with a field that uses a combo box that
draws its data from table tblMachines. If the user searches through
the combo box for a particular choice and doesn't find it, the user
would then open up another form (Form2), which directly shows data
from table tblMachines. There, the user can add a new record (or
change an existing one), save it, and close Form2 and have the new
item available in the combo box now.

This works fine for what I have right now, with just a
Form1.Combobox.Requery on the Form2's AfterUpdate event. The problem
occurs when a user starts to edit the combobox field, then figures out
the option that the user wants is not there, and so proceeds through
the steps to change it. In this scenario, after the AfterUpdate runs,
I get the error:

2118: You must save the current field before you run the 'Requery'
action

To solve this problem I have Form1 save before the Form2's AfterUpdate
event. However this caused more problems, since the field the
combobox was for was a primary field, and gives error messages that
primary fields are null.

Seems like I'm stuck in a pickle between choice A and B, both with
pros and cons. Any suggestions?

 

[George Nicholson]

http://support.microsoft.com/kb/197526/en-us
Use NotInList Event to Add a Record to Combo Box

http://mvps.org/access/forms/frm0015.htm
Forms: Add item to combo box using OnNotinList event

HTH,

 

[gd]

http://support.microsoft.com/kb/197526/en-us
Use NotInList Event to Add a Record to Combo Box

http://mvps.org/access/forms/frm0015.htm
Forms: Add item to combo box using OnNotinList event

HTH,

Hey George, thanks for the reply.

Both of those methods involve just adding whatever the user types into
the combo box. I'd rather not have that, and I'd prefer if the user
added the record through the other form. My whole database is built
upon that kind of system and I'd have to make a lot of changes I think
if I used a method like that.

 

[George Nicholson]

Substitute your "Open Form, let user add record, etc." method, for the "open
recordset, add record" portions of those routines.

My (unspoken) point was to not reinvent the wheel. The NotInList event was
intended to allow exactly what you are trying to do via other means: put
data entry on hold while an addition is made to the combo's source table
(regardless of the method used to make that addition), so that the new item
becomes a legal selection.

Specifically, the Form1 combo requery (which is causing you problems) is
handled by acDataErrAdded:

(from the VBA Help file)
"acDataErrAdded: Doesn't display a message to the user but enables you to
add the entry to the combo box list in the NotInList event procedure. After
the entry is added, Microsoft Access updates the list by requerying the
combo box. Microsoft Access then rechecks the string against the combo box
list, and saves the value in the NewData argument in the field the combo box
is bound to."

This is the built-in method that allows you to side-step the "You must save
the current field before you run the 'Requery' action" error.

HTH,

 

[gd]

Substitute your "Open Form, let user add record, etc." method, for the "open
recordset, add record" portions of those routines.


My (unspoken) point was to not reinvent the wheel. The NotInList event was
intended to allow exactly what you are trying to do via other means: put
data entry on hold while an addition is made to the combo's source table
(regardless of the method used to make that addition), so that the new item
becomes a legal selection.

Specifically, the Form1 combo requery (which is causing you problems) is
handled by acDataErrAdded:

(from the VBA Help file)
"acDataErrAdded: Doesn't display a message to the user but enables you to
add the entry to the combo box list in the NotInList event procedure. After
the entry is added, Microsoft Access updates the list by requerying the
combo box. Microsoft Access then rechecks the string against the combo box
list, and saves the value in the NewData argument in the field the combo box
is bound to."

This is the built-in method that allows you to side-step the "You must save
the current field before you run the 'Requery' action" error.

HTH,

Hey George,

It sounds like a good alternative but heres my issue with that. The
choices in the combo box the user selects from are just one field (the
primary key) of another record. I don't want the user to just add
another record by primary key and leave all the other fields blank,
since there are many other fields and they are very important. Its
not as simple as a "Status" or something where I could see this method
to be really useful. I wanted the user to fill out the entire record
if the situation came up where another would need to be added, hence
the reason for wanting Form2 to pop up.

 

[George Nicholson]

We are both repeating ourselves. You can open your form, I've never
suggested otherwise.

Once again: use the NotInList event to open Form2. The code in the provided
links uses a slightly different method (just adding a single value), but
there is no reason I know of that you can't replace that method with the one
you want to use. The key element is that you should be *using the NotInList
event and acDataErrAdded response* if you want to avoid the "You must save
the current field before you run the 'Requery' action".

You'll need to heavily modify the NotInList sample code to your situation,
but the general methodology of how to do what you are trying to do without
raising an error is right there.

HTH,