Vary combobox options?

[RainLover]

the combobox now updates on focus so the dropdown list reflects the options available (based on the current status textbox) but only shows the 'new status' column from the previous posted table image. The short/long/full description columns all appear blank?

You may need to adjust the column widths in the properties box.

Does your query show correctly when opened in Datasheet view.

 

[RainLover]

I disagree with the way you are going about this. It should ALL be table driven, not code driven. I am attaching a sample DB. When you change the criteria on the join in the query, the contents of the combobox will change as well.

Can you please explain what Table driven and Code driven are.

I can follow Code driven as this simply means an event has happened so the code takes over.

But as far as a Table driving things I am not sure of your meaning.

 

[here4real]

See the DB I attached in earlier post. There are If statements at all. Everything is driven by the tables and the relationships defined in the tables. Then, when you do the join with selection criteria of a single userid, you obtain the subset of statuses that you want to display in the combobox. The beauty of this is that you can add and/or subtract statuses going forward and you never have to touch code, just update the tables.

 

[here4real]

Should have said there NO If statements at all.

 

[here4real]

The problem with doing it in code is what if they add another status in the future or change which statuses a particular userid should see? You will constantly be editing code. By keeping it all in tables and relating the data in those tables, you just add or delete or modify records in a table - much easier.

 

[RainLover]

The problem with doing it in code is what if they add another status in the future or change which statuses a particular userid should see? You will constantly be editing code. By keeping it all in tables and relating the data in those tables, you just add or delete or modify records in a table - much easier.

I am going to put this aside for now.

It is not the way I would do it. But then I can't say you are wrong.

 

[here4real]

adh123 - did what I say make sense to you?

RainLover - I hear you. After more than 30 years of coding, I have learned that the more that I can build into the data and relationships the easier the coding and maintenance of code becomes. I always build referential integrity into my DBs so the DB itself can do the checking and I don't have to code it. Same concept here.

Take care.

 

[adh123]

Here4Real:

I was confused at first due to you describing 3 tables and originally I only had 1.

The database was very useful as it prompted me to firstly tidy up the new tblDrpEnqOptions table (so now has PK, Current_Status_no, Available_status_no) and link this last column to the PK of tblDrpEnquiries (which stores the options once and their descriptions) in a query, based on the forms text box.

Creating a combobox based on the query works a treat and is much simpler than I was first anticipating (as this is my first time delving into Access if it is something new I usually dive straight in and search on google for an answer, needless to say the results are sometimes overly complex when they do not need to be!). I was initially hoping to produce something which would not need an extra table being created but on reflection the users will not have any need to know/understand the back end and so this is a simple, manageable solution.

Apologies for my dimness and a huge thanks for everyone's help

 

[here4real]

My pleasure. You should mark this as solved (assuming it is) and click the Thanks button on the responses.