Change list in combo box on continuous form based on textbox

[McSwifty]

I am not sure how possible it is, but I want to be able to do it.

I have a form that lists employee info - simply name(txt), staff number(num), two dates (next review and last review) and a comments box(memo).

I have added a bound combo box for a who reviews next field.
I need this combo to update dynamically based on the people to whom the staff mebmer reports, which I have in a table.

It may be messy, but the table refers to staff number, name and 5 possible reporting fields (manager and 4 supervisors).
I want to use this connection to link the combo box to the 5 possible people who the staff memeber reports to.

I have tried queries and SQL and even query on a query, but no luck.
I get to a point where i am stuck now and I have the following ex of what I get.

what I want to do is where the Name field is on the form, list the reporting to people in the combo box.
In simplicity, switch the axes on the query.
I got stuck here.

I am using A2007 at home in a 2003 db, just playing as at work I can only work on 2003. I do more testing at home and research as internet a huge amount faster -LOL
Hope someone out there can help me.

tia.

 

[McSwifty]

SOLVED. I found the solution, if anyone is interested please note here.

Cheers for the prospective help anyway.

I did have to use a me.formcontrol.requery in the onclick and the ongotfocus events to make it work.

 

[Newbie101]

SOLVED?!

Where did you find the fix? I may be running into a very similar issue.

Thanks!

 

[McSwifty]

This is how I solved it.

http://i.imgur.com/LIII0iD.png - has all the relevant details in the table and the way I did the query to get it to work in conjunction with the onclick and ongotfocus - both of which i have linked to requery the combo box control.

it may not be the most efficient way but it works for what i wanted.

PS> I know its bad to use name as a table field entry but I haven't got around to fixing the whole db as yet. My compay IT dept is looking to expand and take this over with full programmer support and SQL server backend, so I am only implementing fixes and not recreating the whole thing at this time.

 

[Newbie101]

I think this is going to be PERFECT for me!!!! Nice job!!

Could I bother you for a snapshot of the SQL statement in the "SQL View"??? It would really help me with the syntax of what I'm attempting to do.

Please and Thank you!

Youre the best...