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 have main form WorkOrder which user can select 1xlga, 1xworkordertype etc which are bounded to different tables.
this main form is having continuous subform payitem which means 1xworkorder can have multiple payitems
Now user wants to have more LGA, workordertypes for each workorder which in turn have payitems for each LGA
as we can't have nested continous forms so i thought to go with datasheet type
main form - workorder(single view)
subform - LGA, workordertype etc(datasheet view)
subsubform - payitems(continuous form view)

but when i tried this way datasheet view form not allowing me to add new LGA, workordertype etc in the form but i can add payitems in continuous form
NOTE:I am modifying these changes for existing main form - workorder with LGA, workorder type
subform- payitem continous form

As per my knowledge i am going with this design so is there anyother way i can implement the same so that existing workorder details should also get replicated with these changes but just they show that they have only 1xLGA, workordertype with many payitems in the workorder

please let me know if my question is not clear enough so .i can explain more clearly
thank you for your time

I'm having an issue get the Base64 HMAC on Amazon MWS correctly, I can get the SHA256 one to match perfectly but when I then try to convert to Base64 the result is incorrect, the code I am using to do the SHA256 part is as below

Dim lngLoop As Long
Dim oUTF, oEnc
Dim HMAC() As Byte
Dim strTemp As String
Set oUTF = CreateObject("System.Text.UTF8Encoding")
Set oEnc = CreateObject("System.Security.Cryptography.HMACSHA256")
oEnc.Key = oUTF.GetBytes_4("--------------")
HMAC = oEnc.ComputeHash_2(oUTF.GetBytes_4(StringToSign))
For lngLoop = 0 To UBound(HMAC)
     strTemp = strTemp & LCase(Right("0" & Hex(HMAC(lngLoop)), 2))

Open in new window

But I believe this has be passed to the Base64 encoder as binary or a byte, but i'm having trouble converting a string in Access to a Byte? Is there an easy way to do this?

I have continous form as subform with some bound combo boxes, text box bound and unbound.
User wants to move records which are displayed as row wise up and down
Say for example if sub form has total 10 rows and he wish to move row2 to row3 or vice versa
can you please let me know how to do this in ms access form
do i need to have up button and down button for each row with some vba code so if user click on up button it moves 1 row up or if user clicks on down button it moves 1 row down
or is their any other way to work around on this
I am new to MS access and VBA code
Please guide me on this
This is bit urgent so kindly help me
thank you
need to change firstdayofweek in an access report
Hi experts and thanks in advance for any help I will get on this one:
Im trying to show 3 different charts . my code is an upgrade from vb6 . I get only 3 dots on the chart space instead of lines. this is my code:
       Dim oChart As Microsoft.Office.Interop.Owc11.ChChart  
       Dim oSeries As Microsoft.Office.Interop.Owc11.ChSeriesCollection  

            For i = 1 To 300
                For j = 1 To 13
                    oReportForm.Spreadsheet11.ActiveSheet.Cells(i, j).value = Arr(i, j)
                Next j
            Next i

            ReDim XChart(300)
            ReDim YChart(300)
            Dim XChart1(300) As Object
            Dim YChart1(300) As Object
            minX = oReportForm.Spreadsheet11.ActiveSheet.Cells(1, 2).value
            minY = oReportForm.Spreadsheet11.ActiveSheet.Cells(1, 3).value

            maxX = oReportForm.Spreadsheet11.ActiveSheet.Cells(1, 2).value
            maxY = oReportForm.Spreadsheet11.ActiveSheet.Cells(1, 3).value
            For i = 1 To 300

                If oReportForm.Spreadsheet11.ActiveSheet.Cells(i, 2).value >= maxX And oReportForm.Spreadsheet11.ActiveSheet.Cells(i, 3).value >= maxY Then
                    maxX = oReportForm.Spreadsheet11.ActiveSheet.Cells(i, 2).value
                    XChart(i) = oReportForm.Spreadsheet11.ActiveSheet.Cells(i, 2).value * 100
                    maxY = oReportForm.Spreadsheet11.ActiveSheet.Cells(i, 3).value
We have some coding inside of Microsoft Access where we pull data out of a database, create a .pdf and then send to users using Outlook.  This works most of the time but probably once a week, the email is generated with the report and then it won't send (we get an undeliverable error):

Your message did not reach some or all of the intended recipients.

      Subject:      Daily Lab Reports for 6/24/2019
      Sent:      6/25/2019 9:12 AM

The following recipient(s) cannot be reached:

      QC Database Email Group on 6/25/2019 9:12 AM
            This message could not be sent. The client operation failed. Try sending the message again later, or contact your network administrator.


Diagnostic information for administrators:

Error is [0x80004005-0x000004b9-0x00000501].
Submit-Message failed: message id(1), failure enum(6), HResult(0x80004005), EC(0).

This doesn't tell me much so I have no idea why it works most of the time but fails sometimes...

I have no issues sending emails using Outlook (manually) but it just seems to be problematic when something else is kicking off opening Outlook and sending the email.  I did turn off the "never warn me about suspicious activity" in the trust center - otherwise, we get the popup every time the task would run to "allow" another application to send email.
Dear Friends, I need a VBA Access function that has two Array Arguments, the first array is a list of Items:

List of Items
The second array is a product structure, like this one:

Product Structure
So you have to do some calculations:

And the final result, should be this one:

Kindly note I have marked in Yellow the values that were grouped/sum

I hope you can help me. I have been fighting with this one for weeks.
I had this question after viewing Not Like IN Access Query not Working.

I have not like values in a query that keeps returning those specific values.  I do not have any "null" values included, nor "or" and even tried to put parenthesis around the value such as Not Like ("Phase").  It's still not working.  Any suggestions are welcome!
I have a client with an Access-based inventory system with very complicated pricing. The pricing is based on a query that performs a lot of calculations based on several tables. The pricing query is set up to work on all the records in inventory (about 50,000 items).

This query is used in pretty much every inventory report.

The problem I have is that even if a report has only a handful of items on it, the underlying pricing query seems to be calculating pricing for every item in the database meaning that every report is now becoming immensely slow.

As a test, I ran a report of all items for a vendor and I chose a vendor that only has 5 items. It was extremely slow. Then I deleted every item in the database except those 5 and the report was lightning fast.

In this example, the report is based on a parent query that pulls vendor and item data (including the pricing query) and the parent query has a record source that specifies just the one vendor. However, it appears that the pricing query is running on every item in the database and then subsequently being filtered based on the parent query.

How do I get Access to only run the child query (i.e. the pricing query) for the items that are on the report? I should add that the pricing query is a child query in dozens of other reports in the database so I'm hoping for a solution that isn't going to require a lot of coding dozens of times over.

Thanks in advance.
I need to append data to an existing Excel files worksheet

1:lLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

I have the following code provided by Daniel Pineault

Function ExportRecordset2XLS(ByVal rs As DAO.Recordset, _
                             Optional ByVal sFile As String, _
                             Optional ByVal sWrkSht As String = "Hardware", _
                             Optional ByVal lStartCol As Long = 2, _
                             Optional ByVal lStartRow As Long = 7, _
                             Optional ByVal lLastRow As Long, _
                             Optional bFitCols As Boolean = True, _
                             Optional bFreezePanes As Boolean = True, _
                             Optional bAutoFilter As Boolean = False)
    '#Const EarlyBind = True    'Use Early Binding, Req. Reference Library
    #Const EarlyBind = False    'Use Late Binding
    #If EarlyBind = True Then
        'Early Binding Declarations
        Dim oExcel            As Excel.Application
        Dim oExcelWrkBk       As Excel.Workbook
        Dim oExcelWrkSht      As Excel.WorkSheet
        'Late Binding Declaration/Constants
        Dim oWSHShell
        Dim oExcel            As Object
        Dim oExcelWrkBk       As Object
        Dim oExcelWrkSht      As Object
        Const xlCenter = -4108
    #End If
    Dim bExcelOpened          As Boolean
    Dim iCols                 As Integer
    Dim lWrkBk              

Open in new window

I need a function/macro that will randomly pick a video link from a database but have no clue how to get started, can anyone help please?
I'm using MSAccess with Office365 to run an application created with MSAccess 2K.

I have a routine that runs from a form until a stop button is pressed.  This routine worked fine in Access2K, however in Office 365 I get the following message:

The Macro or function set to the BeforeUpdate or Validation Rule property for this field is preventing <dbname> from saving the data in the field.

In fact, I get this same message when I attempt to click any control on the form.  I'm pretty sure that none of the controls use the BeforeUpdate event, and I know I am not using any macros.  I do use a number of DoEvents throughout the code, which are intended to allow access to check a flag that indicates the stop action has been requested.

I need to solve this problem so I can stop the routine!  Please help.
i used a sql command in Access vba code to add a record into a table ie INSERT INTO Results '  .  now i want to read another table that has 1 field in it and if it is a number add one to it and save it again .  if it is not a number then do not do anything with it.

your thoughts?
Access 2013 using combo box wizard with a linked table getting error  message 'no valid fields can be found'   See attachment
I am running a query in Access via VBA (actually a lot of them), they look more or less like this one:
CurrentDb.Execute "UPDATE [PRODUCT REQUEST] SET Status = ""Completed with rejects"" WHERE ID = " & RequestId
RequestId is basically the ID of the record.
I am able to make it work from the Immediate window  most of the times, sometimes only it does not work in VBA break mode.
I am not able to make it work from the module, at least in most of the times.
At first I thought it is something with quotes (", '), so I experimented with different settings and for a moment it worked in the form showed above, but after a few tries it stopped working.
Then I thought it is something with locked records, but it did not get me anywhere.
I tried using options like dbSeeChanges and dbFailOnError, but it did not help.
I never see any error message from VBA, the record is simply not being updated.
I would be grateful for some insight, since I am currently stuck with this issue.
Access ver. 1904, MS Office 365 ProPlus
I have a complex code question.
I did have the list box (LstProvNPI) as a single select only that worked flawlessly.  
Once a user selected a row, the data would populate fields within in my form.  
Once the record was saved a SQL insert statement would update the record back to tables in SQL.

The problem is now the user would like to multi-select, I do not know how to do that.  
I can no longer have the Dynamic list box populate fields.  What is the best way to do this?

Step 1:
When a member ID is searched from a form menu, the LoadMember Function is loaded when my form opens
Step 2:
The Load Member will display a list of choices in a unbound list box.
Step 3:
The on LstProvNPI_Click() property within the list box allows the user to select the entry and this will populate the fields on the form.
Step 4:
When a user clicks save, A call module will call a function to Insert date from all the fields on the form to SQL.
Step 1:
Function loadMember(HID As String)
Dim server      As New SQLServer
Dim rs          As ADODB.Recordset
Dim SSQL        As String
Dim n           As Long
n = 0

If Len(HID) = 9 Then
    HID = HID & "00"
End If

Call server.initializeToRSMS(False, False, True)
    SSQL = ""
    SSQL = SSQL & "SELECT DISTINCT HumanaID, " & vbCrLf
    SSQL = SSQL & "         [Last_Name] + ', ' + [First_Name]   AS [Member Name], " & vbCrLf
    SSQL = SSQL & "         Last_Name, " & vbCrLf
    SSQL = SSQL & "         First_Name, " & 

Open in new window

Dear EE,

Can you help to share info about any third party software to monitor any program exe, e.g. (Winword, or any software exe) is running or not and if not running then send email to a user.

I have a problem that has been Happening for years. It certainly
not alarming but I just want to clean up our program.

In a part that deals with a/r and check amounts I am running many
functions and I believe that one or more of them is getting interrupted
and thus produces incorrect results. This program is networked and
approximately used by 10 people at a time. Because there are many data
bases that are modified by these functions I was wondering if someone else could
unknowingly  be interrupting the functions?

I know that Access has a function to deal with this but I can not remember is name.
I am hoping some one could give me some guidance on a solution.
We are encountering an intermitent error upgrading a legacy Access 2003 application to Access 2016 for a client.  

Our office has done dozens of upgrades from 2003 to 2013 in recent years but this is the first one to 2016.  I don't know if the fact that this is going to 2016 is a factor, just thought I'd mention it.  When doing the upgrades we know what needs to be changed and all the 2003 to 2013 upgrades have gone smoothly.  Most of the functionality in this 2003 to 2016 is fine but one menu option is causing issues.  Issues I've never encountered before.

Sometimes the menu option will run fine but sometimes I get the following two messages when it executes.  Then the application shuts down.

Access Stopped Working
When I select the 'Check Online for a solution' I then get
Access not working 2
I have error handling in every procedure in the application but the error handling routines are not being invoked. When I get the Access has stopped working' message.

This error occurs on a client machine that is running Access 2007.

My first thought was to ask the IT support people to re-install Access but I wanted to see if the EE experts had any other ideas.

We have thoroughly tested this application in-house (and this menu option dozens of times) and never once had any issues or failures.  We use an exact copy of the clients data to do our testing.  

I realize I've not supplied specifics about the functionality of the menu option but I thought these …
I want to create a dashboard in an Access application which connects to data store in SQL Server. I have created a mock up of how I would like to display a table reporting actual to goal for employee hours.  The SQL for the actual and goal is no problem. I am looking for advice as to how you would build the query and then display it. Would you create a table-valued function or take some other approach?

 Employee hours - actual to goal.
MRO Report CodeI have a drug screen database that is using Number Types zeros and ones for substances that are tested and blank if not tested. I need to create a report that will return specific substances tested for each person and the result (i.e.: if 0 the substance was tested for, and it is negative, if 1 the substance was tested for, and it is positive, if blank the substance was not tested for.

Microsoft Access 16. I have report working for DOT results because they always test for the same substances, which I have each substance field in my report. I cannot get non-DOT to work because there are many possible substances that could potentially be selected for testing. I would like the report to pull for each person what substances they were tested for (only. i.e. each company tests for different substances) and the result.

A line for each substance that can be tested for DOT (i.e.: Marijuana, Cocaine, Amphetamines, PCP, Opioids, etc). Non-DOT has many more potential substances.
=Switch([Negative]="1","NEGATIVE" & IIf([Dilute]="1","/DILUTE","") & "  RESULT",[Positive]="1","POSITIVE" & IIf([Dilute]="1","/DILUTE","") & "  RESULT",[Cancelled]="1","CANCELLED TEST")

I can't figure out how to have the report only list substances specific to a test. I only want the specific substances that are tested to return on the report. On the DOT all substances are set always (never …
We have a financial application with a SQL backend.  There is a users group for the application users that allows them to write/edit the data in the tables.

Now, ownership has come to me and wants me to allow a member of that application user group the ability to access the tables via ODBC and MS Access, but have read-only permissions on the tables.  Every thing I've tried so far does not work.  He still can edit the data in MS Access.

Any suggestions?

My database is divided into a front-end and back-end. I regularly make updates to the front-end and then copy the front-end from my computer to my client's computer. Today, I added an attachment field (i.e. data type = attachment) to one of the tables in the back-end. It works fine on my computer. But when I copy the front-end to the client's computer, I get this error whenever I attempt to open a form that references the table with the attachment field in it:

The Microsoft Office Access database engine cannot find the input table or query ''. Make sure that it exists and that its name is spelled correctly.

When I go into the Linked Table Manager, it says it is pointed to the right back-end file. But if I check the box beside that table and click OK, the above error goes away and I can use the database without any further problems.

Or, if I execute this command:


it also fixes the problem. But I have to do that every time I copy a new front-end to the client computer.

If I delete the attachment field from the table, the above problem goes away and I can copy front-ends without any errors or the need to refresh links. But as soon as I re-add an attachment field to the table, the problem comes up again.

Any suggestions?
Access 2016 application:  Suddenly (I certainly did something dumb) in the Left Objects Pane no more Macros appear.  They should be there, I created them.  Tables, Queries etc... are still there but no Macros!

Also in the older versions, in order to make a specific Form appear on startup, that was done from the "Startup" menu.  How do we do this in 2016.


I want to create a search term generator using vba.  I have a table with several records each containing a different search phrase, [Search].[Phrases].  I want to randomly concatenate the records with a space between each to generate one long string that is close to but not exceeding 250 characters long.  Can anyone help on how I would do this with VBA in ACCESS 2016?

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.