Other subreddits of interest:

I have simple form and simple table inside. These four events with simple MsgBox for tests. Cannot get these events working. File is in trusted location, all the protections turned off, VBA and ActiveX allowed. Code is written thru the form properties to avoid mistakes.

Googling, copiloting, nothing helped.

Edit1: tried to add Enter event to table in form - Table1_Enter() - and that one is working. Insert and Update not.

Hi

https://preview.redd.it/vf5nyp5iw02e1.png?width=489&format=png&auto=webp&s=b8ecfd85b5c606258527ecebdad652eb01d3b233

I am fairly new to MS Access, but I trying to import a data sheet with a date and timestamp into MS Access from Excel.
If I choose Date with time data type the data wont appear in my table when imported. It only works if I choose short text. But by doing that none of the date functions work. I would like to be able to import the file as is without having to remove any data before importing. Can you help?

https://preview.redd.it/v9moml12x02e1.png?width=398&format=png&auto=webp&s=ae0aa4b350e709115e4ebce856732ff06cffa50f

https://preview.redd.it/q3fqdx25x02e1.png?width=391&format=png&auto=webp&s=4858a4f75ddf889a299d3484a6ff080d386b9fe0

Again I am really new to this program, so please any suggestion would need to be really specific.

Hi, I have a structure of tblCase to tblContravention which is a many to many relationship. I have created a junction table called tblCaseContravention.

tblCase

CaseID

CaseName

tblContravention

ContraventionID

ActName

Clause

ClauseDescription

So I have created a Junction table with both primary keys from tblCase and tblContravention in tblCaseContravention.

This all works fine typically when I have one combo box selecting the Contravention. But I am trying to create a solution where I have a subform on frmCase, where I select the ActName from one combo box, then a cascading (after update code) Clause in the second combo box, then the ClauseDescription is displayed relating to the ActName and Clause selected. I suppose I may have to create a commit button on a continuous form, that commits the INSERT of the CaseID and ContraventionID to the junction table.

That is the goal, has anyone done anything like this and do you have any advice?

I have an unbound form named Frm_CustomerCard.

On it is a combo box control named Sel_CustPlatID

The Row Source for this control is:

SELECT tbl_CustPlatform.Cust_Platform_ID, tbl_CustPlatform.Platform_Screenname, tbl_CustPlatform.Website_Customer_ID, tbl_CustPlatform.Platform_ID
FROM tbl_CustPlatform
WHERE (((tbl_CustPlatform.Platform_ID)=[Forms]![Frm_CustomerCard]![SelSalesPlat]))
ORDER BY tbl_CustPlatform.Platform_Screenname;

The bound column is 1 tbl_CustPlatform.Cust_Platform_ID.

I have a subform named "Sub_AddNewCustCat"

I am attempting to set the recordsource of this subform to:

tbl_CustCat

where the feild tbl_CustCat.Cust_ID matches the value of tbl_CustPlatform.Cust_ID for the tbl_CustPlatform record identified by the bound value of Sel_CustPlatID

Here is the code I'm attempting to use:

Dim SQL As String
Dim CustLook As Integer

CustLook = DLookup([Cust_ID], [tbl_CustPlatform], [Cust_Platform_ID] = Me.Sel_CustPlatID.Value)



SQL = "SELECT * " _
& "FROM tbl_CustCat " _
& "WHERE tbl_CustCat.[Cust_ID] = " & CustLook & " ; "


Me.Sub_AddNewCustCat.Form.RecordSource = SQL
Me.Sub_AddNewCustCat.Form.Requery

When I attempt to execute the code I'm getting a runtime error: 2465

MS Access can't find the field '|1' referred to in your expression.

The debugger is highlighting this as the problem:

CustLook = DLookup([Cust_ID], [tbl_CustPlatform], [Cust_Platform_ID] = Me.Sel_CustPlatID.Value)

I have no idea what's wrong in that statement.

I'm building a database to track design changes to a product.

Each design change comes with affected serial numbers. This can be several ranges of, or single numbers, then usually (but not always) all subsequent. For example "340-348, 352-364, 366, 368 and subsequent", or just "356", or "all".

I need to store all this in a searchable way so that I can create a list of all design changes affecting a specific serial number.

For context I have last used Access ~10 years ago in high school so I'm a bit out of my depth, but this would replace manually going trough a very old and janky excel sheet with 1000+ design changes every time, so it's worth a lot of effort.

https://preview.redd.it/7tionbpvvu1e1.png?width=474&format=png&auto=webp&s=7f0fde5ba58652027c62b667b7f99be08db822ae

i mean, i have this field which i dont know if is being used, but am afraid to delete it to not cause more troubles.

any idea if i can browse queries, forms and reports to track this?

I have local files which display google charts. I am aware I have to use the https:/msaccess prefix to display the page, but the chart does not display.

Anybody had any success with google charts in Access?

Hello! I hope everyone is doing well. I am new to Access and am in need of some assistance. I have a form in my database of "Active cases" and a form of "Archived cases". I would like to send records from "Active cases" to "Archived cases" once a person adds their name to a cell from a drop down box in the "Active cases" form.

I think I should be using the "After completion" event and a VBA, but I could also be wrong. Is there an easy way to do this? Do I need to create any relationships between the forms? I am also not super sure of the exact code I would need if I go the VBA route. I have looked on YouTube, within the FAQ here, and Stack Overflow. I may also be phrasing my queries incorrectly or missing something.

Any help is appreciated. Thank you all so much!

Hi guys My application which was running fine a week back has been giving me the out of memory error all of a sudden. The strange thing is, the Access application is working fine when it is opened the first time, but once I close it and re-open it, it is throwing the out of memory error. The MS Access is acting same with 2 of my applications now and these both are micros enabled complex applications. Please suggest any fixes.

Hey people, hopefully one of you can help me out a little. I have not done any VBA/form design etc... since MS Access 200 and am trying to get back in to things. I quickly came in an issue dealing with text. The issue is the text starts out using TextPad or Notepad. The text is properly formatted and I paste it in to a field in my Access 2016 table and it looks like this:

https://preview.redd.it/cda9kjmusj1e1.png?width=701&format=png&auto=webp&s=fb355b1b299e3b0c74125667ab6852ce781b9440

Then it shows in the textbox like this:

https://preview.redd.it/vj4ncrmxsj1e1.png?width=985&format=png&auto=webp&s=9d09d07746ab060c5fb4e8750be7d99af04f90b4

But, I can copy the text straight from the database table and paste it in TextPad or Notepad and get this:

https://preview.redd.it/sv9clh12tj1e1.png?width=897&format=png&auto=webp&s=6cfcfacba1a51a28f79f7f1dafc8130de415d9f8

What setting am I not setting correctly? Any help would be greatly appreciated and I will shower you with praise.

I am making a document from my database. Several of the fields are coming over incorrectly. These are rich Text and webpages. Does anyone have a suggestion on how to fix this in Word?

Hi all,

I have a table called tblDynamicTreeInfo. In it, I have the field dtiRoost_lkp, which is a lookup field. This field stores the value from tblRoost.rstRoostID, and displays the value from tblRoost.rstName. From tblDynamicTreeInfo, I would like to delete all records that meet the following criteria: Records where the roost name contains the string "2021" AND the Year from tblDynamicTreeInfo.dtiDate is 2022.

When I try to do this using a delete query, and I click on "View" I can see all the matching records. However, when I click run, I get the error message: "specify the table containing the records you want to delete", since I am adding both tblRoost and tblDynamicTreeInformation tables to the query design grid.

I cannot use tblDynamicTreeInformation.dtiRoost_lkp to find roost names that contain a "2021" in their name, since that is just a number field, so I have to add two tables to the design, and get the name from tblRoost. Is there a way to go about this?

Here's the query in design view. I typically don't work in sql view as I don't know much sql

Here's all the records it returns

Error message when I try to run the query

Hey guys currently I'm working on producing a form but I cannot comprehend how to link command buttons to pages!!!

Here is the form:

https://preview.redd.it/eummigzhb41e1.png?width=1299&format=png&auto=webp&s=001c14a7761775b6b682bdf2f2220f8cb1cab246

On the left of the form there is a light green column which includes all of the command buttons which i would preferably like to join-up to the pages.

https://preview.redd.it/g8q2uz5cc41e1.png?width=723&format=png&auto=webp&s=da45bcceca79dd05918046fcfc36f9cb1e0b4b71

- DASHBOARD NEEDS TO BE LINKED WITH PAGE51

-PANTIENTS WITH PAGE52

-EXTERNALS WITH PAGE53

- AND SO ON......

THANKS YOU GUYS

Still new to Access. I need help connecting my Order_line table to the Product table. In the Order_Line Table I have the ProductID and the Price, which in the PRODUCT table is already listed. How can make the price will automatically generate if I list the ProductID in the Order_Line Table

https://preview.redd.it/4axysd79x21e1.png?width=801&format=png&auto=webp&s=1f7fa620587c9c2e15ef3d56ec6f3a49c2aa4df4

https://preview.redd.it/7kdvee79x21e1.png?width=754&format=png&auto=webp&s=994f2a146585e4a8d2f10c848c84796fe6ed0720

https://preview.redd.it/upc62g79x21e1.png?width=1328&format=png&auto=webp&s=dd08e3a1c7c973bd71ea68038b901984d95a1ed5

Whenever I make a new .accde of the front-end, it needs to be distributed to the users (clients) the next time they open the .accde on their local drive. So in the .accde, when it is opened, I have code that looks at the creation date/time of the master .accde on the server. The problem is as soon as it opens on the client it updates the date/time of the client copy. So what I did is when the .accde gets copied to the client I make an extra copy on the client that the user never opens, so I can always get the original date/time from that copy. So basically when the user opens the .accde on their local drive, it compares the date/time of the copy that is on their local drive to the master copy on the server, and if there is a newer version on the server it then informs the user that there is a newer version and instructs them to run a bat file on their desktop that copies the new version to their local drive. I know there is a way to have this automated so that the user doesn't have to do anything (click on a desktop icon that runs a bat file). I don't mean automate it by trying to push the new version of the .accde out to each client whenever a new version is made - that is too messy. I mean that when a new version is detected, you chain to another Access program that does the copy and then chains to the new one that is now on the client.

Hi everyone! I'm a chemist working in quality control, and I've been using MS Access extensively to manage databases that track production and testing data. Over time, my projects have grown more complex, and I’m realizing the importance of proper documentation to keep everything understandable and maintainable—not just for me, but for anyone who might work on these databases in the future.

I wanted to gather insights on what best practices you use when documenting your MS Access databases:

1. What kind of documentation do you maintain? For instance, do you use data dictionaries, process flow diagrams, or detailed comments within queries/VBA?
2. How do you organize and store documentation? Is it integrated within the database (e.g., using comments in code or hidden tables) or kept separately (e.g., using an external document, wiki, etc.)?
3. What’s worked well for you and your team? Are there practices you swear by that help keep things clear and concise?
4. What should be avoided? Any pitfalls you’ve experienced or seen when it comes to documenting MS Access projects that others should steer clear of?

I’m particularly interested in what is considered highly regarded in the industry, and what might be overkill or unnecessary.

Any examples, templates, or suggestions would be fantastic. I’m hoping this can turn into a bit of a guide to improve database documentation practices.

Thanks in advance for sharing your wisdom!

I love to work with it, and

Hi! I am new to MS Access and cant find a solution to my (pretty basic?) problem. I have a table with employees, a table work positions and a table of work groups.

The table with the work positions consists of:

- ID (primary)
- positionID
- positionTerm
- positionShortTerm

Table of groups:

- ID (primary)
- groupName
- groupShortname

The positionIDs belong to one of several groups. In the employee table I set the group with the search feature (data) to search other tables. I dont know the exact english name since I use MSAccess in a different language.

Employee table consists of

- ID
- groupName (importet from group table)
- positionID (importet from positions table)
- ... many different things

Now I want to make a Form and a Report in which the user can set the group (no problem here) and choose one of the positionIDs. When the positionID (a number) is choosen from the drop down menu it will fill out the positionTerm in a field unter the number by itself. The data is there but I dont know how to link it. Basically the same goes for the report page. The positionTerm, positionShortTerm and groupShortname are not importet in the employee table but I have the feeling that I dont need to import it since the info is already there in the other table

Many thanks!

A required assignment for my ‘Applied Info Management Systems’ class required us to download a MSAccess database, add new tabs, tables, relationships and queries, THEN upload the database to my university’s learning management system (d2L). Can anyone tell me the best way to do this? When viewing and testing my uploading ms access file, it opens in an unviewable or unreadable format. I’ve researched for days on how to convert or export and have had no luck. Long story short: how do I upload my ms access database (with multiple sheets/tabs) to be viewable and accessible from an online submission platform?

Hi Access Peeps,

Hope one of you can help me with a issue I am facing. So my access form opens full screen, it's set to popup and dialog true. Also, show ribbon is false. Everything works amazing until I open outlook or some other desktop application running on windows that interactivity is being block, it makes that sound we all hate. Do any of you know how to make a access form always display on top of any other application. You advice and guidance would be a great help!

I'm preparing for my MOS exam in Access, but all the sites that have practice tests seem to charge for them. I was wondering if anyone knew of any good resources that would give me a more in-depth idea of what to expect than just the list of skills that Microsoft provides without having to pay for anything.

Hi, try to import excel spreadsheet in access and I an getting the error "The search key was not found in any record". I have compacted and repaired the database and I am still getting the same error message. Any ideas on how to fix the issue?

So I started tracking work verification using access (Prior, we were using an outdated Excel sheet)

Edit - Updated Better picture of the Relationships

https://preview.redd.it/48gyin0imp0e1.png?width=2277&format=png&auto=webp&s=67b7b2016cb73654bead1a6761e8da1221707d5c

Explanation:
So the main form is "tblSMT," and there is a subform "tblPar."

so my issue is that for most of the fields (29 of them)(52 of them) in "tblSMT," the values are going to be the Employee #s. I started setting the relationship one by one to the employee ID, and I noticed that if I keep going, I'm going to end up with "tblEmployeeInfo_1-29." I don't think I'm doing it correctly.

The same issue came up with "tblPar" when creating the relationships. I ended up "tblEmployeeInfo_1-4"

Is there a more efficient way of doing this?

Edit: 11/13/24

https://preview.redd.it/sut1yahcjp0e1.png?width=253&format=png&auto=webp&s=a45a865f3ea1cb51aa1076132b08a6221177f0fa

This is what the original Excel looked like (before it was papered, we had stacks of 1000s of paper and could not find individual sheets through a book.)

The red is the area that would be filled in the employee # (said emp can do more than 1 field sometimes 1 emp will do 70% of the work.

Top area is supposed to be the form related to "tblPartPrep," which is a different department that pulls the same "tblEmployeeInfo" that the "tblSMT" pulls from same with tblProducts

The area under "Pulled By" is another department. That area data is under "tblSMT" and is only connected to the Main record "ID" and the tblPartPreps is connected by "LinkID" cause we may have situations where we have multiple products for sheet bill of work.

https://preview.redd.it/bmqv1b09hp0e1.png?width=1244&format=png&auto=webp&s=b2ce0f87bc535f10057c2b60f6e415baf954b06e

https://preview.redd.it/irr9jiwbop0e1.png?width=594&format=png&auto=webp&s=6c2ab3cfe6346b38c81085e5e64065ff4be0510a

Mock Form Typical Record

Hi everyone,

I am working on an access database and I am trying to have the query print out the species of fish.

I right now have the criteria set for Like “” &[Enter Species:]&””. When I open the query I type in the species I want and it pops open the form. However, I would like when the enter parameter value box to pop up I can have a drop down of all of the species I have in my species table so I can select from there. How can I do this?

Also, I want to be able to have the form open on a particular species and if that species does not show up in that water body I want it to pop up with what is listed as “other”. Example: I am looking for yellow perch. So if yellow perch, print yellow perch, if not yellow perch in that water body print other. I would assume I would use IIF for this but I’m not sure how.

Any advice on any of this would be amazing! Thank you!

I have a field called "Expiration Date". I would like to have a calculated value entered in here based on 2 other fields in a table: "Contract Date" and "Contract Length". The Contract Date is in Date Format, 12/1/2015. The Contract Length is always in years, but in Short Text field. It will say 5.

Example:

Contract Date: 12/1/2015

Contract Length: 5

Expiration Date: 12/1/2020 (what I want it to automatically display)

Currently I have (Contract Date) + (Contract Length) and it's giving me 12/6/2015.

I think this is a simple fix, but have tried many variations.

THANK YOU!

Hi reddit(ors) :)

I'm trying to build a better anchoring system for Access Forms (not MSforms), because the current one doesn't expose the calculated rectangle for controls after anchoring, so i my quest to position forms relative to anchored controls has proven impossible.

So i went at it and got some code put together on which i'd love to get your opinions (and help if possible).

My problem is, when i'm passing the control from one variable to another, if the receiving variable is of generic type Access.Control, whenever i cast it back to the correct control type (like Access.Subform), if i test both variables using the IS operator, they don't match anymore, even if they point to the same address... So i have to rely on other properties like Name and Parent combined.

So, the question is: how can i cast a specific access control to the Access.Control type, and then get back the same initial pointer?

In the link is the accdb, if anyone wants to take a look. it's too big to paste it here, and it uses some VB_ATTRIBUTES here and there, so those will be lost if i paste the code.

Github Project

Thanks for all your help and time :)

Function over form, for sure, but all my forms look so drab. Just wondering if anyone knows of any co-op online resources for Access that might show off some really nice style choices for database forms.

I run a programming services company and one of our (wealthy) customers wanted a pretty reasonable upgrade (adding a new field for a shipper). I havent done Access before, but I've been programming in ~10+ languages over the last 20 years.

Apparently some other company refused to do it, and wanted to upgrade everything out of Access. Not the best sign, but our company specifically specializes in custom code/upgrades, so this isnt unheard of.

Anything to consider? Do I need to use their computers because buying a similar Access environment on my computer in 2024 is impossible? Any thoughts appreciated.

I get errors trying to link tables. A pity because duckdb seems to be really nice and I want to add it besides sqlite.

"Reserved error (-7701); there is no message for this error." when I try to link the table.

"Reserved error (-7702); there is no message for this error." if I add a duckdb file as a DSN.

test_odbc

[6/31] (19%): Test SQLConnect and SQLDriverConnect

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

test_odbc is a Catch v2.13.7 host application.

Run with -? for options

-------------------------------------------------------------------------------

Test SQLConnect and SQLDriverConnect

-------------------------------------------------------------------------------

D:\a\duckdb-odbc\duckdb-odbc\test\tests\connect.cpp(132)

...............................................................................

D:\a\duckdb-odbc\duckdb-odbc\test\tests\connect.cpp(132): FAILED:

{Unknown expression after the reported line}

due to unexpected exception with message:

Could not find storage_version.db file.

[29/31] (93%): Test SQLColAttribute for a query that returns an interval SQLColAttribute: Success with info

[30/31] (96%): Test SQLColAttribute for a query that returns a uuid SQLColAttribute: Success with info

[31/31] (100%): Test SQLColAttribute for a query that returns a uuid

===============================================================================

test cases: 31 | 30 passed | 1 failed

assertions: 45429 | 45428 passed | 1 failed

and

test_connection_odbc.exe

[0/1] (0%): Test SQLConnect with Ini File SQLExecDirect (FROM string_values): Error: Error

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

test_connection_odbc.exe is a Catch v2.13.7 host application.

Run with -? for options

-------------------------------------------------------------------------------

Test SQLConnect with Ini File

-------------------------------------------------------------------------------

D:\a\duckdb-odbc\duckdb-odbc\test\tests\connect_with_ini.cpp(9)

...............................................................................

D:\a\duckdb-odbc\duckdb-odbc\test\common.cpp(27): FAILED:

REQUIRE( (((ret)&(~1))==0) )

with expansion:

false

[1/1] (100%): Test SQLConnect with Ini File

===============================================================================

test cases: 1 | 1 failed

assertions: 6 | 5 passed | 1 failed

seem to indicate it is a problem.

I tried to fix by installing the most recent ms-c-redistributable and by adding 'utf-8' under language settings.

Hi everyone,

I am new to Access. I created a chart that I wanted to have three combo boxes or list boxes linked to the chart to where when I select something from the list, the chart would update the way slicers update a chart in Excel. I created a new query with the Well_ID column, grouped the column to display only unique values and excluded nulls. I then inserted the list box on my form and renamed it: WellIDList. Under Event > OnClick, I entered this code:

Private Sub WellIDList_Click()

DoCmd.OpenForm "subfrmAHLChart", , , "Well_ID=""" & WellIDList & """"

End Sub

This previously worked with a table; however it does not work with my chart. Anytime I try to select an ID, I get this error:

https://preview.redd.it/ya0cdlyx7rzd1.png?width=1179&format=png&auto=webp&s=58c59a7a68bab97621c9b16e43d8c9526ce0e410

Prior to this, I tried combo boxes with the chart using "After Update" and still continued to receive the same error. I created individual queries for each and changed the values to Unique instead and only displayed one column per three queries that is linked to the same query as the chart. The chart isn't a subform anymore as I didn't update the title.

I would like to be able to multi-select IDs, however, my first baby step is to get this to work. I thought this would be simple. Does anyone have any direction or different ideas? I checked and all of my names are correct. Does the chart need to have certain values in certain axes for this to work?

Private Sub cmbWellIDs_AfterUpdate()

ApplyChartFilter

End Sub

Private Sub cmbMonthFilter_AfterUpdate()

ApplyChartFilter

End Sub

Private Sub cmbYearFilter_AfterUpdate()

ApplyChartFilter

End Sub

Private Sub ApplyChartFilter()

On Error GoTo ErrorHandler

Dim strFilter As String

Dim chartRowSource As String

' Start with a base SQL query for the chart row source

chartRowSource = "SELECT [YearofDate], [MonthofDate], [CH4_Percent], [CO2_Percent], [Balance_Percent], [O2_Percent], [Init_Temperature_F], [Init_Static_P], [Init_Flow] FROM GasQuery WHERE "

' Filter by Well_ID if selected

If Not IsNull(Me.cmbWellIDs) Then

strFilter = strFilter & "[Well_ID] = '" & Me.cmbWellIDs & "' AND "

End If

' Filter by MonthofDate if selected

If Not IsNull(Me.cmbMonthFilter) Then

strFilter = strFilter & "[MonthofDate] = " & Me.cmbMonthFilter & " AND "

End If

' Filter by YearofDate if selected

If Not IsNull(Me.cmbYearFilter) Then

strFilter = strFilter & "[YearofDate] = " & Me.cmbYearFilter & " AND "

End If

' Remove the trailing " AND " if it exists

If Right(strFilter, 5) = " AND " Then

strFilter = Left(strFilter, Len(strFilter) - 5)

End If

' Finalize the SQL statement

If strFilter <> "" Then

chartRowSource = chartRowSource & strFilter

Else

' If no filters, select all records

chartRowSource = "SELECT [YearofDate], [MonthofDate], [CH4_Percent], [CO2_Percent], [Balance_Percent], [O2_Percent], [Init_Temperature_F], [Init_Static_P], [Init_Flow] FROM GasQuery"

End If

' Apply the row source to the chart

Me.AHLWellfieldDataChart.RowSource = chartRowSource

Me.AHLWellfieldDataChart.Requery

Exit Sub

ErrorHandler:

MsgBox "An error occurred: " & Err.Description

End Sub

Thanks in advance!