Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 8th, 2007, 12:41 PM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
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
Reply With Quote
  #2 (permalink)  
Old March 8th, 2007, 12:51 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #3 (permalink)  
Old March 8th, 2007, 12:52 PM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
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

Reply With Quote
  #4 (permalink)  
Old March 8th, 2007, 12:52 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #5 (permalink)  
Old March 8th, 2007, 12:59 PM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
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


Reply With Quote
  #6 (permalink)  
Old March 8th, 2007, 02:02 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #7 (permalink)  
Old March 9th, 2007, 05:02 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
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

Reply With Quote
  #8 (permalink)  
Old March 12th, 2007, 10:18 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
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
Reply With Quote
  #9 (permalink)  
Old May 23rd, 2008, 01:07 AM
Registered User
 
Join Date: Jan 2005
Location: Beirut, , Lebanon.
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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 08:20 AM.


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