problem when requering a combobox

[Guest]

Hi,
I receive an error
"run-time error 2118 you must save the current field before you run the
requery action." when I requery a combobox.
The combobox is bounded to a field in a table. If the value is not in
list, it opens a different form (modal) and forces the user to enter
information. When all required data is entered, it'll close the opened form,
requery the combobox, and set value of the combobox to the newly entered
field.
Any idea how I can fix the problem?
Thanks

 

[Guest]

I think you need to cancel the erroneous (i.e. not in list) entry before the
requery. Try this in your NotInList event (the second & thrid lines are the
key):

Private Sub ContractID_NotInList(NewData As String, Response As Integer)
Combo1.Undo
Response = 0
<Open your other form, etc. here>
Combo1.Requery
End Sub

 

[Guest]

Brian,
Thx for the reply. It fixes 1/2 of my problems (that gets rid of the
run-time error). However, even it runs the combo.requery and combo.value =
strNewValue, it still doesn't update combo box items and set the combo box to
the new value.

Thx

 

[Guest]

Are you saying that the new value (entered on the other table) is not on the
list even after the requery? Perhaps it is being preempted now by the
Response = 0. Remove the requery from the NotIntList event and put it in
Form_Activate (see below for the full code for this).

Now, how are you passing the new value back from form2 to form1.Combo1?
Perhaps you already have the answer for this, but here is how I would do it:

Make two public variables: one to transport that data between form2 & form1,
and another to tell Combo1 when to use this value.

Put this in the declarations (General) section of form1's module so that the
variables are dimensioned when the form opens:

Public UpdateFlag as Boolean
Public strNewValue as String

Capture the germane field of the newly-created data before closing form2. It
should be something like this, where form2. Combo99 holds the value you want
to pass back to form1.Combo1. Just make sure this runs while Combo99 still
has the current value, not in Form_AfterUpdate, when form2 may be on a new
record.

DataNew = Combo99
UpdateFlag = True

Now, in the Activate event of form1, do this:

Private Form_Activate()
If UpdateFlag then
Combo1.Requery
Combo1 = strNewValue 'you don't need the .value here
UpdateFlag = False
strNewValue= Null
End If
End Sub

This will check each time form1 is activated. If the UpdateFlag is True, it
will proceed to requery Combo1 and set its value to DataNew, then reset the
UpdateFlag back to false. The UpdateFlag prevents it from trying to get its
value from DataNew when the form is activated because you opened it, not
because control is returning from form2 to form1; DataNew will have no value
at this point.

 

[Guest]

Brian,
Thx for your time. I've tried to requery it on form activate and form
gotfocus, neither works. I've set break points on those event, and they
dont' seem to fire up.
When the requery statement is on the Not in list event, even the new
form is set to modal, but it seems to go straight to the requery and set
value code before the form is closed. Maybe that's why it doesn't show up on
the combo box?

Thx

 

[Guest]

Yes, the code will continue on to the next statement after it opens the
second form, so the requery will happen before the second form is closed (I
missed that the first time). Remove the requery and data-setting parts from
the NotInList. Let's back up a little:

1. Does your second form open correctly when called?
2. If so, is the record that is being created there being saved correctly
(so that it is available for the requery)?
3. Are you able to capture the data to the public variable correctly? Try
putting a MsgBox strNewValue on form2 right after you set its value. Do it
again in the Form_Activate of form1 (first line after the If). Does it appear
correctly in both places? Perhaps strNewValue is being re-declared & losing
its value.

 

[Guest]

Brian,

Thx for ur time.
1) Yes.
2) Yes. If I close the form and re-open it, the newly added entry is on the
list. Also, it shows up on the table.
3) Yes. I can trace the global variable in the immediate window.

The problem seems to have something to do with the event not being
trapped. Even I set a break point on gotfocus, activate on the form. it just
didn't fire off.

Thx

 

[Guest]

Brian,
Thx for all your help.... It's finally working. I found that on the
couple posts down... i need to save the record first using
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
on the form where I add new entries.

thx

 

[Guest]

One more test. Put a MsgBox "Got Back" in Form_Activate and see if you get
the message box text when you close form2 (i.e. when form1 is activated
again). This will tell you if the Activate event is firing correctly. Let me
know.

 

[Guest]

brian,
it does not.

One more test. Put a MsgBox "Got Back" in Form_Activate and see if you get
the message box text when you close form2 (i.e. when form1 is activated
again). This will tell you if the Activate event is firing correctly. Let me
know.