Error 2950 on Requery Method with Combo Box

[ProgEvalJAA]

My specific question is: What step(s) am I overlooking with the Requery Method that I receive an error?

I have a form with a field labeled "Staff Contact." After a user inputs a new Staff Contact, saves the record and then moves to the next record, I want the requery method to refresh the list of values. That way, if the user has typed in a new Staff Contact name, the list will be updated with that Staff Contact and all previous Staff Contacts. Then, when the user moves to the next record and starts typing the new Staff Contact name, Access will suggest the typing without the form and its subform ever being closed.

To accomplish this on another subform of a different form, I created a combo box called Company Name. Then, I created a query of Company Names and used that as the Row Source for that combo box. Next, I created a macro called Requery Company Names and attached the macro to the After Update Event of the Company Names property box. This works fine and does exactly what I expected it to do in suggest the typing for any new names without the form or subform being closed.

For some reason, when I attempt to replicate this for the staff name on another subform of another form, I receive an error message of 2950 saying there is no such field named Staff Name. I did some research about the error, and according to Microsoft, it means the database is not trusted. However, all the databases I use are on a trusted location. I also verified this using an AutoExec macro, and it indicates the database is trusted as well.

Does anyone have any additional thoughts?

 

[RuralGuy]

If it is necessary to requery the RowSource of a ComboBox, then do it in the OnEnter or GotFocus event of the control.

(RG for short) aka Allan Bunch MS Access MVP acXP ac07 winXP Pro
Please respond to this forum so all may benefit

 

[ProgEvalJAA]

I moved the macro to the GotFocus event, and I still received the same error.

 

[RuralGuy]

What version of Access are you using and do you have queries as the RowSource of your ComboBox *and* form?

(RG for short) aka Allan Bunch MS Access MVP acXP ac07 winXP Pro
Please respond to this forum so all may benefit

 

[ProgEvalJAA]

I am using Access 2007.

Yes, I have queries as the RowSource(s) of my ComboBox and Form. They are two different queries.

 

[RuralGuy]

I'm not a big fan of macros but any chance you can post the db so we can look at it? Maybe one of the free file hosting sites since I don't think you can attach a file here. Remove or scramble and sensitive data of course.

(RG for short) aka Allan Bunch MS Access MVP acXP ac07 winXP Pro
Please respond to this forum so all may benefit

 

[TheAceMan1]

How are ya ProgEvalJAA . . .

[ol][li]Most of what you've posted is kinda vague. Can't tell if your talking two controls or one! A field labeled [blue]Staff Contact[/blue] is one thing, compared to the [blue]Name[/blue] property of this field (what we need to know!)... which is?[/li]
[li]Are you talking and additional combo/list box control your trying to requery/refresh? ... we need to know the [blue]Name[/blue] property of this control as well![/li][/ol]




See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[ProgEvalJAA]

AceMan1,

Let me try to explain things better.

1. I have a combo box for Staff Contact as follows:

The label portion of the control is Employee Name.

The unbound portion of the control, the part I am trying to refresh is set as follows:

Control Source = StaffContact (comes from Main table)
Row Source = qryStaffContact

Since I am trying to refresh the Staff Contact, I created a query titled qryStaffContact (see the Row Source). Then, I created a macro. It has one line in it, a line to requery the query. The macro is attached to the After Update event of the Properties for the unbound portion of the control.

This combo box is on a subform linked to a Main form. The subform details customer information. The Main form and subforms are linked by a customer id number. The Main form merely lists customer name and id number.

 

[ProgEvalJAA]

I finally discovered the problem.

In reviewing the Property Sheet for the ComboBox for Staff Contact, I discovered the Other Tab (the tab right after the Event tab) was missing something. The name StaffContact needed to be filled in here, and it was blank. Once I did that, everything worked perfectly.

Thanks to those who attempted to help me.

 

[RuralGuy]

Thanks for posting back with your success.

(RG for short) aka Allan Bunch MS Access MVP acXP ac07 winXP Pro
Please respond to this forum so all may benefit