Requery

  • Thread starter Thread starter John T
  • Start date Start date
J

John T

I posted this yesterday and got a great answer, however,
I missed something when I posted. Here is what I postred
along with the response:
I have a form with a combo box called Color. I also have
a button next to it called Add Color. If a color is not
in the combo box, I press the Add Color button and it
takes me to the Add Color form. When I finish adding the
new color I have a button called Done. When I press that
it closes the Add Color form and returns me to the
original form. The Done button has an event programmed
for the On Click event that does a save (DoCmd.Save). The
problem I have is when I return to the original form, the
new colors aren't there. I have to close the form and
open it again to get the new colors to appear. Is there a
way to have this combo box update so the new colors
appear without having to close and open the form?
Click to expand...

Response

The DoCmd.Save is accomplishing nothing for you. Replace
it with...

Me.Dirty = False
Forms!NameOfFirstForm!NameOfComboBox.Requery

This works great. The problem is I have more than one
form that will do this. Form 1 and form 2 both have the
color combo box and the Add Color button. I tried adding
the code for both forms but I get an error message saying
Access can't find the other form. For instance when I go
to the Add Color form from form 1, form 2 is closed and I
get the error message because form 2 is closed.

Is there a way to solve this.

Thanks for all the help.

John T.
 
you shouldn't need to "update" the form that is closed. when that form
opens, all the colors in the combo box's row source (table or query),
including the "new" color, should populate the list - unless you have done
something to restrict the list (query criteria).
 
John
When you call the Add Color Form I would also added the cod
DoCmd.OpenForm OtherFormname, acNormal, , , , acHidde
This will open the other form and then hide it so that no one notices that it is open
Then you can do an on close action lik
DoCmd.Close acDefault, Form_OtherFormname,acSave?
You will have to decide what the save is, either acSaveyes, acSaveNo, or acSavePromp

Hope this helps.
 
I agree with this ecxept that if I don't add this code to
the event for the Done button, Access doesn't know which
form combo box to update. That is why I repeated the code
for both forms.

There should be a way to tell the Done button on the
Colors form where it came from, and to update that form,
I just haven't figgured that out yet.

John
 
Thanks, Jessica. I'll give that a try.

John
-----Original Message-----
John,
When you call the Add Color Form I would also added the code
DoCmd.OpenForm OtherFormname, acNormal, , , , acHidden
This will open the other form and then hide it so that
Click to expand...
no one notices that it is open.
Then you can do an on close action like
DoCmd.Close acDefault, Form_OtherFormname,acSave??
You will have to decide what the save is, either
Click to expand...
acSaveyes, acSaveNo, or acSavePrompt
 
i'm fairly sure there is a way to check whether a form is open or not, in
VBA. but i can't remember it, and i can't find it in Access VBA Help (A2003
Help is awful, the worst Access Help yet!). MVPs can you tell us how to do
it?

here's one alternative (in case we don't hear from an MVP):

create a public variable. i used

Public ctrlName As Control

set the value of the variable in the same code that opens the "Add a color
to the list" form, as

Set ctrlName = Me!ComboBoxName
(if Me doesn't work, use the full form reference: Forms!FormName)
DoCmd.OpenForm "Colors"

make sure you add the "Set..." code to both Forms-with-the-combo-box.
change the requery code on the Done button to

ctrlName.Requery

hth
 
Any reason you don't use the built-in "not in list" event?

The not in list event is designed exactly for what you are trying to do:

The not in list event also has provisions for re-loading the combo box for
you.

So, you don't have to write any re-query code anyway.

I would strongly recommend you use the "not in list" combo event.

However, since you do have the combo and the button code now then why not
just pick up the name of the calling form in that prompt form. That way,
your "close" button can requery the calling screen combo.

You can actually pick up the previous form in the on-open event, or even as
late as the on-load event.

at the module level in the form:

dim frmPrevious as form

Then, in the on-load event:

frmPrevous = Screen.ActiveForm

And, just in case the combo box name is different each time, then why not
just pass the name of the combo box to the "prompt" form.

DoCmd.OpenForm "yourform", , , , , , "comboName"

Then, you can go:


frmPrevious(me.OpenArgs).Requery.

So, you can pick up the previous forms name,and you can pass the combo you
want to requery. However, as mentioned, you can do ALL of the above with the
not in list event as follows:

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

If MsgBox("Add new group type?", vbOKCancel) = vbOK Then

DoCmd.OpenForm "frmAddGroupType", acNormal, , , acFormAdd, acDialog

Response = acDataErrAdded

End If


End Sub


The above few lines of code in the combo box "not in list" event will do the
whole shebang for you. When you close the form...you will be right back to
the combo box with the value entered..and you can then just move on to the
next field........
 
Back
Top