Change row source based on value of text box

[dbDamo]

I am having trouble working out how to change the row source of a combo box based on the value of a text box.

I have 2 controls - txtCurrentStatus is a text box and cboNewStatus is a combo box.

I would like to change the row source of cboNewStatus based on the value of txtCurrentStatus, for example if the value of txtCurrentStatus is "Open" then the row source of cboNewStatus should be changed to my tblOpen table which holds all possible "new status" values when the txtCurrentStatus value is "Open"

I've been trying to perform this in the forms On Current event as it is the After Update event of cboNewStatus that updates the txtCurrentStatus.

I've tried adding list items 'dynamically' based on txtCurrentStatus by using the additem and removeitem functions, but I can't get this to work properly either.

The aim is that only the 'next status' values that are possible for the 'current status' are displayed to the user, rather than all statuses.

I've attached a small sample database which should sho what I am trying to do.

Any help greatly appreciated

 

[wiklendt]

ok, not really sure why you're doing it that way, but first of all i think you may have normalisation issues with your tables.

the setup is a little too abstract for me to understand how this may be applied to any real situtation, so it's hard for me to conceptualise a solution for you. can you possibly explain a little more, perhaps with a short, descriptive example?

 

[dbDamo]

Thanks for the interest wiklendt - I agree it is a strange setup but I couldn't find any other way around it.

I have finally managed to get it working and it was actually a lot simpler than I was making it - only needed to use a SELECT statement for the rowsource of the combo box and change that rowsource depending on the value of the text field.

I've attached my sample, just in case anyone else ever has the strange need for similar functionality...