Combo box issues

D

DevTycoon

Registered User.
Local time
Yesterday, 16:13
Joined
Jun 14, 2014
Messages
94
I have a combo box that needs to only show approved values for any current data input but also be able to show legacy values (that were approved at one time but are no longer approved)

My current issue:

How can I only allow the user to select from current approved values but still be able to pull a legacy record and show the legacy value (that was approved for use at some point in history)





Table structure

tblRepairReason
ID (Nnumber)
Name (Text)
ApprovedValue (Yes/No)

The recordsource of the combo box:
Code: 
SELECT tblRepairReason.ID, tblRepairReason.Name, tblRepairReason.ApprovedValue 
FROM tblRepairReason 
WHERE (((tblRepairReason.ApprovedValue)=True));


Thanks for your interest in my question
 
You can perform a check using the After Update event of the Combo box.
 
Or validate the value in the form's Before Update event. Whichever way you choose.
 
GinaWhipp said:
Side note: You have another problem, you have named a field *Name*. This is a Reserved Word for Access and will eventually cause problem. You'll be wanting to change that. For a complete list of Reserved Words see...
http://allenbrowne.com/AppIssueBadWord.html
Click to expand...


I am sorry, I used a very generic term. My actual project does not use "name" for the field. Thanks for the reminder though. I just got carried away overgeneralizing my issue.


vbaInet said:
Or validate the value in the form's Before Update event. Whichever way you choose.
Click to expand...



I do not want the user to even be able to see "not-approved values" in the combobox selection. The little pickle that I am in is that I need to preserve legacy values for viewing in the same form if they switch to a legacy record.

How will the afterupdate code make sure that information already input even if it is not approved will still show if the data was from the import. while at the same time not allowing "unapproved values for future data entry"

Basically going foreword the system will have consistent terminology but before the conversion there was less consistancy. I just need to accommodate the old data in the same combo box that only approved values can be used in going foreword.

I am not sure if my proposed logic will work.

I am going to prepare a test database that we can discuss specific code because I can not quite visualize how the after update event would work.
 
vbaInet said:
What is the view of the form?
Click to expand...


Singl record - Form view. It is basically a data entry form with three cascading comboboxes. Each child has approved values for use and values that are no longer approved for use.
 
You have two options, both of which uses the Current event of the form:

1. Programmatically change the Row Source of your combo box depending on whether it's a legacy record or not
2. Have two combo boxes one that shows all values and the other that's cut down. Programmatically hide/unhide and change Control Source

Your choice. By the way, how do you identify which records are legacy and which aren't?
 
vbaInet said:
You have two options, both of which uses the Current event of the form:

1. Programmatically change the Row Source of your combo box depending on whether it's a legacy record or not
2. Have two combo boxes one that shows all values and the other that's cut down. Programmatically hide/unhide and change Control Source

Your choice. By the way, how do you identify which records are legacy and which aren't?
Click to expand...


If it is a legacy record it will have a repairJob ID < 1500. Can I use that as a the reference for the trigger.


Supposing #1 will not work, if I go with #2 can I have them sit on top of eachother? The dataentry form is maxed out on its length :eek:.

Another issue may arrise that I would like to discuss...

Today there are certain values that are approved or not approved. As time goes by, the values may again change approval disposition. This can happen as items become outdated that are tracked or industry terminology changes. I need to not only accomodate legacy values (that were at one time approved) but also accomodate current values input that may also have their disposition change down the line.

Thanks for your clarification.:)

I should be done at work soon and will try to replicate my issue in the test database.
 
DevTycoon said:
If it is a legacy record it will have a repairJob ID < 1500. Can I use that as a the reference for the trigger.


Supposing #1 will not work, if I go with #2 can I have them sit on top of eachother? The dataentry form is maxed out on its length :eek:.
Click to expand...
That's right!

Both will work. #1 will depend largely on how you write the query. You can write the query to filter the values for you and all you have to do is Requery it. I would much prefer #2 anyway because you don't have to keep requerying.

One of the combo box's can sit just below the other (in case you need to make any design changes) and during the form's Load event change its Top property to match the other combo box's Top value.

DevTycoon said:
Another issue may arrise that I would like to discuss...

Today there are certain values that are approved or not approved. As time goes by, the values may again change approval disposition. This can happen as items become outdated that are tracked or industry terminology changes. I need to not only accomodate legacy values (that were at one time approved) but also accomodate current values input that may also have their disposition change down the line.
Click to expand...
That's fine! Just work out the logic and apply it in the query.
 
I am really struggling with how to deal with this issue on a fundamental level. I started another thread to make sure my table structure is going to accommodate my question on this thread. I have not loaded data into the repair tracking log table because I want to make sure I am on the right track before I continue.

Thanks for your help!


Table structure for cascading combo boxes

http://www.access-programmers.co.uk/forums/showthread.php?p=1366428#post1366428
 
Oki doki! If you're having second thoughts about your design or just need a quick going over then yes it's good that you created a new thread for that.

We'll see what comes of your new thread.
 
I had a similar issue, and somebody a while ago showed me a neat way of sorting the items, so that active ones were at the top, and inactive ones at the bottom.

add an extra field to the query, "inactive" or similar, assuming you have a suitable Boolean in your table. sort this column descending, so that active items are at the top, and inactive ones at the bottom.

now you have something you can use to stop people selecting an inactive item, but still displaying it on existing rows.
 
I have resolved my table structure issue and it turns out there is a better way!!

http://www.access-programmers.co.uk/forums/showthread.php?p=1367961#post1367961


Big thanks to forum member GinaWhipp..

I am now trying to figure out logic on how to programmatically set the component combobox to only approved values on the main form.

If there is a change in the approval status for the facility type ,item type or component type I want to accommodate that *filter* in the component combobox. At the moment all the records are approved because I do not know how to accommodate the *filter* logic for the combobox.

I am now considering some way of documenting the approval status change date (when a record in the facility type, item or component table become not-approved.). I could default the approval setting to approved for any future records that would be added to those tables.

This is just an idea for a way to have logic that works like the following

Logic Idea
If date of the data entry record entered is less than the (non) approval date then show non- approved values in combobox else only show approved values.

I know this logic will need some work but I need to think outloud so that is my first attempt at trying to wrap my head around this issue. Thanks for your feedback.
 

Attachments

It's up to you however which way you want to do it. You can have a IsLegacy Yes/No field to indicate which values are legacy. Or you could have a LegacyDate Date/Time field to indicate the same.
The former if it's a simple legacy and current value list, and the latter if you want to set dates from which a value should be regarded as legacy (you could even have LegacyFrom and LegacyTo Date/Time fields if you want a range).

If you want to keep things simple, go with IsLegacy Yes/No field and I'll show you two ways of doing it (as already discussed):
1. Using one combo box, set criteria in the query to filter accordingly, one line of code in the Current event to requery the combo box.
2. Using two combo boxes, one filtered to show all records and the other filtered to show non legacy. Show/Hide relevant combo box depending on current value and set Control Source accordingly.
 
vbaInet said:
It's up to you however which way you want to do it. You can have a IsLegacy Yes/No field to indicate which values are legacy. Or you could have a LegacyDate Date/Time field to indicate the same.
The former if it's a simple legacy and current value list, and the latter if you want to set dates from which a value should be regarded as legacy (you could even have LegacyFrom and LegacyTo Date/Time fields if you want a range).

If you want to keep things simple, go with IsLegacy Yes/No field and I'll show you two ways of doing it (as already discussed):
1. Using one combo box, set criteria in the query to filter accordingly, one line of code in the Current event to requery the combo box.
2. Using two combo boxes, one filtered to show all records and the other filtered to show non legacy. Show/Hide relevant combo box depending on current value and set Control Source accordingly.
Click to expand...


I see that IsLegacy yes/no will be simple. My question is how do I accomodate values that may become "Not Approved in the future" If I use the IsLegacy for legacy import I think I will still have issues with future changes to terminology disposition. Would you still recommend the IsLegacy for legacy values and deal with the other situation with a date field? Date of disposition change perhaps...
 
You must log in or register to reply here.

Similar threads

B
Combo Box Search Issue
2
Replies
34
Views
896
MajP
MajP
HavingDatabaseRelations
Solved Combo Box will only work if Access is not minimized
Replies
10
Views
236
Mike Krailo
Mike Krailo
Minty
SSMA - Issue with Boolean Access Fields
Replies
6
Views
371
isladogs
isladogs
R
Solved Need help with displaying ComboBox contents (hyperlink involved)
Replies
7
Views
364
theDBguy
theDBguy
R
Delete record in dependent cascading combo box
Replies
4
Views
156
RFreund
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom