Strip first 4 characters from names in combo box

[sheusz]

Hello Inmates



I am not sure if what I want to achieve is actually possible, but here goes.

I have a form that contains a number of combo boxes. Each of the combo boxes lists all the reports in my DB whose report name starts with a particular prefix.

I have given the reports a specific prefix based on the type of report. All the report prefixes are the same length (4 characters). In the example below the reports displayed in this combo box are Customer related.

This system works really well from an end user point of view, however I would like to tidy up the listing in the combo box by not having the prefix (ie first 4 characters) of the report name displayed, so the report cus_COD Customers would be shown in the combo box as COD Customers

The code is on the Row Source control of the combo box, and the Row Source Type is defined as Table/Query.

Is this even possible?

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") And (Left$([Name],4)="cus_") And (MSysObjects.Type)=-32764 ORDER BY MSysObjects.Name;

Thank you in advance for your suggestions.

[moke123]

Why not just iterate through the reports collection and include a column using the Mid() on the name property?
I usually try to avoid using system tables.

Using a value list combo use the .additem method (2 columns with 0;1 widths)
Something like

    Dim rpt As Variant
    For Each rpt In CurrentProject.AllReports
        If Left(rpt.Name, 4) = "cus_" Then
            Debug.Print Mid(rpt.Name, 5)
            'YourComboName.AddItem rpt.name & ";" & Mid(rpt.Name, 5) 
        End If
    Next

[sheusz]

Thanks Moke123 for the reply. This seems like a good solution.

I've added your code to the OnOpen Event of the form. Is that correct?

Forgive my ignorance. How do I set the combo box row property to a value list in this case?

Never mind. I had a brain f**t. I'll play around with this and advise.

[moke123]

heres an example

rpts.zip

[sheusz]

Hi Moke123

Thank you very much for the sample. I did achieve the result after your initial post once I put my brain in to gear

BTW, I've been trying to alphabetically order the combo box. Any ideas on how to do this?

[moke123]

you could try an array, then sort it, then add it to the combo. I dont have a sample sort procedure however.

[sheusz]

Thanks. As always 3 steps forward, 1 step back.

I like your overall solution over my original approach. I'll have to research the sort order issue. Never ventured in to arrays before, so time to roll up the sleeves.

Thanks again for your help.

[CJ_London]

if you keep to your sql solution change

SELECT MSysObjects.Name

to

SELECT mid(MSysObjects.Name,5) as RName....

you can then order by it as well

[sheusz]

Hi Ajax

Thanks for the input. I'm not sure what you mean by RName.. however I have changed my original code to;

SELECT mid(MSysObjects.Name,5) FROM MsysObjects WHERE (Left$([Name],1)<>"~") And (Left$([Name],4)="cus_") And (MSysObjects.Type)=-32764 ORDER BY MSysObjects.Name;

and this has given me the sorted list I'm after, which is great, but now I am a bit torn.

Of the two versions (mine and moke123's) which is the a more preferred option in terms of more robust application? Your suggestion gives me the correct sort order, but I'm not experienced enough to further develop moke123's suggestion.

[CJ_London]

RName is an alias - Name is a reserved word and should not be used as a field name - MS do defy the rules however so you just have to live with it with regards their tables. Since this is for a rowsource you don't need an alias unless you are showing headers. run it as an ordinary query, you will see what I mean. It's just a good habit to get into

Also with this part of your where clause

WHERE (Left$([Name],1)<>"~") And (Left$([Name],4)="cus_")

you only need this bit (Left$([Name],4)="cus_") because any other name will be excluded

Which is more robust? probably the same. Go with the one you are most comfortable with.

[sheusz]

Hi Ajax

Thanks for the explanation. Now I understand

I really appreciate both your and Moke123's input and solutions. The project is working as I would like it and looks "polished" for the end user.

Once again the generosity of members of this forum seems to know no bounds.