[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


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

While writing a custom ribbon for Access, I'm having troubles removing 2 elementsin the info tab of the backstage. See attached image, I would like elements circled in red to be removed.

RibbonCreator did not help much as it hardly support the backstage view (or is that just the shareware ?), and isMso provided by Ribbon Wizard were frowned upon by Access (GroupFileProperties and GroupFileLocation).

Below, the XML I obtained:
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
         <command idMso="Help" enabled="false"/>
    <ribbon startFromScratch="true"/>
         <tab idMso="TabInfo" visible="true">
                   <group idMso="GroupDatabaseCompact"/>
                   <group idMso="GroupDatabaseEncryption" visible="false"/>
                   <group idMso="GroupFileProperties" visible="false"/>
                   <group idMso="GroupFileLocation" visible="false"/>
         <tab idMso="TabOfficeStart" visible="false"/>
         <tab idMso="TabRecent" visible="false"/>
         <button idMso="FileSave" visible="false"/>
         <tab idMso="TabSave" visible="false"/>
         <tab idMso="TabPrint" visible="false"/>
         <button idMso="FileCloseDatabase" visible="false"/>
         <tab idMso="TabHelp" visible="false"/>
         <button idMso="ApplicationOptionsDialog" visible="true"/>

Open in new window


I have a subform in my form. In this subform I have several columns. In first one I have a combo field filled with data from a table A. When I choose a particular value from this combo I'd like to display appropriate value in the next column in my subform as a text field. This value is also from table A. How I can obtain?
Access 2010   vba

I'm updating a field in a subform with a calculation from 2 other fields.

Is there an easier way to write this  code ?

' =====================================
Dim g As String
Dim j As String


' ============================================
'  < 4%
If g < 4 And j < 250 Then
Me!ACTION_NEEDED_FRM = "Update Quote"
End If
' < 4%
If g < 4 And j > 250 And j < 1000 Then
Me!ACTION_NEEDED_FRM = "RSVP/Director Approval"
End If
' < 4%
If g < 4 And j > 1000 Then
End If

' =========================================
'  >4  and < 8%
If g > 4 And g < 8 And j < 250 Then
 Me!ACTION_NEEDED_FRM = "Update Quote"
End If

'  >4  and < 8%
If g > 4 And g < 8 And j > 250 And j < 1000 Then
Me!ACTION_NEEDED_FRM = "RSVP/Director Approval"
End If

'  >4  and < 8%
If g > 4 And g < 8 And j > 1000 Then
 Me!ACTION_NEEDED_FRM = "RSVP/Director Approval"
End If

' ===========================================
'  > 8%
If g > 8 And j < 250 Then
Me!ACTION_NEEDED_FRM = "Update Quote"
End If
' > 8%
If g > 8 And j > 250 And j < 1000 Then
Me!ACTION_NEEDED_FRM = "Update Quote"
End If
' > 8%
If g > 8 And j > 1000 Then
Me!ACTION_NEEDED_FRM = "Update Quote"
End If
I have a form with a sub-form.  If the user is on the sub-form and leaves the 2nd field on that sub-form empty, I would like the tab to take him to the next field on the main form.  Is this possible?
Is there a way to display a field that has currency in it with the $ at the very left-hand edge of the field and the number at the very right-hand edge of the field?
A few days ago an expert provided a solution so that if I double-click in a text box, the entire contents of the text box get highlighted, not just the work I d/c on.  Here is the code as provided:

Private Sub txtName_KeyPress(KeyAscii As Integer)
On Error GoTo Err_txtSymbol_KeyPress

    Const vbKeyNone As Integer = 0

    If KeyAscii = vbKeySpace Then
        KeyAscii = vbKeyNone
    End If

    If Trim(Replace(Me!txtName.Text, "_", "")) = "" Then
        Me!txtName.SelStart = 0
    End If

    Exit Sub

    MsgBox "Error Number: " & Err.Number & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Source: " & Err.Source
    Resume Exit_txtName_KeyPress
End Sub

Open in new window

However, I just discovered that if I d/c on the field and it is a currency field, it does not highlight the entire field.  How can I fix this so it does highlight the entire field?
excel document every time i open asking to save changes even though i have not made changes? how to avoid this message prompt every time(even i made no changes) which i have to click no button
please advise
I have two excel sheets  TagDesign and TagActual    There are many entries which are similar but slightly different  (For example TG-1234 and TG1234)
Can we have a list in excel  showing the entries  with difference in just one character
(It can be MS Access or Sql Server or a small macro by which I can get this result)

  The typical result I look will be something like
   Tag design        TagActual
       TG-1234          TG1234
       P#3456            P3456
       PVT 12             PVT-12        ( Space and hyphen)
Using Microsoft Access (2003)
I have a form with a text box called PicFileName and an Image Control called PersonImage
The intent is that the following code will cause .jpg (picture) to appear in PersonImage, without the .jpg being embedded in the table behind this form (in order to save space)
The following code accomplishes this perfectly:

Dim strPicName As String
Dim strPicPath As String
Dim strSource As String

strPicPath = C:\Pix                 C:\Pix is the folder containing the jpg
strPicName = ABCD.jpg       ABCD.jpg is the filename of the picture

strSource = "" & strPicPath & strPicName & ""              in this case it will be C:\Pix\ABCD.jpg

If Not IsNull(Me.PicFileName) And Me.PicFileName <> "" Then
    Me.PersonImage.Picture = strSource
End If

As I said this works perfectly in all computers on my LAN, EXCEPT for ONE which returns an error 2114 saying that this code is not supported OR the jpg is too large!  I have reduced the size of the .jpg to a few K, to no avail.

ALL COMPUTERS ON THE LAN USE THE SAME OS (Win 10 Pro), AND THE SAME VERSION OF ACCESS, AND GET THEIR JPG FROM THE SAME FOLDER,  and the above process runs perfectly for the last six years!!

QUESTION: What needs to be adjusted in the one computer where this is not working?
access 2010
subform calculation not doing correctly

These fields are from a Linked table in sql server.



This value(RENEWAL_ESCALATED_PRICE_INCREASE) is always coming out to   0(zero)   ?

Here is my data:

| OEMCurrentPartNumber | OEMDescription       | OEMSubNumber |
| 19M7796              | SCREW                |              |
| 19M8365              | USE PN 19M7796       | 19M7796      |
| AT256104             | USE PN 19M7796       | 19M7796      |
| CH12356              | USE PN 19M7796       | 19M7796      |
| M134162              | USE PN 19M7796       | 19M7796      |
| M74138               | USE PN 19M7796       | 19M7796      |
| AE42460              | BEARING WITH HOUSING |              |
| DA25307              | USE PN AE42460       | AE42460      |
| AH223030             | USE PN AH227807      | AH227807     |
| AH227807             | FRAME                |              |
| AH208565             | USE PN AH223030      | AH223030     |

Open in new window

| OEMCurrentPartNumber | OEMDescription       | OEMSubNumber |
| 19M7796              | SCREW                | 19M7796      |
| 19M7796              | SCREW                | 19M8365      |
| 19M7796              | SCREW                | AT256104     |
| 19M7796              | SCREW                | AT257892     |
| 19M7796              | SCREW                | CH10599      |
| 19M7796              | SCREW                | CH12356      |
| 19M7796              | SCREW                | M134162      |
| AE42460              | BEARING WITH HOUSING | DA25307      |
| AE42460              | BEARING WITH HOUSING | AE42460      |
| AH227807             | FRAME                | AH223030     |
| AH227807             | FRAME                | AH208565     |
| AH227807             | FRAME                | AH227807     |

Open in new window

| Item       | OEMItem  | Description  |
| AM19M7796  | 19M7796  | Screw        |
| AMAE42460  | AE42460  | Bearing Assy |
| AMAH227807 | AH227807 | Frame        |

Open in new window

The purpose
I am cross referencing OEM part numbers with my companies part numbers.

In the RAW_OEM_DATA table, any rows without a number in OEMSubNumber are the "latest" numbers. All other numbers are sub numbers that reference the "latest" .

The [OEMItem] in COMPANY DATA is the "latest" OEM number. when a user searches a part number my query searches the OEMSubNumber column and brings back the OEMCurrentPartNumber to match with the [OEMItem] in my companies numbers.

To get the raw OEM data to the format in the FINAL_OEM_DATA is what i'm trying to achieve. But I don't need all 1.6 million records in that table. just the ones that match the [OEMItem] from COMPANY_DATA.

Final Result
Both the RAW_OEM_DATA and COMPANY_DATA tables reside on my web server so employees can access via the secure website. It is a aspx site with MS SQL server. Twice a year i have to run my companies new item numbers through this process and update my tables with the new data.

Couple of notes
Some numbers in RAW_OEM_DATA "piggy back" one another. example
AH208565 subs to AH223030 which subs to AH227807
That means not all [OEMSubNumber] will be the "latest" OEM number.

The description for the "latest" OEM number has to replace all the "USE PN ***" numbers

My process in the past
run quite a few queries to get the OEM raw data to the above desired result
Need help with this syntax for a complex Iif statement in a Microsoft Access query.
The following is taken from the Contacts MS Access database in a query.

Contact Name: IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[FirstName]),IIf(IsNull([FirstName]),[LastName],[FirstName] & " " & [LastName]))

Obviously if the First and Last Name fields are not blank then it goes and makes Contact Name's value those two fields combined.

I'm happy with that - however I have another contingency where I want to override and only show PreferredName field to show as a priority over first and last name.

How do I change the above Iif statement to include that contingency?  

Again, don't want another solution - just want the updated syntax to include PreferredName field and not first and last name if it is not blank.   If PreferredName field is blank then use both First and Last Name fields as stated above.

Thank you in advance for your help with this question!  Also not looking for another similar type of solution.  Only getting the correct syntax using Iif statement only.
I am using a text box on an Access form to find data on a subform. It works fine. I want to take the input from the text box on the form
to run a query. My text box is using the "Like" criteria. See below.

Private Sub TestPart_Change()
Dim strSQL As String

    strSQL = " Desc1 LIKE '*" & Me.TestPart.Text & "*'"
    Form_Siliconix_Br17_subform.Filter = strSQL
    Form_Siliconix_Br17_subform.FilterOn = True

End Sub

I tried using the below formula on the query in the criteria row.

[Forms]![Siliconix-Branch17]![TestPart], but this returns NO data.

I suspect the query is looking for an exact match to whatever data was passed from the form and if there is none, it returns no data.

I assume that it should be some "Like*" criteria in the query.
What do I put on the criteria row in the query so that it knows it should search for whatever data is passed from the text box on the form?

Thanks for any help!!!!

In MS Access, if I have a 10 character Cost Center field formatted as Text, and I want to replace the letters with numerals so I can convert the field to a numeric data type...how would I go about executing a find/replace operation so that the letter A would be replaced by a 1, the letter B would be replaced by the number 2, etc.?

The syntax of the current data is (where # is a number and ? is a letter)
####?####, or

Reset Auto Number field in Microsoft Access


I have a split database with fontend and backend files. There is a table tblinvoice which has a DCID field as a primary key field. This field has relation with another field in a table which is the base table for a sub form. What I want to do is reset the auto number to start from 1 as I have completed the testing and now want to implement the access database. When I did the testing I generated few records which incremented the auto number Field.

Here is what I have already tried:

1) Unlinked the relations between the main form table and subform fields.
2) Remove the field as primary key
3) Create an Append Query which will append the value to 1.

I tried to run this query on the table opening the back end file and also tried to run it on the frontend file.

But its is not working.

Any ideas other then what I have tried above. ? On what file do I need to run the append query - Font End or the back End file ? Can anyone guide me step by step what needs to be done. I am not at all an expert but a starter.

Thank you for your help

ACCESS 2013 Query
I want to delete records from table 1 with inner join.  My query is

Delete Table1.* from Table1
Inner Join Table2 on Table1.invoice=Table2.Invoice

The above query works but it deletes records from Table2.  I switched Table1 with Table2 but it still deletes records from Table2.  How can I delete records from Table1?

Thank you very much in advance.
In Access 2010 is there a way to make the ControlTiptext display longer without writing my own module?

I need a technique or code for Access 2002 where I supply a table name and the results list field names, data type (optional) and row count for each field.
Can't seem to find code to set the value of all toggle buttons on a form to true or false.

I have over 50 toggle buttons on the form so need a way to do this in a For Loop.
How could i write Nz function with this expression:
LaborHours: Sum(Left([ActualLaborHours],InStr(1,[ActualLaborHours],":")-1)+(Right([ActualLaborHours],InStr(1,[ActualLaborHours],":")-1))/60)

Open in new window

Hey, guys, I am by no means an expert in Access, but I've used it plenty to maintain a database. I thought i'd ask an expert.

HR is wanting to be able to go back to (Example) October 22nd 2018 to see how many employees we had in the active table and their names at this date to get an accurate count of employees in the database that day. Is there a way for me to setup something in Access to achieve this for HR? The only thing I can think of is running a query that will export the table to an excel sheet into a folder on the Shared Drive. So every day at the end of day run that query so it'll create an excel export. What do you guys think?
Access report;
I need to convert   10000 hours into       1 years, 1 months, 21 days, 16 hours

I have a field on my report showing the hours and I need to create another one to show the hours converted in years, months, days, hours.
I'm trying to use the update query  builder in access 2013 and get an invalid name error when I try add 2 fields to update another.

Expr100: [mai]+[mae]    !!!  Gives the ERROR >>>  "[mai] + [mae] is not a valid name"
Expr100: =[mai]+[mae]    !!!  Gives the ERROR >>>  "=[mai] + [mae] is not a valid name"
Expr100: Sum([mai]+[mae])  !!!  Gives the ERROR >>>  "Sum([mai]+[mae]) is not a valid name"
Expr100: Sum(CStr([mai])+CStr([mae]))  !!!  Gives the ERROR >>>      Sum(CStr([mai])+CStr([mae]))
Expr100: [mai] or Expr100: [mai]  works fine!

Access Report: I have inserted a sub report but it is not auto sizing and it seems to be on top of the main report instead of being imbedded and resizing as I am accustom to.

I have the Auto Height set to Yes for the page header where the sub report is. I also have the sub report set yes for Can Grow and Can Shrink.

**I work in MS Access. So I don't have access to query performance tools**.

Anyone know a best practice for dealing with joins on text fields?

If I have many data sets in the 500k to 1mil record range with a Cost Center field that is Text data type because the Cost Centers are 10 char with 4 numbers, a letter,  and 5 numbers in the format xxxx?xxxxx where x is a number and ? is a letter. I always both index and do joins on this field. My understanding is that joining fields with the numeric data type is more efficient than on text fields,  so I'm considering, to improve performance, creating an additional CostCenterID field with the letter removed so it would be a 9 length number of the Long data type. It does not appear that removing the letter will create any duplicate values.

My question is what is the best way to do this?
Lets say I have two tables:
Table A has 1mil records and a Cost Center Field of Text data type.
Table B has 25k records and a Cost Center Field of Text data type.

I could add a calculated CostCenterID field to my source tables with an Update Query using a formula such as Left([tA]![Cost Center],4) & Right([tA]![Cost Center],5) AS CostCenterID. The new field would be a 9 length number of Long data type.

I don't like altering my source tables but this seems like it would work . How do db admins typically handle this kind of problem?


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.