Requery Issue: Getting a Msg When Clicking Combo Box

[ml8889]

(Office 2003 / 2000 format) I have a Main form created from a Main table. However on the form are combo boxes that allow you to choose from the list and the lists show row sources from other tables. (e.g., The form has a combo box called "Manager" and it is pulling the results from the MgrTbl. The form has a "Status" combo box and it shows what's in the StatusTbl, etc.)

Since I limit to list for these combo boxes, I have a button by each combo box that allows user to update the, for example, MgrTbl. On click, it opens the MgrTblFORM and a new entry can go into that form to update. Then they close the MgrTblFORM. Now they are back to the Main form. If they click the dropdown box for that combo box, they will see the list including the item they just added when they updated the MgrTblFORM. (The list gets refreshed/requeried.)

The problem that I'm having is this.... All Requery commands work fine on the "On Got Focus" line in Properties for each combo box. However when I get to the one for "Manager", it gives me an error message and then the Action Failed, Halt box. When I look at the properties of the "Manager" combo box and those of the other combo boxes, they are the same. (Each have a Requery macro set at "On Got Focus".) (Please see attachment for screen shot examples.)

Why do I keep getting this error for this item? I must mention that this field is a newly added field to the Main table and the Main form. Is it rejecting because it wasnt with the original setup? Do I have to recreate the whole form from scratch? (I hope not!) Is there a better way for me to make the lists update after the other form associated with that list is updated? Please advise and thanks!

 

[AccessMSSQL]

It looks like you need to open your main table in design view and change the Manager field so it is NOT a required field. This is your problem. I don't think the error is a result of the requery of the manager combo box. It's because a value is required in this field and on the requery the form data is trying to commit the record to the table but it can't because there is no value in Manager field.

 

[ml8889]

Thanks, I went back to my Main TBL and changed all Required fields to "No", although I wish I could keep them. My form didnt respond at first (each time I clicked the Manager combo box, it would not show the drop-down or do anything; but it would delete already entered fields on the form.) I think it was because the requery didnt have a control name.

I ended up adding a Control Name on the Requery macro to keep from losing data in already entered fields. I enterd the name "combo54" since that's what's under the Properties - OTHER tab under the NAME entry for this combo box. Should I do it this way? Should I do this with all of my requery macros to make sure none of them causes a record's content to disappear? (Didnt have to do this before I added this Manager combo box field, but I dont mind doing it.)

WHAT I DID NOTICE, though, is that when I look at the Name section of the OTHER properties tab for my other combo boxes, they have the actual name of the field. But my "Manager" combo box has Name "Combo54". Seems this might be the reason for my issues... your thoughts? And should I change the Name under properties from Combo54 to the field name in the Main table or to the field name from the MgrTbl, or leave the name combo54?

Please provide further comments. Your help is GREATLY appreciated more than you know.

 

[AccessMSSQL]

It sounds like you are making it more complicated. YES, you should make the drop down list the same name as the manager field. Instead of Combo54, please rename it to Manager. Access automatically puts it's own name on a control until you change it. I'm not sure why Access does this in later versions but initially the properites window opens but only shows the control source. The control name in most cases should always match the control source.
I'm not sure what's going on with your requery in the Manager drop down. You shouldn't lose all field values. Also, you didn't have to make all fields in your table not required. It sounds like something else is going on with the form and requery. Let me know if changing the name helps.

Also, is this all happening when you try to ADD a new record? OR is it happening also with existing records. And are values being lost only in combo box fields or all fields?

 

[ml8889]

Actually, I just went back into my Main table and one by one made my desired fields Required: YES. I changed one, then open the form and entered a dummy record and closed it. Then I'd go to table and changed another to Required and save, open the form, created a dummy record and closed. I didnt this for all. I also changed the Name under properties to "Manager" for the manager combo box and I entered the Combo Name on the requery macro for that combo box as "Manager". Everything is working!!! Possibly a glitch of some kind but I now have my required fields, including Manager, and all of my requeries work. So strange. (I dont write code so this is strictly Access.)

When my form opens, it's to add new records. I havent tested the issue for existing records. However it's working fine now. (Still so weird. The only thing that really changed was the name from Combo54 to Manager. Amazing how this can thwart everything!

Thanks again!