Microsoft Access

221K

Solutions

52K

Contributors

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

Hi Experts...
I have form named frmProject.. and I have 3 text box
1-txt for startDate
2-txt for project duration in days
3- txt EndDate
when I put startDate like 1/1/2019 ..and I have as example 30 days for project duration
what I want that in after update the  txt for project duration in days I will obtain the end date of project in  txt EndDate without friday and saturday
i mean it will appear in 11/2/2019..
thank you
0
Hi Experts,
I have two questions about the "Add-Ins" on the tool bar in Access 2016
1). How do I create an " Add-Ins"  that shows in the tool bar next to the "database Tools" when you open the database.

2). I have a database that created by an co-worker who already retired.  now I need to convert this DB from MS 2007 to MS 2016.  This DB has an "Add-Ins" in the tool bar, when I click in the "Add-Ins", it has few options, such as file, edit, Event, under the Event, it has add new event etc.  I know MS 2016 will not carry this Add-Ins and I have to re-create it.  How do I know how this co-worker created this Add-Ins?  Under the Event, when I click the "New Event" it actually open a new form.  I just don't know how to create it.  Any help will be very appreciate.

Thanks,
0
I am using MS Access 2003 and have for many years and with many different Outlook versions sending emails from Access using:
Dim objOL  As Object
Set objOL = CreateObject("Outlook.Application")
........

Lately only on one computer  Set objOL = CreateObject("Outlook.Application") fails and I get an ActiveX cannot create object error 429 (as I recall).  Oddly the first error started on Windows 10 but I have another Windows 10 Computer running the same code and version of Outlook (2016) with out issues.

Here is what I've tried without any luck:

1. Same issue whether Outlook is running or not

2. Replace  objOL = CreateObject("Outlook.Application") with  objOL = GetObject("Outlook.Application")

3. Confirmed all Add Ins are the on both computers

4. Confirm all VBA references are the same on all computers


Any suggestions are appreciated.  Thank you.
0
If we open our accdb in Access 2016 we see that some forms show up in the Taskbar. I remember it was a setting in previous versions but It can't find it anymore. Does anyone know how to get rid off these forms in de the Taskbar. I only want to see the Access icon in the taskbar
The three seperate tabs with Mijn CRM\ shouldn't be visible
0
Hello Team

I have a form which contains a listbox, populated with values from a query

What I would like to achieve is, when the user double clicks an itemData, of that selected line, copy the value from Column(0) and then return to an opened form displaying in a text box the value copied from the Column(0)

So here's the forms and controls that are being used.

Search form name:  frmGroupIDSearch
Listbox name: lstTracker
Listbox datasource: qryWorkTypeStatus
Value to be copied: Column(0) from lstTracker

Now, there is another factor to take into consideration.  When the frmGroupIDSearch is opened, it's load event is being triggered from one of three forms so what I also require is to have some sort of reference given to the form that is being used to load the frmGroupIDSearch so that when the ItemData line is double cllicked, the code knows the correct form to return the value to a textbox called txtID.

For example:

There are three user forms, frmAudit1, frmAudit2, frmAudit3

If i trigger the load even of the frmGroupIDSearch form from frmAudit2, when the frmGroupIDSearch is loaded and one of the Itemdata lines is double clicked, I need the code to know to return back to frmAudit2 and place the value from Column(0) into a textbox called txtID.

My apologies if I have made my explanation  a bit long winded.

As always...any assistance is much appreciated.

Sincerely

Dale
0
First of all, I am aware that forms are not typically printed, but that's what I need in this case. I need interactive functionality that reports don't provide.

I would like to print two forms together at the same time so that they print one on each side (duplex print) on a single sheet of paper instead of each printing on separate pieces of paper which my current setup results in. Form 1 is always the same. Then depending on what is entered into one of the text controls on Form 1, Form two will be any of 9 other forms. So:

Form 1: always a form called "ProductionReport"       Form 2: varies based on Form 1, not always same

My printer is already set up for automatic duplex printing; my printer settings are not the problem.

The only way I can think of to print two forms on one paper is to print them from the same print dialogue box, and the only way I can think of to do that is to create a macro or write some code that I can run from a macro using RunCode to print all open tabs (database objects.) It is already set up so that once form 1 is filled out, the corresponding form 2 is automatically opened.

It should be noted that the printing problem I am trying to solve is purely a matter of convenience and saving paper, and has nothing to do with the underlying functionality of the database.

I don't know if what I am trying to do is even possible, but if it is then I'm not getting anywhere and I need some help. Any suggestions?

(I am using MS Access 2010)
0
I created a code for my database to open a recordset and view, edit and update the table, I can't get the code to open the table...

Option Compare Database
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String

Private Sub CustomersRecordset()

    strSql = "Select * FROM tblCustomers;"
   
Set db = CurrentDb
Set rs = db.OpenRecordset("tblCustomers")

End Sub
0
Dear Experts

Kindly see how you can help again in my final part of this Ms Access /Json project, now I want the data in the query below to be posted to the site as per link in the VBA. The problem I have right now is how to format the data in a query into Json so that it can be posted successfully
Qry1
SELECT tblInvoice.Customer, tblCustomers.TaxID, tblCustomers.Address, tblInvoice.INV, tblInvoice.InvoiceDate, tblInvoicedetails.Product, tblInvoicedetails.Qty, tblInvoicedetails.Price, tblInvoicedetails.VAT, (([Qty]*[Price])*(1+[VAT])) AS TotalPrice
FROM tblProducts INNER JOIN ((tblCustomers INNER JOIN tblInvoice ON tblCustomers.ID = tblInvoice.Customer) INNER JOIN tblInvoicedetails ON tblInvoice.INV = tblInvoicedetails.INV) ON tblProducts.PDID = tblInvoicedetails.Product
WHERE (((tblInvoice.INV)=[Forms]![frmInvoice]![INV]));

Open in new window


Below is the VBA that I have prepared requiring completing the Json data formatting:

Private Sub CmdSales_Click()
Dim http As Object
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Dim JSON As Object
Dim i As Integer
Dim item As Object
Set rs = db.OpenRecordset("SELECT * FROM Qry1 Where Qry1.ID = " & Me.INV, dbOpenDynaset, dbSeeChanges)
http.Open "POST", "http://jsonplaceholder.typicode.com/users/?id=" & Me.INV, False
http.send
Set JSON = ParseJson(http.responseText)
i = 2
End Sub

Open in new window

Kindly see how this can be done!
1
I'm using a dynamic query to identify records with write conflicts (different Modified_DT values) between Access and Azure tables (there will eventually be over 20 of these tables that require synchronization).  Because of the number of tables which must be synched, I would prefer not to create separate subforms as this would be extremely time consuming.

Instead, I have a dynamic query which provides me with the records I want to view, including all of the columns in the two tables.  Instead of a datasheet form, I'm using the query as the SourceObject for this subform, as shown in this image:
2019-09-06_DataSynch.jpg
But I would like to hide the PD_ID, Well_ID, and DateRecorded columns.  I know I could do this with a datasheet subform, but I don't want to create a separate datasheet for each of the 20+ tables.

So what I need to know is whether I can hide these columns and if so, how?

FormName: frm_Synch_Data
subformName: sub_Write_Conflict_Records
0
Hi Everyone,
I have a small but complex access application that I developed & use for myself.  Essentially I want to use the vba message box but with custom button text & found the perfect solution a couple of years ago here.

 It has worked for perfectly in the 32 bit environment without any problems (win 10 home 64 bit but office 365 32 bit).  Recently I bought a new computer that automatically installed office 365 64 bit & now things have gone cattywompus with the whole thing crashing both in access and the original excel file.


I have no api experience at all but I think I have managed to track the problem to this line in the messagebox function
m_lProcHook = SetWindowsHookEx(WH_CBT, _
                                   AddressOf MsgBoxHookProc, _
                                   m_hInstance, _
                                   m_hThreadID)

Open in new window


Can anybody help? If its too difficult I am happy just make my own custom message box and use that?
 I have also attached the original excel file containing the code to save having to download.

Cheers & thanks in advance
Bruce
CustomMsgbox.xlsm
0
I need to be able to pull information via VBA from active directory for use in an Access Database.  Thru searches I have been able to use the code below to get the User and the groups they belong to in active directory.  

I can't seem to pull the following three items of information from AD.    ManagedBy,  Email and Department

Any help would be appreciated.  Thank you

Private Sub Command0_Click()

    Dim sysinfo
    Dim wshshell
    Dim objnetwork
   
    Set sysinfo = CreateObject("ADSystemInfo")

    Set wshshell = CreateObject("Wscript.Shell")
    Set objnetwork = CreateObject("Wscript.Network")

    DomainString = objnetwork.UserDomain
    UserString = objnetwork.UserName

    Debug.Print "Domain: " & DomainString
    Debug.Print "UserString " & UserString
    Debug.Print "UserName: " & sysinfo.UserName
   
    Set UserObj = GetObject("WinNT://" & DomainString & "/" & UserString)

    Debug.Print UserObj.FullName
   
    For Each GroupObj In UserObj.Groups
        Debug.Print GroupObj.Name
    Next

End Sub
0
Hi,

I have created a group account on my MS outlook mailbox. so I have 2 accounts in my MS outlook mailbox. One personal and other group. Default mailbox is personal.

Now, I use below vba script in MS Access database to send out emails. How can I code to sent out emails from group mailbox? I used
.SentOnBehalfOfName = "example@abc.com" but got  error message.{You do not have the permission to send the message on behalf of the specified user. Error is [0x80070005-0x0004dc-0x000524].} But I can send emails manually from this group account.



Public Sub email()
Shell ("OUTLOOK")
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = "ab@abc.com; abcd@abc.com"
        .Subject = "WWWW"
        .Attachments.Add "C:\ab\abcd.xlsx"
        .Send
    End With
    If IsObject(OutApp) Then
    OutApp.GetNamespace("MAPI").Session.Logoff
    OutApp.Quit
End If
    
    Set OutApp = Nothing
    Set OutMail = Nothing
    End Sub

Open in new window


Please let me know

Thank you
A
0
I'm having problems consistently downloading files from a Sharepoint Online server, in VBA in Access 2010/2013/2016. The code below works fine on some clients, occasionally on other clients, and not at all on others. It's probably due to authentication differences amongst the clients pc's. Anyone have some code that directly incorporates the authentication in the download ? Mapping a drive letter to the Sharepoint solution is unfortunately not possible at the moment.

Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
    ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Function DownloadFileFromWeb(strURL As String, strSavePath As String) As Long
    ' strSavePath includes filename
    DownloadFileFromWeb = URLDownloadToFile(0, strURL, strSavePath, 0, 0)
End Function

Open in new window

0
Hello,

I'm trying to make this Crystal Report like the Excel report.  I'm having trouble with making it like the Excel report.

I've attached both files...  Thanks for your help.

JoelEE.xlsx

LG.rpt
0
Hello - I apologize for the lengh of this problem description, but I wanted to be clear on what I did to try to solve it.

I am trying to upsize an Access 365 accdb to SQL Server 2017 Express, using Migration Assistant for Access. This is a huge application, developed over the years by a semi-technical manager. The ultimate goal is to re-write the front end, but the app is crashing so often, we are migrating the back end to SQL Server to see if that will help in the short term, while I re-write the front end. (I know; in a perfect world, it should all be done at the same time)

I installed SQL Server Express 2017, Management Studio v18, and Migration Assistant 8.4 (also having to install Access 2013 Runtime to get the DAC's)

Then I migrated a copy of the production Access backend, with ~125 tables, up to SQL Server. All was good. Went suspiciouosly easy, in fact.

A few days later, I did a walk-through with the manager to link the SQL Server tables to the application front end, and as we were prompted, table by table, for the primary keys, he would point out the field(s).

In each case, I would flip back to the back end accdb, and set the primary key in the given table.
In about 2/3 of the tables, the manager did not know or could not remember what the pkey should be, so I would add an AutoNumber field to the table, set it as the pkey, and continue on.

I cancelled out of the prompts for each pkey-less table. This was a throw-away run. My intent was to get …
0
Hi all,

My main question is how do I:

OPEN "Specific" Outlook Calendar from MS Access Form (VBA)?
Platform - Windows 7 x64 and Office 2013 x32.

Bonus question if this is doable?
   IF the user is missing that "Specific" Calendar
   THEN Add that "Specific" Calendar
   THEN open that "Specific" Calendar

So far I have only managed to find a solution on how to open the default calendar:

Private Sub Command0_Click()


Const olFolderCalendar = 9
Dim olkApp, olkSes, olkFolder
On Error Resume Next
Set olkApp = GetObject(, "Outlook.Application")
If TypeName(olkApp) <> "Application" Then
    Set olkApp = CreateObject("Outlook.Application")
    Set olkSes = olkApp.GetNamespace("MAPI")
    'Change Outlook on the next line to the name of the default mail profile'
    olkSes.Logon "Outlook"
Else
    Set olkSes = olkApp.Session
End If
Set olkFolder = olkSes.GetDefaultFolder(olFolderCalendar)
olkFolder.Display
Set olkApp = Nothing
Set olkSes = Nothing
Set olkFolder = Nothing

End Sub

Open in new window


Windows 7 x64 and Office 2013 x32
0
I have an Microsoft Access database table where I am trying to exclude "Wire" and "Fire" from a memo text field called Description.  I want everything to show up that does not have the word "Wire" and "Fire" in it.
Thanks in advance.
0
I am trying to create an access database with

4 tables.

employee_table
non_employee_table
badge_user
badge_table

_Entity-Relationship-Diagram-Example.png
Our requirements are

A badge can be in a few different states: Assigned, Blank, Lost, Destroyed (status column in badge table)
An employee can only have one assigned* badge at any one time.  
All badge users(assigned badges) need a reference
Not all employees have HR ID Numbers

I have attached an ERD to illustrate what we are doing.  

What I am doing now is trying to find a way to update all tables from the MasterTable

Previously we had a form that would import a full excel file into MasterTable and then subsequent forms with append queries would update each table.  

However, we have since changed the table structure and this is no longer valid.  

Now we have one Master table which is imported with a module from an excel file.  

We then appended employee and non_employee based on certain criteria, no problem with those.  

The second part is, we wish to append badge_user with emp_table and non_empy table details as well as updating our badge_table.

Is it possible to do append and update in one query or is there a much easier way?
0
when running a query we sometimes get "System Resources Exceeded"   If they log off and back on it fixes it.  We are using Access 2016 and Win 10.  

One item to note is this is only happening on 4 new Dell Optiplex 3050's   No other computers are having the issue.

I found an article to fix Access 2010, but not 2016.

Does anyone have one?
0
We have sql server with database and tables in it. If I can connect to it via odbc, I can link to the tables in access database. After the linking I am easily able to make changes, add and delete the data in the tables. Can anyone suggest a lock system (user verification) that will enable user to link to the tables but not make any changes to the data in the table. My DBA can do it but I wanted to know opinion from the experts to propose a solution.

Thank you.
0
I've got an access DB set up with a large number of companies and a primary [ID] key number for each one. There are many associated tables which provide data about certain aspects (e.g. revs in 2018) with the company ID as a foreign key. Several of these companies have merged so I've got separate records for the company (and all the linked other tables) for Company X [company Y part] and Company X [company Z part].  
I run a whole bunch of queries and forms from this table set up, and at the moment it obviously brings up the answers as two separate records. But for ones which I identify (perhaps with a separate table for mergers) I'd like it bring up the combined records. Is there a way of doing this?
0
I'm struggling to find the right syntax to connect to an excel.chart OLE object from Access using OLE.
I have managed to link to a specific range on a sheet using the following code (not an easy task itself)

With Forms.Item("Form2").Controls.Item("OLEUnbound2")    'Name of form and OLE object on form
.Locked = False 'Make sure not locked (default setting is locked!)
.Enabled = True 'Make sure enabled (default setting is not enabled!)
.Class = "Excel.Sheet" ' Set class name.
 ' Specify type of object.
.OLETypeAllowed = acOLELinked
 ' Specify source file.
.SourceDoc = ".............\<Filename.xlsx>"
 ' Specify data to create link to.
.SourceItem = "Sheet2!R1C3:R5C7"
 ' Create linked object.
.Action = acOLECreateLink
 ' Adjust control size.
.SizeMode = acOLESizeZoom
End With

However if I now try and attach to a  .class="Excel.Chart" and change .SourceItem to (for instance) "Sheet2Chart5" it doesn't work and I get an error.
There must be some syntax required by the excel OLE server in order to refer to charts that you need to set .SourceItem to in order to get this to work.
Does anyone know this please?

[PS I know I can use a work around for one item to link to an area on a sheet where I put the graph, but would be helpful to understand the Excel.Chart syntax for various reasons]
Many thanks
0
I have a SQL database where I need to cycle thru 25 queries to make and append to a table so I can pull the right information.     I have one table w/25 values spread across 25 fields and in MS Access I have a form button the user presses and it runs the mk table and append queries.   I tried to replicate this in SQL starting w/creating a view and then using the Instead trigger - but that approach didn't work.    I'm not sure how to approach this problem or what terms/key words I should be looking at.   Any assistance would be greatly appreciated.
0
We get data from a client established SSRS.  Is there a way to set up a connection to SSRS in Access to pull that data in?
0
I have an access 2002 runtime running 3 pcs accessing data from a server.  2 pcs are windows 7 and one windows 10. The Windows 10 machine has an access version 16 on it.

When two or more users are accessing data the ethernet speeds are 1-1000kbs when one user is accessing data the ethernet speeds are in the tens of megabits and usually running up to 10 times faster.

We have disabled Leasing on the server (a known Microsoft issue)

I have 15 other installations with exact same application and all data on servers all using various windows OSs including a good number of Windows 10. The server has been checked by two independent IT companies and checked Ping rates etc. etc.

The only thing I am not sure about is if Leasing has to be disabled at each pc.
0

Microsoft Access

221K

Solutions

52K

Contributors

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.