All Questions
Tagged with vba microsoft-access
84
questions
1
vote
1
answer
79
views
How do I get the full sql code for all queries in all MS Access databases of a folder?
I want to run RegEx on the full SQL code of hundreds of MS Access queries that I loop through with VBA over all Access databases that can be found in a folder.
The "SQL" attribute of the ...
- 2,431
0
votes
1
answer
38
views
"You can't modify the structure of table "xyz", because it is already in use by another person or process.", and the Access instance cannot be closed
While I changed some VBA code in MS Access to fill new columns that I added to an Access table, I struggled with a crashed instance that stayed open even if I clicked on "Close"/X.
I ...
- 2,431
2
votes
1
answer
83
views
MS Access replace function is not giving the expected result
I am concatenating two fields, however in some cases the second field begins with \, but not always.
I'm trying to remove the possible extraneous \ using replace.
stDigitalFolderLocation = ...
- 51
0
votes
1
answer
65
views
Is it possible to have bidirectional relationships in Microsoft Access?
I have a database including two tables for suppliers and people:
the suppliers table has a look up column where one can select the people relevant to that supplier from the people table. and there is ...
- 662
0
votes
0
answers
67
views
Error in duplicate record in Access 2010 [duplicate]
I should work with a database into the Access 2010 recently. It has few Tables, a few Queries based on their tables and finally a Form. The Form has a button for duplication records. When I click the ...
- 1
0
votes
1
answer
495
views
Update a field in other records based on the value of same field in a record automatically
I want to transfer the contents (value) of a field in an Access form by VBA to its previous and next records, while I am still in the current record. Could be said I want to copy it to the same field ...
- 39
0
votes
1
answer
97
views
MS Access Runtime closes database when there is a mirror error. How to Ignore?
There is a MS Access Database. Sometimes there are pop ups errors: "The value you entered isn't valied for this field" and then a Macro error. But that's OK, I just click "Stop All ...
- 105
0
votes
1
answer
107
views
Why is this VBA data-fetching function hanging?
I have written this function in VBA to fetch data from a URL. I am calling it from another function that is called in an Access query. The code is adapted from code in an article cited in the comments ...
- 788
0
votes
0
answers
75
views
Why is this VBA data-fetching function hanging in Access?
I have this function in VBA, which I have used successfully before in Excel to fetch data from a URL:
'========================================================
Public Function sURLfetch(ByVal sURL As ...
- 788
0
votes
0
answers
179
views
MS-Access keeps stealing focus from VBA
When I'm working on MS Access VBA scripting, the Access application keeps stealing focus from VBA, even mid typing. So I will be typing code, there will be a pause, a brief lock up, and all of a ...
- 1
0
votes
1
answer
79
views
Excel to access file data tranfer by SQL server
I have an issue While Transfer Data From Ms Excel to MS Access by SQL Server
here is my program
Public Sub DoTrans()
DoEvents
Sheets("Temp").Select
Set cn = CreateObject("ADODB....
- 115
1
vote
0
answers
292
views
What is the VBA coding for getting data from multiple tables in Access?
I am a newbie in access VBA coding. I would like to get the data from multiple tables which table name begin with "AB" and show the result in the combo box. I have around 50++ tables and ...
- 11
0
votes
1
answer
1k
views
Copy attachments from records in one recordset into records in another using VBA in MS Access
I've almost got this working. I'm thinking I really just need another set of eyes on this. I think my problem is just a simple programming structure issue. Either too many loops or opening/closing ...
- 347
0
votes
2
answers
1k
views
Using VBA in Microsoft Access to read a Microsoft Excel workbook and then close it gracefully
I almost have this working. However, what I'm finding is that Microsoft Excel remains open in the background (as visible in Task Manager) even after my VBA procedure is completed. If I run the ...
- 347
0
votes
1
answer
1k
views
MS Access VBA; select records from table filtered by date
I have a table called tblAuditForms. This table includes a date column called AuditDate. The table stores the date in "short date" format, so the time is not stored in the table. I want ...
- 347
0
votes
1
answer
81
views
MS Access 2013, where is my toolbar and vba editor?
I am trying to get into the codes for my access file, my file has no toolbar options, do I have some light version of access or something? or is this file locked? Was trying to get to full options ...
- 391
0
votes
0
answers
131
views
Is there a way to run ms access applications with vba code correctly on mac?
I am trying to run MS Access application that includes VBA code on a mac device but unfortunately, there's no access version for mac os. So I ran windows 10 on it using bootcamp and installed ms ...
0
votes
1
answer
263
views
Conditionally update field in datasheet view when row is modified? [closed]
I have a requirement for an Access Database to add 3 new fields: [Entered By], [Entered On], and [Reviewed By].
Part of the requirement is that when the record is modified the [Reviewed By] field ...
- 983
1
vote
0
answers
620
views
Disable shift key on startup in ms-access (Doesn't want to work)
I'm an advanced Access VBA programmer, and even though I have programmitcally set the 'Use Access Special Keys' to FALSE (using VBA), or set that option through the Access Options screen, I can still ...
- 11
0
votes
1
answer
234
views
Link/share Macros between Access files
I have several accdb files.
Most of them (I will call them slaves) have only tables and queries and only one (I will call it master) have vba macros that actuate over the other files as an engine.
...
- 134
0
votes
0
answers
626
views
Speed up vba for Access
Excel has means to speed up macros by adding statements like
Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.DisplayStatusBar = False
Application.EnableEvents = ...
- 163
0
votes
1
answer
584
views
MS Access - #DELETED when adding a record
I'm using an MS Access 2010 .accdb database connected to a SQL Server 2016 database. My tables and views from SQL are managed as linked tables in MS Access. I'm using a DSNless connection with the ...
- 31
1
vote
2
answers
3k
views
When importing a spreadsheet into Access, how do you prevent Excel from auto assigning a data field type?
When I import an Excel worksheet into Access, it automatically assigns each column a data type based on what is in the first couple of rows and independent of what the Access table field type is.
In ...
- 13
0
votes
1
answer
875
views
counting records in an access table that occur between 2 dates
first the problem
i have a table with 2 dates I'm trying to create a function i can pass the 2 dates to work out the number of working days between these dates
i have another table that contains a ...
0
votes
0
answers
749
views
How to change IME mode (ON & OFF)?
VBA for Microsoft Access.
Before showing a inputbox, how can I turn IME on or off in vba for microsoft access?
- 13
3
votes
1
answer
2k
views
MS Access - Grouped query based on MAX field
I am trying to build a SQL Query that takes the following data:
+-------------+--------+---------+---------+--------+
| Primary Key | ID | Version | Class | Fruit? |
+-------------+--------+----...
- 31
1
vote
1
answer
2k
views
Exporting Access query to Excel using VBA and creating a secondary axis chart
In MS Access I have a database. I have Form with three TextBoxes and one Command Button.
In txttask_plot user writes Plotid
In txttask_from user selects date1
In txttask_to user selects date2
The ...
0
votes
1
answer
664
views
Re-query and refresh not working on main form after popup form edits
I have a main form, CaregiverFactSheet, that displays all types of information. It has a button, Edit, that opens the caregiverDemographics popup form where the user can edit the caregivers address.
...
2
votes
3
answers
2k
views
How to stop Excel changing cell format defined in a template?
I'm using an Excel template to generate a report from Access using VBA. When the newly created file is opened a number of cell's formatting has been changed from "Number" to "Date".
A table in the ...
- 340
1
vote
1
answer
1k
views
How to include text import specs into a batch import script for MS Access 2013+?
This code to import multiple CSV files into MS Access works really well for me: Import a large number of .csv files into Access
The one thing I would like to add is an import spec that imports each ...
- 11
1
vote
0
answers
30
views
VBA and Access, trying to Combine two types of outputs, both from the same field, and display the associated sums under one newly created type
I'm pretty new to programming in VBA and using Access in general. I'm trying to design queries for an industry survey, where I need to display sales sums for each of our various distribution channels....
0
votes
1
answer
1k
views
Default number of digits after decimal in Windows
I am exporting a database from Access to a CSV, and I was (finally) able to get more than two decimals to transfer after changing my Windows Region settings settings to make 9 decimals the default. Is ...
- 13.4k
0
votes
0
answers
29
views
Access 2010 VBA IDE watch window graphics bug
I'm experiencing a weird issue with the watch window. When I first set a watch the details appear just fine, but in break mode as soon as I advance (F5 / F8) the text changes to white, please see ...
- 524
0
votes
1
answer
427
views
How to highlight Selected variables and object in Acess IDE
I was wondering if there is a way or a plugin or anything that will make me able to highlight variables in Access IDE . I mean like when I select a variable or a object or a method , I want to see it ...
- 111
-1
votes
2
answers
528
views
Vb SQL query in Access not working
I'm trying to have a drop down menu populate a list of items under a given selection. So far I am able to bring the entire table:
Drop down list here
I'm having however, a hard time to fit in the "...
- 109
1
vote
1
answer
131
views
Auto checkbox form filling
I have a data set like this:
40.45.68.70
50.51.74.90
46.49.25.30
77.78.45.12
5.78.90.94
.
.
.
goes on...
I would like to insert these values into a checkbox form code automatically.
The form code is ...
- 27
0
votes
1
answer
556
views
More efficient way of creating an array of months & revenue metrics?
I am relatively new to VBA and had a question about defining arrays.
I am writing a function in Microsoft Access to project five revenue metrics for the rest of year based on the most recent month's ...
- 3
1
vote
1
answer
4k
views
ms-access VBA, can't unlock locked project
I`m trying to edit a project in MS-access VBA. If I right click on the project and choose Properties , VBA shows a popup: Project is unviewable.
I cant even get to the screen to unlock anything or ...
- 149
0
votes
1
answer
1k
views
Add leading zero to a field without formatting as text
I have a column of UPC codes, some of which are 7 digits long and I need to convert them to UPCA codes which are 12 digits. I used the VBA below to create a module that I am calling in a query on all ...
- 145
0
votes
0
answers
854
views
Saving a record to a table from a form, which includes a subform, only after button click
I'm a newcomer to Access, so I haven't been able to figure this out yet. After Googling, I found the code below, but there are issues with it.
I have a bound form that includes a subform. I placed a ...
- 145
0
votes
0
answers
1k
views
How to you detect which input device is being used in MS Access?
I've tried googling this but all the search results are on how to detect a device period, not how to detect which input device is being used.
I have a barcode scanner we're using to check-in patrons ...
- 184
0
votes
1
answer
7k
views
How do you calculate age from a birth date in MS Access?
How do you calculate the current age of an individual based off of their birthdate in MS Access?
I've tried =DateDiff("yyyy", [DOB], Date())
But that only returns how old they will be that calendar ...
- 184
3
votes
1
answer
1k
views
Data on excel from access database based on criteria in excel
I have a MS Access database to which an excel file is connected to view output. MS Query is used to interface and fetch data based on two conditions date & ID. I am unable to specifiy the ...
- 31
1
vote
1
answer
3k
views
Tell If Local Table Is A Linked Table In A Separate Database
I just took over as an IT guy at a company that has a myriad of Access databases all with local tables. A database may have 20 local tables, and 10 of the tables are linked into different databases ...
2
votes
1
answer
66
views
Disable Warning Sign
Is there any way to stop this warning sign from popping up in Microsoft Access 2007-2013 via VBA method? I have tried (DoCmd.SetWarnings False) and (Application.DisplayAlerts = false)
Apparently Both ...
- 179
0
votes
0
answers
1k
views
How To Catch Any Error In Access VBA
I found a tutorial which showed me how to log errors in Access VBA, and it works mostly...but when checking my error table there are roughly 40 entries that do not have an error number logged nor is ...
- 2,095
0
votes
0
answers
49
views
Formatting dates in Access
I'm embarrassed with a seemingly very simple problem. I have about 7 million of rows in Access, with a date column formatted as numbers. They are the 5 digit type of numbers excel uses when dates are ...
- 13.4k
1
vote
1
answer
587
views
Access MsgBox for automated email
I have an Access DB set up with VBA code for a yes/no command button to send an automated Outlook email. If user selects Yes, it opens the email to allow for attachments.
My problem is that when the ...
- 45
1
vote
0
answers
145
views
How to put name and registration number in a form
I'm creating a database which stores client related information, in which there are two tables:
A. Client registration
B. Work done.
So I have created 2 forms:
a. ...
- 111
0
votes
1
answer
194
views
Hardwareinventory - Copy Record with values in Access 2016 Web App
I want to make a WebApp in Access which help me inventory all our Hardware.
Below is a VERY wip ERM for the Database behind it. Every CPU-Object is its owm physical thing (if we have 2x i5-3450 there ...
- 35