Questions tagged [ms-access]

15152 questions
1

votes
1

answer
343

Views

Multiple Left Joins in MS Access using sub-queries

I'm trying to run a query with multiple left joins. I understand that MS Access has additional parameters requiring () around the joins, otherwise I get a 'Missing Operator' error, however I'm using sub-queries which adds even more complexity. I can't figure out the syntax. Can someone help? Here is...
Brian Michael
0

votes
0

answer
19

Views

is there a way of reding individual cells in an access table using row and column indexing

Hey:) am trying to display text in a singe cell of an access table in a text box how can I read that specific cell am new to databases so I can only go as far as connecting to the database itself.
Kevin Gombe
0

votes
0

answer
8

Views

How to set the “collate” property of a Printer

I am running Ms-Access reports through my application in vba. When a user wishes to print a report, my application exposes its own print dialog to choose the content and format of the report. In some cases the user may decide to create multiple copies of the report. The report is generating tickets,...
Perry Sugerman
1

votes
1

answer
24

Views

MS Access 2016: CTRL+A saves the form?

I have a strange problem that I don't know how to track down. I have a form that closes itself using an _AfterUpdate event. I found, by accident, that when I am inside a TextBox control and I attempt to select all text using CTRL+A that the form updates. This causes the _AfterUpdate to run and the...
HackSlash
1

votes
2

answer
66

Views

Use VBA to enable a text box when a check box is checked

I am new to VBA and I'm trying to create a form in Access where a text box is enabled or disabled based on whether a check box is checked or unchecked. So if the 'Survey Requested?' box is checked the 'Date Survey Requested' box is enabled for the user to enter a date. I have the following code: Pri...
Alex
1

votes
2

answer
34

Views

MS Access DoCmd.Transferdatabase when source has a password

I need to import tables from various databases on a monthly basis. Once the tables are imported, the databases are archived and not looked at again. I have the following VBA code which works fine when a DB is not password protected: Private Sub ImportTheData(ByVal dbImport As String) DoCmd.SetWarnin...
rohrl77
1

votes
1

answer
31

Views

Access SQL query with JOIN and a COUNT statement not counting anything

I have two tables in MS Access. One for planets and one for the type of star it orbits. I want to get a count of the planets per star type...so something like: +----------+--------------------+ | StarType | PlanetsPerStarType | +----------+--------------------+ | A | 4 | | B...
SkyeBoniwell
1

votes
1

answer
46

Views

MSACCESS, how to edit a record after insert using the “after insert” trigger

MS Office 365 ProPlus, Access 2007 - 2016 I'm trying/failing to change the value of a field in a table after it is inserted using the 'after insert' trigger. Something like this... EditRecord SetField Name orig_id Value = [mytable].[id] End EditRecord This doesn't seem to work. USysApplicationLog g...
daveg
1

votes
3

answer
61

Views

MS Access VBA, How to Send an Email with Outlook?

I am working with an Access application within Acess 2016. Part of the application outputs to a PDF file via the DoCmd.OutputTo method. I then want to either send this PDF attached to an email I build out in code, or open a new Outlook email with the file attached. My issue is that when I click the...
DevonRyder
1

votes
3

answer
96

Views

Is there a way to interrogate the status of AS400 jobs from VBA?

I'm trying to provide a user interface in Access to display the status of scheduled AS400 jobs, to show them as either pending, running, or complete. I've inherited some code which schedules several jobs, which runs fine - but there's no way the user can see what the job status is, and they sometime...
Belladonna
1

votes
1

answer
33

Views

MS Access collection object value is being overwritten

I have a global collection object in a form that I am using to store IDs. Dim newCollection As New Collection When a user hits a btn Private Sub btn_Click() Dim key As Variant newCollection.ADD Me.ID, CStr(ID) For Each key In newCollection Debug.Print key Next key End Sub When a user is at ID 1 in...
jedu
0

votes
0

answer
2

Views

Is there a way to add existing fields to a split form in Access only to the data view portion at the bottom

I am setting up a database for some coworkers and they asked for something a little different involving forms and split forms. There are three separate people who put info into the database based on location. Due to certain reasons the forms they use are different by a couple factors. Those forms ar...
Hov Gula
0

votes
0

answer
6

Views

How to populate an append only long text data type field

I use a form to update/insert a database in access 2016. I found access has an option to record version of a field value change if data type of this field is long text and append only property is set to yes. I decided to concate all the values of a single record and save to this long text (history)...
SIslam
1

votes
3

answer
1.4k

Views

Batch updating of an Access database (DDL) . Possible?

I have taken over support of a VB .Net application that makes use of an Access database. Now I discover that the developer who wrote this application did not create any unique indexes on the primary key columns for the tables in the database. So none of the primary key fields are actually flagged as...
BruceHill
0

votes
1

answer
14

Views

Access SQL Is a string-attribute part of another string in the same string-attribute of the same table?

In a Access Query, which is performed by the Microsoft JET 4.0 Driver, i have to ask if a string-attribute part of another string in the same string-attribute of the same table? Example: In the table above, there is the string-attribute 'name' and i want to have a query that gives me all [Name]-wor...
0

votes
0

answer
21

Views

Open Recordset with nested queries

I am trying to open a recordset in Access VBA. I have written my SQL string in VBA as I am using Select Top with a variable. Currently I am getting run time error 3061 - too few parameters. expected 1 My SQL string is populating a form as a RecordSource so I know that the string is functional. My pr...
MaryLou
1

votes
1

answer
1k

Views

MS Access Multi-control KeyPress CTRL+A Handler

I have an Access database with 10+ text controls. I'd like to have some code to handle the CTRL + A KeyPress event. Normally, when pressing CTRL + A in Access, this selects all records. My end goal is to have CTRL + A only select that control's text (like pressing CTRL + A in your browser's URL bar,...
Pat
1

votes
2

answer
57

Views

Requery form after relinking tables

I am developing an MS-Access application with the data sitting in a separate backend database (Also MS-Access). The main form contains a button to open a settings dialog, an exit button (both in the form header) and a navigation control with a number of tabs showing subforms in form details. The set...
Oliver Bienert
1

votes
2

answer
44

Views

How to create a SQL table without the use of INSERT function?

The aim is to have a table with only one field called FieldA and with only two values ('Value One' and 'Value Two'). I would like to don't use CREATE or INSERT functions. I would like to use a code like this: SELECT ('Value One', 'Value Two') AS FieldA; But what is the right syntax to use?
Stefano
1

votes
1

answer
53

Views

Why can I not use format function while Jet-SQL to T-SQL?

I was trying to convert one of my MS Access query containing format to a SQL Server view. I have my view connected to MS Access as linked tables. I was looking at this MS Access to SQL server cheat-sheet to convert Jet-SQL to T-SQL. The cheat sheet says: Access: SELECT Format(Value, FormatSpeci...
jedu
1

votes
1

answer
40

Views

Access VBA Application.Quit Closing Multiple Databases

I created a launch app for the database users to open the front end of a particular database. For example, the users open the launch app (which is an Access DB on a shared network), which then performs the following actions: Copies the front end db from the shared network location to the user's loca...
Tim
1

votes
2

answer
38

Views

How do I automate this date code,s o that I don't have to change it every year. I need all dates greater than or equal to Dec of last year

How do I automate this date code,s o that I don't have to change it every year. I need all dates greater than or equal to Dec of last year. For example in 2019 i need dates of dec 2018 +. In 2020 I will need load dates of Dec 2019 + where (Load_Date) >= #12/01/2018#
user3860954
1

votes
3

answer
64

Views

The expression you have entered refers to an object that is closed or doesn't exist error

I am trying to open 2 ADODB recordsets on a button click event like: Private Sub btn1_Click() Dim rs As New ADODB.Recordset Dim rst As New ADODB.Recordset rs.Open 'UPDATE table ITEM = ' & Me.ITEM & ' where ID = ' & ID, CurrentProject.Connection rs.Close Set rs = Nothing rst.Open 'Update Table2 set I...
jedu
1

votes
1

answer
41

Views

Inner join with like operator?

I'm working with access and trying to create a sql-query. Basically I got two tables, that I want to join. The first one has a 'short form' of the name and a value. The second one has a 'complex form' of the name and another value. The tables look like this: Test_Tabelle1 Feld1;Feld2 asdf;14 test;21...
ADarkHero
1

votes
1

answer
24

Views

Odd problem - Form won't close when opening new form using WHERE

I have two forms (in question here). frmContactList and frmContactDetails. frmContactList is a datasheet list of last names, first names, and e-mail addresses. The idea (which works and hasn't been a problem so far) is that when you double-click either the last name or the first name, the form fr...
BeardedSith
1

votes
4

answer
2.6k

Views

Ms access query for dont show blank filed

I have a query that has a couple of text fields where in the filter criteria I put 'Power'. When I put this criteria in, it also does not show me records where the field is blank. If I leave the criteria empty, it shows me all records including the records with the blank fields. How do I get the q...
Phani
0

votes
0

answer
10

Views

I cannot update a table field, error 3027 keeps popping up

The email is able to be sent out but I keep receiving this error. This wasn't a problem before, the code worked fine and I was able to update a table field DateEmailSent but now that field can't be updated. This only happened after I created another form, could it be that the code in that form affec...
acts17
0

votes
0

answer
14

Views

I have an Azure SQL Server backend database with 50,000 rows. Can I use Access to edit this? [on hold]

My database is currently using a web front end for access but maintaining this is hard work and I would like to move to a Microsoft solution. My problem is that I don't know what I can use. Would appreciate some suggestions here. For example, can I use an Access front-end to connect to my SQL Serve...
Alan2
1

votes
1

answer
94

Views

Object variable not set (Error 91) when using AHK to close an Access Application

I have an Access Form named 'Form1', and in the form it has a couple of subs like, Form_Load, Form_Unload, etc. I want to use an AutoHotKey script to control the opening and closing of the form. Form opening code: acc:= ComObjCreate('Access.Application') acc.OpenCurrentDatabase('\\...\target_databa...
Aaron_Geng
1

votes
1

answer
38

Views

Access UPDATE statement in query fails to actually update

The following tables exist: Passerine_Survey_Observation Species_Codes I'm trying to set the common name in Passerine_Survey_Observation with that of Species_Codes: UPDATE Passerine_Survey_Observation INNER JOIN Species_Codes ON Passerine_Survey_Observation.SPEC_FK = Species_Codes.SPEC SET Passeri...
Kevin R. M.
1

votes
0

answer
45

Views

MS Excel SQL Query via ADO

I have a problem that I have been attempting to solve all morning, and it is really beginning to bug me. Can anyone help? In short: A 'simple' SQL query pulling in info from an Access .MDB into MS Excel. I can get ALL of the following THREE queries to work without problem: SELECT * from Table WHERE...
Phil
1

votes
2

answer
1.9k

Views

Run SQL Update Query in MS Access VBA

I am trying to run this code to fire an update query from VBA. Access is giving me a syntax error. I suspect this has to do with the fact that I'm trying to run an update query using an INNER JOIN with a form. Is what I'm trying to do at all possible? Private Sub Btn_Edit_Data_Click() Dim db As DAO...
OneLineAtTheTime
1

votes
0

answer
33

Views

Cannot connect to MS Database with java

When attempting to open a connection to a database it fails giving me a NullPointerException. This happens when it is trying to get the connection on the con = DriverManager.getConnection(url); line. It goes from this line to the catch exception. What is the correct way? public void openDatabase ()...
lorddalek
1

votes
2

answer
288

Views

ConcatRelated() - query or tables

I have similar issue to this question Combine values from related rows into a single concatenated string value. I've got two queries: This is what it looks like now without ConcatRelated(): I need to get return: I tried to use this SQL: SELECT DISTINCT Q_Fakt1.FakturaID, Q_Fakt1.DatumVystavenia, Q_F...
Miroslav Daniš
1

votes
1

answer
385

Views

Why is there a result of `#Error` in an Access query with a join on two type-converted fields?

I’m getting a result of #Errorin an Access 2010 query which has a right join on two fields which are type-converted. I can eliminate the error by improving my queries, but I’m confused as to why it happens. I have the following two tables, T1 and T2. Both consist of an ID field and a text field....
Scott H
1

votes
1

answer
311

Views

How to Display data on a Label using MS Access Database in C#

I tried to get the balance and customer name to show up on the labels by getting user's input the customers ID on textbox1. But every time i tried to input the ID even just the first digit of the ID, it already shows error 'Data type mismatch in criteria expression'. con.Open(); OleDbCommand cmd = n...
Mango
1

votes
0

answer
61

Views

msaccess.exe not closed on remote machine

I try to find answer on this topic but still fail to resolve my question. I use a batch file to call a simple macro which only write a log into an access table. 'C:\Program Files (x86)\Microsoft Office\Office15\MSACCESS.EXE' 'G:\Work***.accdb' /X mymacro following is the vba code: Public Sub TestSes...
jason zhang
1

votes
1

answer
61

Views

How to backup/export my current database's settings?

I want to be able to put all of my database's settings, table layouts, queries forms - in short: everything that is relevant for its structure and behavior - into text files. This is of course to enable me to then track changes via SVN. This answer offers a great base in that the scripts export/impo...
Inarion
1

votes
1

answer
34

Views

formatting combobox elements

In my database serial-numbers are stored as an Integer. I want to represent these numbers in a Access-frontend combobox with a different formation: '121212345' --> '12.12.12345' When a number is chosen it should be stored as an Integer again. I already tried to populate the combobox by myself with s...
daly
1

votes
1

answer
141

Views

ADO.Net retrieving autoincrement values for MS Access database in strongly typed DataSets

I write a database client application in C# using MS Access as a DB server. Users insert new rows using a DataGridView. I need to retrieve autoincrement values of the inserted rows on this.MyTableTableAdapter.Update(MyDataSet.MyTable) operation. I can't use this.MyTableTableAdapter.Fill(MyDataSet....
DmitryB

View additional questions