Connecting to Access database to populate a combobox.

Hi

Using Excel 2000 and Access 2000, the code below was working to
populate a combobox on a spreadsheet.

I'm attempting to use it to populate a combobox--cboQpn--on Userform4.

I want the user to type a competitor part number in textbox--
txtCompNum.

Then the macro will use that value to find our part number in the
access table and put the result in the combobox. I'm using a combobox
because there may be more than one record returned.

The code fails on this line:    Dim wspDefault As Workspace with the
error, "compile error: User-defined type not defined"

Thanks for your feedback,

Dan

Sub CreateRecordSet()
    On Error GoTo CreateRecordSetErrorHandler
     Dim oldDbName As String
     Dim wspDefault As Workspace
     Dim dbsEAIQuote As Database
     Dim strSQL As String
     Dim strCompetitorPart As String
     Dim strEAIPart As String
     Dim rstFromQuery As Recordset

     strCompetitorPart = UserForm4.txtCompNum.text
     strEAIPart = UserForm4.cboQpn.text


     'Set the path to the database
     oldDbName = "K:/Customer Service/Quote/Database/EAIQuote_be.mdb"

     'Create a default workspace Object
     Set wspDefault = DBEngine.Workspaces(0)

     'Create a Database object
     Set dbsEAIQuote = wspDefault.OpenDatabase(oldDbName)

     'The SQL statement
     strSQL = "SELECT tblCrossNoDash.Scrubbed, " & _
        "tblCrossNoDash.EAIPartNumber FROM tblCrossNoDash " & _
        "WHERE (tblCrossNoDash.Scrubbed= '" & strCompetitorPart & "')"

     'Create a Snapshot Type Recordset from the SQL query
     Set _
     rstFromQuery = dbsEAIQuote.OpenRecordset(strSQL, dbOpenSnapshot)

     'load up combobox

     'Show the number of fields returned
     'MsgBox "there are " & rstFromQuery.Fields.Count & _
     '" fields that were returned"

     'Move to the last record in the recordset
  '   rstFromQuery.MoveLast

    'Put the EAI part number in Combobox2

        Do While Not rstFromQuery.EOF
            UserForm4.cboQpn.AddItem rstFromQuery(1).Value
            rstFromQuery.MoveNext
        Loop

'    Sheet6.ComboBox2.DropDown = rstFromQuery!EAIPartNumber
     'Show the number of records returned
    ' MsgBox "there are " & rstFromQuery.RecordCount & _
    ' " records that were returned"
    Exit Sub
CreateRecordSetErrorHandler:

  End Sub
1
dan
3/11/2010 6:13:23 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
1656 Views

Similar Articles

[PageSpeed] 21

Ok. I hadn't selected the proper reference--Microsoft DAO 3.6 object
library.

Also I had mispelled the table name in the sql statement.

So now the code I'm using (shown below) doesn't populate the combobox.

I know sql part is working becuase I typed my varialbe in the
Immediate window and copied the sql to the access database and it
returned the proper data.

However the recordset is never created with this line:
Set rstFromQuery = dbsEAIQuote.OpenRecordset(strSQL, dbOpenSnapshot)

and the combobox is not populated.

Here's the code I'm using.

Does anyone have a suggestion?

Thanks,

Dan

Sub CreateRecordSet()
    On Error GoTo CreateRecordSetErrorHandler
     Dim oldDbName As String
     Dim wspDefault As Workspace
     Dim dbsEAIQuote As Database
     Dim strSQL As String
     Dim strCompetitorPart As String
     Dim strEAIPart As String
     Dim rstFromQuery As Recordset

     strCompetitorPart = UserForm4.txtCompNum.text
     strEAIPart = UserForm4.cboQpn.text


     'Set the path to the database
      oldDbName = "K:/Customer Service/Quote/Database/EAIQuote_be.mdb"

     'Create a default workspace Object
     Set wspDefault = DBEngine.Workspaces(0)

     'Create a Database object
     Set dbsEAIQuote = wspDefault.OpenDatabase(oldDbName)

     'The SQL statement
     strSQL = "SELECT tblCompetitorScrubbed.EAIPartNumber " & _
        "FROM tblCompetitorScrubbed " & _
        "WHERE (tblCompetitorScrubbed.CompetitorNumber= '" &
strCompetitorPart & "')"

     'Create a Snapshot Type Recordset from the SQL query
     Set rstFromQuery = dbsEAIQuote.OpenRecordset(strSQL,
dbOpenSnapshot)

     'load up combobox

     'Show the number of fields returned
     MsgBox "there are " & rstFromQuery.Fields.Count & _
     " fields that were returned"

     'Move to the last record in the recordset
     rstFromQuery.MoveLast

    'Put the EAI part number in Combobox2

        Do While Not rstFromQuery.EOF
            UserForm4.cboQpn.AddItem rstFromQuery(1).Value
            rstFromQuery.MoveNext
        Loop

'    Sheet6.ComboBox2.DropDown = rstFromQuery!EAIPartNumber
     'Show the number of records returned
    ' MsgBox "there are " & rstFromQuery.RecordCount & _
    ' " records that were returned"
    Exit Sub
CreateRecordSetErrorHandler:

  End Sub
0
dan
3/11/2010 10:03:02 PM
Reply:

Similar Artilces:

Problem with OutputTo in Access 2007 + Visual Studio 2005
Please forgive me if I'm posting to the wrong group. I'm converting an application I wrote in Visual Studio 2002 + MS Access 2002. It was a console application that was executed from an Access Macro. The application would open up the current Access Database, it would export a particular report to HTML, then it would call a webservice on a remote server to send out the exported file via email to a distribution list. I'm converting this to Visual Studio 2005 + MS Access 2007, so that I can export the reports to PDF. Everything converted over just fine, except that I can no longe...

Can't Send Messages Using Outlook Web Access & XP
Our users with Windows XP cannot send messages using OWA. I've read a few KB articles about some incompatabilities between XP and OWA, but none of the proposed fixes (using the "basic" rather than "premium" client, or changing the security settings in IE) seem to solve this last problem. When they click on "send", it generates an unspecified "error on page". The problem occurs using IE and Firefox, but just on XP machines. My Windows 2000 machine works fine using IE and even my Macintosh using Safari (gasp!) works. ...

Administrator Access
I need a way in exchange for an administrator to be able to easily gain access to a user's account... without changing their password. I read that adding users to the "Send on Behalf" list in the active directory account does this, but this is not the case. We need to be able to see a user's account in the exact same way as a user sees it either via outlook or web mail without having to go in and change their password. Additionally I need an easy way to dump email into a user's account. The migration wizard IS NOT the solution. All I want to do is take RFC822 ...

the program "access"
I have a lot of data in the program "access" which I am unable to get to using MAC. I need someone to assist me in migrating the data over. my attempts are failing In article <1191720385.419936.207900@y42g2000hsy.googlegroups.com>, stevelaudig@gmail.com wrote: > I have a lot of data in the program "access" which I am unable to get > to using MAC. I need someone to assist me in migrating the data over. > my attempts are failing See http://www.agentjim.com/MVP/Excel/RelationalOffice.htm and http://www.agentjim.com/MVP/Excel/ExcelHome.htm You ca...

Excel to Access database
Does anyone know how to convert a Excel document to an Access database? I got this link off of Microsoft.com http://tinyurl.com/2nqo5 If I understand correctly, I could enter data into an excell spreadsheet, which I am at least somewhat familiar with. When I'm done, I could convert it into a new Access database without setting up the Access forms. Any help is greatly appreciated. Gordon --- Message posted from http://www.ExcelForum.com/ Gordon: Hi. If there are headers to your columns, they should be in row1. In Access, start a new database file. In the "Tables"...

Excel template and access questions
We are going to use the Excel purhase order templates. We need to save each order in order to refer back to it. Is there a way to do this without saving each individual file (order)? Can we save in some sort of easy to locate "batch"? Also, is there a way to transfer a template that we have done (and all the data realted to it) into a Acess database withouthaveing to so much tweaking? If you have Access, I would suggest creating your purchase order database in Access and then having a mail merge push this information into an Excel template as needed, rather than going the othe...

Convert old Q&A database to access
Does anyone know a free way of converting an old DOS based Q&A .dtf database to Access? There are only 6 fields: first name, last name, date of birth, date of death, cemetary, war if any. This is for a non profit that has been building a cemetary database for years and need to get something modern. I will build the Acces piece for free. But if I can't convert the data for free the project is dead. There may even be an export option within Q&A that would help. I didn't have enough time to look at it so far. thanks in advance, Mark Can Q&A export to .CSV (or any...

Report on a specific printer, bug in access 2007?
Dear all, in access 2007, I can click the "page setup" menu then the "page setup" button, and choose to print the report on a specific printer. However, after saving the report and re-opening it, he still prints on default printer. The specific printer setting is not saved with the report (this worked with all previous versions of access, it seems the specific printer setting has no effect anymore). Does someone know where I could find a patch or a workaround for this? (no updates are found through office update, and this might be critical for some applications). Regard...

Unhandled database exception: A get/change operation on table 'Bat
During Edit Checks, we get the following message: Unhandled database exception: A get/change operation on table 'Batch_Headers' could not find a record. Then we get this message: This transaction was recovered during normal processing. You may continue processing this transaction. Any suggestions? You might want to run Check Links on payables. Before doing this, make sure you have a backup of your dynamics and company databases. Brenner -- www.KlenzmanConsulting.com "GPI" wrote: > During Edit Checks, we get the following message: > > Unhandled databas...

connecting
i need how i conected my computer in the mail?... You just set it up in Tools - Accounts. Yahoo must be a "Plus" account - see: http://help.yahoo.com/l/us/yahoo/mail/original/mailplus/pop/pop-06.html "Raymond01040" <raymond01040@yahoo.com> wrote in message news:#iE16fsnKHA.3164@TK2MSFTNGP06.phx.gbl... > i need how i conected my computer in the mail?... ...

Automatic Find and Populate
Ok, I have several spreadsheets that represent bills and have tabs that represent the months. So, when I recieve a new bill for the month, I copy last month's bill onto a new tab, and rename it using the billing cycle. Each year, for each account number is contained in its own workbook- ie: account 245-9999 has a workbook for 2002, 2003, 2004, and 2005; within that workbook are tabs for Jan, Feb, Mar, etc. Ok, on a single month's tab, there are anywhere from 10 to 900 individual line items representing circuits. What I want to do is create a separate spreadsheet that lists unique c...

Error accessing database
Hi all, I'm trying to log into a newly added database, but get the error: [Microsoft][ODBC SQL Server Driver][SQL Server]Prodecure or function smCleanUpFilesBeforeLigin has too many arguments specified. Then, clicking OK, you get another one: The stored procedure smCleanUpFilesBeforeLogin returned the following results: DBMS: 8144, Great Plains: 0. What has happened there, and how can I resolve it please? This happens even when logging in as 'sa' to the database. Thanks all, John ...

Install program build on Access 2003 on Window 7?
I have an application designed by a professional progammer that was built on Access 2003 with VBA. I am going to give it to students to run on their laptops. Some of them may have Vista or Window 7 operating systems. Will the application install and run? It installs with a 2003 RT version. I have found that the RT has to be removed when install on a machine with Office 2007. -- Ed Comp Analyst Whether or not it'll work on Vista or Win7 depends on whether the application was packaged correctly. Both Vista and Win7 are stricter about where the application (i.e. the MDB o...

just testing connectivity
alt.test "Test1" <a@b.com> wrote in message news:%23EVqQ9T2EHA.1860@TK2MSFTNGP15.phx.gbl... > > ...

Problem connecting to outlook 2007
Hi, my boss can't access his outlook 2007 when he comes in to the office with his laptop. He can only connect if he has his wireless or the vpn on. He gets this error message ox80042108 when he tries to open outlook without wireless or vpn. Thank you for any help or suggestions offered. What type of mail account is it set up as? "k" <k@discussions.microsoft.com> wrote in message news:CA28F0E9-5505-4A12-8833-D2411219A6C8@microsoft.com... > Hi, my boss can't access his outlook 2007 when he comes in to the office with > his laptop. He can only co...

Using Access database to "populate" Excel Sheets
Please help!!! I am willing to PAY anyone who can get this to run fo me. I have been trying for 5 days now trying to use a DBVlookup function t populate fields in Excel. I used examples from 4 different forums usin this function, but I cannot get any of them to work. I am somewhat ne to VBA so forgive me if I am not making sense in my questions. I have Excel Spreadsheet called "Account_Number". It is set up a follows: Column A is called "Account Number"..... Column B is called "Looked u description in Access". Account Number Description...

Backend Crash Access 2007
One of our Access 2007 databases seems to have encountered some difficulties with crashing. There are 18 users, and approximately 75000 unique records. This morning the backend crashed and displayed the following error message: unrecognized database format '\\r5\accumteam\prod2010\clmd_be.accdb' r5 is the server name The database then recreated a copy of itself, and all the tables were empty. We were able to restore from backup but are very interested in fixing whatever may be wrong Thanks for any suggestions Marsh Tony Toews has an excellent web page on database cor...

How do I make a column be my default column in Access
I need to make my desricption field my default field. How do I do that? Right not it defaults to my items field. me.controlname.setfocus or in macro GoToControl "controlname" Bonnie http://www.dataplus-svc.com michelle wrote: >I need to make my desricption field my default field. How do I do that? Right >not it defaults to my items field. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1 Or, if you don't want to use events, simply set your tab order from the form design view. -- Frank H R...

Will there be a SP1 for Access 2007 runtime
I have an old access application that I would like to port to 2007 - use the Developer Extentions and the accessruntime.exe to be able to distribute it to client without access installed. Unfortunately one of the reports crashes the runtime environmet with an unknown error. The error did also occur in pure Access 2007 - until SP1 was applied. Is there any way to get an updated version of accessruntime.exe Hallo, lnielsen7 meinte: > I have an old access application that I would like to port to 2007 - > use the Developer Extentions and the accessruntime.exe to be able to > dist...

Outlook 2003 can not connect to Exchange 2003 server through VPN
Hi, We have a Exchange 2003 server running on windows 2000 server. Everything is working fine (Outlook client, OWA) for LAN. The server has two NIC, one connect to LAN, another one connect to firewall with different subnet. I can ping both NIC after I established VPN connection from outside internet, also can open OWA using both NIC ip address. I updated "hosts" file on remote computer for the exchange server name and ip address translation. But, When I setup exchange server in outlook 2003 on the remote computer, type in the exchange server name and user name(User account is al...

is it possible to host multiple CRM databases on 1 SQL server?
Hi, Is it possible to host two separate (on premise) CRM server databases on one SQL server? I am trying to achieve the above scenario, but there can only be one MSCRM_config database on any one instance of the SQL server. The Implementation guide isn't too clear about what to do in this situation. - Can two CRM databases share the same MSCRM_config file? - Do I need to install another instance of SQL server for the second CRM database? Or is there another solution? Thanks Depends on your CRM installation. If you are running the Enterprise version, then you can add a new org...

No data appears when I do a mail merge from Access to Publisher 2.
When I do a mail merge from access to publisher 2003 there is no data from the access database (it is closed and the query does work) What settings do I need to change? If I remember correctly you cannot merge from a query... -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Jenny A" <JennyA@discussions.microsoft.com> wrote in message news:88F611CA-389D-4076-8BC5-B6E435B727C4@microsoft.com... > When I do a mail merge from access to publisher 2003 there is no data from > the access database (it is closed and the q...

Categories in Databases
Hi, I have a database of things in Sheet1 and one of the columns is labeled "country". Let's call this the master list, with about 150 rows. I want to make a list in Sheet2 with just things in "China". The way I've been doing it is by scouring through the master list, seeing which ones have "china", and then manually copying and pasting. Is there any way in which I can get this automatically done instead of doing it the manual labor way? Thx -- yl358 ------------------------------------------------------------------------ yl358's Profile: htt...

CFileDialog / access to the listbox of filetypes
Hello, I have a customized file dialog window, which is derived from CFileDialog. Into the dialog is a subwindow in a group box integrated (stc32) which contains the functions of a file dialog. This procedure is from "Inside Visual c++". For my program I need to retrieve a message when the user has changed the filetype which can be chosen in the integrated list box. How can I access this list box? Thank you very much for any clue! Kai Sandner There are notifications that are sent to indicate the current selection. See CFileDialog and read the articles about subclassing CFil...

Where can you download a trial version of Access 2002?
We're still running Windows 2K SP4, and cannot try out the trial version of Office 2007 (minimum system requirements is Windows XP). I need a copy of Access 2002 to open a MDB that was created in 2002 (it won't open in Access 2K). Thanks in advance for your help! Heidi Assuming that you have Access 2000 and this is a one-time event, find someone who has Access 2002 or 2003 and send them the database. They could covert it to the Access 2000 format for you. That would be simpler, and cheaper, than trying to install another version of Access. Assuming that the database isn&#...