How to select a value from a combo box in Access from Excel using VBA?

I tried the below code and it kind of works.  It puts the value into the combo box, but it’s not triggering some of other text boxes that should auto populate as they would of if the same value was selected from the dropdown menu.

Sub ExportToAccess()
DoCmd.SelectObject acForm, "frmQMT", False
[forms]![frmQMT]![cboPSI] = Range("D29")
End Sub

Open in new window

kbay808Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IrogSintaCommented:
You need to call the specific event that is associated with this combo box.  For instance if the combo box has an AfterUpdate event handler, you would need add: Call Forms.frmQMT.cboPSI_AfterUpdate to your code.  You also need to change the procedure to Public instead of Private.  It should look like this if it uses the AfterUpdate event.
Public Sub cboPSI_AfterUpdate()

Ron
kbay808Author Commented:
It does have an AfterUpdate and an OnEnter event handler.  I made the changes above, but I'm still getting a runtime error-2465 on the below line.  I attached a screen shot of everything evolved.
Call forms.frmQMT.cboPSI_AfterUpdate

Open in new window


Public Sub cboPSI_AfterUpdate()
DoCmd.SelectObject acForm, "frmQMT", False
[forms]![frmQMT]![cboPSI] = Range("D29") 'PSI
Call forms.frmQMT.cboPSI_AfterUpdate
End Sub

Open in new window

Screen-Shot--Run-time-error-2465-.JPG
Screen-Shot--Event-Tab-on-Property-Sheet
Screen-Shot--cboPSI-Enter-.JPG
Screen-Shot--cboPSI-AfterUpdate-.JPG
IrogSintaCommented:
The procedure you were supposed to make Public was the cboPSI_AfterUpdate procedure, not the procedure that calls it.  The reason for making it public is so that this procedure is visible to everything in the project and not limited to the form that it's in.

Ron
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

kbay808Author Commented:
I made the change.
Public Sub cboPSI_AfterUpdate()

Open in new window

Sub ExportToAccess()

Open in new window


Now I get the attached error for the below line
Call forms.frmQMT.cboPSI_AfterUpdate

Open in new window

Screen-Shot--Run-time-error-3075-.JPG
IrogSintaCommented:
When you click on Debug in the error dialog, what line is the error on?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm a little confused ...

If you're calling Access from Excel, you must have a "handle" to the Access application you're trying to control (i.e. you're "automating" that instance of Access in some manner). Therefore, your code would look like this:

MyAccessApplication.DoCmd.blah blah

And also:

MyAccessApplication.Forms("YourFormName").blah blah

So the "MyAccessApplication" would be the Access Object you created (or grabbed), and you'd preface all calls into that object with the MyAccessApplication name. Simply calling the Forms collection from within your Excel application shouldn't even work - unless I'm overlooking something here (which is highly possible).
kbay808Author Commented:
@IrogSinta - The error is on line "Call forms.frmQMT.cboPSI_AfterUpdate"
kbay808Author Commented:
@Scott McDaniel (Microsoft Access MVP - EE MVE ) - Would it still look like that if the DB and form are already open?  The below code will put data into the form from excel.
Public Sub ExportToAccess()
DoCmd.SelectObject acForm, "frmQMT", False
[forms]![frmQMT]![cboPSI] = Range("D29") 'PSI
End Sub

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Assuming you're running this in Excel, then yes, it should look something like what I posted.

Again, perhaps I'm missing something obvious, but calling Access code (like DoCmd) from within your Excel application should not even work. You must somehow hook into the Access application and database before you can manipulate or automate it.
IrogSintaCommented:
My mistake, I didn't realize you were doing this from Excel.

Ron
kbay808Author Commented:
I am running the code in excel.
Helen FeddemaCommented:
Here is some code to open an Access database from elsewhere and do some things with it:

Public Sub OpenAnotherDatabase()
'Created by Helen Feddema 14-Feb-2010
'Last modified by Helen Feddema 14-Feb-2010

   Dim appAccess As New Access.Application
   Dim strDBNameAndPath As String
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim dbe As DAO.DBEngine
   
   'Change to your db name and path
   strDBNameAndPath = "G:\Documents\Access 2002-2003 Databases\General.mdb"
   appAccess.Visible = True
   appAccess.OpenCurrentDatabase filepath:=strDBNameAndPath, _
      exclusive:=False
      
   'Run a procedure
   'appAccess.Run "PrintOrdersReport"
   
   'Run a macro
   'appAccess.DoCmd.RunMacro "mcrPrintOrdersReport"
   
   'Run an action query
   'appAccess.DoCmd.OpenQuery "qryDeleteSomeOrders"
   
   'Run SQL code
   strSQL = "DELETE tblOrders.ShippedDate FROM tblOrders WHERE ShippedDate = #8/4/1994#;"
   Debug.Print "SQL string: " & strSQL
   'appAccess.DoCmd.RunSQL strSQL
   
   'Iterate through a recordset
   Set dbe = appAccess.DBEngine
   Set dbs = dbe.OpenDatabase(strDBNameAndPath)
    
   Set rst = dbs.OpenRecordset("tblCategories")
   Do Until rst.EOF
      Debug.Print rst![CategoryName]
      rst.MoveNext
   Loop
   rst.Close
    
   Set dbs = Nothing
   Set appAccess = Nothing
   
End Sub

Open in new window


(The code may not work with an .accdb database.)

I agree with Scott about not expecting DoCmd stuff to work in Excel, without going through a lot of other code first to set up the connection to the database.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kbay808Author Commented:
No joy.  I get the following error: "compile error: User-define type not define" for the below line.
Dim dbs As DAO.Database

Open in new window

kbay808Author Commented:
I figured out that I needed to add the additional reference so that error is no longer an issue.  Now the issue is that it's trying to open up the access DB when it's already open.  I'm also getting an error.  Please see attached screen shots.

Public Sub OpenAnotherDatabase()
'Created by Helen Feddema 14-Feb-2010
'Last modified by Helen Feddema 14-Feb-2010

   Dim appAccess As New Access.Application
   Dim strDBNameAndPath As String
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim dbe As DAO.DBEngine
   
   'Change to your db name and path
   strDBNameAndPath = "C:\Users\My user name\Desktop\QM_Tool.accdb"
   appAccess.Visible = True
   appAccess.OpenCurrentDatabase FilePath:=strDBNameAndPath, _
      exclusive:=False
      
   'Run a procedure
   'appAccess.Run "PrintOrdersReport"
   
   'Run a macro
   appAccess.DoCmd.RunMacro "cboPSI_AfterUpdate"
   
   'Run an action query
   'appAccess.DoCmd.OpenQuery "qryDeleteSomeOrders"
   
   'Run SQL code

   'appAccess.DoCmd.RunSQL strSQL
   
   'Iterate through a recordset
   Set dbe = appAccess.DBEngine
   Set dbs = dbe.OpenDatabase(strDBNameAndPath)
    
   Set rst = dbs.OpenRecordset("tblCategories")
   Do Until rst.EOF
      Debug.Print rst![CategoryName]
      rst.MoveNext
   Loop
   rst.Close
    
   Set dbs = Nothing
   Set appAccess = Nothing
   
End Sub

Open in new window

Error.JPG
Debug.JPG
kbay808Author Commented:
Found a work around
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.