FAQ Database Discussion Community


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] ...

MS Access 2010 query match and output

sql-server,ms-access-2010
I was wondering how to go about doing something in Access. So I have a two tables, one has data about universities by zip code and the other has a list of counties by zip code. i.e. Table 1 Zip code University 90005 Harvard 90006 yale 90007 columbia Table 2...

MS Access vba, open select query with parameters, run-time 3065 cannot execute a select query

ms-access,access-vba,ms-access-2010
I am getting frustrated trying to open a select query with a "parameter" set. I fully suspect I am building my query all wrong. I want a form with an unbound date textbox for the user to input a date (I have this). Then I want that date passed to...

How do I compare a date range to values in a table?

ms-access-2010,date-range
I have a form where the user will plan material transfers based on how many days he wishes to plan ahead for. Today is Monday. Let's say he selects that he wants to plan for 2 days ahead - up until Tuesday and Wednesday. Therefore, he would enter "3" into...

Optimize MS Access Double SubQuery

sql,ms-access,subquery,ms-access-2010
This MS Access query I'm building is very slow. I am testing only one Month of data (One table driving this query with 28,577 records, 36 columns). Here is what I am trying to accomplish: I want to return the count of unique vehicles by Model that are sold in...

MS ACCESS 2010 SQL Trickery

sql,ms-access-2010
I have a dates table and a customer fact table like below. I want to try to get the customer code on the dates left over that do not match the customer fact table. I'm not sure what to do in SQL to get this. Dates: Day of year| TY...

Hiding a row in a table with a query when 3 columns have null values

sql,access-vba,ms-access-2010
I am looking for the best way to build a query which would hide the record (row) in the event that three field values (in three different columns) would be null. The code below is giving me a syntax run time error message of 3075. Also, I am not sure...

Inner joined query with multiple where clauses linked to multiple controls

access-vba,ms-access-2010
I am getting a Runtime error 2424 message when building a string containing my SQL UPDATE statement. Access complains that one of the controls or tables is unrecognised. My instinct tells me that I have issues with my parentheses or the quotation marks. st_sql = "UPDATE tblCompetency02 INNER JOIN (tblCompetency05...

Adding new record in access 2010

vba,ms-access,ms-access-2010
I want to enable a few fields when i click on the Add button on my form.I have change the onclick to Event Procedure to be able to add the following code Category_Desc.Enabled = True My Category field get enable as expected but the Add button no long work Private...

Access table design

ms-access,ms-access-2010
I have the following requirement for an Access table and I'm having trouble figuring out the best way to design the table. I'm not sure if what I have is the best practice in this case. table fields are name, Status, Date each name will have minimum three status, and...

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...

Calculation Returns Zero Value Access 2010

vba,ms-access,access-vba,ms-access-2010
I have a sub in Access 2010 that takes the total cost of a column in millions of dollars (txtSumEstimatedValue) and multiplies it by 1,000,000 to give the cost in whole dollars (DesignEstProgramValue). The problem is that whenever one of the costs is changed, the final cost becomes 0 and...

Rename Duplicates in Access with SQL

sql,ms-access,ms-access-2010
I have a table in Access, and there is one column of data that has duplicates. I need to rename these duplicates (preferably by appending a 1, 2, 3, etc. at the end). For example, assume my data table looks like so: ID Name Title 1 George Washington PRES 2...

Add a new record to database using a query

ms-access,ms-access-2010
I have a form (formSale) that is based on a query and contain fields from multiple tables(all the tables are related ) .I want to add a new record to the tables , what code do i need to do that ? The following command doesn't work DoCmd.GoToRecord , ,...

Photos as Varbinary(MAX) in SQL Server 2012 causes error 502753 in Access 2010

sql,ms-access,sql-server-2012,ms-access-2010,varbinarymax
I have an Access 2010 odbc front end with a SQL Server 2012 back end. My images (.bmp) are stored as Varbinary(Max). When I insert an image using my bound object frame in an Access form, the image displays on the form and report perfectly. I had a large number...

Access query across multiple tables with potentially non-existent related records

ms-access,ms-access-2010
I'm working on an MS Access 2010 database, and I have 5 tables I need to query. Table 1 has an ID, name, a several other unrelated columns. Tables 2 through 5 have their own IDs, the ID of the related record from Table 1, a date field, and then...

Syntax on SQL statement with multiple conditions in WHERE clause

sql,ms-access,access-vba,ms-access-2010
I am getting a run time 3075 issue on the following SQL string below. Is it possible I am missing parentheses? sql_get = "SELECT [tblCompetency02].[HighLevelObjective], [tblCompetency04].[Self], [tblCompetency04].[SelfSpecialLanguage], [tblCompetency04].[SelfChecklist], [tblCompetency04].[Team], [tblCompetency04].[TeamSpecialLanguage], [tblCompetency04].[TeamChecklist], [tblCompetency04].[Organisation], [tblCompetency04].[OrganisationSpecialLanguage], [tblCompetency04].[OrganisationChecklist], [tblCompetency02].[Competency] FROM...

Wrong Number of Arguments Used With Function in Query Expression [closed]

sql,ms-access,filter,ms-access-2010
I have an SQL in Access 2010 that was written by someone else that gives a Wrong Number of Arguments error when I try and run it. It's supposed to filter a report from a search page. SELECT Activity.[ProjNo], Activity.[Code], Activity.[Type], Activity.[ProjNoStatus], Activity.[Preliminary], Activity.[Planner], Activity.[Designer], Activity.[Officer], Activity.[Manager], Activity.[Staff], Activity.[Analyst], Activity.[Manager],...

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?

How to set primary key when linking with CreateTableDef

sql-server,vba,ms-access,ms-access-2010
In a MS Access database, I'm connecting to views in a SQL Server database like this: Dim s As String s = "ODBC;DSN=mydb;Trusted_Connection=Yes;DATABASE=mydb;" Dim td As TableDef Set td = CurrentDb.CreateTableDef("vwMyView", 0, "MySchema.vwMyView", s) CurrentDb.TableDefs.Append td CurrentDb.TableDefs.Refresh This creates a linked table, which is linked to a view in SQL...

I have a visual C# project and I'm trying to insert data in a MS Access Database when I press a button [closed]

c#,ms-access-2010,oledb
private void btnSave_Click(object sender, EventArgs e) { string str = "insert into Checklist_Master values('"+comboBox2.Text+"','"+txtaurno.Text+"','"+comboBox1.Text+"','"+txtdeduction.Text+"','"+txtceckpoint.Text+"','"+mdlconnection.user_name+"')"; int dd = mdlconnection.excuteQuery(str); //MessageBox.Show(str); if (dd > 0) { MessageBox.Show("Data Saved Successfully..!!!"); reset(); } } And connectionstring is : public static void getconn() { con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data...

Parameters Error MS Access SQL

sql,ms-access,access-vba,ms-access-2010
I'm getting a Parameters error here: db.Execute strSQL, dbFailOnError saying "too few parameters, expected 1". I'm not sure exactly why. I'm referencing both tables. I'm also not 100% this is written correctly Workername = " & DLookup("username", "attendance", GetNextAssignee("program", "Language", "username")) I want [Workername] field to update to the workername...

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,...

Can't set CopyFromRecordset results to Date or Number

excel,vba,excel-vba,ms-access-2010
I'm trying to import a data set from Access 2010 into Excel using Range.CopyFromRecordset. There are some fields in the query where some of the values have been cast to dates or numbers, e.g.: iif(isdate([Install Date]),cdate([Install Date]),[Install Date]) AS [Install Date (cast)], iif(isnumeric([Feature Number]),cdbl([Feature Number]),[Feature Number]) AS [Feature Number (cast)]...

Get a Count of a Field Including Similar Entries MS Access

sql,ms-access,ms-access-2010
Hey all I'm trying to parse out any duplicates in an access database. I want the database to be usable for the access illiterate and therefore I am trying to set up queries that can be run without any understanding of the program. My database is setup where there are...

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...

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...

Inner join on a partial column in Access

ms-access,join,access-vba,ms-access-2010
I'm attempting to join two tables in MS Access 2010 where the join condition is part of(A.col1) = B.col2. I've not been able to figure out how to do this so far. My two tables have these critical columns: Table 1 has column ICD9 Code-Description* with values like:842.00 - Sprain/strain,...

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...

Browse for and get path for image then have selection loaded into text box in Access form

ms-access,access-vba,ms-access-2010
I'm using HansUp's code: Private Sub Command7_Click() Dim f As Object Dim strFile As String Dim strFolder As String Dim varItem As Variant Set f = Application.FileDialog(3) f.AllowMultiSelect = True If f.Show Then For Each varItem In f.SelectedItems strFile = Dir(varItem) strFolder = Left(varItem, Len(varItem) - Len(strFile)) MsgBox "Folder: "...

Control a subform from main form

ms-access,ms-access-2010
I have a main form Form_frmSaleand it contain a sub-form frmSale_subform.How can I "access" the fields in subform For e.g with no subform i would do Sale_Date.Enabled = True What code should i put if i want to control subform fields from main form(Suppose Sale_Dateis in a subform I tried...

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...

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,...

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...

Adding calendar in ms access and view the data of specific day by clicking the date

database,ms-access,calendar,ms-access-2010
I'm working on MS Access, using Northwind DB. I'm trying to to add a calendar in a form and link it with the dbo_orders table. such that i can retrieve how many orders made on particular day by clicking on the date in calendar. Ex. Consider the dbo_Orders in Northwind...

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...

How to select the TOP 1 record per group (Partition)

sql,ms-access,ms-access-2010,ms-access-2013
I've got a table called tblRoutes that holds a unique list of from and to routes (f = from; t = to): | fCity | fState | tCity | tState | |========|========|========|========| |New York| NY | Miami | CA | |Houston | TX |New York| NY | ... And then...

Re-directing linked tables

access-vba,ms-access-2010,dao
I am having what I believe to be a syntax issue with the third line of code below where I am trying to source a table located in a back-end database in a directory differing than the front end. Run time error is 3055 - file not valid Function linkToUnc()...

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...

Create a concatenated list of child table values from an Access database without using VBA

ms-access-2010,ms-access-2013
I have an Access 2010 database with a relationship between parent and child tables. I would like to be able to query the database from an external application and show values from the child table as a concatenated list of values in a single column, similar to what MySQL can...

Design an Access Form to Use Different Queries

sql,ms-access,ms-access-2010
I had an Access Form that works very well for my customer, however they want 3 identical forms, but with different underrunning queries for different types of searches. I could copy and paste the same form and attach the different queries, but this can become a huge maintenance and maintainability...

Access 2010 Update Table from Form Field using strUpdate

access-vba,ms-access-2010
I have a form, by which the user selects the current fiscal month-end date. When they user clicks on the check mark, I want that date to update the field CurrentFiscalMonthEnd in the table Tbl_Calendar_SetBucketDates by the following Code: Option Compare Database Private Sub Image_BucketSelector_Click() Dim db As DAO.Database Dim...

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...

Concatenate a Text String to the End of Specific Values (MS ACCESS)

sql,ms-access-2010
I need to add "-CLOSED" the end of certain Job Numbers in an Access Query. I can add it to one at a time but I need to do it for thousands of values. I'm not sure of the syntax to add more than one value in the WHERE clause....

Splitting an Access Database without the ability to create unique fe

database,ms-access-2010
I created a 2010 Access database for my team to use. Right now the database is in a shared folder on the server and everyone uses the same database file. I've run into some problems recently with the database auto-backing itself up and creating extra files (Database.mdb, Database1.mdb, Database2.mdb, etc)....

How is this form bound?

ms-access-2010
We are working with an MS Access Database (2010) that is a front-end to a SQL Server database. One of the programmers has created a form bound to a linked table. My problem is this: All of the controls are unbound, and yet they are always filled with data from...

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...

Export dynamic fields from Access to csv (schema.ini?)

vba,ms-access-2010,export-to-csv
I'm trying to export a crosstab query from Access 2010 to a csv without a text qualifier. I'm able to use the Transfer Text method with my other exports; the trick with this one is that the number of fields (and their names) change depending on what the user selects...

Select Query Doesn't Show All Results

sql,ms-access,ms-access-2010
I have a combobox on a subform (ProgramSubform) in Access that's supposed to list report years for a project from a table (Program). Most projects have more than one report year, but the combobox always brings only 2014. For example, the dropdown for project 5278 should be: 2012 2013 2014...

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,...

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...

How to link a table from front end to back end in split database (MS Access 2010)

database,ms-access-2010
I have created a split database in ms access 2010, In front end database I have added a table and I want to link it to back end database. How can I resolve this issue?

Too few parameters error MS Access SQL

sql,ms-access,access-vba,ms-access-2010
I'm getting a run-time error: too few parameters: expected 2. This code is supposed to get the next employee in line for assignments. The employees [programs] and [Language] have to match the [program] and [language] in the table CFRRR. strSQL = "SELECT TOP 1 WorkerID FROM attendance WHERE [Programs] LIKE...

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...

Running Access from Command line but not loading it?

ms-access,ms-access-2010
I would like to launch a MS Access macro from the command line (it's being run from another tool) to generate an output. Thing is, I'd prefer it if it could supress access from loading it's interface. I just want it to run the macro and shut down. Any thoughts...

Possible for 64bit Python to connect with 32bit MS access driver?

python,python-3.x,ms-access-2010,pypyodbc
How can I use the 32bit MS access driver for odbc connection in python 64bit? Can it be done by altering the environment handle or odbc constants in pypyodbc, or creating a bit conversion file? Another thought (not sure if possible): Is there a way to switch to a 32bit...

How to count records and it's numerical value

access-vba,ms-access-2010
I am looking for the best way to count records in an underlying table (or query) and allow one to capture that number on the active form. In short, I would like to create an object on my form which tells the users which record out of how many he...

MS Access 2010 login form with access levels

vba,login,access-vba,ms-access-2010
My management wants an access database that can have a way to control which users can see specific fields on a form. For example, if a manager logs in, a form will display the performance rating field. For any other user, the performance rating field will not be visible. So...

Find Lowest Value in Columns Access 2010

vba,ms-access,access-vba,ms-access-2010
I have a table in Access 2010 that has 3 separate priority fields. I have a sub that looks through the columns, finds the smallest number, and puts it in an Overall Priority field. Ex. SubProjNo | GOPri | StrPri | SOPri --------+-----------+----------+------------------ 1234-12-01 | 100 | 7 | 61...

MS Access 2010 - Query only half populates drop down list sometimes?

sql,ms-access-2010
I have a query of clients that populates a dropdown list in Access. This list has 41,000 items in it and is used to filter a certain client who you want to look at more information on. I would say 90% of the time the list works and all the...

How do I programmatically retrieve the control associated with a given table field?

ms-access,access-vba,ms-access-2010,ms-access-2013
I have an Access database in which every table/view is linked back to a SQL Server object. The tables have been heavily customized. Many of the fields are comboboxes with simple rowsources: SELECT field1, field2, blahBlah FROM blahTable We want to delete a bunch of tables, and I'd like to...

Access 2010 Error 3035 System Resources Exceeded on Update Query

sql,access-vba,ms-access-2010,jet-sql
I've got a routine that imports and performs some transformations on a 481 MB text file. The routine crashes on this code block for one workstation (but works fine on our other workstations): ' All of the eleven-digit telephone numbers encountered end in 0. If we get rid of the...

MS Access SQL error

sql,ms-access-2010
This code is supposed to select the TOP 1, but it's not working properly. Instead of showing only the TOP 1 record, it is showing tons of records. It may be because I have 2 tables referenced. In another code I only had 1 and it worked. I need to...

Adding a column to existing table in access without using any relationship

sql,ms-access,ms-access-2010
I am working on a project in ACCESS 2010 that requires me to give a rank to 30000 products based on their sales. I have tried using a query to do the ranking and it takes a long time. (Please find codes at Making the ranking query efficient) I figured...

Want to populate a combobox with two related tables in access

c#,visual-studio-2012,ms-access-2010
I have a two related tables in my access DataBase, Table1 and Table2. In Table1 I have "Cd_Table1"(primaryKey) and "Cd_Table2"(foreignKey). In Table2 I have "Cd_Table2"(primaryKey) and "Nm_Atr"(string). I want to populate a combobox with these two tables, but the "DisplayMemberPath" needs to be "Nm_Atr" and the "ValueMemberPath" needs to be...

Filter Access 2010 Subreport by Criteria Not Used In Subreport

sql,ms-access,report,ms-access-2010,subreport
I have a subreport in Access 2010 that basically acts as an index/table of contents. The user can use a search form to filter projects by specific criteria. Only the matching reports should appear in the report and index. The report filters fine, but the Index doesn't. Some of the...

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...

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,...

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...

SQL Update query on query result

sql,ms-access,ms-access-2010,inner-join
I have 2 tables like: Table 1 SID Sdefinition CValue 4057 s1 32 4058 s2 4059 s3 6 4060 s4 Mapping_tbl SID SINID ECFID SID-SINID 4057 1099 4027e 1099_4057 4058 1099 4027e 1099_4058 4059 1121 4003e 1121_4059 4060 1121 4003e 1121_4060 Query1 SELECT Mapping_tbl.SID, Table1.Sdefinition, Table1.CValue FROM Table1 INNER JOIN...

Update a foreign key based it's value in it's primary table

sql,ms-access-2010,relational-database
Given the following two related tables how can an SQL query update a customer key of an order given the customer name. +----------+-------------+ +-------------+--------------+ | OrderKey | CustomerKey | | CustomerKey | CustomerName | +----------+-------------+ +-------------+--------------+ | 700 | 1 | | 1 | 'Idle' | | 701 | 2...

MS Access, update query will not update a linked table

sql,ms-access,ms-access-2010
I have a remote user with a version of my db and a half dozen users interacting with front end versions of the db. The back end lives on the network drive. The remote user cannot use the network drive because it is too slow through the VPN. So daily...

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...

Microsoft Access report shows empty row at the end

ms-access,access-vba,ms-access-2010
I have an Access report. The problem is that it always display an empty row at the end. In the query there are no empty values however the report always shows an empty row. Any ideas? https://drive.google.com/file/d/0B5XFoGik1WSWUmRkRktla2VIYnM/view?usp=sharing https://drive.google.com/file/d/0B5XFoGik1WSWcFF3SnZpekVwTlU/view?usp=sharing On a form I would set "Allow additions" to false. However I...

Differences in referencing forms in VBA Access

vba,ms-access,access-vba,ms-access-2010
Intro: I have two separate forms. frmABC frmXYZ I have frmABC in focus and I run a procedure that will "Tick" a checkbox called ChkConfirmed on frmXYZ. Question: What is the difference between these two ways of referencing another form? 1. Form_frmXYZ.ChkConfirmed = True 2. Forms!frmXYZ.ChkConfirmed = True For some...

Fill Field When All Checkboxes Toggled Access 2010

ms-access,checkbox,access-vba,ms-access-2010
I have an expenditures subform in Access 2010 that lists the predicted costs associated with the project for each year. Most projects only have one year, but some have more than one. Each cost has a Final checkbox next to it that should be checked when the amount is confirmed,...

MS Access 2010/2013 Transition Issues

ms-access,ms-access-2010,ms-access-2013
So currently my boss and I are the two employees that work on our company's access database. I just got Office 2013 on my computer and he is will working with 2010. We have ran into some inexplicable bugs with the database. Most of these can be fixed by just...

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...

MS Access multi (INNER, LEFT & RIGHT) JOIN query

sql,ms-access,join,ms-access-2010,outer-join
Ok, here's the thing. I have the Following tables involved : > YEARS ------------------------------ ID YEAR ACTUAL ------------------------------ 1 2014-15 TRUE 2 2015-16 FALSE > SHOPS ------------------------------ ID NAME ... ------------------------------ 1 ThisShop ... > ITA ------------------------------ ID YEAR_ID SHOP_ID ------------------------------ 1 1 1 2 1 2 ... > INSPECTORS...

Dsum Function in Query Field not working properly

ms-access,sum,ms-access-2010,ms-access-2013,running-total
I have a date based query that returns two fields, Week Ending Date and L2N, and I want to add a third field that provides a rolling total of the L2N field. I have tried using DSum as follows: RunL2N: DSum("[L2N]","Occupied Apts & L2N", "Week Ending Date=" & "'" &...

Synchronizing record navigation control and record selection combo box

ms-access,access-vba,ms-access-2010
I have a form bound to a query, with controls bound to fields of the query. I also have an unbound combobox, whose Row Source is the same query, used to select the current form record via its After Update event handler: Private Sub Loc_cbo_AfterUpdate() DoCmd.SearchForRecord , "", acFirst, "ID...

Access Database Total Field not calculates empty records?

sql,database,ms-access-2007,ms-access-2010
My Query Result is as below Query: SELECT reservations.customerid, (SELECT SUM(balances.balance) FROM balances WHERE balances.customer_id = reservations.customerid) AS Preveious_balance , (SELECT SUM(services.Amount_due) FROM services WHERE services.customer_id = reservations.customerid AND services.status=0) AS Service_due , (SELECT SUM(foods.Amount_due) FROM foods WHERE foods.customer_id = reservations.customerid AND foods.status=0) AS Food_due, ((due_nights.Due_nights - reservations.billed_nights) * rooms.rate)...

VBA Acces Database - Clean up code by the use of a loop with 2 variables

arrays,vba,import,ms-access-2010,excel-2010
I'm trying to write a code that imports 4 different files in 4 different databases. I wonder if there is a way to make this much shorter and simpler by using a loop? I tried one, but I can't figure out how to direct one file to a different database....

Access 2010 SQL: Multiple Condition Where Clause not filtering properly

sql,ms-access-2010,where-clause
I am attempting to write an SQL query for MS Access 2010 to select any combination of 6 fields. I have written the 63 possible combinations into my query, each bracketed and separated by an OR. Internally, each is joined by AND. Furthermore, each field is set to request parameters...

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 ...

SQL selection query linked to two controls with multiple WHERE clauses

sql,access-vba,ms-access-2010
I am getting a syntax error with the below SQL select query. Using the first where clause there is no issue. As soon as I add the second, I get a syntax error message. sql_get = "SELECT tblContacts.ID AS IDResource , tblImmages.Immage , tblContacts.Foto , tblSkillsMatrix01.CurrentLevel , tblSkillsMatrix01.GeneralNotesSymbol , tblSkillsMatrix01.ID...

How to Continuously Develop and Deploy an Access 2010 Database Application

ms-access,deployment,ms-access-2010
I've been developing an Access 2010 Database Application, let's call it Skill.accdb. I'm at the stage where I want to start deploying it so users can start using it and continue developing at the same time. I've been reading MSAccess-Deployment-Best-Practices, Deploy-an-Access-2007-application, and Ways-to-share-an-Access-database which talk about splitting, compiling, installing, etc.,...

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...

Syntax Error MS Access SQL

sql,ms-access-2010
I'm stuck on this SQL, it is saying that I am missing an operator and highlighting "Available". What I am trying to do is create this Update query in VBA, I need the field [TS] to update but only if the [Status] is "Available". Here's the full code below: UPDATE...

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...

If error on Access query

ms-access-2007,ms-access-2010
Hi I am doing a query in MS Access that is the following: Tenure_days: DateSerial(Year(Date()),Month(Date()),11)-DateSerial(Mid([AHT_Tenure].[ABAY Start Date],1,4),Mid([AHT_Tenure].[ABAY Start Date],6,2),Mid([AHT_Tenure].[ABAY Start Date],9,2)) In some records is get #Error when [AHT_Tenure].[ABAY Start Date] is empty, so how do I edit the above query so that when Tenure_days = #Error to put 120...

Data type Mismatch in criteria expression in MS Accss2010

c#,ms-access-2010
I tried execute INSERT query, but it throwing above error. I have given my code below. public string InsertMoveDetails(string desc, string currentLocation, string newLocation, string newBay, string requestedBy, string movedBy, string approvedBy, DateTime dateOfMove) { string desc, currentLocation, newLocation, movedBy, approvedBy, requestedBy,newBay; DateTime dateOfMove; desc = textBoxDescription.Text; currentLocation = textBoxCurrentLocation.Text;...

MS Access, Combobox, select fields bases on query, How do I weight the result fields

sql,ms-access,ms-access-2010
I have a db where based on user input a series of comboboxes will populate. Private Sub txtUserInput_AfterUpdate() cbo1.RowSource = "Select txtValue from tbl1 where (txtValue=" & [Forms]![Form1]![txtValue] & ")" cbo1.Requery End Sub Easy enough. But I would like the order of the results to change based on weights I...

Runtime error 13 on a simple update query

sql,access-vba,ms-access-2010
The simple query below is returning a Runtime error 13. Doing a simple check on the internet the feedback seems to be that I may be some type of bug in MS Access which requires an update.. Is it possible? This type of query I have used time and time...

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?...

Syntax error ( missing operator ) in query expression of

sql,syntax-error,ms-access-2010
I have a table in Access 2010 and I want to make a kind INNER JOIN query from several tables I do is the following: SELECT * FROM (Archivo Maestro INNER JOIN Concepto Presupuestal ON Archivo Maestro.ID Concepto Presupuestal = Concepto Presupuestal.ID Concepto Presupuestal) INNER JOIN asunto_estrategico ON Archivo Maestro.ID...

Access-vBa: Error after splitting database

database,ms-access,access-vba,ms-access-2010
I have an Access database was created, it's working perfectly, after successfully splitting the database to back and front end I shared the _be file in shared drive and front end file in my local, I tried to run the front end and I am getting error when macro is...

Page captions (of tab control) not shown in print preview

ms-access,printing,tabs,ms-access-2010,print-preview
I'm using MS Access 2010 and have created a simple form with a 2-page tab control. On each page is a single label. In Design View, the form looks like this... In Form View, the tab control and appropriate label are shown as expected... However, when I print preview what...

MS Access: How to group by first column- summing some columns while keeping others the same

sql,ms-access,ms-access-2007,ms-access-2010
I have Data that looks like: ID | Year | State | Cost ----+-----------+-----------+----------- 1 | 2012 | CA | 10 2 | 2009 | FL | 90 3 | 2005 | MA | 50 2 | 2009 | FL | 75 1 | 2012 | CA | 110 I...

Excel Not Transferring Data to Access Database

excel,excel-vba,ms-access,ms-access-2010,excel-2010
I have a table in in Access 2010 that stores project information. The main columns - for this purpose - are LocationID, Priority1, and Priority2 (which have proper names but for generalization...) I also have an Excel 2010 spreadsheet that has this information to be exported into Access. What it...