Microsoft Access 2003 VBA code to add lookup display control as combo box and define its rows source, columns, etc.

May 17, 2008 10:53am pdt

1. LSMConsu... 293,052
2. capricorn1 287,153
3. DatabaseMX 181,792
4. harfang 175,200
5. boag2000 160,625
6. TextReport 128,946
7. peter57r 128,281
8. jimhorn 79,799
9. matthews... 71,730
10. ee_rlee 59,772
11. MikeToole 50,825
12. jefftwilley 49,200
13. JimFive 46,607
14. omgang 44,035
15. angelIII 42,620

1. capricorn1 906,598
2. LSMConsu... 525,171
3. DatabaseMX 484,517
4. peter57r 365,790
5. harfang 356,748
6. jefftwilley 345,415
7. jimhorn 274,789
8. mbizup 229,700
9. stevbe 206,414
10. matthews... 202,619
11. boag2000 200,537
12. frankytee 169,793
13. MikeToole 155,875
14. rockiroads 147,183
15. TextReport 129,446

03.06.2008 at 03:27PM PST, ID: 23221513

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

9.4

Microsoft Access 2003 VBA code to add lookup display control as combo box and define its rows source, columns, etc.

Zone: Access Coding/Macros

In Access 2003, I have a field called DeptCode already created and want the display control be converted from Text Box to Combo box. Row Source to be: SELECT PROJ_DPT.Dept_Code, PROJ_DPT.Dept_Name FROM PROJ_DPT

Column Count to be 2 - Column Widths to be 0.5";1"
and list width to be 4"

And I want to be able to do this using VBA code instead of manually doing it.
Thanks in advance!

Stephen

Start your free trial to view this solution

Question Stats

Zone: Microsoft

Question Asked By: stephenlecomptejr

Solution Provided By: harfang

Participating Experts: 2

Solution Grade: A

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
New Net Users
New Users

Software
Digital Music
Gaming World

Home Security
Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Handhelds / PDAs
Displays / Monitors
Components
Networking Hardware

Peripherals
Laptops/Notebooks
Storage
Servers

Desktops
New Users
Misc
Apple

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMWare
Misc
Web Development

OS
CYGWIN
Voice Recognition
Message Queue
Quality Assurance
Security
Firewalls
MultiMedia Applications

Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals

Devices
Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
WebApplications
IDS
Vulnerabilities
Email Clients

File Sharing
Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMWare

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel

Algorithms
Game
Signal Processing
Project Management
Open Source

Database
Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Images

Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration
WebApplications

Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Broadband

Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Community Advisor
Lounge
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles

Community Support

Suggestions
New to EE
New Topics
Community Advisor

CleanUp
Announcements
General

Feedback
Input
EE Bugs

03.06.2008 at 06:26PM PST, ID: 21066779

oldmanbim:

Hi Stephen,

Normally speaking, modifying form design on the fly is not really ideal.

That being said, you can't change a control type unless the form is opened in design mode. This means that the form that contains your textbox (I'll call it ctrlDeptCode) that you want to change to a combobox can't actually do the changing. I'll call the form that contains ctrlDeptCode "frmTarget".

You'll want to make another form (I'll call it "frmEditTarget") that will make the modifications you want and then close. Form frmEditTarget would have only one method:

Private Sub Form_Open(Cancel As Integer)
Dim ctrl As Control

' Open the form you want to edit
DoCmd.OpenForm "frmTarget", acDesign, , , , acHidden

' Access the control to be changed
Set ctrl = Forms!frmTarget.Form.ctrlDeptCode

ctrl.ControlType = acComboBox

' After the control type is changed, it seems one has
' to save/close the form before modifying an combobox-specific
' fields, such as RowSource

' Save/Close the form
DoCmd.Close acForm, "frmTarget", acSaveYes

' Open the form again
DoCmd.OpenForm "frmTarget", acDesign, , , , acHidden
Set ctrl = Forms!frmTarget.Form.ctrlDeptCode

ctrl.RowSource = "SELECT PROJ_DPT.Dept_Code, PROJ_DPT.Dept_Name FROM PROJ_DPT"
ctrl.ColumnCount = 2
'--> Put any other code to modify ctrlDeptCode here

' Save Changes
DoCmd.Close acForm, "frmTarget", acSaveYes

' Open form with edited control
DoCmd.OpenForm "frmTarget"

' Set Cancel = true, so that this form doesn't actually open
Cancel = True

End Sub

This may not be the most efficient code, but I think it will do what you want.

03.06.2008 at 07:35PM PST, ID: 21067145

stephenlecomptejr:

I apologize that I didn't specify that all I'm doing is modifying a table only - not a form.

03.07.2008 at 12:25AM PST, ID: 21068259

Rank: Wizard

harfang:

All you need is to manipulate various DAO properties of your field. For convenience, I added two functions inspired by the examples you can find on the help page for the Properties collection.

Cheers!
(°v°)

03.07.2008 at 12:26AM PST, ID: 21068269

Rank: Wizard

harfang:

Here is the lost snippet (first time this happened!)
(°v°)

                       
                          
                             
                                1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:

                             
                             
                                Function ObjectProperty(objDao As Object, strName As String) As Variant
' Returns an object property, or null if non-existant
 
On Error Resume Next
    ObjectProperty = Null
    ObjectProperty = objDao.Properties(strName).Value
    
End Function
 
Function SetProperty( _
    objDao As Object, _
    strName As String, _
    varValue As Variant, _
    Optional pintType As DAO.DataTypeEnum = dbText _
    ) As Boolean
    
' Sets or creates an object property
' Returns true if successful
 
    Dim prp As DAO.Property
    
On Error Resume Next
 
    If Nz(varValue) = "" Then
        Set prp = objDao.Properties(strName)
        If Err Then
            If Err = 3270 Then
                SetProperty = True
                Err.Clear
            End If
        Else
            objDao.Properties.Delete strName
            If Err.Number = 3384 Then ' Built-in Property
                Err.Clear
                objDao.Properties(strName) = ""
            End If
            SetProperty = (Err.Number = 0)
        End If
    Else
        If ObjectProperty(objDao, strName) = varValue Then
            SetProperty = True
        Else
            objDao.Properties(strName).Value = varValue
            If Err.Number = 3270 Then ' property not found
                Err.Clear
                Set prp = objDao.CreateProperty(strName, pintType, (varValue))
                objDao.Properties.Append prp
            End If
            SetProperty = (Err.Number = 0)
        End If
    End If
    If Err Then
        MsgBox Err.Description, vbCritical, "Set Property: " & Err.Number
        Err.Clear
    End If
    
End Function
 
Sub MakeCombo()
 
' sample according to your specifications
 
    Dim fld As DAO.Field
 
    Set fld = DBEngine(0)(0)("Table1")("DeptCode")
    SetProperty fld, "DisplayControl", acComboBox, dbInteger
    SetProperty fld, "RowSource", "SELECT PROJ_DPT.Dept_Code, PROJ_DPT.Dept_Name FROM PROJ_DPT", dbMemo
    SetProperty fld, "ColumnCount", 2, dbInteger
    SetProperty fld, "ColumnWidths", "0.5"";1""", dbText
    SetProperty fld, "ListWidth", "4""", dbText
 
End Sub

                             
                          

                    

Open in New Window

Accepted Solution

03.07.2008 at 12:58AM PST, ID: 21068369

oldmanbim:

No, my apologies, one (meaning me) shouldn't answer questions on too little sleep and too much caffeine.

Harfang has a very nice solution and deserves full marks.

In the "for what it's worth department", when I tried his code, the line
SetProperty fld, "ColumnWidths", "0.5"";1""", dbText
did not raise an error, but Access 2003 did not interpret the property correctly when I opened the table. It should have, Harfang's code is correct. This might be a peculiarity of my installation, but if you run into the same problem, the line below also works
SetProperty fld, "ColumnWidths", 0.5 * 1440 & ";" & 1440, dbText

03.07.2008 at 01:17AM PST, ID: 21068454

Rank: Wizard

harfang:

Yes, this is strange. It might have something to do with regional settings. Anyway, your solution is probably more portable. When creating the same settings through the interface (in table design view), and looking at them with ObjectProperty(), I find:

ColumnWidths: 720;1440 -- which is 0.5";1" but in twips
ListWidth: 5760twip -- which is 4"

From the interface, I can even use "cm" as unit for both properties; it works only for ListWidth from VB (e.g. "3cm"). Whatever the unit used, it's converted like above.

So, twips seem to be the best choice. Thanks for the testing!

(°v°)

stephenlecomptejr

03.20.2008 at 12:57PM PDT, ID: 21175211

Yea - using Harfang's code I got this exact error:

The ColumnWidths property setting must be a value from 0 through 22 inches (55.87 cm) for each column in a list box or a combo box.

If there is more than one column, separate the numbers with either a semicolon or the list separator character. List separator characters are defined in the Regional Settings section of Windows Control Panel.

stephenlecomptejr

03.20.2008 at 12:59PM PDT, ID: 21175226

Harfang - if you don't mind I would like to split these points between the both of you?

oldmanbim

03.20.2008 at 04:47PM PDT, ID: 21176992

Harfang deserves all the points.

harfang

03.21.2008 at 04:44AM PDT, ID: 21179202

Thanks (to both of you), and success with your project!
(°v°)

stephenlecomptejr

03.21.2008 at 06:51AM PDT, ID: 21179845

harfang, is there anyway I could provide some contact information to you personally.
I really need your advice and was wondering if you could at least send me an email.

How best could I do so without posting my email address here?

harfang

03.21.2008 at 10:42AM PDT, ID: 21181619

There is an e-mail address on my profile page. Feel free to contact me for any EE related matter, but I will not answer questions you could have asked here.
(°v°)

20080236-EE-VQP-29 / EE_QW_2_20070628