Requery Combo Box from another form issue.

[Mouse51180]

I have an form that uses a combo box as a drop down list. This combo box lists a set of employees. If a person is not listed in the combo box when the form is filled out, it spits out an error and opens a "New Employee" form. This form allows a new person to be entered in. There are two buttons on this form "save" and "back". "Save" saves the new person, which works fine and back requerys the combo box on the original for for the updated list and then closes the "New Employee" form.



Issue:
When I press the back button, the New Employee form closes (as expected) but the combo box is not updated. I hear a ding in the background like there is an error, but do not get any error message. If I then close the original form and reopen it...the new employee is listed.

I could just keep closing the original form and reopening it, but this seems like back practise and some of my fields are set to be required so when the form tries to close it spits out errors about other fields not being filled in yet.

"Original" form is frmNewAsset
Combo box on original form is cmbAssest_Owner_FK
"New Employee" form is frmNewEmployee
Back button is btnBack

This is the code listed in the On Click event for the btnBack button.



Any ideas?

[jwhite]

Admittedly I didn't read your entire post, but you can requery a control on another form like:

Forms![FormName]![ComboBoxName].Requery
or
Forms![FormName].Controls![ComboBoxName].Requery (I think the first one, not at computer where I can test)

[Mouse51180]

Admittedly I didn't read your entire post, but you can requery a control on another form like:

Forms![FormName]![ComboBoxName].Requery
or
Forms![FormName].Controls![ComboBoxName].Requery (I think the first one, not at computer where I can test)

Your first suggestions is what is in my code now. I added the ".Requery" to the end of it. This did not succeed.
I tried the ".Controls!" method as well and got the same result.

[Micron]

Looks to me like you already have a macro step to requery the control. What I don't get is

"Save" saves the new person, which works fine and back requerys the combo box on the original for for the updated list and then closes the "New Employee" form.

If you enter the new name and click the back button, you are not saving the record??
Maybe turn on the single step option on your macro and watch what's going on. The beep may be a result of your second If statement, which indicates to me the record is not being saved, yet you say it's there if you close and reopen the form. Hope that helps, but I'm not a macro user.

What's even more confusing is that this back button macro seems to delete a new record (the part that stipulates 'if the form is dirty and this is a new record, undo it).

[Mouse51180]

Looks to me like you already have a macro step to requery the control.

This requires for another list box on the same form. There is a second list box on another form that is not updating.

What I don't get is..If you enter the new name and click the back button, you are not saving the record??

Maybe turn on the single step option on your macro and watch what's going on. The beep may be a result of your second If statement, which indicates to me the record is not being saved, yet you say it's there if you close and reopen the form. Hope that helps, but I'm not a macro user.

What's even more confusing is that this back button macro seems to delete a new record (the part that stipulates 'if the form is dirty and this is a new record, undo it).

Correct. I wanted my user to be able to close the new employee form without saving. I found that if I started to type a name in and then closed the form without hitting the save button...it still saved the information I typed in. So all that other code you are looking at...all it does is deletes the information if the save button is not clicked.
I suspected this part of the code might be causing the issue and so I added 6 new employees all at once...figuring that if the code was deleting the last entriy, I should still get an update with 5 new employees, but this did not work either.

[Micron]

back requerys the combo box on the original for for the updated list
Seems counter intuitive to me. If you're going back to the original list, then it's not being updated.

Anyway, I think your easiest solution is to make the second form unbound, which I think it is not. Then if user clicks Cancel (which I prefer over Back as back doesn't imply a cancellation of the entry) you simply close the form unless you want to trap for anything that might have been entered by checking the .Dirty property. Otherwise, append the data using either .Execute method of CurrentDb or RunSql method of the DoCmd object. The former shouldn't prompt you regarding the append operation. When you close the input form, the previous form should show the list append if it's a comb box. If it's a listbox, you'll probably have to requery it from the input form after the append.

As usual, there is more than one way. You could also use the OnNotInList event if the needed value isn't in the list, but that's not as reliable IMHO.

[Mouse51180]

back requerys the combo box on the original for for the updated list
Seems counter intuitive to me. If you're going back to the original list, then it's not being updated.

Anyway, I think your easiest solution is to make the second form unbound, which I think it is not. Then if user clicks Cancel (which I prefer over Back as back doesn't imply a cancellation of the entry) you simply close the form unless you want to trap for anything that might have been entered by checking the .Dirty property. Otherwise, append the data using either .Execute method of CurrentDb or RunSql method of the DoCmd object. The former shouldn't prompt you regarding the append operation. When you close the input form, the previous form should show the list append if it's a comb box. If it's a listbox, you'll probably have to requery it from the input form after the append.

As usual, there is more than one way. You could also use the OnNotInList event if the needed value isn't in the list, but that's not as reliable IMHO.

That is a lot of new terminology for my skill level. Might take me some time to processes it all and get back here with an answer if it worked or not.

Thanks for the advice.