Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

Microsoft Access

217K

Solutions

51K

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

I've encountered a rather strange problem that I could really use some help with. In a database that I've created, I've created some code that if an edit is made to one of the tables, it will send out an e-mail that will inform people of the change. The code itself has worked great in most of the databases, except for one. With that database, it causes an error upon reaching the specific line :

Set oApp = CreateObject("Outlook.application")

The error caused is Automation Error Library Not Registered 2147319779.
What really confuses me about this error is that the code is exactly the same as the other databases other than a few name changes for the queries it uses. Yet this one just doesn't work. I ran Office Diagnostics and it came up with no errors, and both Outlook and Access are the 2007 versions.

If anyone has any ideas about how to fix this problem, I would be really grateful.
0
Technology Partners: We Want Your Opinion!
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

I am using this code to take a query and save to a Excel template. What I would like is it not to save to the actual template but to a new excel file with a  different name so the template stays intact.

DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel9, TableName:="qry_SectionOne", FileName:="C:\Test\Template.xlsm", Range:="SectionOne"
0
Shift are of four types.: A,B,C,G
After 6 days Off.
How to make this database so that at the end of the month we check there attendance. Shift changes aftet every 2 days.
0
I set some fields in the table to have 2 decimal places, yet every time I enter data, there are 4 decimal places.  What am I doing wrong?
0
I am in need of some assistance. I have created several select queries and made a form. In the form I put in a Autonumber ID such as 2752 and click a button. This button is supposed to run in VBA and Delete * from 4 different queries.

Private Sub btnStep1_Click()

On Error GoTo ErrorHandler


If Me!txtSKU.Value & "" = "" Then
  MsgBox "Please pick a SKU!", vbCritical, "Surpius Says!"
   Exit Sub
Else
 'Do Nothing
End If


DoCmd.SetWarnings False
 DoCmd.RunSQL "DELETE * FROM qryDeleteProdlocations"
  DoCmd.RunSQL "DELETE * FROM qryDeleteAssembliesChild"
   DoCmd.RunSQL "DELETE * FROM QryDeleteProducts"
    DoCmd.RunSQL "DELETE * FROM QryDeleteSKU"
     DoCmd.SetWarnings True
        
Exit Sub

ErrorHandler:
DoCmd.SetWarnings True
 MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbCritical, "Error"
End Sub

Open in new window


In all the Queries I can manually delete all these records perfectly.

3 out of the 4 queries have more than 1 table and will error in VBA
qryDeleteProdlocations
qryDeleteAssembliesChild
QryDeleteProducts


DoCmd.RunSQL "DELETE * FROM QryDeleteSKU" Runs perfectly as QryDeleteSKU is only 1 table.

The error says It can't delete from the specified tables. But I can manually delete them all fine.

Here is the most complicated query that involves 3 tables called qryDeleteProdlocations

SELECT SKUs.SkuID, Products.ProductID, ProdLocations.LocID, ProdLocations.QtyLoc
FROM (SKUs INNER JOIN Products ON SKUs.SkuID = Products.SkuID) INNER JOIN ProdLocations ON Products.ProductID = ProdLocations.ProductID
WHERE (((SKUs.SkuID)=[Forms]![frmDeleteSKUs]![txtSKU].[Value]) AND ((ProdLocations.QtyLoc)<1));

Open in new window


 Untitled.png
0
I have a form with a textbox where UPC codes are entered and I have some VBA code that validates what is entered, and pops up a message when symbols are entered, if the value is more than 12 characters  or less than 12 characters. All works well except if i enter a UPC greater than 12 characters  or one where symbols are entered. Because I if the user selects not to continue or has to correct the Symbol entry the control is cleared and now there is less than 12 characters  and that condition fires. I want to tell the user what they have entered in those conditions  so that they can correct them if needed.

Private Sub UPC_ACTUAL_BeforeUpdate(Cancel As Integer)
Dim MResponse As Integer
Dim LResponse As Integer
'This call a Function and returns True if the value is numeric, False if it is null or not numeric.
If PatsIsNumeric(Me.UPC_ACTUAL) = False Then
    MsgBox "The UPC Number is not numeric. Please correct it.", vbOKOnly
    Cancel = True
     Me.Undo
     End If
    
 'Else
 'This checks if the entered UPC is greater than 12 characters
  If Len(UPC_ACTUAL.Text) > 12 Then
MResponse = MsgBox("This UPC is GREATER than 12 Digits... do you want to continue?" & vbCrLf, vbYesNo)
If MResponse = vbNo Then
   Cancel = True
     Me.Undo
End If
End If

'Else
 'This checks if the entered UPC is less than 12 characters
  If Len(UPC_ACTUAL.Text) < 12 Then
LResponse = MsgBox("This UPC is LESS than 12 Digits... do you want to continue?" & vbCrLf, vbYesNo)
If LResponse = vbNo Then
 

Open in new window

0
I have multi users
I would like to create a chart when the user logged in and logged out daily basis on MS Access.
Which chart should I use?
0
I have an Access database written to keep track of events.  I am trying to combine the following two queries and have the results added together.  Both of these work independently, just trying to find the easiest way to combine their numeric results.  In the second example, the "Spouse" field is blank.  Any help is appreciated.

SELECT Count([Invitee])+Sum(Abs(IsNull([Spouse])=False)) AS RSVPYESCount
FROM Invitations
WHERE (((Invitations.EventID)=[Forms]![Events]![Invitations_Subform]![EventID]) AND ((Invitations.RSVP)="Yes"));

SELECT Count([Invitee]) AS RSVPSpouseOnly
FROM Invitations
WHERE (((Invitations.EventID)=Forms!Events!Invitations_Subform!EventID) And ((Invitations.RSVP)="Spouse Only"));
0
I am creating an inventory database to keep track of hosts in the company building. If a host is scrapped, I can tick the tick box in the 'Scrapped?' field for the host in question and it will appear in the scrapped hosts query. How would I go about automatically populating the field 'Date Scrapped' with today's date, so for future reference I don't need to input the date myself.

Thanks in advance if you can help
0
Hi

I have inherited an old database which uses a MS Access Frontend to Import data into a SQL Backend via SSIS package .
It appears the connections to SSIS in the following Access DB Vb script fails as it only complies with TLS1.0 and not TLS1.2 as desired when using web based access.
Code:
cnn.ConnectionString = "Provider=SQLOLEDB;Data Source=MySQLData;Initial Catalog= MyProject;User ID=MySSISImport; Password= XXXXX;"
    cnn.Open

To resolve this to be TLS1.2 compliant should I be looking for
1- A security patch for SSIS
2- Re-write connection string an alternative way

Thanks
0
[eBook] Windows Nano Server
LVL 1
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

I had this question after viewing Show details of selected record at top of the datasheet.

How can I accomplish this https://www.screencast.com/t/HZNWrukfn ?
0
All this database has thus far is a table but also needs an entry form that is unlikely to corrupt the database if opened and edited by several users at once from a file share.
IDCom.accdb
0
Dear experts

I need to find a way of anonymising the contents of all fields in a table (table-master) to remove peoples names

I can produce a list of names in a separate table (ToAnon) and I need a piece of code that will find a name listed in ToAnon and replace it in table-master with '(deleted)' wherever it occurs

Can anyone help, it's an urgent request
0
I am posting this question as a follow up to the question at this link:

https://www.experts-exchange.com/questions/29057032/ComboBox-on-Access-Form-with-Blank-Selection-Option-to-Clear-Filter.html#a42300096

I have a form with two unbound combo boxes, one for the course and one for the preferred session of that course.  Each are located in the header of a continuous form which displays each request submission that has not been assigned to a session.  The user is able to filter the list based upon both the class and the preferred session.  

I need to add the cascading functionality so that if the user filters the list by course only the preferred sessions for the requests for that particular course show up in the preferred sessions filter combo.

Here is the SQL I have for each combo box:

Course

SELECT qryReqPendingAssignment.Course, tblCourses.CourseName FROM tblCourses INNER JOIN qryReqPendingAssignment ON tblCourses.CourseID = qryReqPendingAssignment.Course UNION SELECT Null, '(View All)' FROM qryReqPendingAssignment
ORDER BY tblCourses.CourseName;

Preferred Session

SELECT qryReqPendingAssignment.PreferredSession, tblEvents.EventDesc FROM tblEvents INNER JOIN qryReqPendingAssignment ON tblEvents.EventID = qryReqPendingAssignment.PreferredSession UNION SELECT Null, '(View All)' FROM qryReqPendingAssignment
ORDER BY tblEvents.EventDesc;
0
Hi,

I have different form's that i develop on my PC that are according to my screen , when the user open on their screen it shows according to their screens like smaller. I want that the form should fit into different screens. I understand that their is property called "Fit ti Screen" but that's somehow not working. is there a code through which my form should fit into different screen or any other way to address this issue.

Any help/idea would be helpful.

Thanks in advance.
0
Hi all, I have set out some data, however I am not sure if it is possible to list it in a specific way

For each product (prod_ID) there is events that happen, no matter what date the event is (like lines 20-24 & 32 & 33)
product is the Formula 1 event

For each departure (dep_ID) there would be different things happening (like lines 25 & 31)
departure is the specific package (where as there could be different-longer packages with different itinerary)

Data:
TABLE: prod_itin
itin_ID	brand_ID	cons_ID	date_added		itin_live	prod_ID	dep_ID	itin_day	itin_IATA_start	itin_start	itin_finish	itin_IATA_finish	itin_item
20		GPT		1	9/08/2017 1:00:00 PM	Yes		3		1		AEGPA		1:00:00 PM	2:30:00 PM				Free Practice 1
21		GPT		1	9/08/2017 1:00:00 PM	Yes		3		1		AEGPA		5:00:00 PM	6:30:00 PM				Free Practice 2
22		GPT		1	9/08/2017 1:00:00 PM	Yes		3		2		AEGPA		2:00:00 PM	3:00:00 PM				Free Practice 3
23		GPT		1	9/08/2017 1:00:00 PM	Yes		3		2		AEGPA		5:00:00 PM	6:00:00 PM				Qualifying
24		GPT		1	9/08/2017 1:00:00 PM	Yes		3		3		AEGPA		5:00:00 PM						Race
25		GPT		1	9/08/2017 1:00:00 PM	Yes			6	1		AUH		2:00:00 PM						Check-in
31		GPT		1	9/08/2017 1:00:00 PM	Yes			6	5						10:00:00 AM	AUH			Check-out
32		GPT		1	9/08/2017 1:00:00 PM	Yes			6	1		AEGPA		8:00:00 PM						Concert: Calvin Harris
33		GPT		1	9/08/2017 1:00:00 PM	Yes		3		4		AEGPA		8:00:00 PM						Concert: P!NK

TABLE: prod_deps
dep_ID	brand_ID	cons_ID	date_added		dep_live	prod_ID	dep_date	dep_type	dep_conf	dep_sold	dep_prod	

Open in new window

0
Hi Experts,

I realized that lately my pc is reacting very slow to my keystrokes, especially when using the mouse to move objects in Access forms, any idea what can be the reason?

I checked task manager and CPU usage is low, and RAM is not to high either..

PS. Windows 7 Pro, NO VIRUS as per AVG..

Thanks in advance.
0
I've completed an Access app using Access365 on Windows10...this interface will be used by people also on Windows10, but using MS AccessRuntime2010.  All of my testing works fine on my machine.  I even copied & the file and renamed the ext from ACCDB to ACCDR to simulate the Runtime environment...again, all is well on my machine.  However, on the users' machines they are getting incorrect data & error messages.  

I know you'll want to know the exact errors & I will post those tomorrow after retesting.  The errors do not stop the users from utilizing the application, but what concerns me is that they are getting wrong information.  I have code that calculates the distance between two zip codes by using the latitude & longitude of both.  When calculating the distance between a zip in NY & one in MN i get over 1,000 miles (the correct value), but when the user is running the app, the result shows as only 8 miles (clearly incorrect).  We walked through the exact steps on both PCs to make sure we didn't do anything differently, but we get different results even though we use the same inputs.

Before I start supplying more specifics as far as code & screenshots of errors, I was wondering if there is anything inherent in the difference between Office365 & Office2010 that could lead to such problems.  Could there be something missing from the reference libraries that could be problematic?  Can MS AccessRuntime even add more references?  i'm guessing not.

I suspect i may need …
0
I have a form bound to a Query. One field in the query is pdfstring, the contents of this Field  is a string of the file location of a pdf related to the record. This is an example

file:///\scanner\Inv\600594.PDF
file:///\scanner\Inv\602135.PDF


scanner is a windows share

In my Web Browser Control Form, the Control Source is: pdfstring

When I view the form, the first image comes up as fast as one could hope for.

The second and all subsequent images come up very slowly as I move from record to record.

Can someone explain this behavior to me?
0
Ransomware: The New Cyber Threat & How to Stop It
LVL 4
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

I'm about to start working on a database to be used by our staff and County/Municipal officials.  Is there a way to consume an Access database online?  Or will I have to just place it somewhere for download?  We have Sharepoint and MS 365.  Would it be easier to create a database to share with Sharepoint?  If so, I'm not very familiar with Sharepoint tables and would love some video or tutorial resources.  Thank you!
1
I use Adobe acrobat XI Pro version 11.0.22 extensively

Have vba code that edits (E) acrobat files adding headers and footers and shrinking file sizes

I understand that Adobe will stop supporting this software on October and they recommend an upgrade

Any advice on the upgrade - how will it affect my code applications?

Examples are

--------------------------------------
Option Compare Database
Private Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As LongPtr, ByVal lpString As String, ByVal cch As LongPtr) As Long
Private Declare PtrSafe Function GetNextWindow Lib "user32" Alias "GetWindow" (ByVal hwnd As LongPtr, ByVal wFlag As LongPtr) As Long
Private Declare PtrSafe Function GetTopWindow Lib "user32" (ByVal hwnd As LongPtr) As Long
Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare PtrSafe Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As LongPtr, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As Long

Private Const GW_HWNDNEXT = 2
Private Const WM_CLOSE = &H10

Public Function MakeEvenPageCount(ByVal SourcePath As String, ByVal SourceFileName As String, ByVal DestPath As String, ByVal DestFileName As String, ByVal FrstPg As Double, ByVal MaxNoPgs As Double, ByVal FileNo As Double, ByVal DoYouWantToSendToPrinter As Integer, ByVal FontSize As Integer, ByVal 

Open in new window

0
I wish to use the button "Edit List items" in a shortcut menu (right mouse button), for opening a lookup table, in Access, by using it in VBA.

I thougt i found it with ID 11229, but this ID is not meant for Access, but for Word. Do you know the ID for the button in Access ?

I have a picture of the icon on the button, but cannot send it, because there is no possibility to send an attachment.

Many thanks in advance for your help.
0
Is there a way to sort text as numbers in access without omitting the data that contains text values? I am currently trying to sort by operation which includes the values 1,2,2A,3,10,11,12,....ect. When I currently sort these values because the are stored as text they appear as 1,10,11,12,2,2A,3.......ect. I have tried using the expression Expr1: IIf([Operation] Is Null, 0, Val([Operation])) but it omits the operations that contain text values such as 2A. Is there a way to sort this information correctly?
0
I am having some issues in access using the DSum function to create a running total in access. I am trying to create a running total for both my Production standard and total conforming parts by using the date as the criteria. I am also doing this in a totals query which is set to pull data between [start date] and [end date]. The formula is working correctly for my production standard creating a running total between the specified dates but the total conforming formula (which is the exact same formula) is summing all of the data and then creating a running total. The are the two formulas I am using:

Production Standard - Running Total: DSum("[Production Standard]","M151-3 by Month (with standards)","Date <= #" & [Date] & "#")

Total Conform- Running Total: DSum("[Total Conform]","Kylee M151-3 by Month (with standards)","Date <= #" & [Date] & "#")


I should also note that I used these exact formulas for a different query (sorting by a different shift) and they worked perfectly.


Production Standard - Running Total: DSum("[Production Standard]","M151-1A by Month (with standards)","Date <= #" & [Date] & "#")

Total Conform- Running Total: DSum("[Total Conform]","Kylee M151-1A by Month (with standards)","Date <= #" & [Date] & "#")


Can anyone tell me what I am possibly doing wrong and why theses equations work for some of my data and not the others? This is all pulled from the same source I am just creating different reports sorted by shift.
0
Hi,

I have been trying to work with the below code. For Norway it changes to summertime 26th March 2017 and wintertime 29th October 2017. I want the query I am running to display if the current date is summertime/wintertime so I know if I need to add 1hr or 2hrs to the GMT time in my database. It does do this, but not for the dates that I want.

Public Function IsDST(ByVal d0 As Date) As Boolean
   IsDST = d0 >= NextSun("Mar 21 " & Year(d0)) And d0 < NextSun("Nov 01 " & Year(d0))
End Function

Private Function NextSun(d1 As Date) As Date
   NextSun = d1 + 7 - Weekday(d1)
End Function


My Query text: Season: IIf(IsDST([Date])=-1;"Summer";"Winter")

Any help would be appreciated as I would like to avoid having to change the dates manually for each year. I could also go for a table with start date & end date if that is easier.

Bgrds
1

Microsoft Access

217K

Solutions

51K

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.