Microsoft Access





Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Share tech news, updates, or what's on your mind.

Sign up to Post


I encapsulated a form in a class, with the goal to catch some of the form's event, but so far, no events are caught.

EventCatcher class:
Option Explicit

Private WithEvents mParent As Access.Form

Private Sub Class_Initialize()
    Set mParent = forms("Form_1")
    mParent.OnGotFocus = "[Event Procedure]"
End Sub

Private Sub mParent_GotFocus()
    Debug.Print "event GotFocus"
End Sub

Open in new window

Any clue about what's going on ?

Note: I'm able to catch control's events without problem,
10 Tips to Protect Your Business from Ransomware
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Google Maps Distance
Many applications need to compute the road distance between two addresses. Numerous websites perform this function, but to build this feature into your application, you must use an API to call these features via the internet. This article discusses how I did so by using Google Maps API and VBA.
I am looking for a working 64bit Font dialog for Excel.

I use below formula in Access query column, when I copy this column data set and paste in excel, then look at the count value, it shows full count of cells in the column including blank cells. How can I modify this formula which keeps only non blank values as it should be and keep blank values as blank.

EvNum1: IIf(Left(Trim([EveNum]),2)="KK",[EveNum],"")

Hope you get my point.

Is there a way to retain the color scheme of a pivot chart when filtering it?  I have reset the color scheme for a bar chart only to have it revert back to its original colors after filtering it.
How can I retain the colors in the pivot chart after filtering it?
Is there a way to filter a pivot table and at the same time update a pivot chart with the same filters?
Looking for recommendations regarding encrypting and decrypting files at run-time, using VBA.

I have a client who has a bunch (thousands) of files on her network share which contain unencrypted PII.  I'm looking for some vba code which I could use to encrypt these files when they are loaded into her application and then decrypt them when a user needs to view the file.

If there is another option (password protecting a zipped folder) that can be implemented via VBA, that would be acceptable as well.

I have 2 Access Forms that I am trying to pass data between.  From From my CustShipAddressList, I have an EDIT button that should open that specific CustShipAddID in a popup where I can edit the address fields.  For some reason, the button will open the pop up form but it is blank and changing my CustShipAddId number to 0 in the source table.

I have tried to 2 different types of Code.  First opens the form with a where condition and the second with openargs. Both are having the same issue.  

Thanks in advance for your assistance.  I am using Access 2016 (from Office 365 Home subscription).

Form CustShipAddressList

Private Sub EditCustShipAddBtn_Click()
   Dim CustShipAddID As Long
   Me.CustShipAddID = CustShipAddID
    DoCmd.OpenForm "CustShipAddress", WhereCondition:="[CustShipAddID]=" & Me!CustShipAddID
   'DoCmd.OpenForm "CustShipAddress", OpenArgs:=CustShipAddID
   DoCmd.Close acForm, "CustShipAddressList"
End Sub

Open in new window

I have a database that has tables for Clients and Jobs (tblClients and tblJobs)

Currently working is a form to add a new job.
This form has a query that joins tblClients and tblJobs
It is called from a button on a menu form using…
stDocName = "frmNewJob"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.GoToRecord , , acNewRec

On this form is a field cboClient.  The user selects the Client in this combo box and it populates the client details on the form.  The user then enters the new job information and saves to create the new job.
This is all working fine and has been for ages.

Now I want to do almost the same thing, but calling a copy of the above form – frmNewJobFromEditClient from the client details form.
So when the new form is opened I want it to open as a new record but with the Client details already populated (as if the user had selected the correct client).

I assumed that I would be able to put something like…
Me.cboClient = [frmEditClient].[Client ID]
in the Open event of the frmNewJobFromEditClient form.
I have tried all sorts of options of syntax without success.

Hope someone can help me out.

I'm using a customised validation rule in access. Whenever an error occurs  I call the Forms FormError event and handle the error message according to the language of the user. For example if a variable is declared as numeric and the user enters characters, access will automatically call the formError event. However, I have recently changed to the new 64bit version and here the error is handled without calling the FormError event and no message at all is displayed. Is this correct or is there anything I can do to have the same behaviour as in the 32bit version? Thank you so much for any help!
Newly released Acronis True Image 2019
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.


Basically, I need to recreate 3 sheets on excel workbook using access vba. The 4th sheet on excel has got table/chart which have got excel formulas and they are looking into the other 3 sheets to generate values. Each day when I delete these 3 sheets, and recreate them, the formulas in the 4th sheet get corrupted. Even if I generate other 3 sheets, the values on the 4th sheet are not being updated because of the corrupted formulas. what is a solution for this? One solution is to generate formulas on 4th excel sheet using access vba. But there are lots of formulas (more than 100 as of now, big chance to increase). Is there any other suggestion like keeping a sheet with formulas and copy paste them using access vba.?But if I copy paste I need to do "replace" sheet name at the end and its going to be complicated. Any help on this greatly appreciated.

thank you
I am importing an XML file generated by a third party application into Microsoft Access, the process works but each of the tables it creates does not import with a unique reference for me to tie everything together, so now I'm left without a way to query the data.

Each XML import is the result of a 'company lookup' which brings in data relating to businesses (directors, names, company details, balance sheet etc.). I will be importing lots of different company data into the same tables, so I need an identifier which will be in each table which identifies which company the data is for - i.e. company registration number. The company registration data is held within each XML import.


I have attached an example XML file (don't worry this is all publically accessible information so there's nothing confidential), I have also attached how this imports into Access.

Can someone help me here to explain how I can get a unique identifier (preferably the company registration number) into all of the tables so that I may link it all together?

Many thanks in advance!

Access Screenshot
hi, I hv a sorting issues, can anyone show me how to query the results from the sample Table? currently the sorting is by Autonum.

the result i am looking for is as follow after sortingSorting-Issue.mdb:

I have an access table with field ID, Item NoofHour this in sequence I like the keep summing the NoofHours
so table Order
id  Item  Hours
1    abc    5
2    ccc    10
3    yyy    2
4   kka     3

I want to make query so result are
id  Item  Hours   Totalhr
1    abc    5             5                
2    ccc    10           15                
3    yyy    2             17
4   kka     3             20

So add the above hours. I will appreciate if somebody can tell me how I can get  sum  of row above.
I am working on a music database with two main tables, tblTitles and tblSongs and other lookup tables.  The main form 'frmTitles'  has a continuous subform 'subfrmSongs'.  On frmTitles I have a field "Status".  It is a lookup field (using a combo box) and the valid selections are "Active", "For Sale" and "Sold".  Now if the status is "Active" or "For Sale" I want all fields enabled except for "SoldTo", SoldDate" and SoldPrice".  On the other hand if the status is "Sold" I want only the Status, SoldTo, SoldDate and SoldPrice enabled with all of the other fields disabled.

Finally, when the form is first opened I want the status determined from the value in Status and the fields setup according to the above.  I have tried using Case statements and If - End If but I just am missing something.

Private Sub Status_AfterUpdate()
 If Me.Status = "Sold" Then
          ' if the user has entered Sold
            Me.MediaType.Enabled = False
            Me.RollType.Enabled = False
            Me.RollOrigin.Enabled = False
            Me.RecutSource.Enabled = False
            Me.TitleNumber.Enabled = False
            Me.Title.Enabled = False
            Me.GroupPerformer.Enabled = False
            Me.Genre.Enabled = False
            Me.PurchasedFrom.Enabled = False
            Me.PurchaseDate.Enabled = False
            Me.CurrentMarketValue.Enabled = False
            Me.Condition.Enabled = False
            Me.RollRating.Enabled = False
I have designed a database application for use by several users on the network. The Access application is a client/server application with an Accde front end and Access runtime libraries installed on each user's computer. Thus not requiring a full copy of Microsoft Access.
The back end linked table database is located on a network share in a folder that all users have [Modify] permissions but not [Full Control].
The client front end file is run from C:\Program Files (x86)\Microsoft Office\Office16\ACCWIZ folder because it is a Default trusted location within Access and this location stops the security messages popping up when a user first opens the application.


When the front end application is run the opening form has the following message displayed across the top:

"READ ONLY. This database has been opened read only. you can only change data in linked tables. To make design changes, save a copy of the database"
An a [Save As] button.
This would be confusing for the users.
How can I hide the message?

Thanks for your time

Hello.  I am adding file attachments to a table using the code below (and I know the consequences but need to do what is required) that is on a submit button.  As you can see, I am adding different fields to the table.  With what I have below only adds the Attachment field and the PropertyInspectionID field.  The other 3 do not get entered into the table.  I do a step thru and the info shows.  The sql statement has the order of the fields in the table.  I rarely work with arrays and I have a feeling that is where the problem is.

Can another set of eyes look this over and see where or what I'm missing?  

Thank you.

... John

    Dim cn As ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim filepath As String: filepath = Me.txtPath
    Dim sqlAttachment As String
    sqlAttachment = "SELECT AttachmentTitle, AttachmentDescription, AttachmentExtension, Attachment, PropertyInspectionID " & _
                    "FROM tblPropertyInspectionAttachment " & _
                    "WHERE PropertyInspectionID = " & Me.txtPropertyInspectionID
    Set cn = CurrentProject.Connection
    'arrays are zero based
    Dim fieldList(4) As Variant 'create array to hold list of fields to insert values into
    Dim valueList(4) As Variant 'create array to hold list of values to insert values into
    fieldList(0) = "AttachmentTitle"
    fieldList(1) = "AttachmentDescription"
    fieldList(2) = "AttachmentExtension"
    fieldList(3) = …
Hi Folks
I would really appreciate any comment/input on the subject
1. What is the best (free?) tool for converting .mdb back-ends to SQL Server?
2. Does it handle all (Access mdb) data-types, autonums and relationships?
3. How difficult / complicated is it to use?

thanks in advance
The current formula in Column E, shown below, concatenates Columns A, B, C and D to create a unique Product Number:


Open in new window

I need to update the current formula whereby if the specific text string "-AAU" is in Column B, it is not to be included in the final concatenated Product Number in Column E.

I have included a sample spreadsheet that has the current formula in Column E, and the updated, desired results I am looking for in Column F.

Thanks in advance for assistance with this...

Webinar: What were the top threats in Q2 2018?
Webinar: What were the top threats in Q2 2018?

Every quarter, the WatchGuard Threat Lab releases an Internet Security Report that describes and analyzes the top threat trends impacting companies around the world. Are you ready to learn more about the top threats of Q2 2018? Register for our Sept. 26th webinar to learn more!

I have a report on which there is a control called CtrlCode, if the information in this control ends with an X, I need the information already in control AccessTotalsSumOfWeight to be copied in control Total_X
I tried the following formula:
= IIF([CtrlCode] Like "*X","[AccessTotalsSumOfWeight]"
I get a mismatch

Any help would be greatly appreciated
How to re-read database for record with a key passed as an argument?

I have two forms: one a list of Products; and second, an edit page of additional Product information.  I have an event on On Dbl_Click on the List page with the following code:

DoCmd.OpenForm "Product_Edit", acNormal, , , acFormEdit, acDialog, Product_Nbr.Value

While I am able to see the Product_Nbr Argument being passed into the Product_Edit form, I'm not able to have this second form display this particular record.  It only displays the first record in the Product table.  

How to I tell Access to re-read the product table based on the Product_Nbr being passed into it?


LVL 26

Expert Comment

by:Brian B
You have entered this as a post not a question, so most Experts will not see it. If you still need assistance you may want to repost this using the "ask a question" button.

Author Comment

by:Andre Fischer
Thank you for your assistance.  I was able to get it to work by specifying the Filter as:

WhereCondition:="Products.[Product-Nbr] = " & Me.Product_Nbr.Value,

Hi!  anyone have any luck with a "TELNET" like connection with MS ACCESS?    I send simple text commands to a connected unit via TELNET.    By TELNET I mean using a communications application that converses with a connected device via IP address.     Something like sending "readbuffer01" and a response like "0,00.4,2" is returned by the conneted device.   I do this over a TELNET application like Tera Term set up for IP of the connected device format for IP and TCP/IP port 3602 emulating a VT100 terminal.   Any way that ACCESS can send commands and listen for a response like these terminal programs.   I am basically trying to automate data collection over the tcp/ip connection and store in a form/table.

Hello Experts,

I wish to make a query like the attached image. I want to Sum the [Total] field for each record in the parent table "tblEOPs". I think this can be achieved with nested SELECT's. I just don't know how. Txs in advance.
General Access Database question - I have linked tables with column names that I refer to in Queries, Forms, and Reports.
Everything functions the way we need it to.

Is there a way of changing the Linked table column names and have the existing Queries,Forms, and Reports still function correctly?

Or, do I have to start with a new Linked table with the new column names and redo all the Queries, Forms, and Reports?

Thank you in advance for any suggestions offered.
I have a single user who just got a new computer and now can't embed MS Word documents in the OLE field of my Access Runtime application.   At first MS Office wasn't licensed, but we took care of that and still have the problem.  I disabled click-to-run, turned off User Account Control, went into the Trust Center of MS Word and enabled All controls related to the ActiveX Settings, I also turned off Safe mode.  I disabled all but the basic Add-Ins that are working fine on my system.  We are going to test disabling Anti-virus tomorrow morning, but wanted to see if anyone could think of anything else that might be causing this effect.  I can successfully embed other document types, like MS Excel Worksheets, WordPad document, but MS Word isn't allowing the Insert Object mechanism to embed a .doc or .docx file.

When Windows makes a shortcut Word icon on his start menu, it adds those two little arrows, like a compressed folder, not sure if that's related.  Before Office was licensed, he had two little gears instead.  He is using Windows 10 Pro, MS Office 2013 without the full MS Access and click-to-run was a background process, but killing that didn't help either.

Microsoft Access





Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.