Filtering a combo box

[RenaG]

I apologize if this was dealt with in a previous post but I couldn't find it if it was.

I am working in Access 2007.

I have a form with a combo box that pulls data from a look-up table:

luID     Desc           Group
1        Newsletter     Format
2        Outreach       Format
3        Screening      Format
4        Education      Format

The RowSource selects records based on the value in the Group field (in this case Format).

SELECT lookupTable.luID, lookupTable.Desc, lookupTable.Group
FROM lookupTable
WHERE (((lookupTable.Group)="Format"))
ORDER BY lookupTable.Desc;

The users want to "retire" some of the values from this group. I can't just delete them so I thought I would change the Desc to begin with XX - for example: XXScreening. I would like to filter out all the records that begin with XX so that they don't display in the combobox. I couldn't find a "Starts With" type option for the query and not sure how to write VBA to handle this. Do you have any suggestions?

TIA,
~RLG

 

[pbaldy]

I'd probably add a status field, but you could use the Left() function to test whether the first 2 letters were "XX".

 

[RenaG]

Thanks, pbaldy!

I like the idea of adding a status field. Is that hard to do once the db is in production? Can I just add it without breaking anything?

TIA!
~RLG

 

[pbaldy]

It shouldn't be hard, if you've split the application. With an Access back end, you'd probably have to get everybody out so that you could add the field. In the front end, relink that table, make the design changes to use the new field, and distribute the new front end.

 

[RenaG]

Hi pbaldy,

I added the field Status to the lookup table. On the subform, in the combo box Row Source, I modified to SQL to:

SELECT lookupTable.luID, lookupTable.Desc, lookupTable.Group, lookupTable.Status
FROM lookupTable
WHERE (((lookupTable.Group)="Format") AND ((lookupTable.Status)<>"X"))
ORDER BY lookupTable.Desc;

When I test it, the combo box comes up empty. If I change it to:

SELECT lookupTable.luID, lookupTable.Desc, lookupTable.Group, lookupTable.Status
FROM lookupTable
WHERE (((lookupTable.Group)="Format") AND ((lookupTable.Status)="X"))
ORDER BY lookupTable.Desc;

Then sure enough, all I get are the records marked with an X. I can't understand why nothing shows up when I set it to <> "X". I am sure it is something that should be obvious to me but I can't see it.

TIA,
~RLG

 

[pbaldy]

What's there if there's no "X"? If you're leaving it Null (empty), try

WHERE lookupTable.Group="Format" AND (lookupTable.Status<>"X" Or lookupTable.Status Is Null)

 

[RenaG]

Hi Paul,

Here is what I tried (with success) before I got your suggestion:

WHERE (((lookupTable.Group)="Handout") AND ((lookupTable.Status) Is Null Or (lookupTable.Status)=" "))

I would say "Great minds think a like" except mine's not so great

Thanks for you time!

~RLG

 

[pbaldy]

Happy to help!

My daughter will be moving to your area later this year (she's in the Air Force, transferring to Mountain Home AFB). Any thoughts on pros/cons of living in Mountain Home versus up in the Boise area? They'd have a longer commute obviously, but are there advantages that would offset that?

 

[RenaG]

It depends on what her interests are. There isn't much in the Mountain Home area but if she is a homebody and not into shopping, museums, plays and that sort of thing then living in Mountain Home will probably work for her. Otherwise she might want to look at living in east Boise (which will put her closer to Mountain Home). The only time I have been to the base in Mountain Home is to see the air show. I don't head that way very often - it's pretty desert-like. Can she rent a place on the base for a while to give her time to look around and see what works best for her?

~RLG

 

[pbaldy]

Thanks, I've forwarded that to her. She and her husband hope to manage a visit before they get transferred, but they're currently in England so it may not happen. If they can't, they'll probably do exactly that. The good news for me is that they'll finally be within driving/short flight distance! Maybe I'll see you at the next air show.

 

[RenaG]

You'll definitely have to let me know when you are in the area!

I wish her well in her huge move! Wow, I can't imagine moving from England to Mountain Home! What a big undertaking and a huge change in climate and culture

I see that you are in NV. I was just there the last week in Feb - Henderson, NV. Yes, she will be much closer to you!

 

[pbaldy]

She's looking forward to seeing the sun again!

I'm looking forward to going up there. Idaho is one of the few states I haven't visited.

 

[RenaG]

Well, there is lots of sunshine here! And parts of it are really beautiful. Plan to spend some time touring the state when you come.

 

[RenaG]

Hi Paul,

I got the changes put into place to filter out inactive records. The only problem is that the combo box doesn't show those values for existing records. In other words, if there is a record already in the db that has one of the inactive values, when we look at that record, it shows up blank. How do I get the form to display all the records that exist but when the drop down is clicked to only show the active records. I hope that made sense. I would insert a screen shot to help clarify but I don't know how to do that.

TIA,
~RLG

 

[RenaG]

Hi all,

Still looking for some assistance on the problem explained above. Does anybody have any suggestions on how to get around this?

TIA!
~RLG

 

[RenaG]

Hi Pat,

This works great with one exception. When I select an invalid option, it displays the message like it should but when I click ok, another message window come up:

The value violates the validation rule for the field or record.
For example, you might have changes a validation rule without verifying whether the existing data matches the new validation rule.
Click Undo to restore the previous value, or enter a new value that meets the validation rule for the field or record.

I do have an Enforce Referential Integrity between the lookupTable (where the records with the status field are stored) and the tblEventTracking (where the ID of the lookupTable is stored for the event - I hope that makes sense). But I don't know why that would cause a problem in this situation. Do you have any suggestion?

Thanks,
~RLG

 

[RenaG]

Hi Pat,

Yes, I entered the commands just like you sent them making only the necessary changes to fit my program:

Private Sub tdescription_BeforeUpdate(Cancel As Integer)
    If Me.tdescription.Column(3) = "X" Then
        MsgBox "This an inactive option. Please select an active one.", vbOKOnly
        Cancel = True
        Me.tdescription.Undo
        Exit Sub
    End If
End Sub

Did I miss something?

~RLG

 

[RenaG]

I have the Enforce Referential Integrity between the lookupTable and the tblEventTracking table. I wonder, is the undo trying to remove the record from the lookupTable (the source of the combobox)? I commended out the undo statement and it seems to work fine. The focus is on the field with the invalid entry and it won't let me go by it as long as the value is an invalid one. Unless you see something wrong with removing the undo statement, it looks like this is the way to go.

Thanks for your help. I never would have figured this out on my own!

~RLG

 

[RenaG]

Hi Pat,

The Undo should replace the newly entered value of the control with the .OldValue which is the value that was there before you started.

I think that's the problem. The .OldValue is nothing (null?). I am not trying to change an existing entry but add a new one. I have attached an image of the form to this post. The third one down is blank and when I click the down arrow and select an invalid option it displays the message stating that it is an invalid option. Without the undo it will leave that invalid option in the field but won't let me go on until I select a valid one. I just tested what happens if I change an existing entry to an invalid option (putting back in the undo command) and when I click ok on the "invalid entry" message it does replace the new value with the oldvalue.

I am new enough to Access to not know exactly where to look for the validation rules. I don't recall setting any specific rules on the table (when I look at it in design mode) and I don't know how to set rules on a form. The only rule I have is the Enforce Referential Integrity between the lookupTable and the tblEventTracking which won't allow me to delete an entry in the lookupTable if it is being used in the tblEventTracking. If you can suggest where to look I might be able to figure out what is going on.

Thanks!
~RLG

 

[RenaG]

Hi Pat,

Thanks for sticking it out with this thing. I changed the Cycle and it didn't make any difference. An interesting note, when I get the validation error, it says to "Click Undo to restore the previous value". When I click Ok and then click the Undo on the Access window, it does clear that field. You'd think the undo in the VB would do the same thing.

~RLG