Full Version: Combo Box Display Value Based On Last Record
UtterAccess Forums > Microsoft® Access > Access Forms
vespertin
Hello
Is it possible to create a combo for a datasheet form that displays a specific value based on the value of the last record.
For example - instead of having the combo display the values 01, 02, 03, 04, 05
the combo will only display 02, if the last record was the value 01.
likewise if the last record was 03, on the next record the value in the combo will only display 04.
The reason I ask is that at present my combo displays 01, 02, 03 etc...and the user can quite easily select the any value they like. Whereas, if it only gives them one value to choose from based on value chosen from the last record - then less mistakes will be made.
Any insight how to achieve this would be very encouraging.
Using A2007
theDBguy
Hi,
welcome2UA.gif
If there will only be one valid choice, what would be the use of a combobox? If the field is required, then I say disable the control on the form and just force assign the value using code. You should be able to do that by modifying the DefaultValue property in code.
Just my 2 cents... 2cents.gif
vespertin
thinking about - perhaps a combo box isn't the way forward.
The idea is I have a field on a datasheet called [status]
If the value of that field is "In Stock" then the next record in [status] should be "Out on loan"
The next record that's entered should then have a default value for [status] as being "Returned"
After that the next value of [status] should be "Item Calibrated"
Hope this makes sense - at present I have a combo box and if the value of [status] is "Out on loan" the user when beggining a new record and sometimes selects "Item Calibrated" but for audit purposes we need to declare the item has been returned first.
For if [status] says "In stock" the next record they select sometimes says "Item Calibrated" when they should first choose "Out on loan" then "Returned" then "Item Calibrated" etc....
Is it possible where or how I would use the default value of a current record based on the value in the previous record?
Apologies if i appear rambling!!
GroverParkGeorge
That makes sense. So this combo box is a way to track statuses for items. In that case, it is probably appropriate to use this combo box. Your REAL problem here, IMO, is user training < But we can't address that with a database, so we look for alternatives within the database.
So, in this case "Last" really means "previous status" of the item. I don't know the design of your tables, but I can make an assumption, based on what you've described, and suggest a possible approach. It's not all that simple, but maybe it's worth the work.
You have a table of "items" and a table in which you record "transactions" regarding those items, and a table of "statuses". This combo box is populated from the statuses table and it has a primary key and a value field. Correct?
So, here's what I would do to limit the choices in this combo to the "next appropriate status" for any given item. Join the statuses table to the "transactions" table, and add a criteria field to the query so that rows are filtered by the current record in the datasheet. THEN, limit the rows further by only showing statuses from the status table that are not used in the transacation table for that item. I can describe it more easily than I can design it in SQL, so maybe, if that doesn't ring a bell for you, you can upload a copy of the accdb or mdb, with any sensitive data removed and just enough sample data to work with this form.
Database files must be compressed into ZIP files before uploading and must be under 2mb.
vespertin
Hi - That's exactly what I'm trying to do
Not able to load the full db - but have uploaded an approx of what I'm after - any input or advice you can offer to best accomplish would be most welcome.
Thanks once again for your time and response. Its a huge help to me.
GroverParkGeorge
Sorry, but looking at the tables, I think you may need to rethink how you're doing this. I assume a single piece of equipment can be checked out multiple times, perhaps by the same person? With this table design, it's not possible to figure out what the status is going to be at any given time. Here's why. A person checks out the equipment, so it goes through all of the steps and comes back. A week later, the same person gets that same equipment again. "Previous" status becomes impossible to capture now, because all of the statuses are already in the table for that person for that equipment. You need at least a transaction date for each record, so you know WHEN it happened.
lso, that table is called "tblDepartment", but the content is transactions involving equipment. I don't quite see what "department" has to do with that.
Tell us about your business process. Maybe we can give you some guidelines on how to restructure the tables to support that.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.