Microsoft Access

219K

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

MS Access 2013
Using VBA; Application.FollowHyperlink to open file from network share.  This all works, but there is an annoying warning box.

Some files can contain viruses or otherwise be harmful... blah blah blah   Would you like to open this file?
warning box
I have already tried setting:
[HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\15.0\Common\Security]
"DisableHyperlinkWarning"=dword:00000001

Open in new window


and setting the privacy settings.
trust center privacy options
So far none of these have worked for me.   I've been googling for answers, but all similar (or for pre 2010 versions) and don't work for me (so far).

How can this warning be "switched off" ?

Thanks for your time and help,

--Paul
0
Ultimate Tool Kit for Technology Solution Provider
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

I need to be able to find a Word document on a web domain, edit it and then save those changes, ideally without downloading and then uploading. Is this possible?

So within Access I will select a file from a table that has that file's exact location, such as "www.mydomain.com/files/myDoc.docx". Once I have selected that file, the Word document will open, and then I will make my changes. Still in Word, I will then Save the document and it will be saved in its original place.
0
I have a TotalCost field on a form that stays blank until a selection of items has been completed. If insufficient items or an incomplete selection of items is chosen I want the total price field to be blank to indicate that the selection is not complete. Sometimes a complete selection is not possible (awaiting further information for example) but I still want to show a total on a  report, what has been selected and the accumulated cost so far but show the items on the report that are not complete either with a blank or a Zero. I then need a Grand Total. IsNull does not neither does NoneZero
0
hi,

I want to store and read a small text file directly within a VBA module.

I could assign it as a variable, eg

strMyFile = "<b>The quick brown fox jumps over the lazy dog</b>"

but the file is multiple lines and ideally i'd like to be able to copy-paste any changes into the module without having to manage line breaks etc.

Is there any way to do this in VBA, eg a command to read the subsequent lines as text input until some stop command is reached?

I'd like to do this in order to create a one-off self contained module that i can drop into an MS Access db and not worry about dependencies.

Thanks
0
When I start an MDB Access 2002 whilst holding down the shift key I get error "There was a problem while sending the command to the program"
0
I have installed an MS Access .accde in C:\Program Files (x86)\Contacts and when I run it I get the message 'A potential security concern has been identified.'

How do I overcome this?

Thanks in advance.
0
I want to make sure I am understanding the Group By completely in MS Access. Basically I am importing CSV files into Access and I want to Group Customers by several fields. The same customer could have several shipping addresses. Therefore I want to make a record in Access for each shipping address of the customers and if there is several duplicate Shipping Address in the CSV file, I want Access to Group them. The fields I am trying to Group on is:
Company Name
First Name
Last Name
Address Line 1
Address Line 2
City
State
Zip Code
Country
Email address

Basically if any one of these field values is different from the previous record. I want access to show a record in the query results and if it is the EXACT same all the way down the line of fields. I want Access to group all of the similar ones and just show one record.

I have attached a photo of my query grid below and thank you for the help:
Group-By-Access.png
0
I have a directory that has hundreds of individual databases that contain name and address information.  Can someone please recommend a way to be able to search thru the directory to find which databases a name and address may appear in?  I'm hoping it's possible, but my Google-fu is telling me it's not.

TIA for any help
0
Is there a way to code my pivot table to open with the max date of a field LOGINDT in MS Access?
0
I would like to get some assistance with dynamically changing a form's **multi-select** property through VBA (without having to close/re-open the form).

Current process:
- I have one listcontrol ("ListBoxActions") with 4 tabs on a form
- In the listbox's property sheet, the value for **multi-select** = "None"
- Upon clicking tab 1, 3, and 4, I would like to have the multi-list property set to "1" (single).
- Alternatively, upon clicking tab 2, I would like to dynamically change the multi-list property to "2" (extended).

I have tried two approaches (see below):
           Me.ListBoxActions.MultiSelect = 2   ' Extended

Open in new window


and/or

           Forms("frmLogin").Controls("ListBoxActions").MultiSelect = 2   ' Extended

Open in new window


Unfortunately, neither of the two methods work.   That is, once I click on tab 2, I only can select a single value (vs. multiple values) from the listbox.  

Below is the full code for the listbox.   What am I missing?   How should the VBA be modified to allow dynamic switching of the multi-select property?

Thank you,
EEH

Private Sub TabControlObject_Change()

    Select Case Me.TabControlObject.Value
               
        Case 0
           MsgBox "1st tab"       
           'Me.ListBoxActions.MultiSelect = 1
           Forms("frmLogin").Controls("ListBoxActions").MultiSelect = 1 'Simple
           Me.ListBoxActions.RowSource = "SELECT tblTabControlRowSources.RowSource_Page_1 FROM tblTabControlRowSources WHERE

Open in new window

0
Cloud Class® Course: Microsoft Azure 2017
LVL 12
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Hi everyone. I'm designing a database app by using Microsoft Access as a front end (FE) to a MS SQL Server backend (BE). I want to optimize performance as much as possible. Currently, I'm using linked tables. However, for some of my queries, I use pass-through queries and it returns its results instantly. As I have observed, I can still use pass-though queries without linking the tables in the BE. The problem is that the results of pass-through queries are read-only.

Presently, for adding/editing records, I build those queries by basing them on the relevant linked tables. Sometime, I add records by building the query as an Append Query (INSERT INTO).  Is there any equivalent of a pass-through query that allows me to add/edit records? Please note that I will like to accomplish this using either the Query Design Grid (QDG) or by writing pure SQL in the QDG.

Thanks.
0
I need to run a query where I count then group by whether a field name has particular set of characters.

So here are the sample field values:
AAR_GUAM HS_2018-07-20(Gibson)1.3(inf)
AAR_KADENA ES_2018-07-19(Johnson,Gibson)
AAR_KADENA ES_2018-07-26(Gibson)1.1-1.5-1.7(inf)
AAR_KUBASAKI HS_2018-07-26(Gibson)

I have the query for counting the number of AAR reports if the name (for example Gibson) appears in the report name.
SELECT tblAAR.AARFileName, Count(*) AS AAR
FROM tblAAR
WHERE (((tblAAR.AARFileName) Like "*" & [TempVars]![tvUserLname] & "*"))
GROUP BY tblAAR.AARFileName;

Open in new window


But I need to separate out those files names that "(inf)" and those that don't and create a separate count for both of them. A little stuck on how I can do this...

Any help would be appreciated!

Thanks!
0
I would like to use VBA to change the color theme of the Access application I have created. I actually use the database when I am at work, along with other employees within the company. I have code that switches between the live data and my development data. As part of that switch, I would like to change the color theme when I am in development mode so that I always know when I am using the development data and when am using live data. I have looked around on the online groups and don't find anything that lets me change the color theme and not just change the colors on a single form or report. If this is not doable, does anyone have another suggestion on how I can make it obvious when I am using the live data and when I am in development mode?
Thanks,
Kim
0
Experts,

I've been away from working for a year or so and am very rusty. I am helping a non-profit group by building them an Access database. They have been around for 5 or 6 years and are working with kids in Africa by arranging for sponsors to send the kids to school. They have been keeping up with everything in an Excel file. They have over 100 kids now and just can't keep up with it in Excel very well.

I've got a form with subforms created that shows all of the information needed on each kid and want to also show the picture of each kid. I can't find a solution on EE that answers what I am looking for. The database will be accessed via Remote Desktop Connection. I was thinking I could store the images in a folder and then show the image on the form. I just don't know how. I know I would need to provide the path and file name, but just can't figure out how to put it all together. By the way, I never have been very good at VBA, but am able to figure out a few things.

Thanks for any help, Dale.
0
Hi Experts,
I am looking for a script that will read all files of a specific folder (.txt,.csv) and count how many records they're in total.
all files have fields header, however some files only contains the header and should not be count.
Thanks in advance.
0
I have a field an MSAccess table where I need to strip off the literal part in the field.
The table is called User_Time and the field is called Stat_1.

This is what the data looks like:
5/17/2018 6:07 PM by Smith, Kyle

I need the data to look like:
5/17/2018 6:07 PM

Then I need to convert the new data to a date time field.

Is there any way that I can do this?

Any help is greatly appreciated.
0
I am using the command DoCmd.Outputto  and I need to know how to complete the command to identify the individual's name (I have it in a text box on the form + "_DailyReport_" & Todays Date

Docmd.OutputTo acOutputQuery, "qPrintDaily", acFormatXLSX,  Me.User & "_DailyTaskReport_" & Date()

Tried doing it this way but keep getting errors.  How can I make this work?
0
Hi Experts,
I post my question few days ago and tried whatever the suggestions from other experts, but it seems still having problem.  I changed some of my code, it gave me an error message "too few parameters, expected 2".  I could not figure it out it error.   ( I would like to do is when the client has two different Employers, for example, Mr. Smith works for ABC Market and TLC Inc.  I would like to print out 2 copies of ABC Market at the same time then follow by 2 copies TLC Inc instead 1 copy of each Employer each time and users have to sort them after both employers print out.  Below is my code:

Dim stDocName As String
Dim rs As Recordset
Dim db As Database
Dim stCriteria As String

EmployerCodeID = DLookup("EmployerCodeID", "qryNoticeLetter/CoverLetter")   ' EmployerCodeID is numeric
    Set db = CurrentDb()
      stCriteria = "Select * from [qryNoticeLetter/CoverLetter] where EmployerCodeID = '" & Me.EmployerCodeID & "'"
    Set rs = db.OpenRecordset(stCriteria, dbOpenDynaset, dbSeeChanges)
    If rs.EOF Then
    Else
    If Form_frmPrintedFormsAll.LabelMessage = "Notify Client" Then                  
         stDocName = "rptNoticeLetter/CoverLetter"
          DoCmd.OpenReport stDocName, acPreview
          DoCmd.SelectObject acReport, stDocName, False
           DoCmd.PrintOut acPrintAll, , , , 2
          DoCmd.Close acReport, stDocName
    Else
        stDocName = "rptCoverLetter"
        DoCmd.OpenReport stDocName, acViewNormal
           …
0
I have a table in an Access 2007 database that has a bunch of different statuses for one row of information. I am trying to match this table's data to another table which separates each status into a different row. Now, my original plan was to separate the first table's records into different rows based on their statuses and append them to a new table that can be used to match data with the original table. That is, I would create a query that focused on one status and assign it a letter that was associated with the status, such as "P" for pending. Then, I would append that data, with the letter replacing the original status column, into a new table that could be used to match data with the second table.

The only problem with this plan is that it would require multiple appending tables. I'm wondering if anyone out there has made a query that could break a row of data into separate rows and assign them data so that I would only have to build and append one query instead of several separate ones. If not, does anyone have any suggestions as to how I could make a single query that could do all this work? Or am I better off building multiple appending queries like my original plan?

Please let me know if my explanation was unclear and you need more information!
0
Creating Active Directory Users from a Text File
LVL 8
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Hi

I have a database which is used to keep track of industrial objects for a project. For each record (object) there are a connecting revision table (one to many) which is utilized to keep track of data changes for the object (revision handeling).

Data structure:
-Main Table
*Revision Table

The interface is a split form with a subdatasheet form for the revision handeling. I want the user to have the possibility to work in both views (form view and datasheet view).

Now to the issue, when deleting or copying records in the subdatasheet form I get the notification "This Operation is not supported within transactions" and the operation is not carried out (even if it looks like the records are deleted).
When directly in datasheet view for the main form this problem does not occur.

I have made a video of the issue
0
Hi Expert!

Just a small help!

I have seen a code for SQL table re-linker here see below:

Dim sConnect As String
sConnect = "ODBC;DRIVER={sql server};DATABASE=YourDatabaseName;SERVER=YourServerName;Trusted_Connection=Yes;"

Dim tdf As DAO.TableDef
Dim dbs As DAO.Database

Set dbs = CurrentDB
Set tdf = dbs.TableDefs("YourTableName")

tdf.Connect = sConnect


Now I have 64 tables to be re-linked as you can see above there is one provision for a table only(Set tdf = dbs.TableDefs("YourTableName")
), then how do I accommodate all the 64 tables there on MS Access start-up form Open event??????????


Regards

Chris
0
Hi,
There are lot written about by hiding Ribbon. Still, it seems not be too easy to hide the ribbon. Previously I used the "menu bar" and a application menu bar for the .accde (.mde) application. It worked fine. For to summaries the findings:

* use DoCmd.ShowToolbar "Ribbon", acToolbarNo
* no add-ins
* use dynamic ribbons to replace right-click
* DoCmd.ShowToolbar is not enough use DoCmd.NavigateTo "acNavigationCategoryObjectType" and  DoCmd.RunCommand acCmdWindowHide
* Search for anything which might do something with the ribbon - but what?
* move everything to a new db - sometimes access mix things up. I know, It is not so long ago I did that.
* ...

I use in Form_open event
         If Right(SetDB.Name, 6) = ".accdb" Then DoCmd.ShowToolbar "Ribbon", acToolbarYes Else DoCmd.ShowToolbar "Ribbon", acToolbarNo ' I want the ribbon while developing

Since, the menu bar is still around I disable that one as well - commndbars("Menu bar").enabled=false

I still use commandbars for right-click.

Anyway, Still the File and Home tabs are around, The File contains Privacy Options which means that they change important properties.

There is a lot of XML - code around for this. I have so far not done very much with XML, there is that code and how can I edit it?

What to do?


Thanks advanced


BAnders
0
Have a db that has an option to save a particular view of a datasheet form that can be recalled and applied against different datasets (ie. column order, columns that are visible).  Save the definitions in a separate views table.  If the user applies filters then those are saved as well using the Form.Filter and Form.FilterOn options.

However I want to take this a step further and be able to change fonts and font sizes, column background colors and row background colors and save them as well so that users can set formats and have them apply to different datasets.  Cannot seem to find the properties that will allow me to do so.  Thoughts?
0
I have a form that users input data into.  At the end of the day they click on a button to load this data into a linked table.  At the bottom right hand corner of the for while the background is running queries, the bottom right hand corner of the form shows "running query".  Is there a way I can make it show "processing data" instead while it is running these queries in the background?
0
I have got a form that is loading records to a table by endusers.  Each time a new record is created I would like the new record StartTime field to reflect the previous record EndTime value.  At the end of the day all records are loaded to the main table for reporting purposes soooooooooooooo all of the todays records are for today's date.

Currently, the EndTime is reflected in the StartTime field and I need it to be reflected every time the user enters a new record.  How can I code that to always put the EndTime of the previous record in the new record StartTime field?
0

Microsoft Access

219K

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.