Combo Box Row Source: Lookup Tables vs. Record Source Query

[willknapp]

I'm working on a form where I have a couple combo boxes. In the past, I'd create/link a Lookup table - in this case, I have about 30 values from which the user can choose, but I also want to allow users to enter a custom value if the one they want isn't on the list.

But I'm now toying with the idea of creating a Group By Query based on the record source table and using that as the Row Source. This query will give the user the option of selecting any of the existing values or, since "Limit To List" is false on both the table and the form control, type in a new one that will automatically show up subsequently due to the nature of the query.

It seems to work pretty well, and as of yet, I haven't really come up with any downside beyond the possiblity of the list getting so big it will cause a problem - but that's not a likely scenario here.

Does anyone have experience with using this method? Any pitfalls I should look out for?

 

[GinaWhipp]

I personally prefer to use the NotInList event that allows the use to add values to my Look-Up tables. Have a look at...

http://www.databasedev.co.uk/not_in_list.html

 

[willknapp]

I personally prefer to use the NotInList event that allows the use to add values to my Look-Up tables.

Thanks, Gina - that's an excellent suggestion. I've never really used that before, and I definitely will in the future. I like that there's a meaningful message box to let the user know it's been added and it's a neat way to save some programming, depending on the situation.

That said, I'm not sure it will work for this issue: I only want the list to be populated with values that already exist in the table. For example, one of these boxes represents the Vendor field in the list, of which there about 30 to choose from.

Let's say someone adds a record with a new vendor - using the NotInList event, I would update the RowSource property of the combo box to add the new value. But if that record ever gets deleted, the value will still be available, and I don't want it to be. I could write an AfterDelConfirm sub-procedure to weed it out, but I'd basically have to rebuild, in this case, four separate combo boxes prgramatically - that's a lot of code, when I can basically cover this by simply requerying the combo boxes using the AfterUpdate and AfterDelConfirm events.

Basically, I just posted this to see if anyone had any philisophical issues with the method - it works great, but seems like kind of a shortcut, and I want to make sure that anyone maintaining this application after I'm through will easily be able to follow my methodology.

 

[Simon_MT]

I would not Add Vendors in a combibox, in fact, I still think that using combiboxes to add records is wrong, To me, combiboxes are aide-mémoirs helping users to input the information correctly.

Users need to get into the habit of creating (Vendor) information properly before that information is available. There maybe a lot more information to be input for each Vendor as the purpose of a Lookup is brevity enough information to identify the vendor correctly not chapter and verse.

Simon

 

[GinaWhipp]

No you would not have to rebuild if a Record goes awy. There are several ways to handle this but my preferred method is to have a check box in the Vendor Profile. The check box remains unchecked as long as the Vendor is Active. Once the become Inactive, the check box is checked and now you can use that to filter your lists. Now, your Combo Boxes will only show Active Vendors.

I prefer NOT to delete a Vendor as they could be used more than once. And since I don't allow deletion of anything it also helps when looking at historical data to know who that Vendor was.

@Simon... While this is true, I'd rather have Users enter the Vendor Name and after they have finished thier data entry go and enter the balance of the information. This way they don't have to go back and forth while trying to get a record entered.

 

[willknapp]

If this system was used to store actual vendor data (contact info, history, etc.) then I would do that - I like the "active" check box idea - very clever.

However, in this instance, the only info we're storing is, in fact, the vendor's name. There's a much larger database doing the heavy lifting, and the app I'm creating is little more than a read-only reference to the bulk of the production team. (At most, only 2 people will have write privileges.) Since there is no additional data to store, I figured I'd just use a value list, rather than a linked table with an arbitrary PK.

Further, at this time, the Vendor's Name isn't referenced anywhere else in the application, so there's really no need for separate storage.

 

[GinaWhipp]

Then use Cascading Deletes when you set up the Relationship and it will automatically take awy the Vendor. However, you may still want to be careful that Vendor is not being used anywhere with a little code to make sure that is the only instance.