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'm getting a syntax error on this line of code when the text of EquipName has an apostrophe in it like "16' BLACK UTILITY"

Me.txtEquipment = DLookup("[Equip Name]", "Equipment", "[Equip Name] = '" & Forms!frmScheduleWorkOrder!subfrmScheduleEquipment.Form!txtEquipName & "'")
Determine the Perfect Price for Your IT Services
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!


I have a point of sales module within the main accounting application , now when printing it is accepting only to print on A4 Paper which is a waste of paper, I have no problem when printing ledgers but a pos receipt must come on a thermal receipt printer and paper size is about 80mm. While discusing about this yesterday someone passed the below code to me to study it and see may be it can sort out the problem. To be honest I did not understand it how it works and where to put either in the report module or a function,kindly can someone help me on this, some may have come up with this before.

(1) It appears like it first opens the report in design mode and that is where I do not understand it!

Sub sSwitchPaperSizeToLegal(rptName As String)
'The purpose of this sub is to set the page size
'for a report to legal
Const SUB_NAME$ = "sSwitchPaperSizeToLegal"
Dim DevString As str_DEVMODE
Dim DM As type_DEVMODE
Dim strDevModeExtra As String
Dim rpt As Report

'Open report in design view.
DoCmd.OpenReport rptName, acDesign 'Open report in Design view.
Set rpt = Reports(rptName)
If Not IsNull(rpt.PrtDevMode) Then
strDevModeExtra = rpt.PrtDevMode ' Get current DEVMODE structure.
DevString.RGB = strDevModeExtra
LSet DM = DevString
DM.lngFields = DM.lngFields Or DM.intPaperSize Or DM.intPaperLength Or DM.intPaperWidth
'DM.intPaperSize = 5 'legal
DM.intPaperWidth = 800 '80mm
DM.intPaperLength = 32760 '3276mm
LSet DevString = DM 'Update property.
Hi Experts,

I have 2 sub forms within a main form and trying to accomplish the following
click on a checkbox in sub form1 and should add a new record in sub form2.
then should call the after update sub of a particular control in Sub form2.

Tried the following and does not work.
Private Sub Check1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    DoCmd.GoToRecord , , acNewRec
    Me.Parent.Patients_Daily_Medications_SubFrm.Form.Medication = Me.Medication
    'call Me.Parent.Patients_Daily_Medications_SubFrm.Form.medication.Medication_AfterUpdate()
End Sub

Open in new window

In my Access 2016 application I have some existing code, supplied by EE, that reads thru all the files in the passed directory.

It works perfectly but now I would like to revise it to read thru the file in order of date stamp, oldest file to newest file.

Here is the existing code:

Public Sub ProcessFilesInDir(passedIncomingDir As String)
    On Error GoTo Error_Handler
    Dim sFile                 As String

    sFile = Dir(passedIncomingDir & "*.*")
    Do While sFile <> vbNullString
        If sFile <> "." And sFile <> ".." Then
            'Debug.Print sFile
            ' process each file here,
        End If
        sFile = Dir     'Loop through the next file that was found

    On Error Resume Next
    Exit Sub

    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: ProcessFilesInDir" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

Open in new window

Any suggestions?
In my last question I was asking how to import data from an excel file to a table in access 2013.  The solution provided worked great.
So now we have a new challenge.  How do I append the data that was in the table that was created from the excel import to another table using VBA.
I know how to use an append query, but I need this done via VBA.

I am guessing it would look something like this:

CurrentDb.Execute "INSERT INTO  tbl_ParentInventory (Field1, Field2, Field3) SELECT Field1, Field2, Field3 FROM ImportedFile

I have a table "Booking" which should always have the following records for each posting:

ResID         SubAccount
9999          00
9999          01
9999          05
9999          09

I now would like to identify all records which do not have for example the record "09"  generated and are thus missing in this specific table. I am struggling to write the correct sql for access. Any help is very much appreciated...Cheers Michael
Hi Experts out there!

I want to populate the three controls on the point of sales form automatically immediately the productID is scanned;below are the required controls:
(1) Product name
(2) QtySold
(2) Selling price

The above controls (Product Name & Selling Price) are combos,While QtySold is a number control but I want it to be assigned a number called 1 (QtySold = 1 but can be editted in case a customer is buying more than one item) here my understand is that a combo box is simply a small query from the underlying table/tables , I still think they can still be looped on the basis of the change on the productID. The business logic here is that there shall be no manual intervention here.

ProductID combo :
SELECT tblProducts.ProductID, tblProducts.ProductName
FROM tblProducts;

Slling Price Combo:
SELECT tblProducts.ProductID, tblPricing.Prices, tblProducts.ProductName, tblPricing.Changedate, tblPricing.Status
FROM tblProducts INNER JOIN tblPricing ON tblProducts.ProductID = tblPricing.ProductID
WHERE (((tblPricing.Status) Is Null Or (tblPricing.Status)<>"2"));

Kindly see how can help here

I am operating Windows 7 with Access 2010.  I have been programming in Access
for close to 20 years. I also been using active X items within our programs. The one that
I need to have is the "Common Dialog" I use mostly for printer functions. I have this particular
active x in many of the forms we use. When I try try insert the common dialog box I get a
message that I don't have the rights to use them. I am not adverse to paying for the rights
if I new were to go to get them? I am curious as why this is happening now.
Hi Experts,
Looking for code that will open a form in design mode, loop thru all controls and change their name to "txt" &  their control source.

I create excel report using ms access. Basically i automated the entire process of transfer queries on access database into excel spreadsheet and format it using vba code. I run these vba code from  access vba editor. So one workbook has got multiple sheets.

Now, I need to do a vlook up on one of the columns in a sheet  and drag it till the bottom cell using a vba script which i can include in my access vba editor.

The lookup table is on the same woorkbook but in a different sheet.

Hope you got the point

Thank you
IT Pros Agree: AI and Machine Learning Key
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

I'm trying to convert this 32 bit code into something that can be ran in both 32-bit and 64-bit Microsoft Access.
Based on this website and what I have - I tried to apply those changes but still getting red.  I know if I have the right syntax the red should be gone.

What is the correct syntax I need to incorporate to allow this to compile with no errors please?

Here's the code and provided a screen shot of the highlight:
#If VBA7 Then
  Private Declare PtrSafe Function LoadCursorLong Lib "user32" Alias "LoadCursorA" (ByVal hInstance As Long, ByVal lpCursorName As Long) As Long
#End If
#If Win64 Then
   Private Declare Function LoadCursorLong Lib "user32" Alias "LoadCursorA" (ByVal hInstance As Long, ByVal lpCursorName As Long) As Long
#End If

#If VBA7 Then
  Private Declare PtrSafe Function SetCursor Lib "user32" (ByVal hCursor As Long) As Long
  Private Declare Function SetCursor Lib "user32" (ByVal hCursor As Long) As Long
#End If
I have a subform linked to a main form in access that updates a main table based on the H_ID.  

See Code Below:
Everything works flawless EXCEPT, when I am done updating and click my button the code is behind (cmdAddReason_Click) it works.  

The issue is before the code starts and I click from my main form to my subform a new record a blank line is added and assigned a record ID (Example we will say I clicked new member and record ID 123 is created).
The code as it functions below updates each record id as a selection is made, however, the original record ID (Record ID 123) is blank.  

Any suggestions on how to fix this?

My table will look like this:
Date         Record ID   H ID          Reason Type       Reason
1/11/18   123                M12        
1/11/18   124                M12        Other                    Other
1/11/18   125                M12        Consult                Med Review
1/11/18   126               M12         Follow-up             Reschedule

Private Sub cmdAddReason_Click()
  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant
  Set db = CurrentDb()
  Set rs = db.OpenRecordset("DSBP Member Case Information", dbOpenDynaset, dbAppendOnly)

'Make sure a selection has been made
  If Me.lstReason.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 Reason"
    Exit Sub
  End If
'Add selected 

Open in new window

I know how to use VBA code to import data from an excel spreadsheet to an access table.  All of it work great.
How do I tell the code that the headers are on row 3 and skip rows 4 and 5.  If its possible.
I am dealing with a very old database (.mdb), that in the VBA code has many forms that have functions (is that what they're called) that do not exist in the VBA code for the form.  I know I can navigate to the top of the code in the General section, find the items that don't exist under Declarations, click on them one at a time which will take me to that code section, and delete it.  But there are so many this would take forever.

Is there an easy way to find them and delete them?

I need to convert this from an MS Access Query to a SQL Server view. How would I write this in SQL to get the format in hours & Minutes?

[PRTTotalMins]\60 & Format([PRTTotalMins] Mod 60,"\:00") AS PRT_Total_Mins

Open in new window

In Access VBA function DateDiff- the firstweekofyear parameter can be set to vbUseSystem - but where can I check what the NLS API setting is set to ?
I have two tables  Tblreadinglist contains a list of books

ReadID autonumber
Level text
Title text
word count number
File location text

Table 2 Tblreadlistlink contains books kids have read

Readlist_id autonumber
Book_ID_FK id of book read from list of books
Student_id  fk of student
readdate date

I am trying to get a list of books the kids have NOT read but I am going around in circles.
Hello everyone.
When "upsizing" an Access application to SQL Server, the Upsizing Wizard offers the possibility concerning adding timestamp field to the tables: "Let system decide". Likewise, SSMA offers the same possibility: "Let SSMA decide". I'd like to ask: how exactly they "decide" about which table "needs" a timestamp field and which doesn't?
The result of such operations is that some of the tables get timestamps and some of them don't. But I couldn't tell how that decision was "taken".
First I thought that small tables (in the sense of the amount of fields they have) don't get timestamp field and big tables do, but then I noticed some small tables that got timestamp by SSMA, and some big ones that didn't. So what is the consideration that SSMA takes when making that "decision"? And when I want to make that decision myself, without SSMA or the Upsizing Wizard, what is the best practice? In other words: When is it recommended to add a timestamp (/rowversion) field to SQL tables? (Assume that it's going to be used as linked tables to MS Access in a multi-user environment, which demands to take good care of concurrency)... I was thinking it to be good practice to add rowversion to all tables. Or isn't it?
Thank you.
I using the following Access SQL and I receive an error 'could not delete from specified tables'.
DELETE tblGlobal.*
FROM tblGlobal LEFT JOIN tblExclusions ON tblGlobal.Account = tblExclusions.Emails
WHERE (((tblExclusions.Emails) Is Null));

Open in new window

Big Business Goals? Which KPIs Will Help You
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Hey experts....

(Ms Access 2K)

I need help writing a query that displays prospects in our database who have never placed an order.
Each prospect is assigned a customer number in our Prospects table.
We maintain a second table which is our Orders Table which I call InvoiceHistory
I need a way to get a query of Customer Numbers that do not ever appear in the Orders table.

The query below does a good job of showing me how many invoices we have generated for each customer,
however, you help is needed to include customers who do not show up in the query because no record with thier customer number
exists in the InvoiceHistory table.

SELECT Prospects.ContactName, Count(Left([InvoiceNumber],5)) AS Expr1
FROM Prospects INNER JOIN InvoiceHistory ON Prospects.CustomerNumber = InvoiceHistory.CustomerNumber
WHERE (((InvoiceHistory.CustomerNumber)>0))
GROUP BY Prospects.ContactName, Prospects.CustomerNumber
ORDER BY Count(Left([InvoiceNumber],5)) DESC;
I'm new to access and I can do simple forms and tables. But I'm trying to create a database to that hold multiple records (historical records) for all committees meeting.  Some of the members belongs to multiple committees and attends meetings either monthly or quarterly.  By building the table and form, it will allow to pull report that calculates the members attendance and percentage at the end of the years.  I'm stuck and can use some help.  PLEASE ANY ASSISTANCE will be greatly appreciated.
Is there a way to put particular reports and sub-reports into a group in the Navigation Pane?  I'd like to create a group named Purchase Order and another group named Orders and move specific reports and sub-reports into those groups.
Wasn't there a setting in Microsoft Access whereby you change a field name it would go and update any query or form that had that field - updating to the new name?  

And if you turned that setting off thereby it would make Microsoft Access run faster?

Do any of you recall what that setting's name was or what I am talking about?  And if I get the technical name of it - then I do a google search on some VBA code to turn off that feature... or if you know it already please paste here.

Btw is there some VBA scripting out there that does the above automatically (without paying for a license)?
I've already did the scripting to change table and fieldnames automatically.  But then I have to go in and change all the queries and forms where the previous names were.

Thank you in advance...
I would like to find out how to secure the tables in MS Express SqL Servers 2016, I’m totally shocked to what I saw today when a prospective client called me to showcase our software which also has both the MS Access Database (As Back End) and SQL Server as Back End. The client has a standard software off the shelf but can move around within tables in SQL Server 2014 freely and was able change setting from the same software, now because of what I saw I cannot supply my software with SQL Server Backend until I know how to protect the tables from intruders. It is now clear why he was calling for our software is because he massed the unprotected back end, thanks god, I have not yet started supplying the SQL Server as backend except MS Access 2016 which has the back end encrypt with a password.
(1)      If in Ms Access, we can encrypt the backend through a password how can we do the same with MS Express SQL Server 2016???????
(2)      If the there are ways of protecting the tables, then doesn’t that affect the users and re-linking the tables, though with Ms Access the re-link table manager has no problem with that, now how are about the MS SQL Server 2016????????


Access VBA - looking for a way to apply a filter to a subreport in a report with vba or alternately a way to change the record  source for a subreport with vba

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.