Advertisement

05.16.2008 at 05:34PM PDT, ID: 23410062
[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.1

default value for combobox on datasheet form.

Zones: Microsoft Access Database, Access Architecture/Design, Access Forms
Tags: microsoft, access, 2003
I have a search form for my database (frmSearch).. the results show in a subform called frmSearchDetails, which is in Datasheet view.

The applcable tables on my database have a One-To-Many relationship.  Each "Customer" Can have Multiple "Account Numbers" associated with them.

By default, when the records display, there are multiple rows per record, due to the 1-to-M relationship. (So if you search for 'John Doe' who has 4 account numbers, it ends up displaying on 4 rows.)

To avoid this (with the help of another user here), I've managed to put a combobox on my datasheet to display the multiple account numbers.

So now there's only one row per record, and the account number field is a combobox.  The Combobox has a query that populates it with the applicable values based on the current record highlighted on the datasheet. (On the GetFocus property of the combobox, it refreshes the combobox with the values of the selected record. (ComboBox.ReQuery).

So this presents the problem:

Since the form is in Datasheet view, and there is only one 'real 'control for Account Number, the following happens:

1) The Account Number field for the combobox's going down the column are all BLANK.  (when you click the little dropdown arrow, you see the correct values though, since it ReQuery's everytime the focus is set.)

2) If I set the value for the ComboBox to ItemData.(0) to show the first item. (ComboBox1.Value = ComboBox1.ItemData(0)) Then everytime a record is highlighted ALL the comboboxes going down the column are refreshed with the SAME account number going down. (which changes whenever you click on a different row.)  --- This is happening since the ReQuery is changing the values for the ComboBox.

So, how do I avoid this?  I want the first account number value to show up in each Row when it returns the search results. (not blank comboboxes)  And when the Account Number dropdown arrow is clicked, it will display the other associated account numbers.

From hours and hours of reading and researching the only hint of a solution I have found is something to do with having the combobox bound to another control---or something along those lines?
Start your free trial to view this solution
Question Stats
Zone: Microsoft
Question Asked By: NO_CARRIER
Solution Provided By: koutny
Participating Experts: 2
Solution Grade: A
Views: 141
Translate:
Loading Advertisement...
05.17.2008 at 01:45AM PDT, ID: 21588467

Rank: Master

koutny:

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.17.2008 at 01:45AM PDT, ID: 21588469

Rank: Genius

peter57r:

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.19.2008 at 06:11PM PDT, ID: 21602825
NO_CARRIER:

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.20.2008 at 11:15AM PDT, ID: 21608720
NO_CARRIER:

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.20.2008 at 11:50AM PDT, ID: 21609039
NO_CARRIER:

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.21.2008 at 02:41AM PDT, ID: 21613338

Rank: Master

koutny:

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.21.2008 at 08:41AM PDT, ID: 21616183
NO_CARRIER:

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
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
OS
  • 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
 
05.17.2008 at 01:45AM PDT, ID: 21588467

Rank: Master

koutny:
With an unbound combo box it won't be possible to show the first value fo each record of the continuous subform - an unbound control shows the same value for all records.

If the purpose of the combo box is simply to display all the values in the list and the users don't need to be able to select a particular value from the list then you could replace the combo box with a text showing all values as a comma separated string.
For that you would need to write a VBA function which would compile the list - see the code snippet.
Place the function in the form's VBA module and set the textbox's control source to

=GetAccountNumbers([N_ID])
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:

Private Function GetAccountNumbers(ByVal ID As Variant) As String
Dim strNumbers As String
Dim rst As Recordset
Dim strSQL As String
    On Error GoTo err_label
    If IsNull(ID) Then GoTo err_label
    strSQL = "Select [Account number] from tblAccountNumbers WHERE tblAccountNumbers.[n_ID] = " & ID
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    If Not rst.EOF Then
        strNumbers = rst.Fields(0)
        rst.MoveNext
        Do While Not rst.EOF
            strNumbers = strNumbers & ", " & rst.Fields(0)
            rst.MoveNext
        Loop
    
    End If
    rst.Close
    Set rst = Nothing
    
    GetAccountNumbers = strNumbers
    
    Exit Function
err_label:
    GetAccountNumbers = ""
End Function
Open in New Window
Accepted Solution
 
05.17.2008 at 01:45AM PDT, ID: 21588469

Rank: Genius

peter57r:
You have hit the nail on the head in your last paragraph.
If you use an unbound control for this data then you will always see the same value in all visible records.
The value displayed and the list might change as you move from record to record, but every record will show the same as the current one.

You need to change the design so that the control is bound to a field in a table.
 
05.19.2008 at 06:11PM PDT, ID: 21602825
NO_CARRIER:
koutny, I'll try that.. .I'm just a little worried about an akward looking display for users.  Since although most records will be 1 or 2 account numbers, some will have up to 10.  I'll try it out, perhaps experiment with the additional accounts showing in a pop-up window?

peter, can you please elaborate on how I will do this?  If I make the combobox bound to a field in a table.  How do I then make each combobox load all the values (account numbers) and display the first value in the combobox as a default programatically?

 
05.20.2008 at 11:15AM PDT, ID: 21608720
NO_CARRIER:
koutny - is it possible to have the following code work to fill a listbox or combobox in datasheet view?

I managed to get it to fill a single line of the listbox, but I would have to create an array of some sort and set the recordsource property to the array... is this possible?
 
05.20.2008 at 11:50AM PDT, ID: 21609039
NO_CARRIER:
Woohoo.. Okay, I figured out a work around.. it's not the most elegant solution, but this is how it works:

Using koutny's code.

Where the string is built, I changed:
strNumbers = strNumbers & ", " & rst.Fields(0)

to:

strNumbers = strNumbers & "      ;" & rst.Fields(0)

the reason for the spaces is to hide the semicolon from the fixed width textbox in datasheet view.

The textbox is changed to a listbox.  So in datasheet view it functions the same as a combobox.

On the GotFocus() event for the textbox I put:

TextBox.DataSource=TextBox.Value

so it's effectively setting the datasource values to itself, and using the semicolons (;) to populate the elements.

It looks like a regular combobox, the account number looks correct.. (looks like it's only one account number), and when the user clicks the dropdown arrow the other numbers appear.

------
phew!  Is there an easier way to do what I did?
 
05.21.2008 at 02:41AM PDT, ID: 21613338

Rank: Master

koutny:
Well, well, well
I would have thought it cannot be done. But you proved me wrong.

What I would have probably ended up doing is changing the function which builds the text string to truncate it to a certain length and append "..." if it would be otherwise longer. Then the user would have to click on the text box to display a pop up form with the complete list of account numbers.
 
05.21.2008 at 08:41AM PDT, ID: 21616183
NO_CARRIER:
Thanks Koutny... perhaps that would've been easier. :) ... This still looks good though, uniform and easy for the user.  I'll probably put a conditional format on the control, that if it contains a semicolon ; to bold.. so the user will know if there are additional accounts.

Thanks again!
 
 
20080236-EE-VQP-29 / EE_QW_2_20070628