Other subreddits of interest:

I have a form and a subform. I create the parent record (Project) and have an event that is triggered on the after update of the first field (project name) to save the record.

I then go to enter the related Project Tasks, and get an error "you cannot add or change a record because a related record is required in T_Projects.

But I just created it and save it via code !

I have added a required and a refresh in the after update and just can't get it to work.

I note the record is actually going in the table !

Any idea why the error is being thrown when I try and enter a related project task and how I can fix?

EDIT: Found the issue. Master and child fields incorrectly lined with PK from master and PK of child, not FK of child. Doh!
Link Master ID: Projectid Link Child Fields: ProjectTaskID.

Doh!

https://www.reddit.com/r/mildlyinteresting/comments/1hgik54/my_dad_still_uses_his_32_yearold_microsoft_access/

I am trying to create a 'drink cost calculator'. I have an excel (2016) sheet with a table that lists the alcohol item and the cost per ounce (along with more administrative information). I would like to be able to create a form/table/whatever is best, to be able to choose the alcohol from a drop down list in the first column, insert the ounces used in a second column, have the third column calculate how much the cost per ounce will be (by pulling the data from the first table that has 'cost per ounce' related to the alcohol chosen) and having a function automatically calculate it, and then have the total of each ingredient cost displayed on the bottom.

So first column is a drop-down list that auto-populates the data from the first table for associated cost per ounce to do the calculation in the third column, the second column as new data inserted, third column as a calculation of second column data and first table data, and a cell that does the sum of all ingredient costs together.

I would appreciate any help given. It has been a while since I've had to fiddle with these two programs (Excel and Access 2016) beyond surface-level tasks and I am not sure what direction to go in. Step-by-step, or links to tutorials would be appreciated.

Thanks in advance!!

How do I convert oracle SQL to ms access query

If anyone can assist with this you’ll be a life saver. I have a script in python that is attempting to automate a manual process in which a user imports a .txt with 1,500,000 rows into an access table. In writing the python, I’ve landed on using a bulk insert which basically takes a data frame, and then splits it into .csv’s with some row size like 50,000 and then I insert into the access table from the individual .csv’s.

The problem is it’s a company PC and I can’t increase the MaxLocksPerFile default value of 9500. I’m doing 5,000 row .csv files and committing every 10 batches. So that’s inserting 5,000 rows until it hits 50,000 then it’s committing. It does about 350,000 before throwing the ‘File Sharing Lock Count Exceeded’ error.

I’ve tried every combination of batch size and commit interval one can conceive. I’ve tried executemany to execute one sql statement many times, I’ve tried execute to load 1.5M rows and then commit them. Everything has failed.

Has anyone done something like this in Access? Also, before you say to use a more robust DB, I would if I could. My director uses Access still so at this point, I’m stuck with it. I would use Sql server if I could.

r/MSAccess Gurus,

I'm a little apprehensive to post this but I think I've reached my mental limit. Long story short, I've taken over as the DBA for a system that runs the small company I work for. Over the past few months I've been trying to migrate data to PostgreSQL over ODBC. I've managed via Apache Airflow and a bunch of docker containers, to get all the data to import from the production database users use everyday into my PostgreSQL development server and it works... kinda.

Issue I'm currently faced with is there are 153 Forms, 119 Reports, ~180 Queries and 94 tables and I'm only one person. The big problem when I started was that literally NOWHERE are there proper foreign key constraints and relationships, so whenever a user deletes a records from say a Purchase Order form, it also deletes any record where a foreign field was referenced within it's query. From what I have tested, this happens basically everywhere, but with the large amount of forms and queries, I feel like my only option is to scrap most of the current forms and menu system and start over...? The users I'm dealing with are very "old school" and they aren't very receptive to change so it makes updating forms tedious but if that's what needs to be done SO BE IT, lol.

If anyone is available for a consult feel please DM. If you're in the Cleveland, Ohio area as an added bonus as I'll buy you dinner, lunch, coffee for your time!

Important details:

• This system will eventually be upgraded to an ERP that uses PostgreSQL (v13), thus the choice but the company is small so they want to take baby steps.
• Microsoft® Access® for Microsoft 365 MSO (Version 2407 Build 16.0.17830.20210) 64-bit (All users are Windows 10 and 11.)
• On a good day this is probably a 10 concurrent user system, but based on current requirements most operations are READS.
• I've stripped the database of all the stupid OLE Object fields prior to PostgreSQL import and replaced with hyperlinks.
• I've added proper auto IDs to most tables.
• Front-end size is 167Mb, back-end after PostgreSQL migration routines 205.26 MB

Links to the FAQ and the Leaderboard are now easily accessible in the Community Bookmarks area, in the sidebar to the right, in the web interface.

If you're on mobile, I haven't been able to find them. So if anyone knows how to find them on mobile, please let us know in reply to this post.

https://preview.redd.it/g085sxsmq97e1.png?width=317&format=png&auto=webp&s=c7064de99a46298699b8c99a0a262d6adf390ead

I cannot put autodate selection n my aceess form can anyone give me a solution

Ever wonder why Report Header/Footer and Detail sections in a report have both an Auto Height property as well as Can Grow and Can Shrink properties?

Well, today I wondered that, and went looking for answers, but found none. No one seems to know why these sections have an Auto Height property when they already have Can Grow and Can Shrink properties.

And why is there an Auto Height property at all? I mean, Can Grow and Can Shrink accomplish the same thing.

Anyone have any insight?

I currently use access to enter demographic information into multiple Word documents. I am using Word merge and going through each document one at a time. Is there any way to have it export the information to all of these documents at the same time or with a streamlined process?

I've had a look but my limited skills won't cope. It would make my life much easier if I could create a button which had the effect of filling in 'todays date' on a database. I looked through the wizard and I'm not sure it's possible?

I think we've all been frustrated with the initial non-beta version of the Monaco SQL editor. It was like expecting a brand new shiny bicycle for Christmas, but instead getting a half-broken wooden cart that barely moved without having to hold one side up.

I personally stopped using it after a week or two. The payoffs weren't worth the hassle -- primarily of having to first save a query before it can be used.

So I wondered if there was any hope for this beleaguered beast. And I found out that, yes, the Access Team is working on fixes! Yay, A-Team!

This post summarizes most of the issues, and notes which fixes are in the works. And, according to the article, my personal least-favorite has already been fixed:

Changing to SQL view requires the query to be saved due to the use of JavaScript. FIXED in Beta Channel - version 2412 build 18324.20012 (27 Nov)

So that's great news!

And as we await fixes in the next version(s), let's all remember the immortal words of Martin Luther King Jr.:

"We must accept finite disappointment, but never lose infinite hope."

So true!

But, then again, let's not forget that "optimism is installing the latest update and expecting things to work better."...

It's my first time using Microsoft Access and I'm trying to create a small Asset tracking database.

I found the Asset tracking template only there are some field that I just don't need. So I'm trying to get rid of the.

But when I try to delete the field "Acquired date" & "Retired Date" I get a error when I try to save "Can't find field Retired Date".

I looked in the dependencies of the forms and reports can't find it anywhere.

Congrats to u/AccessHelper for reaching 100 points some time back. Well done, sir!

And congrats to u/Jealy and u/GlowingEagle for rounding out the top three.

Here are the top 10 point scorers. Congrats to all of you!

(Don't forget that a running total of point scorers is located in our Wiki section, and a link to the list is in the sub's description.)

Thanks to everyone who participates in this group, both those who respond to questions, as well as those who ask them!

https://preview.redd.it/z3vn03k79a6e1.png?width=505&format=png&auto=webp&s=acedae99070daa827cecf97ef9a75f715d81daaa

Hopefully someone can help me understand this. I have a table where I need to INSERT some data. I am using python to do the work. I have all the bugs worked out of the script, except this one. Every time I run the script, the INSERTs all fail with a key, index, constraint conflict. Chased that for a long time on the python side of the equation. Finally, I decided to just remove the PK constraint. The script then worked correctly. It looks like the id field even generated unique, incremental values.

Any ideas about why it would be this way would be great. Thanks!

Edit to add: Python is not trying to write the keys.

SOLVED: The autoincrement of the destination table was out of sync, causing it to try to write duplicate values to the PK. Compact and repair fixed it, and for as long as I have to work with MSAccess, that's my new 'did you turn it off and turn it back on?'

I think I finally found the cause of a very annoying issue I’ve had where the vertical scrolling would get stuck on continuous subforms, so records would be hidden at the top of the list and scrolling wouldn’t reveal them: it was because I had turned off the navigation buttons on the form.

Unfortunately it makes many of my forms with subforms look weird with multiple navigation bars but at least they work properly now.

It can still happen if the form is too small, but just resizing slightly fixes it.

Any other tips or workarounds lemme know, but just wanted to post this for posterity.

I'm by no means an expert in Access but I have created a basic database which I have been using for quite a few years for the guests of our family hotel. It contains two tables, one for guests and one for their room bookings. Each guest may have multiple bookings over a year and may also bring friends along with them, so in some cases a guest may have, say, 10 entries in a single year. For example, by booking three rooms in May, and another six in September.

Since many of our bookings are made directly, I am looking for a system by which I can send the guest a pdf summary of their booking. This is easy enough by using a query, filtering the bookings for, say, 2025 and then creating a report based on that query. But it'd be much clearer if I could separate the bookings in groups that would make more sense for the guest.

In practice, room bookings made under one name in a year may look like this:

1. 12/5-17/5
2. 25/5-2/6
3. 25/5-2/6
4. 28/5-7/6
5. 5/6-6/6
6. 9/9-15/9
7. 10/9-18/9

In this case #1 is a single booking, whereas 2 to 5 and 6 & 7 are two groups. Notice that the dates of 2 and 5 don't coincide but it I think that it's evident that it makes sense for them to be in the same group. So that'd make three pdf sheets.

Would something like this even be possible in Access?

I've inherited a handful of MS Access databases to troubleshoot and help maintain.
Each one having a bunch of forms and subforms. etc
Thankfully, they are using access as a front end for a sql server database.

I'm much more an Oracle and Sql Server person.
I've coded in VB6 and more recently C#.
So, I'm familiar with debugging, stepping through code etc...

(I've been wanting to rewrite the MS Access Forms into C# forms and do away with access to make user permissions etc easier, but not ready for that large undertaking yet yet)

My problem is we have a new user (salesperson) and when the VP runs a report to pull up monthly totals, it errors off due to the new user's name not being a valid column header name on a sql insert statement.
Stepping through the code. I can't even find where an insert statement is being generated.. I do see a TRANFORM statement that apparently takes the results from a select statement and turns each into a column header.

I cannot find anything different about the employee entry for this new user, nor in any work orders

I'm still searching, but so far I am stuck.
If I enter in a date range for the report prior to him starting, all is fine.,
If not, I get the error.

Pertinent lines of code are below:

? sqlstring0
TRANSFORM Sum(IIf(NZ(WORKORDER!QUOTEDSALEAMT,0)=0,0,WORKORDER!QUOTEDSALEAMT)*IIf(NZ(APP_OA_SALES!SALE_MULTI,0)=0,0,APP_OA_SALES!SALE_MULTI)) AS QTEDAMT SELECT WORKORDER.WONUMTXT, WORKORDER.JOBNAME FROM (WORKORDER RIGHT JOIN APP_OA_SALES ON WORKORDER.WOID = APP_OA_SALES.WOID)

? sqlstring1
LEFT JOIN Employee ON APP_OA_SALES.SALESREP = Employee.EmployeeID WHERE (((DatePart("m",[APP_OA_SALES]![DATEIN]))< 13 And (DatePart("m",[APP_OA_SALES]![DATEIN])) Is Not Null)) GROUP BY WORKORDER.WONUMTXT, WORKORDER.JOBNAME ORDER BY Employee.EmployeeName PIVOT Employee.EmployeeName;

Set QryDEF = Dbf.QueryDefs("SELECT_YR_END_SALES_DETAIL")
QryDEF.SQL = sqlstring0 & sqlstring1
QryDEF.Close

Set QryDEF = Dbf.QueryDefs("DEL_YR_END_SALES_DETAIL")
QryDEF.Execute dbOpenDynaset + dbSeeChanges
QryDEF.Close

? dbOpenDynaset
2

? dbSeeChanges
512

THE ERROR:
(Note that I changed the user's name for the purposes of this post)

The INSERT INTO statement contains the following unknown field
name: 'DUSTIN SMITH'. Make sure you have typed the name correctly, and try the operation again.

Is there a way to set up a watch or something so I can see what the actual insert statement is that it is trying to run?

So far, I am at a loss to understand where the insert statement is being generated.
Also confusing is that the APP_OA_SALES table is not in the backend sql server database.
It seem, to be recreated every time the report is ran, but I cannot see where or how.

I went into the APP_OA_SALES table and changed the few records with the new user's name in it to another older sales person's name to see if its actually the name causing issues or something else, and after the report ran, the table was back the way it was initially with the new user's name in it. As if its doing a select into statement I cannot find.

Hoping someone can help me rid myself of this INSERT error

Thank You!,

After several attempts, I cannot load a successful NW Trader Developer Edition 2.4 template. After clicking, "Enable Content," the VBA editor pops up with an error "Compile Error: Sub or Function not defined."

It points to Public Function Startup() in the modStartup module. It highlights OneTimeProcessing.

I can install the starter edition, no problem. Any ideas?

Additional Information:

I'm a work computer. We got a new IT guy. Could he have done something with the Org settings that's causing this?

I'm using OneDrive, but I am not installing inside of one drive. I'm going to try installing in different locations to see if that helps.

I’m stumped. When I do Val(“5D1”) it returns 50 instead of 5. But if I do Val(“5C1”) it returns 5 as expected. Does anyone know why??

This is my first MSAccess database.

So I was tasked with creating an inventory database for the event production company I work at. Since we are through a university a lot of our workers come and go so the people accessing this database would need it to be simple and easy to access without any knowledge of MSAccess.

I have my tables and relationships setup with forms to enter new assets and maintenance tickets. What I am wondering now is if I can access these forms/reports to enter new assets and view queries through share point or some other web access. My hope is that nobody who enters or exits data will have to access the database itself.

Let me know if this is at all possible or if I should’ve used a different platform from the beginning.

Edit: I’ve got a ton of helpful options options to explore. Thank you everyone!

I am building a form to collect observation data. VBA code is at the end. I am a noob, so please be gentle.

Problem: data input for one variable into form stays in first record even as new records are added via the form.

Details:The form (Form2) collects the subjects name that pulls from a table (TeacherNames) and has buttons that are clicked when a phenomenon is observed. It is collected as an integer by the number of times the button is clicked. The data from the is added to a table (TBL_Test). TeacherNames contains fields for “TeacherName”, “Date”, “TaskRead”. When a new record is created (selecting a name from the combo box and appended to TBL_Collection) data for “TaskRead” is added to only first record.

The functionality is built using VBA for the data collection.

Code: Option Compare Database

Public TRead As Integer

Private Sub TaskRead_Click()

Dim TRead As Integer
Dim rst As Recordset

'read, write
Set rst = CurrentDb.OpenRecordset("TBL_Test", dbOpenDynaset)

TRead = DLookup("[Task_Read]", "TBL_Test")

TRead = TRead + 1
rst.Edit
rst.Fields("Task_Read") = TRead
rst.Update

rst.Close
Set rst = Nothing

End Sub

Private Sub Click_Me_Click()

Dim TeacherName As String

TeacherName = Me.TeacherName.Value

Dim CurrentTime As Date

CurrentTime = Now()

' Add record to the TBL_Test table using DAO

DoCmd.RunSQL "INSERT INTO TBL_Test (ClickDateTime, Button1, Teacher, Task_Read) VALUES('" & CurrentTime & "', 'Button1', '" & TeacherName & "', '" & TRead & "')"

End Sub

The actual issue starts on paragraph 3, the first 2 give context and the situation:
Strange issue that started happening since the beginning of November. For context, my company uses Sharepoint online lists as the back end and the front end is the Access database. People download the access file onto their machines and interact with the data on SharePoint online. Everyone had been using version 2410 and the same access file and starting in November this year, some people were seeing errors and some weren't on anything that had to with opening a form with data on SharePoint, saving to a SharePoint record, and only on specific conditions. Come to find out, the people who were on the Insider program who had the Current View (Preview) builds loaded were not seeing errors. So once I had my team upgrade to the Insider builds, the problems went away until the next week and then another build came out and the problems resolved just before Thanksgiving.

Now, on Tuesday this week, problems have come back and I can't figure out why. Everyone has the same Office version, the builds are current for their machines, same Access file, no changes to SharePoint besides edited rows in the lists, and yet, some people working together in their office has no issues but others, including myself that works remote hundreds of miles away, has issues.

The exact issue I can't understand is that when I open a form, the OnCurrent event kicks off for a new record, and it's supposed to populate a field on that control source (the source is a direct table, no join query) followed by saving the record: DoCmd.RunCommand acCmdSaveRecord
However, when it runs at run time, I get an Invalid Use of Null error. If I disable the new Monaco SQL Editor, I get a different error at the same moment, "Access OpenForm failed to launch". When I debug, it crashes on the saverecord command. But 2 lines above it, is my field set: URL = filepath
And URL at the error code reports back as Null, even though filepath has a value. It's like that line of code did not even process. If I step back and step through it, it doesn't open the value. This form uses the SaveRecord to save a record in a table i call FILES. And in the immediate window, if I run: PRINT DCOUNT("ID","FILES"), it comes with 0, and also if I do: PRINT DMAX("ID","FILES"), I get a Null answer. Here's where it gets more bizarre. If I break execution on the button that opens the form for a new record, and I step through it line by line, no errors occur and window and everything functions after that. AND, anytime during that session while the file is open, without a breakpoint set, it will execute without errors. BUT ONLY if I step through the openForm command until it gets past the save record on the OnCurrent event. And Interestingly now, in order for me to get an accurate record count of my linked list, I have no not only have the navigation area visible with the table showing, but I have to also open the list itself and load it onto the display before it will give me a true record count (if I don't want to step through the code line by line every time I open this access file).

So here's what I've done: rebuilt the form. I've deleted the links to the lists and re-added them. I've gone ahead and created a new access file and copied all the forms and everything over to the new file. Nothing is working despite it working Monday flawlessly. So, I'm thinking Microsoft is doing stuff behind the scenes and it's messing up the experience for some users on my database but not others. and it seems to occur since the beginning of November on a weekly basis.

Complete novice here. I'm setting up an engineering database for my company. We build specific rooms inside of buildings. Any given project may have multiple chambers. Any given chamber may have multiple drawings. Any given drawing may show multiple chambers.

In the database, I want a sheet which lists drawings by their number and states the project they're on and the room number of the chamber(s) they depict.

The only method I have found of listing multiple values in a field is with the Lookup Wizard. Every method I have tried with that tool leads to creating a dropdown checklist of room numbers. This would work, but it would need to be on the order of 10,000 room numbers and that's a lot to scroll through.

Is there a way to put multiple values into a field without a dropdown menu? Or a way to limit a dropdown menu to include only values which match the project ID in the same row?

TIA and let me know if more detail is needed.

https://preview.redd.it/mjro77tua25e1.png?width=258&format=png&auto=webp&s=94ea18aae6b1e25447206ce1c9c40b77831969cb

https://preview.redd.it/766utozxa25e1.png?width=331&format=png&auto=webp&s=933003ae3c1cd751fe686b07c42f18ffa9ec3368

Update: I posted the wrong query. The one shown does not work at all. When I remove the phone criteria, the address search works. What I'm not understanding is why it doesn't work. The SQL looks like this:

SELECT [Copy Of CustomersT].CustomerID, [Copy Of CustomersT].NAME_1, [Copy Of CustomersT].NAME_2, [Copy Of CustomersT].MAILING_1, [Copy Of CustomersT].Mailing_2, [Copy Of CustomersT].PHONE_1, [Copy Of CustomersT].PHONE_2
FROM [Copy Of CustomersT]
WHERE ((([Copy Of CustomersT].MAILING_1) Like "*" & [Forms]![frmDashboard]![lblAddrSearch] & "*")) OR ((([Copy Of CustomersT].Mailing_2) Like "*" & [Forms]![frmDashboard]![lblAddrSearch] & "*")) OR ((([Copy Of CustomersT].PHONE_1) Like "*" & [Forms]![frmDashboard]![lblPhoneSearch] & "*")) OR ((([Copy Of CustomersT].PHONE_2) Like "*" & [Forms]![frmDashboard]![lblPhoneSearch] & "*"));

Original:

I'm developing a search form, and so far, the address lookup is working. When I apply the same logic to the phone lookup, it doesn't. The address search works by typing in part of the address, and I'd like the same for the phone search.

The phone box name is: lblPhoneSearch
The address box name is: lblAddrSearch
The result list box name is: lstAddrResult

I've attached a screenshot where I'm using one query with multiple ORs. I've also tried a separate query for the phone search, and that didn't work either.

https://preview.redd.it/vlv67wkme15e1.png?width=973&format=png&auto=webp&s=2be3eec39271aca39371950f3ead6d83a21510b6

https://preview.redd.it/gpq1gikre15e1.png?width=418&format=png&auto=webp&s=e35c1d9a1895aafbe4fd79013f402829297ef4b3

I appreciate you all having a look.

I have a large collection of trading cards, and I’m looking to create a database to track the locations and value of my more valuable ones. I haven’t used access since high school almost 20 years ago, and in that class we mostly just learned how to record and query a handful of values, not effectively manage lots of data.

The problem I am having is that I am stuck trying to figure out the most effective way to even approach this. Basically, I have 28 containers storing the cards each of them numbered. I would like to record and be able to query based on Name, location, edition, or condition.

I am working on a project that will relate a part number with assemblies that it is a part of, and with the next higher assembly after that, and so on until the end item. Each part will also be related to specs relevant to that part at that level.

I have two tables set up: PartNumberT, which contains the part numbers and relevant specifications; and PartPartT, which relates parent/child parts. Example tables below:

PartNumberT

PartPartT

In this example, Part 1 contains parts 2 & 3, Part 2 contains parts 4 & 5, and part 3 contains parts 5 & 6.

I created three forms: ParentF is a continuous form that lists all the parents, ChildF which lists all the children, and PartF which will be the actual interface to add/edit data. Examples below:

ParentF

ChildF

PartF

Note that ParentF is the subform on the left, and ChildF is the subform on the right. I have set up master/child references in the table properties so that the subforms show the parents/children of only the selected part. In the above example. Part 1 contains part 3, and part 3 contains part 5.

From this form, I want to add new parents/children using the subform. The ParentF works perfectly, I can add new parents without issue. When I start typing in a new record in the ChildF subform, I get the below error:

https://preview.redd.it/nuj37cb42x4e1.png?width=809&format=png&auto=webp&s=634da6a478c2bccb591ec732f6dc310c63aca2e2

An error occurred while referencing the object
You tried to run a Visual Basic procedure that improperly references a property or method of an object.

I did not write any VBA code, and I checked just in case, and there was no VBA code in the code editor. After clicking Ok and completing the new entry, the PartPartT table looks like the below:

https://preview.redd.it/denn5xmj2x4e1.png?width=426&format=png&auto=webp&s=caf686400814df4a05f757cdc224162023c886a2

Note that NewPart2 was added as a child without a parent.
NewPart1 was added as a parent to show that the parent subform works when the child subform doesn't.
This is strange because I used the same concept to create both (Create form that lists all parents/children of the selected part, then adding a new record in the subform should add the new part as the parent/child of the selected part)

Any idea how to fix this? Relating parts to their children is much more practical than relating to parents because all our drawings have complete part lists, but the Next Higher Assembly lists are not as reliable.

I'm building an unbound combobox named "Sel_Cat" where I want the user to be able to type text into the box and immediately restrict the box to show only items that include the User's entry characters.

I wrote the following code:

Private Sub Sel_Cat_KeyUp(KeyCode As Integer, Shift As Integer)
Sel_Cat.RowSource = "SELECT Tbl_ContentCat_L3.ContCatL3ID, Tbl_ContentCat_L3.ContCatL3Descr " & _
"FROM Tbl_ContentCat_L3 " & _
"WHERE Tbl_ContentCat_L3.ContCatL3Descr Like '*" & Sel_Cat.Text & "*' " & _
"ORDER BY Tbl_ContentCat_L3.ContCatL3Descr;"
Sel_Cat.Dropdown
End Sub  

The code kind of worked, but only after the typed text string reaches 5 or more. For example, the typing of "Read" it shows only one result: "Read Online." But if I type "readi" I see two options: "Easy Reading"; "Light Reading" which should have appeared in the results of the shorter string.

I just can't see why this isn't working. Any thoughts what I've done wrong on this piece of code?