Requery a form to update combo query

[Don]

On my form I have a combo box with a select query that populates the choices
in the combo box. Sometimes during entry an item must be added to the combo
box. To address this, in the double click event of the combo box, my code
opens a pop-up form so I can update the table that holds the items shown in
the combo box. This part works well. What I want the form to do once I close
the pop-up is to update the combo box so that I can choose the updated entry.
When I put a button on the form with the DoCmd.DoMenuItem acFormBar,
acRecordsMenu, 5, , acMenuVer70 code, it updates perfectly. I have tryed to
add the following code to refresh the page and also have tried to add code to
requery the form but have not been sucessfull. I have tried both of the
following code seperately and together in the after update event, single
click, and several other events of the combo box. without sucess

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
and
Me.Form.Requery

I guess the button will work but I would like to reduce the key strokes not
increase them. Any suggestions on where I am going wrong?

 

[boblarson]

1. Don't use the DoMenuItem coding structure. This is outdated code. Use
the DoCmd.RunCommand acCmdXXXXX (where XXXX is the appropriate selection for
whatever you are doing).

2. If you have this code on the form that updates the information then you
would want to requery the COMBO box. You would do this by not using the ME
keyword but instead:
Forms!YourFormNameWithTheComboHere.YourComboNameHere.Requery

Hopefully that will help.
--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

 

[boblarson]

Oops! Forgot to mention - What I meant is to put the code in the CLOSE event
of the POPUP form.
--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

 

[Linq Adams via AccessMonster.com]

If I understand what you're trying to do, try setting your popup form to also
be modal (this will stop execution of subsequent code until the 2nd form is
closed) then in the next line of your DoubleClick event do your requery.

BTW, you don't need to requery your form, which will return you to your first
record in your main form. Instead, simply requery the combobox. So, something
like

Private Sub YourComboBox_DblClick(Cancel As Integer)

'This opens the secondary form as a PopUp/Modal form
DoCmd.OpenForm "SecondFormName" , , , , acFormAdd, acDialog

'This requeries the combobox
Me.YourComboBox.Requery

End Sub

Replace

YourComboBox

and

SecondFormName

with the actual names of your objects.

Good luck!

 

[Linq Adams via AccessMonster.com]

Hey, Bob! I'd have saved my fingers if I knew you were still up! But you're
on west coast time, aren't you?

Linq

 

[Don]

Bob,

On my pop-up form on the close event I added the following code:
Forms!Enter_New_128-G.Combo102.Requery
The name of the form is [Enter New 128-G] and the combo box is [Combo102]
following the sample:

Forms!YourFormNameWithTheComboHere.YourComboNameHere.Requery

I am not sure where I went wrong. As a result I received the following error:

Compile error
Syntax error

What do you think,

Dennis

 

[Don]

I tried the suggestion Bob gave me on another Pop-up form for a similar type
of combo box and it worked without error. I then copied the same thing only
changing the combo box name and it worked!!! The following is the code:

Forms![Enter New 128-G].Combo102.Requery
Forms!Enter_New_128-G.Combo102.Requery
The only difference is the [] around the form name which must be the reason
for the Compile, Syntax error. Thanks for the help.

 

[Don]

Bob,

As stated in my other post I was sucessful. Now I have a little wrench in my
plan. As I have two forms with the same pop-up form to add the combo box list
items, how can I make the code you suggested work. I tried the following:

Forms![Enter New 128-G].Owner.Requery
Forms![Track 128-G].Owner.Requery

Of course it does not work. I think I need an If then statement but I am not
sure how to make it work. I have the same need on two forms to update the
same table that contains the information for combo boxes ifor the different
forms. The code above shows both the form name and the combo box name. I
don't know where to start? Here is what I am thinking:

If Forms![Enter New 128-G] is open Then
Forms![Enter New 128-G].Owner.Requery
Forms![Track 128-G].Owner.Requery

Not sure how to code an open form?
-
Thanks,

Dennis

 

[boblarson]

You wouldn't need the brackets around the form name if you don't use spaces
or special characters (-). Underscores are okay but any other characters
should be avoided in object names.
--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

I tried the suggestion Bob gave me on another Pop-up form for a similar type
of combo box and it worked without error. I then copied the same thing only
changing the combo box name and it worked!!! The following is the code:

Forms![Enter New 128-G].Combo102.Requery
Forms!Enter_New_128-G.Combo102.Requery
The only difference is the [] around the form name which must be the reason
for the Compile, Syntax error. Thanks for the help.

--
Thanks,

Dennis

Hey, Bob! I'd have saved my fingers if I knew you were still up! But you're
on west coast time, aren't you?

Linq

 

[Bob Quintal]

Bob,

As stated in my other post I was sucessful. Now I have a little
wrench in my plan. As I have two forms with the same pop-up form
to add the combo box list items, how can I make the code you
suggested work. I tried the following:

Forms![Enter New 128-G].Owner.Requery
Forms![Track 128-G].Owner.Requery

Of course it does not work. I think I need an If then statement
but I am not sure how to make it work. I have the same need on two
forms to update the same table that contains the information for
combo boxes ifor the different forms. The code above shows both
the form name and the combo box name. I don't know where to start?
Here is what I am thinking:

If Forms![Enter New 128-G] is open Then
Forms![Enter New 128-G].Owner.Requery
Forms![Track 128-G].Owner.Requery

Not sure how to code an open form?
-
Thanks,

Dennis

I would do the following:
Set the double_click event to open the form and include the name of
the calling form as the parameter in openargs.
e.g. docmd.OpenForm "Whatever",acNormal,,,acFormAdd,acDialog,me.name


In the close event, use the name passed in openargs to identify the
calling form

Forms![(me.openargs)].Owner.Requery

or
If me.openargs = "Enter New 128-G"
Forms![Enter New 128-G].Owner.Requery
else
Forms![Track 128-G].Owner.Requery
endif