Exposing table information for field list combobox

[JohnH]

Ok maybe someone can help me with this. I've designed a form that
acts as a wizard for report creation. One one of my wizard screens
I've implemented an Order By group of combo boxes almost identical to
the screen found in the actual Report Creation Wizard.

Each combobox's rowsourcetype is 'field list' and the rowsource is a
query that uses aliases for field names so that the combobox's
dropdown choices will make sense (Date Entered, Date Sold, Last Name,
First Name, etc).

The report creation happens in the Report_Open event where I apply a
custom filter (and now Order By) to the report's record source by
piecing together the user choices in my report creation wizard, which
are passed to this report in the OpenArgs.

My problem is resolving the user's choice, which is an alias, to the
field name so that I can generate the Order By string on the fly in
VBA. How do I do this?

Thanks in advance.
John

 

[Allen Browne]

Hmm. How did you get that alias, John?

If the alias is from a query, as in:
SELECT tblClient.Surname AS FamilyName FROM ...
you could get it like this:
Set rs = "SELECT * FROM Query1 WHERE (False);"
Debug.Print rs.Fields("FamilyName").SourceField

If the alias is the Caption property, you could get that from the Field of
the TableDef.

You may get some ideas from the GetFilterField() function on this page:
http://allenbrowne.com/AppFindAsUTypeCode.html

 

[JohnH]

Hmm. How did you get that alias, John?

If the alias is from a query, as in:
SELECT tblClient.Surname AS FamilyName FROM ...
you could get it like this:
Set rs = "SELECT * FROM Query1 WHERE (False);"
Debug.Print rs.Fields("FamilyName").SourceField

If the alias is the Caption property, you could get that from the Field of
the TableDef.

You may get some ideas from the GetFilterField() function on this page:
http://allenbrowne.com/AppFindAsUTypeCode.html

Allen,

Thanks for your reply. First of all, I am using aliases in my query,
specifically, using the AS keyword.
(And this is Access 2000 format btw)

Your example:

Set rs = "SELECT * FROM Query1 WHERE (False);"
Debug.Print rs.Fields("FamilyName").SourceField

First of all, would you mind explaining why this SQL works? I don't
understand "...WHERE (False)."
Secondly, I adapted your code snippet for testing. Here's what I've
got:

Dim rs As DAO.Recordset
Set rs = "SELECT * FROM qlCapReport WHERE (False);"
Debug.Print rs.Fields("Date Sold").SourceField

The second line is throwing a "Type Mismatch" error.

I'm also curious if there's a way to just set a recordset object equal
to the rowsource of the combobox. I was playing around with that and
wasn't able to figure it out.

Thanks for your patience. I really appreciate your website btw, which
I've visited many a time, and which inspired some of the code in this
current database.

John

 

[JohnH]

Allen,

Thanks for your reply. First of all, I am using aliases in my query,
specifically, using the AS keyword.
(And this is Access 2000 format btw)

Your example:

Set rs = "SELECT * FROM Query1 WHERE (False);"
Debug.Print rs.Fields("FamilyName").SourceField

First of all, would you mind explaining why this SQL works? I don't
understand "...WHERE (False)."
Secondly, I adapted your code snippet for testing. Here's what I've
got:

Dim rs As DAO.Recordset
Set rs = "SELECT * FROM qlCapReport WHERE (False);"
Debug.Print rs.Fields("Date Sold").SourceField

The second line is throwing a "Type Mismatch" error.

I'm also curious if there's a way to just set a recordset object equal
to the rowsource of the combobox. I was playing around with that and
wasn't able to figure it out.

Thanks for your patience. I really appreciate your website btw, which
I've visited many a time, and which inspired some of the code in this
current database.

John

Well thanks to your hint I figured out what I wanted to do, which of
course turned out to be ludicrously simple.


Dim db As DAO.Database
Dim qdef As DAO.QueryDef

Set db = CurrentDb
Set qdef = db.QueryDefs(cmbOrder1.RowSource)

Debug.Print qdef.Fields(cmbOrder1.ListIndex).SourceField


Your tabledef mention led me to learn about tabledefs and querydefs,
which are wonderful.

Thanks again.
John

 

[JohnH]

Allen,

Thanks for your reply. First of all, I am using aliases in my query,
specifically, using the AS keyword.
(And this is Access 2000 format btw)

Your example:

Set rs = "SELECT * FROM Query1 WHERE (False);"
Debug.Print rs.Fields("FamilyName").SourceField

First of all, would you mind explaining why this SQL works? I don't
understand "...WHERE (False)."
Secondly, I adapted your code snippet for testing. Here's what I've
got:

Dim rs As DAO.Recordset
Set rs = "SELECT * FROM qlCapReport WHERE (False);"
Debug.Print rs.Fields("Date Sold").SourceField

The second line is throwing a "Type Mismatch" error.

I'm also curious if there's a way to just set a recordset object equal
to the rowsource of the combobox. I was playing around with that and
wasn't able to figure it out.

Thanks for your patience. I really appreciate your website btw, which
I've visited many a time, and which inspired some of the code in this
current database.

John

Well thanks to your hint I figured out what I wanted to do, which of
course turned out to be ludicrously simple.


Dim db As DAO.Database
Dim qdef As DAO.QueryDef

Set db = CurrentDb
Set qdef = db.QueryDefs(cmbOrder1.RowSource)

Debug.Print qdef.Fields(cmbOrder1.ListIndex).SourceField


Your tabledef mention led me to learn about tabledefs and querydefs,
which are wonderful.

Thanks again.
John

 

[JohnH]

Allen,

Thanks for your reply. First of all, I am using aliases in my query,
specifically, using the AS keyword.
(And this is Access 2000 format btw)

Your example:

Set rs = "SELECT * FROM Query1 WHERE (False);"
Debug.Print rs.Fields("FamilyName").SourceField

First of all, would you mind explaining why this SQL works? I don't
understand "...WHERE (False)."
Secondly, I adapted your code snippet for testing. Here's what I've
got:

Dim rs As DAO.Recordset
Set rs = "SELECT * FROM qlCapReport WHERE (False);"
Debug.Print rs.Fields("Date Sold").SourceField

The second line is throwing a "Type Mismatch" error.

I'm also curious if there's a way to just set a recordset object equal
to the rowsource of the combobox. I was playing around with that and
wasn't able to figure it out.

Thanks for your patience. I really appreciate your website btw, which
I've visited many a time, and which inspired some of the code in this
current database.

John

Well thanks to your hint I figured out what I wanted to do, which of
course turned out to be ludicrously simple.


Dim db As DAO.Database
Dim qdef As DAO.QueryDef

Set db = CurrentDb
Set qdef = db.QueryDefs(cmbOrder1.RowSource)

Debug.Print qdef.Fields(cmbOrder1.ListIndex).SourceField


Your tabledef mention led me to learn about tabledefs and querydefs,
which are wonderful.

Thanks again.
John

 

[Allen Browne]

QueryDefs and TableDefs are very handy.
Glad you got something that worked.

If you want to explore a couple more examples, this one shows how to read
the field descriptions from a table, as well as their name, type, and size:
http://allenbrowne.com/func-06.html
And this is the tree for the Access Objects:
http://allenbrowne.com/ser-04.html

Re your question about the WHERE (False) clause, the idea was to get
information about the fields of the query without loading any records. The
WHERE clause in a query is an expression that ultimately evaluates to True
or not. If it's True for a record, the record is selected; otherwise the
record is rejected. The expression:
WHERE (False)
evaluates to False for all records, so no records are returned.
The trick has many applications, e.g. a search form that open with no
records.