Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 8th, 2007, 12:41 PM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default Populate combobox from ADO recordset - RESOLVED!

Hi all, this is my first post on this forum so a very warm hello to you all!

A quick question, I have done a search (or 4) and not come up with any results, but I apologise if this has been answered before.

I have a form, which is unbound and on said form is a combo box.
I am setting the recordset property of the combobox to a ADO recordset, this is working fine, when I click the drop-down arrow, the expected values are being listed.

However, when I choose a value, the value of the box changes (and the new value is being picked up by a simple MsgBox in the AfterUpdate event) but then returns back to null after the AfterUpdate event!?!

This is confusing me! There is no other code to result the combobox.

FYI - I have disabled the "Not In List" property, even though this should not make a difference (the NotInList event should just fire).

Any help/suggestions will be greatly appreciated!

Best Regards,
Rob
__________________
Rob
http://cantgrokwontgrok.blogspot.com
 
Old March 8th, 2007, 12:51 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Where are you getting the data from? I imagine a remote table, perhaps on a SQL server that you want to use as a look up to pass parameters to the next operation.

Here is a quick and dirty way of doing this, that is fairly robust, and does not rely on too much code.

Assume your look up is for LastName, FirstName, and takes a PK to pass to the next operation.

Build a local table, and create the requisite fields (PK - Number, LastName - text, FirstName - text) and then create a DELETE query to empty this table every time you want to use the combo box. Remember to DoCmd.SetWarnings False before the query, and True after.

So on the Form's On Load event:

DoCmd.SetWarnings False
DoCmd.OpenQuery "DELETENames"
DOCmd.SetWarnings True

Open two recordset, one on the remote data, and one on the local table.
Populate the local table.

Then base the combo box on your local table.

Then take the PK field (not a PK on your local table, but you only want the row number anyway) and do all your subsequent operations on this integer.

I do a lot of Access / SQL development where I build local tables for local processing.

To make this simpler, create a view on your SQL server (If that is what you are using) that already has this combo box info, so that the query is already optimized on the server. This should speeds things up and keep the wire a bit cooler.

Did that help?

mmcdonal
 
Old March 8th, 2007, 12:52 PM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Just thought I would add, the combobox is not "Locked".

Also been playing some more, and the value is actually remaining after selection, I placed a msgbox displaying the value elsewhere (in another command button), and the right value is being displayed, however the text is still not appearing in the combobox.

I have a feeling this is something very stupid and I am going to end up kicking myself!
Its been a long day =)

Best Regards,
Rob

 
Old March 8th, 2007, 12:52 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

On the last method you would want to delete the table each time, and use DoCmd.TransferDatabase.

HTH

mmcdonal
 
Old March 8th, 2007, 12:59 PM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

mmcdonal,

Thanks for your reply.

I am trying to avoid the creation of temp tables.

I will attempt to explain my situation, please ask if anything is unclear.

Basically the project I am working on involves an MDE frontend, which will access the MDB backend data files.
Multiple files will be open to enable comparison (much like any regular MDI app).

I have constructed an object model to support all this, managing connections to the files etc.

What I have is a class which houses a "project" file (not my actual project) and I want to list all the IDs within the project file, and be able to select one. So what I had in mind was the project file would construct a recordset containing its IDs, which I could then use to populate a combo in the frontend, when this value is selected, I then go back to the class and say "give me all the data on this" etc.

I have been successful in everything thus far, the combo box is being populated fine, returning the correct results but the 2nd col's value is not remaining in the combo caption after selection (altough the 1st col's value is!?!)

As I said this is bound to be a really stupid error and I will kick myself!
I need stronger coffee!

Thanks Again,
Rob


 
Old March 8th, 2007, 02:02 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I think if you just want to manipluate the combo box, you need to make sure that the ID is the bound column, but that it is not displayed, so Column Width is 0";2" for a two inch list with the ID and a meaningful name field.

You may want to take the ID in a Public variable, and then after your operation causing the grief, reset the combo where Column(0) = ID.

Would that work? This sounds like it is more cosmetic issue so no deep redesign needed.

mmcdonal
 
Old March 9th, 2007, 05:02 AM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

hi mmcdonal,

thanks again for your response.

The ID is currently set to the bound column, and column widths are set to 0;5cm.
I tried your global variable assignment and that still didn't work!

This is becoming a real thorn now!

Is is definately possible to have a combobox based on a ADO recordset to work in this way!?!?
I know sometimes there can be stupid little issues/quirks/"features" ;) with things like this...

I have used a similar process to populate a listbox and this all works fine (but then again, listbox values are always on display).

Are you able to replicate the issue?
If you need any more info, then please ask.

Thanks again for your help.

Rob

 
Old March 12th, 2007, 10:18 AM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Cracked it!

I needed to set the CursorLocation to adUseClient!
Having done this all combo boxes are now displaying correctly.

I knew I'd end up bloody kicking myself!

Thanks for your suggestions thus far mmcdonal!

Best Regards,
Rob
 
Old May 23rd, 2008, 01:07 AM
Registered User
 
Join Date: Jan 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello,

I was very interested in reading this (very) old topic, as I am sure that using ADO recordset as data source for combo box could really improve speed when opening a form (and could also allow the use of xml files as datasources...). But I'm sorry to say that I am not even able to implement the first step :"I am setting the recordset property of the combobox to a ADO recordset" ... I do not have such property available for my combobox. Recordset property is only available at the form's level. Were you talking here about Access forms and Access combobox controls?

Thanks for you answer,

Philippe






Similar Threads
Thread Thread Starter Forum Replies Last Post
Populate ListView From ComboBox Select eusanpe C# 4 August 20th, 2007 06:49 PM
Clone DAO Recordset into ADO Recordset kamrans74 VB How-To 0 March 6th, 2007 11:57 AM
populate combobox in xsl mausumee XSLT 2 February 23rd, 2007 06:37 PM
populate data in a windows form combobox from a da anibiswas General .NET 0 March 4th, 2005 03:57 PM
Convert ADO recordset to DAO recordset andrew_taft Access 1 May 5th, 2004 02:31 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.