Rename query name in form's combo box 1

[iuianj07]

Hello guys,

I have a frmDailyReport wherein I have a unbound combo box, and a subform names subFrmQueries... I actually found this form here in Tek-Tips (I guess from MajP..) and found it useful, what it does is in the combobox, it will list down all queries in the database and show the results into the subform.. I find it very useful in our databases as this is a great replacement for having a button on a form to run different kinds of queries for users....

Anyway, the Combobox's (cmboQueries) Row Source is:

SELECT DISTINCT MSysObjects.Name, MSysObjects.Type FROM MSysObjects WHERE (((MSysObjects.Name) Like "qryDailyReport*" Or (MSysObjects.Name)="qryInProcess" Or (MSysObjects.Name)="qryNeedToReview") AND ((MSysObjects.Type)=5));

and the After Update Property is:

Private Sub cmboQueries_AfterUpdate()
  Me.subFrmQueries.SourceObject = "Query." & Me.cmboQueries
  Me.subFrmQueries.Locked = True
End Sub

This form works great... the only problem I have though is that the name of the queries that shows up in the combobox are names that we use for our standard naming conventions of query names.. (having "qry" at the start of the name)... Is there a way though to rename the Query name in the combobox? So for example qryDailyReport will show as "Daily Report", qryInProcess will show as "In Process" etc...? some of the query names have only short names which only me (who created the queries/db) could only understand what it's for and if I put that in the combo box, the users will have a hard time what the query is for... that's why I was hoping there is a way to rename it just in the combobox?

I hope I was able to explain correctly... and please let me know if you have any questions...

I appreciate any help or guidance...

Thank you,

 

[PHV]

Why not create a table of queries with name,description fields ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[iuianj07]

Hello PHV,

Thanks for the prompt response... I created a table named tblQueryNames:

tblQueryNames:

QryNameID -- PK
qryName -- Text -- the system name
qryDescription -- Text -- the friendly name

I am not sure on how to populate this into the combobox or include this into the SQL Row Source of the combobox that would relate qryInProcess and "In Process" from qryDescription field?

I tried:

SELECT DISTINCT tblQueryNames.qryDescription
FROM MSysObjects INNER JOIN tblQueryNames ON MSysObjects.Name = tblQueryNames.qryName
WHERE (((MSysObjects.Type)=5) AND ((MSysObjects.Name) Like "qryDailyReport*" Or (MSysObjects.Name)="qryInProcess" Or (MSysObjects.Name)="qryNeedToReview"));

and it gives a Microsoft Visual Basic Run time error saying:

The Microsoft Office Access Database engine could not fine the object 'Deals In Process'. Make sure the object exists and that you spell the name and the path name correctly.

Can you please help me with the SQL for this?

Thank you...

 

[iuianj07]

Hi,,

Okay I tried to change the SQL to:

SELECT DISTINCT QN.qryDescription, MSysObjects.Name, MSysObjects.Type 
FROM tblQueryNames AS QN 
  INNER JOIN MSysObjects 
  ON QN.qryName = MSysObjects.Name
WHERE (   (MSysObjects.Name) Like "qryDailyReport*")
       Or (MSysObjects.Name="qryInProcess") 
       Or (MSysObjects.Name="qryNeedToReview") 
  AND (MSysObjects.Type=5);

and it is now giving the friendly names in the combo box.

however, it is still giving me the VBA error message whenever I choose a query friendly name in the combobox...

is there any way you could help me with this?

Thank you very much...

 

[PHV]

Why not simply use this as RowSource ?
SELECT qryName, qryDescription FROM tblQueryNames

set the 1st column as the bound column and set its with to 0.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[iuianj07]

oh, I didn't know it should only be that simple... as always I try to over complicate things..

Thank you very much PHV, it looks like it's working now.

Thanks