Requery does not work for me

  • Thread starter Thread starter Anne
  • Start date Start date
A

Anne

In this particular situation (I have several similar situations) I have a
combobox on a main form. This main form is FrmJobs2. Using a combo box the
job is selected, which is no problem. If there is a new job, the addition of
the new job works well until I get to the combobox for the contractor. If
there is also a new contractor, I created a double click event on the
ContractorSelect combo box and it opens the contractor table and a
contractor can be added. After the addition, the contractor form is closed,
works well.

Now I am back at the main form, and for the life of me, I cannot make it
that contractor shows up in that combobox unless I close the FrmJobs and
reopen it. I tried all kinds of requery and refresh, but the new name does
not show up.

Main for is: FrmJobs2.
Combobox is ContractorSelect. Bound Column is ContractorID.

What is the code to make this new contractor show up in the combobox and
where should it be placed.
Anne
 
Did you try Me.cboComboboxName.Requery? Also, this would need to be done in
the code that opened the other form. In the DoCmd.OpenForm call, use the
acDialog WindowMode argument. This will open the other form as a "pop-up"
and pause the calling code until you close or hide the other form. Place the
Requery command after the OpenForm command. When you close the other form,
the Requery will be executed.

It may be easier to use the built-in capability of the combo box. Set the
Limit To List property of the combo box to yes. If you have a new
contractor, Type in the name of the contractor (or whatever is actually
displayed in the text portion of the combo box) and when you try to move
away from the combo box the NotInList event will fire. In this event, open
the form as mentioned above. After you close the form use the command
"Response = acDataErrAdded" in the NotInList event. This will tell the combo
box to look for the new data. You could pass the new data entered by placing
the variable NewData in the OpenArgs argument of the OpenForm call and
retrieve that value in the Open event of the pop-up form.
 
Hi Anne

You need to create a form to enter the contractor.
Change your DblClick event to open the form, instead of the table.

In the AfterUpdate event procedure of the contractor form, you can requery
the combo on your original form:

Private Sub Form_AfterUpdate()
If CurrentProject.AllForms("FrmJobs2").IsLoaded Then
Forms("FrmJobs2")!ContractorSelect.Requery
End If
End Sub

If you want to remove the contractor from the combo after a deletion, add
this to the contractor form as well:

Private Sub Form_AfterDelConfirm(Status As Integer, Response As Integer)
If Status = acDeleteOk Then
Call Form_AfterUpdate
End If
End Sub

Notes:
1. If the original form has an unvalidated entry in the combo, you will need
to Undo the combo before the requery will work.

2. Older versions do not have the AllForms collection. Use the IsLoaded()
function from the Northwind sample database instead.
 
First, is there a criteria in the row source of the combobox that prevents
the new contractor from appearing in the list?

The code is Me!ContractorSelect.Requery
 
You are all bringing up items, that I have been having problems with. Using
the not on list Event, I believe, does not help me, because there are a lot
of items to be added, not just the name. I don't think that it is going to
work. There is address, phones, contact, etc.
I really think that the FrmContractor need to be opened because there is too
much data to be entered.
Please clarify what you are saying with: this needs to be done in the other
from. Do you mean the FrmContractor or the FrmJobs2?
Anne
 
Have you perhaps been requerying/refreshing the form, rather than the
combobox itself?
ContractorSelect.Requery
should do it.

HTH
- Turtle
 
My DblClick event actually did open the FrmContractor, it was somewhat of a
typo.
This is it, when I put my requery on the FrmContractor instead of FrmJobs2,
the new contractor showed up in the combobox..
I put all my requeries attemps were on the form FrmJobs2, not the form
FrmContractors.
That is were my problem was.
Thanks you,
Anne
 
When I say, do this in the other form, I mean the one you are popping up to
enter the data for the new contractor. Yes, this can be done is a separate
form to enter more than just the name even though you are using the
NotInList event. The trick is to pause the code until you have finished the
entry, this is done with the acDialog window mode argument when you open the
pop-up form. The code will continue when you close or hide the pop-up form.
After the OpenForm line, use the command Response=acDataErrAdded so that the
combo box will update itself with the data you just entered in the pop-up
form.

I reread your original statement and you say you are opening the contractor
TABLE in the double click event. Open a form to do this instead, not the
table itself.
 
Back
Top