Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access Form and Controls cannot capture user's selection in VBA

Posted on 2016-07-21
6
Medium Priority
?
65 Views
Last Modified: 2016-07-28
I am using a remote session to log in to a PC where I am using Office 2016 to open an Access 2016 database on the server. An autoexec.bat calls up the form. The form has a multiple-column ListBox. I set the listbox dblclick event to grab its selection (multiselected=none), but the code cannot get the selection. I have tried many different ways.

However, if I flip to the code behind and then back to the form again, it then works. I created a copy of the form and, if I first try the original form and it doesn't work, if I then try the copy, I can also capture the the user's choice.

I have no idea what is going on here.
0
Comment
Question by:edison04
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 83

Accepted Solution

by:
David Johnson, CD, MVP earned 2000 total points
ID: 41724030
on the machine  that runs access is the server share location trusted to allow VBA interaction?
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 41724751
Are you saying that you are trying to manipulate a form using OLE automation?  What is an autoexec.bat?  Are you saying that you have an autoexec macro that opens the form?  Where is the code that is attempting to manipulate the form running?  What you are describing sounds like a focus issue.
I set the listbox dblclick event to grab its selection
Please post the code and make sure that the procedure header is included so we can see the event if that is where the code is running.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 41724764
Many versions ago, an AutoExec macro was needed to open a specific form when a database was opened.  For quite a while now, you can do this by specifying the form in the database's Options dialog:

File to use when opening a database
So I recommend dumping the Autoexec macro (I assume that is what it is, not an actual batch file as in older versions of Windows), and selecting the form in the Options dialog.

Then, please post your DblClick code so we can see if there are any problems with it.  For comparison, here is some sample DblClick code for working with a single-select listbox:

Private Sub lstCompanies_DblClick(Cancel As Integer)
'Created by Helen Feddema 22-Jul-2016
'Last modified by Helen Feddema 22-Jul-2016

On Error GoTo ErrorHandler

   Dim strCompanyName As String

   strCompanyName = Me![lstCompanies].Column(1)
   
   Me![txtSelectedCompany].Value = strCompanyName
      
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit
   
End Sub

Open in new window

0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 26

Expert Comment

by:Nick67
ID: 41724908
Autoexec.bat
It is the autoexec part that may be causing some consternation.
Waaaaaaaaaaaaaaaay back in the DOS days, autoexec.bat started all your TSR (terminate and stay resident) background processes.  Any DOS and Win9x O/S parsed the autoexec.bat file and ran it.  For giggles, you can name your logon script autoexec.bat, and if your AD or TS profile has that file as its logon script, or if it is in the Start Up folder of the start menu, it gets run.  And if you are old school enough to remember that the file that ran at startup was autoexec.bat, you may very well keep that name.

Access also used that terminology.  To this day, if you have a macro named autoexec, it gets run when Access starts.  I have one.  It calls a VBA function that then does a whole whack of things, including refreshing all the linked tables based on the folder that the database was started from.
The possibilities of what you can do with autoexec in Access are limited only by what you can do in VBA (virtually anything) and not just firing up a starting form.

That all being said, @edison04 has a TS environment that opens an Access database on logon.  I've got that, too.  I don't think any of that is pertinent to the grief at hand, though.

I set the listbox dblclick event to grab its selection (multiselected=none), but the code cannot get the selection. I have tried many different ways.

A double-click in a TS environment might be problematic.  The difference between a double-click and two clicks is all about timing -- and TS may screw that up.

So let's try something for fun.  Put a command button beside the listbox.  Put your capture code in the command box's click event.  Select something in the listbox.  Click the button.  Does THAT work correctly on the first try?  If so, maybe my hunch is right.
0
 

Author Comment

by:edison04
ID: 41725248
Sorry about that Autoexec.bat comment!  very tired. Yeah, it was a macro I named Autoexec.

It was the Security settings. The last version of Access I coded in was mostly 2002 with a little 2007. So I didn't know about the trust settings.

Allowing the network to be trusted took care of the problem. Thanks for all the responses. David thanks for recognizing those symptoms and for your help.
0
 

Author Closing Comment

by:edison04
ID: 41734023
Thanks for your help David.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question