Requery not working

[gsandy]

I have a form (frmHourEnter) with a combo box (cmbJob) and if the number entered into the combobox is not in the list then another form (frmJob) is opened and a new number is entered. On closing this form I run:

  Me.Form!frmHourEnter!cmbJob.Requery

The combo box (cmbJob) does not show the new number unless I close the form (frmHourEnter) and re-opened it.
If I add the following code to the one above:

  DoCmd.Requery "Form!frmHourEnter!cmbJob"

Then click out of, and then back into cmbJob the number is in the list! What can I do so that the number is in the list when the second form is closed? Thanks Sandy

 

[MarkK]

This seems like a workflow design problem to me. A combo provides a convenience for users by presenting them with a list of data they may wish to choose from, so the combo should make things faster. But if a user knows the data, and knows it's not in the combo, why interrupt her workflow and make her go update the combo, which she won't be able to make use of?

That seems like the tail wagging the dog.

 

[JHB]

Have you in form frmHourEnter, set the cmbJob's property "List Items Edit Form" to "frmJob"?
If yes, it should automatic put the "new" entry in the list.
http://www.access-programmers.co.uk...V.14.1033/MSACCESS.DEV/content/HV10296094.htm

 

[gsandy]

JHB - I did not have cmbJob's property "List Items Edit Form" set to "frmJob", so I did this but it still did not show new item.

Mark - The object of my database is to enter hours from a time-sheet and allocate them to a job number. If a job number is not in the list then the operator cannot proceed until the new job is created. Hence the reason to automatically open another form to enter a new job number. If the new number is in combo box cmbJob (which is still open) the operator can continue entering hours against that new job number (which is now always available). If new job number is not in cmbJob then the operator has to go out of the form (frmHourEnter), re-open it and continue.
Thanks Sandy

 

[MarkK]

When you close frmJob, you should run this code . . .

Forms!frmHourEnter.cmbJob.Requery

. . . to target the combo on the other form.

Me.Form!frmHourEnter would target a control on the form in which code is running.

 

[gsandy]

I changed the code, still no joy!:banghead:
Interestingly I tried similar thing on two other forms and the requery worked, which makes me think it is a properties issue? Any thoughts?

 

[MarkK]

Can you be more specific about "no joy?"

Do you get a compile error, runtime error, no error and combo is not updated (fails silently). If it fails silently, how do you know it ran? Where did you put the code?

Cheers,

 

[gsandy]

It fail silently.
Attached is database in question (with non relevant bits removed). If you open frmHourEnter and enter a job number that is not in the list and tab to the next field, frmJob opens. Add a new number and press "Save & Close". Its now that I would like the new number appearing in the combobox.

 

[MarkK]

The code never runs. Your button click is handled by an [embedded macro], not the [Event Procedure] where you put the code.

Open the form in design view, select the button, open the button's property sheet, select the 'Event' tab, and notice the click event. Change [Embedded Macro] to [Event Procedure] and your code will run. But then you will find more problems, because there is an unsaved edit in the combo, so the Requery will fail.

 

[JHB]

Try it now, database attached, (I've have removed some of your DlookUp because they were not use full).

 

[gsandy]

Thanks for your help JHB (the code worked fine) and Mark, I have almost completed my first database. Cheers Sandy.

 

[JHB]

You're welcome.