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 am working on a report that has a sub-report on it.  The sub-report has a text box on it.  Here is my challenge...

If the textbox on the sub-report is the same text value on all of the sub-reports, then they should all appear on the main report.  But any time the text field value is different, then the sub-report is to be on it's own main report.  Make sense?
What is wrong with this?

=DLookup("[CombinedDesignElements]", "tblOrderDetailSizeQtyInfo", "[OrderID] = " & Reports!rptOrderForm!txtOrderID & " AND [OrderDetailID] = " & Reports![subrptOrderDetail]![txtOrderDetailID] & ")
Can you have more than 3 colors with Conditional formatting?
it is possible to hide the Ribbon and keep the tabs?   I know how to hide the ribbon.  See attached

Is there are way to send update tasks to the MySQL server that releases the user interface to move onto other things?

I'm trying to improve the performance of my application when a user makes a change that effects data in other tables, as it triggers a handful of various update and delete queries.

I thought I could use the trigger capability in the MySQL table, and simply append a temporary  table with needed parameters, and then have the trigger  run though the list of queries that use those parameters and all of this would run on the server and the UI would be released so the user could navigate to their next task instead of waiting for all this to finish processing.

I know the Events I set up in MySQL can run in the background and run a list of queries at a set interval that don't effect the UI, so I'm thinking there must be a  way to trigger a similar capability.

So far I'm still getting the spinning cursor wheel while the UI waits for the trigger queries to run.  I need more speed Scotty!!!  What am I missing?

The UI is MS Access using VBA and passthrough queries with MySQL ODBC connections to the MySQL server.

And I'm using Navicat for MySQL for all my MySQL development.

I am trying to open the window pictured in the following link using a commmand button in a form. Is it possible that anyone can show me how to do that?

Thank you in advance!
Please see the attached file.  When you open the table you will see this data:

Sample of Table Data
I need to create a query that takes the information and uses the fields that are not "0" and combine them into a result that looks like this:

CD-2, GH-3, IJ-6, OP-2, ST4

I don't know where to begin.  Any help much appreciated.
I have again another challenge on a sub query in Ms Access , what I want from the sub-query is to pick only the figures with the matching parameter :


If  Mnths  = ZZDone  then this query should pick whatever is in TaxCum and slot it in a new column called TaxPaye

Below is the query I'm trying to sort out:

SELECT QryMonzeSalaries.EmpID, QryMonzeSalaries.Fname, QryMonzeSalaries.Lname, QryMonzeSalaries.PayDate, QryMonzeSalaries.ZZDone, QryMonzeSalaries.Paye, (QryMonzeSalaries.FinalPaye) AS Taxcum, QryMonzeSalaries.Mnths, IIf(([Mnths]=[ZZDone]),[Taxcum],0) AS TaxPaye
FROM QryMonzeSalaries;

The problem here is how to turn IIf(([Mnths]=[ZZDone]),[Taxcum],0) AS TaxPaye into a sub-query , is it possible to sort out this?????????

The reasons why I need to be grabbing the previous raw figures is for use to calculate the differential Cumulative tax.



I have an application written in Access 10.  It is installed in over 100 sites.  Just recently some users have been getting the attached error message after years of running the program without a problem.  It seems to be associated with the Access 10 Run Time.  If I repeatedly click on Ignore, Cancel, or Retry it tries to install it and fails, and rolls the update back.  If I do this 4 or 5 times and then start the application, it runs.

Any clues?

Thanks in advance.

I used the Event Procedure to hide the ribbon.  Now how do I create the accde when I cannot see the ribbon?
I have a form that has a sub-form.  The subform is a datasheet.  On the main form is a checkbox named chkPrint.  when the user clicks the checkbox I want only the records in the subform to show those with a Code that equals "P".  

Here is what I have but it doesn't work.

    Me.subfrmVendorList.Form.Filter = "Code =" & chkbxPrint.Value
    Me.subfrmVendorList.Form.FilterOn = True

 I have thousands of emails in Microsoft ACCESS tables.
 Is there a software out there that is capable of either linking to ACCESS table or Import a table from ACCESS and run email verification?

We have 2 users of an access 2010 application who get an error ''could not update;  currently locked'. I have tracked down the row in the table that these forms are accessing and can see no issues at all, e.g. signs of corruption. Is there anything else that can be done. There are a few forms that show/update date in this particular row, unsure really what may cause this, but as its affecting multiple users each with their own FE the common denominator being that row, what can be done.
If I connect to the backend of the system and try and manually edit a single column in the record it gives the same error.

My database use several forms, each with a custom ribbon as well as a default one loaded at application level that customize the backstage view.

I need to keep track of each ribbon reference, I maintain a collection for that purpose.
The database have an autoexec macro wich load the first form, but default and 1st form ribbons (default and the one attached to the 1st form) are loaded after the 1st form.
How can I identify each ribbon in those conditions ?
I am exporting a number from Access 2010 to Excel and Excel is labelling the cells with the error Number Stored as Text.

The export VBA uses:
DoCmd.OutputTo acOutputQuery, "Beneficiaries Report Anonymised", acFormatXLSX, , True

The query field in question is:
IIf(IsNull([dob]),"",FormatNumber(getage([dob]),0)) AS Age

[dob] is the person's date of birth

The GetAge function is:

Public Function GetAge(pDOB As Date, Optional pDate As Date = 0) As Integer

'get age as of a specified date, or today
   GetAge = 0
   If Nz(pDOB, 0) = 0 Then Exit Function
   If pDate = 0 Then pDate = Date

   GetAge = DateDiff("yyyy", pDOB, pDate) + (pDate < DateSerial(Year(pDate), Month(pDOB), Day(pDOB)))
End Function

When the number gets to Excel it is on the right of the cell, so that indicates it's a number, but it persists with the little green error flag.

Am I missing something, or will I have to write some code to clear the error in Excel.
I have an access form with a subform on a table. The field in the subform has an on click event on a field but it gives an error that I don't understand when I click on the field. I want to grab the value in that valves field.

Option Compare Database

Private Sub Valves_Click()

End Sub

Open in new window

I have other subforms that react the same way but they all work.

This event has no code in it yet.
I know how to hide the Ribbon in Access.  It is possible to still give them the Spelling and Refresh buttons?
I am trying to export the results of a query into an existing excel workbook and I'm having a hard time getting this code to work completely. It works but it doesn't always skip to the next blank cell in column B. When I fire it for the first time it works but then if I fire it again after that it just overwrites the last entry I just put into the workbook. It's not going to the next blank cell in column B after that first initial run. Can anyone help me fix this code to do just that so every time I run it the entry will go into the next blank row where there is no value in column B? There are values in row 1, then a blank row in row 2, then the headers in row 3, and then the values start in row 4. The last entry I have currently is row 350. So when I would run this it would put the results in row 351. Then when I would run it again instead of putting the value in row 352 it would put it in 351 and overwrite the values I just put in there. Also, how can I paste the values and then run the format painter to make the format of these new entries the same as the last row with values from Column B to H? Any help would be greatly appreciated.

Private Sub cmdExcelExport_Click()
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean

blnEXCEL = False
blnHeaderRow = True

' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")

Open in new window

I have a form that has a listbox.  Also on the form is a textbox.  When the user enters a letter (will always be a single letter, in the textbox, I want the listbox to be filtered and only show the records that have that selection code in the table.  I've tried this and it isn't working:

Private Sub txtFilteredVendorCode_Change()

    Me.Form.lstVendors.Filter = "SelectionCode like '" & Me.txtFilteredVendorCode.Text & "*'"
    Me.Form.lstVendors.FilterOn = True

End Sub

Open in new window

How would I write a query that would delete everything from table1 based on what is in table2?

Example: table1 has 10,000 records and table2 has 3,000 records that match the ones in table1 using a key field match.

so after running the delete query table1 should now have 7,000 records.
I have a number field in access query that I need to be exactly 10 character long with leading zeros and including the cents, but no decimal.

What I have formatted now:
Amount: Format([GROSS],"00000000.00")
which displays
What I need displayed is
Access need help understanding why this query will work as a SELECT Query but when I change it to a DELETE Query it keeps asking for me to specific the table containing the records I want to delete.
I want to delete the records in MyTable based on the records in Query2.

DELETE MyTable.TAG_Number, *
FROM MyTable INNER JOIN Query2 ON MyTable.ID = Query2.ID
WHERE (((MyTable.TAG_Number)<>"False"));

It would be great if someone could help me out with following query.

I am looking for MS Access VBA script which can copy range of cells (A1:S35) from a spreadsheet(sheet name: aaa) from one workbook(aaaa.xlsxs) and paste (paste special with format) on to another workbook(bbbb.xlsx) spreadsheet (sheet name :aaa).

Thank you
I have a date in Access field that I need to reformat as 03062019, from the current format of 3/6/2019

Is this possible ?
Current code:  Issue: Int([Administrator_ApCheque].[CHEQ_DATE])
I need to be able to use the LinkedIn API to extract data for a selected individual, in conjunction with Microsoft Access and VBA.

I envisage that I would supply their LinkedIn profile ID, and then I can extract certain fields. But I can't find any info on this API, so wondered if anybody has had any success and if so, could they supply me with the relevant code please?

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.