I know this has been asked 1000 times - I myself, tried it for the last 15 years (since acc2007), but to noavail: is there a way (using APIs or not) to get the real position and rectangle of a control after MS applies anchoring?

Thanks for any help :)

I've seen and created nice looking user friendly tools in MS Access.

The main statement I hear is that amateur coders are making terrible tools using MS Access.

But this argument doesnt make any sense... any fool can try to create some business system or tool in excel or any other coding language from scratch and still make a terrible system.

Blaming MS Access for the end-quality of a badly made application is down to the creating User dev expertise more so than the development tool used.

Anyway, I wish code-snobs would admit users create these issues and its not always the fault of the tool used.

I have a number of complex databases for horse racing. One of them calculates the % winners from a day's runners of all the races they ran in, in the last 3 months. It probably takes an average of 20 minutes per meeting to run, so perhaps averages around an hour to run each day. Another creates web pages for all horses that ran in the last week and updates all the race pages of races ran in the last 3 months. (writes around 3-5,000 web pages each week) This one probably takes 2+ hour to run. I should also say, that the databases are running tons of queries in macros, rather than code and are running in Access 2016 on Windows 11 home.

Current system - LG Gram laptop (2022) with a 12th generation Intel i7-1260P, Intel Iris Xe graphics, with 16GB DDR4 memory, 64 bit.

So, my question is, would an expensive PC, with a modern processor, memory & graphics card be likely to have a significant impact on the time it takes these databases to run. Thanks for any help/advice given.

Hi, I use following tables: The first table includes parts with material numbers, some parts with the same material number are listed several times, the second table includes serial numbers for the material numbers. In a query I tried to combine this two tables, but for the same material numbers the lines are duplicated now because of different serial numbers. Is there a way to divide the different serial numbers (SN) to same material numbers (MN)? Example: Table 1: Part 1 MN 1, Part 2 MN 1, Part 3 MN 1 / Table 2: MN 1 SN 1, MN 1 SN 2, MN1 SN 3 -> Solution: Part 1 SN 1, Part 2 SN 2, Part 3 SN 3, instead of Part 1 SN1 SN2 SN3, Part 2 SN 1 SN2 SN3, Part 3 SN1 SN2 SN3.

No, I'm not talking about Halloween. I'm talking about the long-awaited Monaco SQL editor for Access!!!!!

I went away from my computer for a couple of hours, and when I returned and opened a query, it was like I was transported into a whole new world -- like Dorothy in the Wizard of Oz when her world went from black and white to color. It was a beautiful, wonderful, magnificent thing. I think I could cry. 😂😂

Seriously, folks: Build 16.0.18129.20100 has this new, magical universe.

For more information, see: https://www.reddit.com/r/MSAccess/comments/1fo34nn/new_sql_editor_preview/

I cannot find any way to replace the standard Access splash screen. I can find several references online to using a .bmp file in the same folder with the same name, but this does not work. Tried with .accde, .accdb, both with and without a database password. Cannot get it to change. Anyone have any ideas?

My goal is to use the text box to filter queries by date plus whatever number is in the textbox. Such as Between Date() And Date() + [Forms]![StartingForm]![Future_TextBox].

I got that part done but where I run into a snag is when the textbox is empty it breaks the query. Here’s what I’ve got so far but now the query ends up empty.

Between Date() And Date() + [Forms]![StartingForm]![Future_TextBox] Or [Forms]![StartingForm]![Future_TextBox] Is Null Or [Forms]![StartingForm]![Future_TextBox] = ''

Thanks in advance!

I am trying to connect a query I ran in Access to an excel sheet, so that I can refresh the data in Excel and it will pull the new data into my pivot charts. I am getting an error, see picture. Can someone tell me what the issue could be??

So I was brainstorming the best way for users to have a feedback loop where they could communicate to other users who use the same database and who access the same records via different forms along various stages of a record's life cycle, and I wondered if, instead of each form/stage having it's own long-text field for users to add comments, which would cause someone to have to read through multiple comment fields and try to make sense of when each comment was added (each field could have multiple comments from different times) and then make sure they read each comment in the right order across various comment fields, if it would be possible to have a single long-text field in a record, and locking edits on it on all forms, but having a text entry field where each user could type up their comment, click a Send/Save button to then add their comment to the bottom of that one text field so that there is a uniform sort of chat field that people can read sequentially from top to bottom?

It seems like a simple enough idea that I cannot be the first person to imagine. If it's possible/feasible, does anyone know of any examples of this where I might find the necessary VBA code to reproduce it? I am an Access novice and more of an ideas man, haha. I wonder what that code would look like!

Bonus points if the VBA could also list who made each comment and the date/time as well!

I have a database in a VDI environment (the database is located on a mapped drive). There is also a printer on the VDI with defaults set to print in black and white.

My problem is that if the printer is the users default printer, and I want to print something from the database it sets it to color instead of black and white. If I select the same printer manually from the print list, it sets it to black and white.

If I don't have the printer set to default it works fine since I am forced to choose the printer from the list.

If I create a new blank database and try to print, it works fine, i.e. is black and white.

Any suggestions on what may cause this?

I have a table that contains assignable user roles, and each of these roles can contain a varying combination of permissions - these permissions are set values that are listed in a permissions table.

For reference, I have approx. 150 permissions - these are each listed as individual records on my permissions table. I also have approx. 20 roles - each role contains various permissions (ranging from 149 permissions to a single role, down to 5 permissions for a single role). I was able to create a lookup field and set it to allow multiple values, and assign permissions this way, but it seems rather cumbersome.

I'm curious if there is a way to achieve this functionality with a form similar to the one pictured below?

https://preview.redd.it/cf2ms1qq8rxd1.png?width=800&format=png&auto=webp&s=8ef32271972fdadf53dcb0abee621dcd26731047

Essentially have my full list of available permissions on the left, and my permissions assigned for that role on the right - and use an add and a remove button to assign specific permissions to a particular role.

Is Access capable of this? Is there a better method?

This is probably a dumb question, but I am at a fork in the road and would need to know this. One of the reasons I want to built a front-end with Access is because of the robust options for applying controls to various text-input fields so that certain users can only access or edit certain fields. Now, If I have to grant every single user the same level of access to the linked SharePoint list (which will act as a sort of backend data warehouse for each record), then the controls I want to build will sort of be moot because those users could then go and make changes to the records directly in the SharePoint list.

Thanks!

Code: Like IIf([Forms]![StartingForm]![Item_Check]=True, [Forms]![StartingForm]![Item_TextBox], [enter item number or press enter for all] & "*")

Summary: The goal is to have the criteria only follow the text box if the checkbox is market. It is following that halfway.

Issue: regardless if the box is checked, it will show the “enter item number or press enter for all” message box. It will still follow the if statement, though. If it’s checked, it filter just what’s in the text box and if unchecked, will show all.

I have several of these functions for other criteria’s so I don’t want to eliminate the message box but how do I get it to not pop up when the checkbox is checked?

Thanks!

I am new to access programming so I will probably not use the right terminology. I am creating a database for a histology distributor. We have purchase orders coming in that can be partial shipped out. For example, an order of 10 microscope slides and 5 tubes comes in but only 5 of the microscope slides and 2 of the tubes can be shipped; the remaining are in back order. I have created a junction table which only collects 2 foreign keys (see attached for my relationship). I have a created parent form (see attached) to show:Parent Form: Order base information (i.e po#, order date, customer, order type)

Relationship

Parent Form

Child Subform ( Q-rs shipping tracker): order details.

Now to my problem, linked my junctiontb subform (child) to Q-rs shipping subform (parent) and linked junctiontb subform (child) to Shipping subform (parent). The foreign keys are present in the junction subform but when I save the forgein keys don’t show up in the junction table.

I am expecting the foreign keys are automatically save to the junction table

The Group Header is somehow spilling outside the page, and I have no idea why - in properties it's 7.5 inches. There used to be a Report Header doing the same thing, but I deleted it. I've applied no color to the Group Header, yet on the third page of this report, it's a blush color, and the fourth page shows the "stub" of the leftover Group Header. It's not colored on every page, and every other page is blank. I've attached the Design View and the Print Preview view to make it make more sense.

Can someone tell me what's going on?

Report is grouped on Customer ID

Actually, the stub is a bit lower

Nothing in group header is too far to the right

I have an Access application that I've been tasked with fixing. I'm not an MS Access expert at all, but we don't have a DBA at our company. I had one semester of level 1 database administration in college 12 years ago, so they decided that makes me the "expert" here. So now I'm trying to guess and prod my way through fixing this.

When users open the application, they are immediately peppered with 5-6 database table permissions errors from various linked data tables like so:

AttachDSNLess Table encountered an unexpected error: You do not have the necessary permissions to use the <dbo_tableName> object. Have your system administrator or the person who created this object establish the appropriate permissions for you.

We did some troubleshooting on this last week (I managed to pull in the customer and my team lead on a group call to go over this in detail), and we noticed that the dbo tables are linked tables to a SQL Server instance on another host. We looked at the connection string and noted the service account and password that are connecting to the other tables. It is not an ODBC connection but a "SQL" connection as per the Linked Table Manager.

We tried refreshing the link and just received the same permissions error as above.

The connection string is:

DRIVER=SQL Server;SERVER=<SQL Server Instance>;UID=<SvcAccount>;PWD=<SvcAcctPW>;APP=Microsoft Office;DATABASE=<Database on SQL Server>

When we looked at the SQL instance, the service account had "DB Owner" rights to all databases and tables for the instance the MSAccess application uses (which as I understand is the HIGHEST permissions level you can get to a database and its table objects), yet Access STILL insists it doesn't have permissions to do anything with the tables when users open the application and just throws those errors out still.

We tried rebooting the SQL server after-hours last weekend, but yesterday morning, users were still peppered with these errors. We're at a loss as to what's causing this.

Hi all, hopefully this is just some little thing I am missing. I am populating a subform with a couple of inserted lines. It won’t display on the subform until I requery it, but as soon as the requery fires, I get an additional blank like in the table. Any idea what might be causing this?

**EDIT** Thanks to everyone who has offered advice and inputs. Sorry for the delay, my boss an I spent the afternoon trying several of the suggestions over a teams video call and once work was done I had to immediately head out to a meetup. OneDrive provided a unique issue with its pathing system that has made a lot of the very good suggestions a bit difficult as the pathing keeps them as unique instances so we cannot double check who else would be in a file.

We are looking at some of the other Options tomorrow. Sharepoint is an option we are looking at. in previous tests though my boss had issues with some of the backend tables properly updating in Sharepoint so we are diagnosing that. Thanks again for all the responses and inputs but I need to head to bed as I go into the office tomorrow.

**ORIGINAL MESSAGE** Hello MSAccess. I have been tasked by my work to find a solution for this problem and after a couple hours of research not providing what I was looking for I figured I would ask the experts.

So our office is doing away with our sharedrive and asking us to migrate our Database, which is in use by my department plus approximately 25 people spread out, to OneDrive. I have already expressed concerns with localization issues as well as all the other typical concerns about moving a database to cloud storage, but as is always the case I was ignored and we are being forced to go forward with this. While dealing with another example of leadership genius I need to now find a way to work around the obvious issue of our people constantly overwriting updates. While on the sharedrive, we could see when another individual was in the folder as a result of the "ghost" file that was created. Unfortunately, through our testing, while on OneDrive we can only see our own "ghost" when open but we cannot see anyone else's meaning we cannot identify when another is working at the same time.

My question to you boils down to 1 of 2 options. Is there a way to set the maximum number of users in the file at any time to 1 causing a block if a 2nd or 3rd user tries to log in at the same time? Or alternatively, Is there a form or warning message we can have Pop up to identify when another user is updating? This was so much easier when the files would only open as Read Only.

Thank you for your time and assistance.

I have a Form, which has a subform based on a query. The subform must be in datasheet mode tor the use case. This query gets Data from three Tables: one is a table which Contains article IDs and article Names, the second contains article IDs and stock, the third contains its primary key, the desired article IDs, quantity, and width of the articles.

When the Form is opened or another line is entered, the article ID in the third Form gets automatically filled with a defined value with VBa. The user should he abe to see the name and stock and input a desired amount and width.

The problem is, when the second table is joined its not possible to edit oder input data in the form. When it isn't joined, inputting data is possible.

Has anyone a clue, how i can join the second table without impeding the possibility to edit data? Alternatively other solutions with the same end result for the user would be sufficent.

I seem to have trouble understanding the logic behind not beeing able to edit the data. If anyone has recources to get this in my head i would be thankfull.

Hello,

I have 2 tables.

Tests table contains info about batches. Table has id and batchNr

Results table has Id, Name and Testid(tests.id) value.

I want to scan through Tests table and get disctinct count values from results table.

For example:

Tests table

Results Table

Result i'm expecting:

Lamp Detection - 1

Lamp Calibration - 2

Battery Backup Test - 1

By using the sql below i'm receiving

Lamp Detection - 1

Lamp Calibration - 3 --> This should be 2

Battery Backup Test - 1

SELECT 
    Results.Name, 
    COUNT(Results.Id) as Count
FROM
    Tests
        LEFT JOIN Results
            ON Tests.Id = Results.TestId 
            WHERE 
                Results.Status = 'Failed' AND 
                batchNr = '1939 39.0'
            GROUP BY [Results].Name

How can i fix that?

I managed to run a process on a ADODB connection to a SQL server and it ran much faster than a ODBC linked table by using the MSOLEDDBSQL driver. I know in the linked table manager I can set a custom connection string and specify driver=ODBC Driver 17 for SQL Server. Is there a way to specify the OLE DB driver in place of this in the connection string for a linked table?

Appreciate any insight I just can't find any documentation saying if this is possible. Most of the code I can go with the adodb method I just want to leave a few parts easier for users by using linked tables. I can't find any documentation for what to put in the driver string to target that driver.

What the query currently show

I would like the querry to show the sales order once and under material combine them all in to one cell

Example [120787585] [2L4, 2L6, 2l6C.....ect. ]

I am not sure how to go about doing this I am fairly new to Access

I am new to access and am doing this for a database assignment (school project).
for context, im making an employee database and want to make my "Bonus" table dependent on "Performance evaluation" table through queries. so if performance score is x>3(based on a likert scale), an employee is eligible for a bonus. My idea is that i only want to key in data for performance and have the bonus of my hypothetical employees be automated in the bonus table.
after hours of chatgpt-ing whether or not this is possible im close to giving up already

this is query grid but when i run it, nothing shows up

this is my performance evaluation table (there are values where >3)

bonus table; nothing showing up either

can anyone let me know maybe this just isnt possible so i can move on... thanks ;(((

Monaco SQL Editor*

Taken directly from the Microsoft slides, here are some highlights of the upcoming feature:

• Powered by the same UI library that drives the popular IDE VS Code
• Supports syntax highlighting, line numbers, light/dark themes, and more
• Auto completion support
• Comments and Format support

Latest info I could find; NEW - Monaco SQL Editor

* from Sneak Peek: Monaco SQL Editor for MS Access

edit: changed the flair.

My apologies, here we go again.

I would like to create a point of sale, I am new to MS access. Just watched a few YouTube videos and sort of understand forms, tables, queries, but not enough to get it done, especially when it comes to creating the relationships between fields and formulas.

So, here i am; POS doesn't have to be complicated and really just needs to accomplish the following,

spit out a receipt with the items bought, price-per item, taxes, and total amount. This would be my form

as for my TABLE, i suppose it needs

UPC --- short text

ITEM DESCRIPTION----SHORT TEXT

FIXED OR VARIABLE ---- YES/NO? for per pound items (tomatoes, etc)

SALES TAX? ------ YES/NO?

yes = (price x 6.875%)

no = price

i really do not need to keep track of inventory, or give discounts or cupons or anything of that nature.

thank you all for your help

https://preview.redd.it/slc56crf8bxd1.jpg?width=3060&format=pjpg&auto=webp&s=6d094032f7ce09f6af872f613d1412a1a695d219

https://preview.redd.it/1di1a2rg8bxd1.png?width=951&format=png&auto=webp&s=04ab34222c8142a606b0980c8ff2e6a772a6925e

I'm a social worker trying to build better tools for tracking my client interactions. One key feature is a case note form.

Relevant tables: Clients and Issues. Clients is my clients, Issues is all the many things we're working on with each Issue tied to one Client. I already have a tabbed form with a combo box to filter by client. The case note form would ideally be one of the tabs.

Mockup image to illustrate, color-coded for references below:

https://preview.redd.it/zwb26r0ru7xd1.png?width=746&format=png&auto=webp&s=2f58995c7747c49f7986e0f079003af9c7347712

I want to have three outputs from this form:

1. Create a CaseNote table that adds a record for the info in yellow
2. Update existing records in the Issues table based on the info in orange (except for ProgressNote)
3. Create an IssuesHistory table that adds a record for every ProgressNote including its related Issue ID and CaseNote ID

The hard part is the blue section. I want to manually select Issues from a list to create the filtered subform in orange.

I'm comfy with the basics of Access, but inexperienced with macros and code.

Any help is appreciated, even just pointing me to the right resources to self-teach!

I am a very novice user of MS Access. I am building a Health & Safety database for our organization. When Inevwntually split the database I need to ensure users (e.g. operations supervisors) can put data into tge database using the forms, etc. but can ot have access to the drive where the data is hosted.

We have a typical corporate network with various drives that different departments have access to.

The "Safety" drive is only accessible to members of my department.

Is it possible to host the database on a drive frontend users will jot have access to?

We also have access to MS 365. If I host the database on SharePoint is it possible to host the database on a Sharepoint that frontbend users will not have access to?

Is there another way to accomplish what I want to do?

Im getting a weird issue when running VBA through some records.

The sub opens a record set based on an SQL query through a table, the particular field I am referencing is set up as a double in the table, and the variable I am trying to place the field value to is also a double. In the table the value for the fields are all containing decimals so I know there’s no issue there.

For some reason the Rs![fieldname] method to input the value into the variable keeps ignoring the decimal places.

I’ve tried format(Rs![field],”0.00”) to no avail. Anyone else has a similar issue?

I need help designing a MS Access data input form that mimics an electrical power breaker panel.

The panel has 42 slots into which circuit breakers are installed, arranged in a column of odd-numbered slots and another column of even-numbered slots.

Most slots have a single breaker, but some have 2, while some other breakers span across 2 or 3 contiguous slots. So for instance, the breaker at the top of the odd column could occupy slots 1 and 3, or one at the top of the even column could occupy slots 2, 4, and 6.

The database has tables named tblPanel, tblBreaker, and a tblSlotAssignments; the last one implements the many-to-many relationship between slots and breakers. So far, so good.

My problem is designing the data input form corresponding to these tables. It would be nice to mimic the physical layout of actual electrical panels as closely as possible.

I also wonder if somebody has already done this, even on some other software platform.

Thanks for your help!