Programatically override displaycontrol?

[EJ Williams]

Does anyone know of a way to programmatically override the displaycontrol
property? This is my situation, I am dynamically creating a querydef that is
displayed in a subform control. The querydef is based on user selections and
is working fine. I have a button on the main form that allows the data to be
outputto Excel. I would like for the exported data to contain the
coded-domain values rather than their descriptions from the second column of
the combo-box. The combo-boxes are being set within the field displaycontrol
property of the table.

Anyone know any way around this. It is great for display purposes but I need
the underlying field values for my exports.

TIA,

Eric

 

[strive4peace]

Hi Eric,

I do not use lookup fields in table design

The Evils of Lookup Fields in Tables
http://www.mvps.org/access/lookupfields.htm

~~~

.... but I imagine you can do something like this:

Fieldname_: CLng([Fieldname])

NOTE:
The field alias ends in underscore so it is different than the actual
fieldname
I used cLng to convert to long integer -- use whatever function is
appropriate for your data type


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[EJ Williams]

Crystal,

Thanks for the suggestion but I found another solution. I also appreciate
the feedback regarding "The Evils of Lookup Fields in Tables". I never use
"Lookup Wizard" as the datatype but do set the displaycontrol property to
combobox with the appropriate SQL to populate it. I find that this simplifies
the process for both the programmer (me) and enduser and thereby encourages
good relational design. What are the alternatives? Hardcoding value lists is
out of the question or adding the queries manually for each field that should
be constrained by a related table? No thanks.

Here is the workaround I came up with for this particular occassion. This
can also be perfomed manually for a query.

Since I am dynamically creating a querydef for subsequent display in a
subform control I iterate through all fields using a For.. Each loop setting
the displaycontrol property to acTextbox where the property is present. The
code except looks like this:

'Create querydef (excluded)
For Each fldTemp In qdfTemp
'Check for existence of DisplayControl property (excluded from sample)
fldTemp.Properties("DisplayControl") = acTextbox '109
Next fldTemp
'Append querydef (excluded)

Hope this benefits someone else.

Eric

Hi Eric,

I do not use lookup fields in table design

The Evils of Lookup Fields in Tables
http://www.mvps.org/access/lookupfields.htm

~~~

.... but I imagine you can do something like this:

Fieldname_: CLng([Fieldname])

NOTE:
The field alias ends in underscore so it is different than the actual
fieldname
I used cLng to convert to long integer -- use whatever function is
appropriate for your data type


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Does anyone know of a way to programmatically override the displaycontrol
property? This is my situation, I am dynamically creating a querydef that is
displayed in a subform control. The querydef is based on user selections and
is working fine. I have a button on the main form that allows the data to be
outputto Excel. I would like for the exported data to contain the
coded-domain values rather than their descriptions from the second column of
the combo-box. The combo-boxes are being set within the field displaycontrol
property of the table.

Anyone know any way around this. It is great for display purposes but I need
the underlying field values for my exports.

TIA,

Eric

 

[John W. Vinson]

Thanks for the suggestion but I found another solution. I also appreciate
the feedback regarding "The Evils of Lookup Fields in Tables". I never use
"Lookup Wizard" as the datatype but do set the displaycontrol property to
combobox with the appropriate SQL to populate it. I find that this simplifies
the process for both the programmer (me) and enduser and thereby encourages
good relational design. What are the alternatives?

Using a Form (with combo boxes or other tools) rather than trying to use table
datasheets in a way that they cannot support (or only very crudely).

Users should, as a rule, never even SEE datasheets, much less enter data into
them.

 

[strive4peace]

Hi Eric,

"I never use "Lookup Wizard" as the datatype "

a lookup in a table design means that you are using a combo or listbox
as the Displaycontrol -- however it is done, with wizard or doing it
manually. This can create problems that are VERY difficult to find. If
you just use lookups in the table design until your forms and reports
are done, then put them back to textboxes, that is better than leaving
them. Many of us feel it is best not to use lookups in the table design
to begin with ... but there are those who set them temporarily to aid
form and report development.

As John says in the next message, users should never enter data into
tables directly ... you should always use forms

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Crystal,

Thanks for the suggestion but I found another solution. I also appreciate
the feedback regarding "The Evils of Lookup Fields in Tables". I never use
"Lookup Wizard" as the datatype but do set the displaycontrol property to
combobox with the appropriate SQL to populate it. I find that this simplifies
the process for both the programmer (me) and enduser and thereby encourages
good relational design. What are the alternatives? Hardcoding value lists is
out of the question or adding the queries manually for each field that should
be constrained by a related table? No thanks.

Here is the workaround I came up with for this particular occassion. This
can also be perfomed manually for a query.

Since I am dynamically creating a querydef for subsequent display in a
subform control I iterate through all fields using a For.. Each loop setting
the displaycontrol property to acTextbox where the property is present. The
code except looks like this:

'Create querydef (excluded)
For Each fldTemp In qdfTemp
'Check for existence of DisplayControl property (excluded from sample)
fldTemp.Properties("DisplayControl") = acTextbox '109
Next fldTemp
'Append querydef (excluded)

Hope this benefits someone else.

Eric

Hi Eric,

I do not use lookup fields in table design

The Evils of Lookup Fields in Tables
http://www.mvps.org/access/lookupfields.htm

~~~

.... but I imagine you can do something like this:

Fieldname_: CLng([Fieldname])

NOTE:
The field alias ends in underscore so it is different than the actual
fieldname
I used cLng to convert to long integer -- use whatever function is
appropriate for your data type


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Does anyone know of a way to programmatically override the displaycontrol
property? This is my situation, I am dynamically creating a querydef that is
displayed in a subform control. The querydef is based on user selections and
is working fine. I have a button on the main form that allows the data to be
outputto Excel. I would like for the exported data to contain the
coded-domain values rather than their descriptions from the second column of
the combo-box. The combo-boxes are being set within the field displaycontrol
property of the table.

Anyone know any way around this. It is great for display purposes but I need
the underlying field values for my exports.

TIA,

Eric

 

[EJ Williams]

I can appreciate what you are both saying but this has nothing to do with
entering or accessing tables directly. It has everything to do with accurate,
efficient, consistent design. What Crystal says about removing the
DisplayControl property upon completion of the database design makes sense
and has its place but in reality most Access databases are never "complete".
Access has its place as the user-friendly office database application. Users
will forever want modifications and enhancements but as long as the interface
is well designed they will never see the tables directly anyway. Using
properties such as DisplayControl appropriately will streamline additional
development.

Just my two cents.

Eric Williams

Hi Eric,

"I never use "Lookup Wizard" as the datatype "

a lookup in a table design means that you are using a combo or listbox
as the Displaycontrol -- however it is done, with wizard or doing it
manually. This can create problems that are VERY difficult to find. If
you just use lookups in the table design until your forms and reports
are done, then put them back to textboxes, that is better than leaving
them. Many of us feel it is best not to use lookups in the table design
to begin with ... but there are those who set them temporarily to aid
form and report development.

As John says in the next message, users should never enter data into
tables directly ... you should always use forms

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Crystal,

Thanks for the suggestion but I found another solution. I also appreciate
the feedback regarding "The Evils of Lookup Fields in Tables". I never use
"Lookup Wizard" as the datatype but do set the displaycontrol property to
combobox with the appropriate SQL to populate it. I find that this simplifies
the process for both the programmer (me) and enduser and thereby encourages
good relational design. What are the alternatives? Hardcoding value lists is
out of the question or adding the queries manually for each field that should
be constrained by a related table? No thanks.

Here is the workaround I came up with for this particular occassion. This
can also be perfomed manually for a query.

Since I am dynamically creating a querydef for subsequent display in a
subform control I iterate through all fields using a For.. Each loop setting
the displaycontrol property to acTextbox where the property is present. The
code except looks like this:

'Create querydef (excluded)
For Each fldTemp In qdfTemp
'Check for existence of DisplayControl property (excluded from sample)
fldTemp.Properties("DisplayControl") = acTextbox '109
Next fldTemp
'Append querydef (excluded)

Hope this benefits someone else.

Eric

Hi Eric,

I do not use lookup fields in table design

The Evils of Lookup Fields in Tables
http://www.mvps.org/access/lookupfields.htm

~~~

.... but I imagine you can do something like this:

Fieldname_: CLng([Fieldname])

NOTE:
The field alias ends in underscore so it is different than the actual
fieldname
I used cLng to convert to long integer -- use whatever function is
appropriate for your data type


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




EJ Williams wrote:
Does anyone know of a way to programmatically override the displaycontrol
property? This is my situation, I am dynamically creating a querydef that is
displayed in a subform control. The querydef is based on user selections and
is working fine. I have a button on the main form that allows the data to be
outputto Excel. I would like for the exported data to contain the
coded-domain values rather than their descriptions from the second column of
the combo-box. The combo-boxes are being set within the field displaycontrol
property of the table.

Anyone know any way around this. It is great for display purposes but I need
the underlying field values for my exports.

TIA,

Eric

 

[strive4peace]

with all due respect, Eric, even if the users do not enter data in the
table directly, that is not the main reason we tell you not to use/leave
the lookups there -- they cause problems! Some of these errors can be
very difficult to chase down. It is 'best practice' not to use them at
all ... why spend a couple days chasing a ghost only to find out it is
because you have a lookup defined in your table? Let us save you some
headaches...

Make queries to show your data while you are developing -- then, as your
forms are done, get rid of the queries that the system does not need
(unless they are still useful). Use the query Description (database
window) to note what system object uses a query so you can delete
temporary queries when the development is done -- or, each time it is
rolled out, since development is never done


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*