FAQ Database Discussion Community


Extract employees between 2 Hire Dates VBA and SQL

sql,vba,excel-vba,ms-access,access-vba
I have designed a macro which should extract from a workbook database all employees who were hired between 2 Dates. Unfortunatley I'm getting a error mesage when I run the query. Error: Data Type mismatch in criteria expression. I don't know how to fix the issue. My regional settings: Short...

DSum Object Required in Access

vba,ms-access,access-vba,ms-access-2010
I have a subform in Access 2010 that lists the costs associated with different parts of a project. On another page, I have a TotalValue field that takes the sum of the costs for each project, and multiplies it by 1,000,000. Originally, I had just Parent.[TotalValue] = Me.Est_Value.Value * 1000000,...

Syntax error in Search function

access-vba,ms-access-2010
I found this search function code and followed the instructions but I'm getting a syntax error stating missing operator in expression and I can't find where the error is. Here is where the error is: Me.RecordsetClone.FindFirst "dateassigned" _ & Chr(34) & "*" & Me.txtSearch & "*" & Chr(34) Here's the...

DMin with Empty Column

vba,ms-access,access-vba,ms-access-2010
I have three DMins that I use to find the lowest number within three columns. The only problem is when I have an empty column, it returns blank. How can I take into account empty columns? This is the code I'm using. Private Sub UpdatePriority_Click() Dim MinGOPri As Variant Dim...

Change Index Settings Access VBA

vba,ms-access,access-vba
I'm trying to automate a process in Access, and one of the steps I wish to automate is changing the Indexing settings of certain fields in my tables. I need to do this to increase the speed of subsequent queries (the query is about 100x faster with the indexing). In...

Passing values from one form to another in Access

vba,ms-access,access-vba,ms-access-2010
I have a popup form with a combobox that lets you select a value from the primary key of one of my tables. After I select a value, I want the popup form to close and for another form to open with the observation I selected in the first form...

What is the default date format for access input?

sql,access-vba,ms-access-2003
I am not really experienced with ms-access db. I have the following sql-code that ask for input from the user: SELECT c.the_date, E.[iD #] AS ID, E.[first name] AS fname FROM [date-table] AS c, employeetbl AS E WHERE c.the_date between [enter a beginning date] and [Enter an end date]; What...

Browse Button for folder not files

access-vba,ms-access-2010
I am adding a browse button on an Access 2010 form; it will only allow me to attach files, not folders. Here is the code: Option Compare Database Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _ "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long Private Type OPENFILENAME lStructSize As Long hwndOwner As...

MS Access Form Filter Date range

access-vba
I do have an Access form with a dynamic form filter using many controls as criterion. All work just fine excepted for my date range... Here is the piece of code I am using.. Private Sub Command4_Click() Dim strWhere As String Dim lngLen As Long Const DMY = "\#dd\/mm\/yyyy\#" '***********************************************************************...

Access: Setting a source object to another source object path

vba,ms-access,access-vba
I'm trying something crazy again. I would like to use this line of code: Reports![PrntRep].SourceObject = Forms![Search Form]!Subform1.SourceObject The source object of subform1 is dynamically depending on which query you need to search. I want the source of the report to be set to the same query, so that I...

Don't understand why this query crashes Access

sql,vba,ms-access,access-vba,ms-access-2010
I have a query with a nested query in it like so: select table1.name, table1.address from table1 where table1.year=[forms]![form1]![year] and table1.name not in (select table2.name from table2 where table2.year=[forms]![form1]![year]) This crashes Access every time. Am a doing something that isn't allowed?...

Match previous record after SQL Select

sql,access-vba,ms-access-2010
I'm trying to build on this code but I don't know how to tackle the 2nd part of this code. In the beginning the code in the SQL it is selected a record's caseid, program, and language from table Intake WHERE assignedto is Null. This is working perfectly fine. Once...

How to Set ActiveWindow.Zoom in Excel Through Access VBA?

ms-access,access-vba
I'm trying to create a macro in VBA that will format an Excel sheet through Access VBA. I came up against some surprises in Access which wouldn't recognize certain Excel VBA commands like .Select, .Interior.Color, and .ActiveWindow.Zoom. I got around the first two problems but can somebody help me with...

Run time error 7: out of memory

access-vba,ms-access-2010
I was recently adding a new query to a routine when i unexpectedly encountered the 'run time error 7: out of memory' error. I am trying to understand how this has come about and why. Is it fixable?

Run-time error '2498' - Access 2010 VBA - DoCmd.Trasnferspreadsheet

access-vba
I am writing a script that filters a query based off user input, then data in this query will be used to make a new table qryMyExportedData. From there, the data will be exported to ExportedData.xlsx. When I attempt to run my code, I get the following error: Run-time error...

printing label at specific position

vba,ms-access,access-vba,ms-access-2007,ms-access-2010
I have 11 inch X 8.5 inch paper to print labels. The paper is divided into two columns i.e 4.25 width of each column containing 11 labels in each. So, Size of each label is 1 inch X 4.25 inch. Now my Question is: I'm using Northwind DB in MS...

In SQL how do I update a table with a similar table?

sql,ms-access,access-vba
In my current Database I have a table whose data is manually entered or comes in an excel sheet every week. Before we had the "manual entry option", the table would be dropped and replaced by the excel version. Now because there is data that only exists in the original...

access vba code returns empty recordset despite table having records

vba,ms-access,access-vba
I have this code in VBA but its returning an empty recordset... Sub total_disc() Dim dbs As DAO.Database Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordset("pre_pl") rst.MoveFirst Where pre_pl is a table with more than a million records. Despite that I am getting rst = nothing. Any ideas what am...

Set date format as dd-mmm-yyyy in activex textbox VBA

excel,vba,excel-vba,access-vba
Is there a way to set date format as dd-mmm-yyyy in ActiveX TextBox? I'm using the code below to separate the quote but there is one error while user is putting month., i.e. 31-May-1993 but sometimes user is putting 31-may-2015. Because of that unable to fetch the data through server......

MS Access: select maximum column value of a table

vba,ms-access,access-vba
In my microsoft access database name is DBMS.accdb and I have an employee table and one of the column of the table is salary. I have created a form and on form load I want to get the max salary of the table and do some calculation and then set...

Checking if record matches another MS Access VBA/SQL

sql,ms-access,access-vba,ms-access-2010
I'm not sure how to find a matching record via VBA. In the SELECT SQL it is selecting a record that matches the field of program and language from table CFRRR. In the IF statement I want to know if the selected record's caseid (which is also a field in...

SQL Null statement not working MS Access

sql,access-vba,ms-access-2010
So I'm not really sure what is going on here but in my database I have a table that has 1000 records and 36 of them have [workername] empty. I was trying to run this SQL to select the unassigned, empty [workername] records to assign but nothing populates when I...

me.visible for labels and textboxes access

ms-access,access-vba,ms-access-2007,ms-access-2010,ms-access-2013
I'm trying to toggle conditionally visibility of textboxes and labels using the me.visible = false statements. However, it doesn't seem to function properly in the on format event for my report footer. I tried for onclick and onprint as well but none of them seems to work. Initially i tried...

How to sort records in a form in decending order from joined query

sql,access-vba,ms-access-2010
The below code is suppose to create a query and feed form 'frmstaticdatadepartments08' I am trying to sort the records in descending order based on the field tblContacts.TotalTeamSkills. Unfortunately the sort is not working. sql_get = "SELECT tblContacts.Group, tblTeams.ID as TeamID, tblValueChain01.MacroProcess, tblTeams.Team, tblContacts.CompleteName, tblContacts.Foto, tblContacts.CurrentPosition, tblContacts.Level, tblContacts.ContractType, tblContacts.Beginner, tblContacts.SemiSkilled,...

What is wrong with this VB/SQL query

sql,vb.net,vba,access-vba
This is the error I receive while debugging. {"Syntax error in string in query expression ''Laptop);'."} This is the SQL statement I have that isn't working properly. No matter what I do it seems to add a random . at the end of the statement, and I have no idea...

If String Value is empty, 0 , or Null MS Access, SQL, VBA

sql,access-vba,ms-access-2010
I'm trying to get figure out a work around for this coding. Essentially this code checks the values of fields in Table CFRRR if program and language match the values in table attendance Programs and Language. This normal code works. However, if there is not a matching value for language...

Fill primary key of form when openened with key of current record on form

ms-access,access-vba
I have an access form with button to open a form to add new entities to a table with related data. When this form is opened, I would like the primary key of that new entity that is being added to be autofilled with the primary key of the record...

Selected Index and Cell Value in Detail SubForm

ms-access,access-vba
I'm trying to implement a very simple subform document grid on a form: I have everything except getting the file path when the user clicks the grid. How can I get the filepath value from the user row click event ? Sorry if my terminology is off I rarely write...

MS Access validation rules not firing in subform

vba,validation,ms-access,access-vba,ms-access-2013
I have a datagrid that is a subform inside another form. I need to validate items inputted into this datagrid subform, but I'm having an excruciatingly hard time figuring out how. Whenever I read about this on forums and on Microsoft's help site, they recommend you use Required and Validation...

RTE 91 vs CTE Object Required: Fixing One Causes the Other?

vba,ms-access,access-vba
Function printerpart(outputtext As Collection) As Collection Dim TotalRecords As Integer 'Original build didn't include this line; no other declaration of TotalRecords, though? Set TotalRecords = outputtext.Count For i = 1 To TotalRecords outputext = outputtext(i) outputext = Replace(outputext, "&", "and") Print #1, outputext Next i Set printerpart = New Collection...

Access 2013 VBA automating Excel losing windows

excel,vba,ms-access,access-vba,office-automation
I'm supporting an Access application that's been in production for years and the Excel automation part of it stopped working with our Office 2013 upgrade and conversion from .mdb to .accdb. The Access databases contain a reference to Microsoft Excel 15.0 Object Library. The Excel objects are declared: Public objXLApp...

Get the VBProject of a Database

vba,ms-access,access-vba,vbe
Given a database object in MS Access VBA, how can one get that database's VBProject? Function GetVBProject(ByVal db As Database) As VBProject Set GetVBProject = ??? End Function The only way I know how to get VBProjects in Access is through Application.VBE.VBProjects.Item(???). However, I won't know what order of the...

Iterate Databases and Delete Query

ms-access,access-vba
I am trying to iterate a table with database names in it, open the database, check if a query exists, if it does delete it, then import a query from the database this code is being executed from. I keep getting a compile time error on the line of Set...

Adding Access totals cause report to not run

ms-access,access-vba,ms-access-2010
I am trying to add a few totals (sums and counts) using the wizard to an Access report. When I add them to the report and try to view the report, the report fails. Without the totals the report will display. The only thing I can figure that may be...

How to get label text from control?

ms-access,access-vba,controls
On my Access form I have an edit control and a combobox. Each has a label that is attached to it on the form. In the code, how can I fetch the text of the label for each control? I want to produce a msgbox using the label's text. I...

On click change chart query in ms access

vba,ms-access,access-vba
In my ms access project, I have one table CustomerT and 3 columns 2012 2013 2014. I have created a form named TestForm which have a graph named MyGraph and a combobox named SelectYear. In my graph row source I have a query named qGraph SELECT CustomerT.[2012], CustomerT.[2013], CustomerT.[2014] FROM...

Run various queries based on a combo box selection

vba,ms-access,drop-down-menu,access-vba
I have a form where a user will select a value from a dropdown. Based on this selection, I would like a series of queries to execute. For example, if a user selects 'A', then queries 1, 2 and 4 would execute. If user selects 'B', then queries 4, 2,...

I need help setting the RecordSource of a Report within a VBA Function

vba,ms-access,access-vba
Ok, so I've been looking around here for a few days (and a few other sites) and while I have modified my code a good bit to find a solution, it still doesn't work. I have four source queries, and I'd like to set the record source of my report...

Excel Import Data from SQL- Date comes in as text value

sql,sql-server,excel-vba,access-vba,toad
I'm importing data from oracle toad database into an Excel sheet using data connection. The table includes a Date column, but this column comes to Excel as text rather than the date. Is there any way I can fix this issue? Sub Show_data() Dim con As ADODB.Connection Dim recset As...

Access Set Unbound Form to Current Record

ms-access,access-vba
First post so hoping this makes some sense. I’m adding data to tables from unbound text boxes using a command button and the .AddNew method. The form itself is still bound at this point. FYI, I’m using unbound boxes as the UI needed for junction tbls was not an option....

Listing specific requirements on Access Database form

database,ms-access,access-vba,relational-database,entity-relationship
I am trying to create a database using Microsoft Access Database, but I have a problem. I want to create a database that holds information for computer components and a form to list certain components to build a computer. For example, I want to build computer A, and I have...

Creating a query which references a sub form from a sub form

access-vba,ms-access-2010
I am trying to reference a control 'txtDescription' located on sub form 'frmStaticDataSkills02' from the sub form 'frmStaticDataSkills03'. Both of these forms sit in form 'frmStaticDataSkills01' which is a sub form of form 'frm_StaticData'. Below is my string that is not working. =[Forms]![frmStaticDataSkills01]![frmStaticDataSkills02].[Form]![txtDescription] ...

Is there a more efficient way to display multiple query results in different text boxes on a Form

vba,ms-access,access-vba,ms-access-2007
I have a form with several different text boxes. Each text box is populated with the result of a different query. I'm not very familiar with VBA / Access (I am teaching myself) and am not sure if what I'm doing is the most efficient. I was hoping someone could...

MS Access VBA: UBound & LBound functions returning subscript out of range error on class array

vba,access-vba
I am receiving a "Subscript out of range" error when calling the LBound() and UBound() functions on a global object's array in MS Access 2003 VBA. I don't understand why this is occurring because I am certain the array has been initialized since I can access values from the array...

What are difference between referencing a DAO Recordset field?

ms-access,access-vba
I am curious what are the differences between the following and if there are advantages for one over the other. Assume MS Access and DAO Recordset rst![field] rst.Fields("field") ...

Why can't I remove a reference from an Access 2010 .accdb project?

access-vba,ms-access-2010
Experimenting with shared modules in MS Access 2010, I want to remove a reference to a compiled database (.accde) containing the code module, but the 'remove' option is greyed out for both the module and the containing database in the project browser. How can I drop the reference? The code...

How to escape a character in vba

vba,ms-access,access-vba
How do I escape a character in vba? I am trying to escape a ; since I have a string that the compiler cuts at ;. I tried the \ but it doesn't seem to work. Any other special escape in vba? Set mySqlCon = wrkODBC.OpenConnection("connection1", , , "DRIVER={MYSQL ODBC...

MS ACCESS SQL Join Syntax

sql,ms-access,join,access-vba,left-join
I can get this query to run if I remove the Join, but once I add the join, I get the following error: Run-Time error '3135': Syntax error in Join Operation sourceDB = "C:\sourcedb.accdb" SQL = "SELECT e1.lid " & _ "FROM (eventlog e1 IN '" & sourceDB & "'"...

Using the VB function split I get Compile error: “Can't Assign to array”. [duplicate]

ms-access,access-vba
This question is an exact duplicate of: Using split function into array is causing Compile Error: Can't Assign to array 1 answer I am attempting to use the split() function to split out a name based on spaces in the given name string in Microsoft Access. When attempting to...

Using “V” to represent vowels in user input in SQL

sql,ms-access,access-vba
I need to run a query, in which the user inputs a string, and then the query returns any records, in which any part of the data matches a string. For example, the parameter "w" would return "wa", "ew", etc. I can do this easily enough: SELECT getSourceID.Title, getSourceID.Author, word.form,...

Populate Text box on report based on value on another form

ms-access,access-vba,ms-access-2007,ms-access-2013
Very novice question but I have a report that has a couple of columns including usernames. I also have a form where a user can select a user name in a combo box and the report will open up only showing records that pertain to that user. Is there a...

Specify Format / set up placeholder for multi-column ComboBox

ms-access,access-vba,ms-access-2013
I want to show a placeholder in a ComboBox if no values has yet been selected or the previously selected one has been removed / deselected. For single-column ComboBoxes that works like a charm using @;"My placeholder" as the Format of the ComboBox. So far so good. The problem starts...

Access-VBA Non-Error Values Displaying as #Error

vba,ms-access,access-vba
I am attempting to handle errors caused by blank fields in an Access database. I have a function that takes the Latitude field's value, parses it to another format (from DMS to decimal), and is used in a query as a new field. As long as Latitude contains a valid...

Passing vba variable date to sql statement

sql,vba,ms-access,access-vba,ms-access-2003
As a newbie in vba-sql, I am trying to populate a table using a for loop in vba and the Docmd.runsql to populate the table. The code works fine but then the values in the table are: 12/30/1899 for all the records. I used a message box to check and...

Getting Error 3048: Cannot open any more databases

access-vba
I recently split my database. My form is a calendar with a Tab for Month View, Week View, and Day View with 42, 7, and 7 subforms, respectively. All of these subforms are unbound. When a tab is selected, all the subforms in that tab is assigned a ControlSource while...

MS Access programmatically edit a datasheet cell value

ms-access,access-vba,ms-access-2013,datasheet
I'm validating my datasheet programmatically because it's not possible to do it the "normal" way (linked tables). The "normal" way to tell the user they entered a badness is to punish them with MsgBox popups - I want to display a message in a label on my form (this works)...

Is it possible to disable/enable controls in an Access Subform for a specific record? Access 2000 - VBA

vba,ms-access,access-vba,subform
As the title says, I am trying to disable some controls of a specific record based on the combobox value of that specific record. So when a User chooses A in their combobox field X will be enabled but if they press B in their combobox the field X will...

VBA How to exit function on error? Not working. Access 2007

vba,ms-access,error-handling,access-vba,ms-access-2007
In Access 2007 My Error Trapping is set on Break on Unhandled Errors I want the code to stop at the line where an error occurs and exit functions instead of resuming to the next line of code. However it doesn't seem to be working for me. I purposely created...

Using UDF output string in SQL SELECT Query?

sql,vba,ms-access,access-vba
I have a user-defined function (in Microsoft Access) that takes a value from two fields and returns a string, which corresponds to a column name. This might not seem very clear; below is a simplified example of what I'm trying to do. Unfortunately I can't use the real problem as...

Run Excel Macro via VBA in Acces

excel,vba,access-vba
I'm new at Access and VBA and I wanted to created an automated process. But I think I'm all over my head with this one. I'm trying to make a macro in Access that: checks if the file exists opens the excel file and runs the macro imports the results...

When should I use value() on cells() in vba?

vba,access-vba
I'm trying to read and set Excel cells values with Cells(1,1). I was successful with reading this value. But I've seen code with Cells(1,1).Value(). So when should I use the notation itself: Cells(1,1) and notation with .Value() (Text()/Value2())?

manipulating variable and retrieving report

access-vba
I'm trying to work out a problem and need some help. I have 3 types of users (T8XXXXX, P9XXXXX and XXXXX) each of these users have different elements and as such has a different report. The top two if statements work, as far as opening the reports, one report is...

Data type mismatch error with FilterOn MS Access

access-vba,ms-access-2010
I'm completely stumped. This code was working and is currently working on a different database but just stopped on the one I am working on now. It doesn't make sense why I'm getting a "Data type mismatch in criteria" error when I step through the code all the data that...

How to create a date using strings in vba?

vba,ms-access,access-vba,ms-access-2003
I want to run a for-loop from sometime to some specific time. Let's say from the first day of the year to the last day: I am given the year and I need to add the month and the day to it: I am trying to concatenate into a full...

Using DCount function with a query linked to a control

access-vba,ms-access-2010
The following code is giving me a syntax error message. I would like to use the DCount function on the current event of a form to count records in a table where the value on the control 'TeamID' is equal to that in field 'teamID' in the table 'tblCompetency06' Me.etcRecordNumber.Caption...

VBA - Using Typeof … Is ADODB.Recordset Results in Compile Error

ms-access,access-vba,ado,late-binding,adox
I am building a function with a set of supporting sub-functions to create ADOX.Catalog objects to help me build automation for Access database generation. I like to use late-binding for my applications because my user base doesn't always have the same version of office applications, so I can't always rely...

pass parameters to record source with query Access2007

ms-access,access-vba,ms-access-2007
I am relatively new to access and I have been tasked to improve the navigation of the app. I have a form which is used to open the report. The record source of the report is as follows, SELECT Projects.Project_Number, Projects.Customer, Projects.End_User, Projects.Engineering_Company, [Merged Ship and Delivery Dates].Sched_Next_Delivery_Date, [Merged Ship...

DMedian in access 2013, no values returned

sql,ms-access,access-vba
I have the following query in MS Access 2013 than does not return a Median value. The field IU is always NULL (blank) or 1. Column GM is a number formatted 0.0000 between -5 and 5 with NULL values in it occasionally. SELECT IU, DMedian("GM","tblFirst250","IU=1") AS MedianByIU FROM tblFirst250 WHERE...

Access SysCmd function not working as expected for some action values

vba,ms-access,access-vba,ms-access-2010,statusbar
I've read the relevant historical material and this is not the same issue others have had. Adding DoEvents has no effect. First Problem RetVal = SysCmd(4, "Here's an Update!") clears the status bar text and meter, instead of updating the text. I have tested this in a sub and in...

Access 07 VBA Get Table by Path

vba,ms-access,access-vba,ms-access-2007
Is it possible to reference (in VBA) a non-linked Access table by its full path? For instance, say you're building a form that draws from a set of tables, but your user needs to add tables as time goes on; you might start with tableA, tableB, and tableC, but a...

Recordset.Edit or Update sql vba statement fastest way to update?

sql,vba,ms-access,access-vba,ms-access-2003
I recently came across vba update statements and I have been using Recordset.Edit and Recordset.Update to not only edit my existing data but to update it. I want to know the difference between the two: recordset.update and Update sql Vba statement. I think they all do the same but I...

Access query based on query results

access-vba
I am new to Access and queries, however trying to learn fast :) The project has 2 tables: tblClientData contains only data of clients, tblClientComments contains all the commentaries made to all clients. Database Table with Clients data called tblClientData Table with comments to Clients called tblClientComments Form showing all...

Using the MAX() clause in Access 2007 SQL View

vba,access-vba,ms-access-2007
I am newer to Access and VBA, I'm coding in Access 2007 and attempting to build a query that will pull records from two different tables, the records in the tables have revision numbers assigned each time a revision is made. I can get the query to run and display...

Vba built in function for holidays during a year?

sql,vba,ms-access,access-vba,ms-access-2003
So basically i have two tables. A date table and a statHoliday table. The statholiday table has like all the holidays you can find in this country which is fine. I auto-populate myDate table to get every monday which is not a stat-holiday. I delete everything from myDate table before...

Status bar to display a cell value?

vba,ms-access,access-vba
I use the status bar in nearly all my macros . If implemented correctly , allows you to see where your code is at. E.g SysCmd(4, "Sorting...") SysCmd(5)’Set status bar back to normal Question - How do I get the status bar to display a cell value? E.g SysCmd(4, "Sorting...")...

Syntax error in Microsoft Access SQL Select query in VBA procedure

sql,ms-access,access-vba,subquery,select-query
I am having following VBA Code that has been giving a syntax error. Can someone please help me figure out what is causing the error? Private Sub Command11_Click() Dim EndingDate As Date 'Getting ending date from Label named endDate EndingDate = endDate StartingDateTxt = DateSerial(Year(EndingDate), Month(EndingDate) - 15, Day(EndingDate)) Dim...

Pass argument from command button on Access form to python script

python-2.7,access-vba
Using Access 2010 and python 2.7.8 Have a command button on Access 2010 form. I am trying to pull the value from the Field1 text box and pass it to a python script. I am struggling with passing the variable. Commented out stuff is other things I tried. Value in...

Load HTML file into VBA Microsoft Access Email

html,vba,outlook,access-vba
I am trying to load an HTML file into an email that gets sent by my Microsoft Access database. The email gets sent when the user clicks a button (Command109) Here is my code that sends the email: Private Sub Command109_Click() 'Start of code Dim strEmail, strBody As String Dim...

VBA keeping excel files locked from myself after code

excel,vba,excel-vba,ms-access,access-vba
I've been running into issues with this code. It works fine if I restart the computer and run it, but once the code has been run once it starts to cause errors. I will either get either the "save error" or the "admin error" because the file (either the original...

MS Access crashes every time I try to open Visual Basic

vba,access-vba,ms-access-2013
So I have a split db. dbBackEnd: Lives on the network drive, users don't touch it except through the front ends. dbFrontEndv1:Stored by me for safekeeping, works fine dbFrontEndv2:Stored by me for safekeeping, works fine dbFrontEndv3:Users are interacting with this now, works fine dbFrontEndv4: Freezes every time I try to...

Md Access: User defined function value is not showing in Textbox

vba,ms-access,access-vba
In my form I have a textbox, TextBox1 and I have set the control source of the textbox to a user defined function as =UserTracking(). UserTracking function is defined in a module named User_Defined_Functions. the function is as follow Function UserTracking() As String Dim test As String test = "TEST"...

Edit Update not working on MS Access?

access-vba
I'm new to MS Access. This is a basic stuff. I'm doing an edit button in MS Access forms, but I keep getting runtime errors. Here it is the code: CurrentDb.Execute " UPDATE FLIGHT SET " & _ "STD= ' " & Me.Text3 & " ' " & _ ",Destination...

Find the missing dates - vba Sql

sql,vba,ms-access,access-vba
I am trying to spot which student didn't submit his task and for what date. I want to check for every student whether current or not. I don't mind if the answer is in sql or vba code. More specification below: Task Table ------------------------------- SubID |ID | Task | Date...

Syntax for opening a report

ms-access,access-vba,ms-access-2010
I am getting a Runtime 2465 error message trying to open a report using the following routine: DoCmd.OpenReport "rptSprechi", acViewPreview, , [qry_rptsprechi].[WasteType] = "UE", acIcon ...

Not Looping MS Access

sql,access-vba,ms-access-2010
I'm not sure how to approach this loop. I've seen various ways and tried the If Not (rs.EOF And rs.BOF) Then in various ways but it only goes through the coding once. This code is supposed to loop through table Intake that meet the SELECT query's criteria. There are definitely...

Formatting Excel texttocolumn using Access VBA

excel,access-vba
I'm trying to convert all columns in an excel file using access VBA. I want to convert the columns from text to general format, using the TextToColumns function. The code that I'm trying to use is along these lines: Dim oExcel As Excel.Application Dim owb As Workbook Dim oWS As...

copy formatted text into access using vba

ms-access,ms-word,access-vba,ms-access-2010,word-vba
I need to save formatted text from Word in an Access Database. So far I've managed to figure out how to store formatted text in an Access Field (Create a Memo Field in a Table and set the Text Format as Rich Text). Searching SO I have not yet come...

MS Access: How to add multiple rows to table within 1 query

sql,vba,ms-access,access-vba
I need to create tables for temporary use in my database, each of them should have many rows (from 500 to 1200) that consist of two columns: id and bool element. Is there any way to create a query that will insert such number of rows into table? Or is...

Use DMin with Access Query

sql,vba,ms-access,access-vba,ms-access-2010
I have a table in Access 2010 that stores priority numbers for a project. There are three priority numbers. What I'm trying to do is to find the lowest number between the three columns in the Projects table, and fill it in as Overall_Priority. Right now, I have a query,...

iteration (for-loop) ms Access with past value

vba,for-loop,access-vba,iteration,recordset
I tried to translate a code from VBA excel to access. My data is a column of prices and I want to compute the returns. This is the original VBA code in excel: DerCol = Cells(T.Row, Columns.Count).End(xlToLeft).Column Cells(T.Row, DerCol + 1) = "Returns" For i = T.Row + 2 To...

Access filter on time

sql-server,sql-server-2008,ms-access,access-vba,ms-access-2007
I have a table with a time in a datetime field on an MSSQL2k8R2 server. I have linked the table in Access 2007. The table was migrated with the migration assistant from Microsoft. i.e. the Managemantstudio will give on SELECT TOP 3 [AbZeit], [AbBrP] FROM [dbo].[tSollFahrten] the Result AbZeit AbBrP...

Run time error 462 Access VBA using Excel

excel,ms-access,access-vba,runtime-error
I occasionally get a run time error when trying to open/manipulate Excel files using Access VBA. The error is "Run-Time error '462': The remote server machine does not exist or is unavailable What is frustrating is that the error occurs only for certain files and not others and in different...

Editing a query in another DB

vba,ms-access,access-vba,ms-access-2010
Is it possible to open a second Access database from within an Access database, and edit a query in that second DB? I know you can open one Access DB from another, but I'm just not sure whether or not you can edit a query that way. If it's possible,...

Taking the difference between current and previous data via Access VBA

vba,ms-access,access-vba
I have a table on MS Access 2010 that has data similar to the following with two columns DateLog and GenKW: DateLog | GenKW ------------------- 04/28/2015 | 6696.0 04/29/2015 | 6806.0 04/30/2015 | 6921.0 05/01/2015 | 7037.0 I want to take the difference between the two and get an output...

VBA Run-time error 3075

access-vba,syntax-error,runtime-error
I'm trying to write an SQL query in VBA but it's not working. Not sure why, its a simple SELECT query. Below is my SQL query: strSQLPharmContact = "SELECT TOP 1 tbl_Contacts.ID, tbl_Contacts.idSite, tbl_Contacts.role, tbl_Contacts.name, tbl_Contacts.email, tbl_Contacts.phone, tbl_Contacts.involvement, tbl_Contacts.Taken" _ & "FROM tbl_Contacts " _ & "WHERE (((tbl_Contacts.role)= 'Pharmacist') AND...

Access 2010 VBA - Open New Recordset - Values Unexpectedly Saved From Before Opening

access-vba
I am having a script auto-detect if a user exists based on their last name and an identification number. If it exists within the database, the form is auto-completed with the correct data and will be updated upon saving. If not, a new record will be saved after the user...

VBA Access/Excel - Toggle Read/Write

vba,excel-vba,ms-access,access-vba
Using VBA Access Is there a more efficient way of making an open read only excel file to read/write mode? Or check if read only is true wait till read/write is active I created a continuous loop that opens and closes the file till read/write is active. However sometimes it...

Microsoft Access VBA Run Time Error 2465

vba,ms-access,access-vba,runtime-error,ms-access-2010
I am currently working on a form to update fields in my database. The button (cmdFind) is meant to find the record for the part # (entered into text box txtFindPart), and then populate the data into In1-52 and out1-52. When I run it I get Run-time Error 2465 Microsoft...

From Access, how do I open a Windows Explorer folder knowing only part of the folder name?

vba,ms-access,access-vba,folder
My incomplete file path is stored in Me!txtFilePath. It contains a complete path to the folder's location and the first 9 out of ~30 characters of the folder name. Those first 9 characters are guaranteed to be unique. Currently I have a button that executes the code below when clicked:...

Update Microsoft Access Table Field from Excel App Via VBA/SQL

sql,excel-vba,ms-access,access-vba,sql-update
I am successfully able to connect to database, but the problem is when it updates table field. It updates the entire field in table instead of searching for ID number and only updating that specific Time_out Field. Here is the code below, I must be missing something, hopefully something simple...