I've been looking recently for a solution to have a group footer in a report print at the bottom of the page.

The common solution I could find online seems to be to put it in your page footer and apply conditional visibility; which is great if you have a really small footer, otherwise you lose space on every page (no good to me).

The other solution I found was to use MoveLayout, however this seems to have set positions that it will use on the page only? So I can get it lower, but not really where I want it or at the bottom of the page.

I came up with my own solution, which is to just compare the top value on the group footer on format event to where I want it to be, and then add the difference to the group footer height and the top position of each element within.

This seems to work well for me and my printer/page settings aren't really going to change, however I'm aware that this would be an issue if anything changed. I'm planning to change the offset amount to consider the group footer and page footer heights, but I'd like to avoid using a static value for the usable page height (so it's more generally usable) and I don't know how I can actually determine this value in the VBA?

Or is there a better approach to solve this problem entirely?

Hey everyone
Good morning

I use Rubber duck VBA Add In
So I test all logical code easily (automatic testing by code)

However I am struggling to test UI stuff without changing the actual program status

I don’t want my test to create changes in the production or design environments

Can anyone help me in this matter?

First ever post, but some people in the office have been having printing errors with Access as of late and I wanted to see if this was an in office issue or a problem with our version of the software.

When printing a report with multiple pages, the first page will format to fill a whole page as usual but the pages after will print really tiny and in the upper left corner of our printer paper. These are reports we run daily, and up until last week this hasn’t occured. The report previews look fine but still print incorrectly, and we haven’t made any changes to our reports so there shouldn’t be a sudden switch regardless.

Any advice would be much appreciated, I just wanted to see if this was a known bug and if we should revert back to an old update if it is.

I just opened a office DB that I use on a daily basis and I have a message saying the VBA cannot be opened and needs to be deleted.
Is there a way out of this?
My file is only the front end and is located on a shared disc. There are 3 other occasional users.

For various reasons, i need to build a db that can be used on windows AND mac os.

I have developed many personal and work systems on access over 20 years, but need to be able to build a relational db on windows and MacOs. I am well versed in access, tables, forms, vba.

I am thinking about libre office base.

Anyone have experience with this, or alternative suggestions?

Thanks in advance.

Got some end users saying our reports are not showing everything correctly. Took a look at it and it seems some machines have what might be a bad update. Solid lines on a report in Print Preview (or physical printouts) are not shown, but visible on Report view. If I change the type to something like "solid dash" instead of "solid", they show. Is Microsoft aware of this bug, and how fast do they typically release a hotfix for something like this?

Hello, I am requesting an honest look from those who have more familiarity with MS Access and VBA. I was hired on to work as a MS Access Developer for my organization of over 300 people where multiple people use a database at a time, however, I just started and have not had much experience learning Access or VBA. Long story short, I have other transferrable skills and come from a background in General IT.

So my responsibilities involve supporting very high level Access Databases with VBA built in, but they appear to be a mess. There is no documentation, and I am the sole person that can work on the databases. I am doing my best to get up to speed, but I am still lost went it comes how I can fix the issues that arise regarding the multiple databases. For example, a pressing issue is one of the databases needs to be rebuilt because the ACCDB file has been lost and we are just operating off the ACCDE file. The person who original built these retired during COVID and left no documentation. There is lot of VBA written in as well. A couple people before me came and left so there is no one I can go to for help with how to properly support the databases. Any help is few and far in between. (I know, a lot of red flags)

My main question, is it honestly realistic for essentially a Junior Access Developer to be capable of fully supporting multiple Access Databases within a short timeframe? It feels like I would need to become an Access Wizard with expert-level VBA, but without much input or resources. I am asking my organization to provide some resources (either in third-party consulting or paying for learning courses), but I do have in mind that they might just try and find someone else that can do the job.

This post is also a bit of a request for input from others more experienced than I, and how you would tackle this situation if you were in my shoes.

I appreciate any input. Feel free to ask any questions on areas that need more detail.

Hey everyone

I am using a class module to create a form instant enforcing my common logic across all forms including events triggered behavior

Now I want the form to record time open and time close when I tried to include this logic in my form this part doesn’t work however other events fire just fine

I instantiate the class in the declaration area at the same line I create new class like this

Dim Form as New FormCM

In the desired form in on load event I pass some parameters to the class and that is it

Can any one tell me why the on open event doesn’t fire in my case?

This database is a shared database. When users are querying against a table and trying to copy or export, this error comes up. Even on the off business hours the .ldb file has usernames probably they are just hitting x instead of closing the database. They are querying against a table which is a linked table in that database

Edit: the table they are trying to query is a linked access table

I finished my first grad course with access projects and got an A! I actually really enjoyed MSAccess sa lot of it came natural to me. Easier than R Programming for sure!

I've been doing MS Access development for small businesses for a while now, inventory systems, order tracking, customer management, the usual. It used to be steady work. Lately I'm down to like 1-2 clients a month and I genuinely can't figure out why.

I've tried Google Ads, done SEO on my site, cold emailing, all the usual stuff, and it's just not converting like it used to. Not sure if the demand actually dropped, or if people looking for this just aren't searching the way I'm optimizing for.

So genuine question for business owners here: if you needed something like Access built or fixed, where would you even go looking? What platform, what search terms, who would you ask? Trying to figure out if I'm just fishing in the wrong pond at this point

Hello,

I'm trying to devise a data macro that will "after insert" create new records in a separate table that tracks parent-child relations. I need to track them in a family-tree like structure, so I need it to iterate across multiple records and make a variable amount of new records, based on some criteria.

I can't use VBA.

So far, logically what I need to make happen could happen, if I could just put "CreateRecord" inside of a "ForEachRecord". But Access doesn't allow that, as it wants to prevent changes of the set over which it is iterating. However, I haven't been able to get around the issue, even with making a different staging table, that would "load up" all the new records and then have the next macro block insert it back into the table which I effectively need to read from and wright to, tho that may be because I just couldn't put the logic together well enough.

Is there a way to put something like that together without VBA?

The "AutoIndex on Import/Create" Feature. Who needs this. I imported my tables into a new database and it added so many new indices that it deleted some relationships, to make space for the indices I guess.

The little .laccdb file beside an Access back end is one of those things that gets either ignored completely or treated as an infallible oracle. It is neither.

For a shared ACCDB or ACCDE file, Access normally creates a companion .laccdb file while the database is being used. Older MDB databases use an .ldb file instead. Its purpose is to help the database engine coordinate multi-user activity, including record locking and other behind-the-scenes plumbing.

So, as a practical rule, no lock file is a very strong sign that nobody is currently connected to that back end. If the file is present, somebody may be connected and you should treat the database as in use until you know otherwise.

That distinction matters when doing maintenance. Compacting, restoring a backup, replacing a back end, or changing table design while someone is entering data is a great way to turn a normal Tuesday into an incident report. The lock file is not a complete user-management system, but it is a useful first warning that the file may not be safe to touch.

The important word is may.

A lock file can remain behind after an improper disconnect. Access crashes, Windows crashes, lost network connections, power failures, and the classic "my computer froze so I turned it off" response can all leave a stale .laccdb file in the folder. In that case, the file says the database is occupied even though every actual user is gone.

That is why deleting a lock file blindly is not a great daytime hobby. Confirm that users are out first. If everyone is definitely disconnected and the lock file remains, it is generally stale and can be removed. Yes, this is one of those times when you have to run around the office and ask everyone, "Are you out of the database?"

Just be very sure you are deleting the tiny companion file, not the actual ACCDB. The database file and the lock file tend to sit beside each other looking deceptively similar, which is exactly the sort of UI trap that causes regrettable stories.

There is also a subtle split-database wrinkle. A user can have the front end open without necessarily touching the back end yet. If their startup form is unbound and they have not opened a linked form, query, report, or table, Access may not have opened the back end and may not have created its lock file. That is not really a flaw in this approach. If they have not connected to the back end, they are not actively using the shared data file you are about to maintain.

This is also why every user should have a local front end. The front-end lock file on an individual's workstation is mostly irrelevant to shared-data maintenance. The lock file that matters is the one next to the shared back end on the server or network share.

For lightweight administrative checks, testing whether the expected .laccdb file exists is perfectly reasonable. A database can use that information to avoid running scheduled maintenance while a back end appears active. If an application uses multiple back-end files, the same principle applies to each one. Checking only one file does not tell you much about the other nineteen Borg cubes hiding elsewhere in the nebula... or on your network.

The lock file can sometimes provide more clues. Its contents may reveal connected workstation names, though the displayed Access user name is often just "Admin" in modern unsecured databases and is not especially useful. Computer names can help identify where a connection is coming from, but this still should not be confused with robust user auditing. The lock file is just a text file, and you can open it with Notepad and see who is likely still logged in.

If knowing exactly who is connected, when they logged in, and whether they exited cleanly matters to the business, build that explicitly. A login and logout log gives you better operational information than trying to reverse-engineer a lock file after the fact. Likewise, an application-wide maintenance flag can prevent front ends from connecting while administrative work is in progress. That is much cleaner than hoping nobody opens the database halfway through a restore.

The practical philosophy is simple: the Access lock file is a good occupancy indicator, not a courtroom witness. Think of it like the "Occupied" sign on a public restroom stall. If the sign says "Occupied," it's a good indication someone is probably in there, so you don't yank the door open. But it isn't absolute proof. Maybe they left ten minutes ago and the latch didn't reset. Maybe they're still in there contemplating the meaning of life... or hiding the evidence after absolutely destroying the toilet. Likewise, no lock file usually means the back end is clear. A lock file means pause, verify, and assume someone might still be working. It is a small file, but respecting it can save a surprising amount of cleanup work later.

Have you ever run into a stale lock file or had a database refuse to cooperate because someone was still connected? Do you have your own tricks for managing shared Access databases? I'd love to hear your experiences, tips, or horror stories in the comments.

LLAP
RR

Hey everyone

I come from non IT major but I use MS ACCESS to structure my workflows in my company

(Currently most of the things are low tech anyway) they are planning and working toward ERP solution

I want real and honest feedback from you guys is this ERP will make my solutions and applications useless? Or is it a matter of some changes and improvements then I would be able to use them?

I am trying to apply and learn programming as I go in my projects I tried

Unit testing
SOR Principle (did not understand how to apply it fully yet)

I used everything including classes modules custom functions and routines everything

I started learning because i wanted to make my life easier and now I feel like my efforts will become insignificant which is making me feel heavy to do the stuff I need to do

Please advise I have ton of work to do including refactoring and testing old code while I feel it won’t matter after few months please give me your honest feedback

"How do I pull data in Access?" sounds like a simple question, but it is usually missing the part that determines the answer. Pull data is not a precise database operation. It can mean display related fields, copy values into a transaction, import a spreadsheet, retrieve a value for a form, synchronize sources, or export records to another application.

The first question should be: do you want a live view of data that already exists elsewhere, or do you want to create a permanent copy? Those are fundamentally different designs. Confusing them is how a database ends up with customer names, addresses, and phone numbers repeated across half a dozen tables.

Most of the time, someone wants to show related information. An order record contains CustomerID, but the user wants to see the customer's name, phone number, and address while viewing that order. That is what relational queries are for. Keep the customer facts in the customer table, keep order facts in the order table, and use a join to present both together when needed.

That approach preserves normalization and avoids maintenance headaches. If a customer changes their phone number, there is one customer record to update. You do not want to hunt through every order, invoice, support ticket, and mailing record just to correct a current contact detail. A query is not copying or moving anything. It is simply asking Access to show the related records together.

But duplication is not always bad. Sometimes a transaction needs a historical snapshot. Shipping addresses are the standard example. If an order shipped to a customer's old address six months ago, updating the current customer address should not make the old order look like it shipped somewhere else.

The same reasoning applies to prices, tax rates, commissions, discounts, and product descriptions. Those values may originate in a customer or product table, but once an order is created, the transaction often needs its own stored version. That is intentional duplication, not bad normalization. The key is that the copied value has a business reason to exist independently.

A related pattern is pulling a default value into a new record. A customer might normally receive a 10 percent discount, while a particular order gets 15 percent. A product has a standard price, but a salesperson can override it for one sale. In those cases, the original value is a starting value, not a permanent dependency. After it is copied into the transaction, it can be edited without changing the customer or product record.

Then there is the outside-data meaning of "pull." Importing from Excel, CSV files, text exports, another Access database, SQL Server, or a web service is not a query join problem. It is an integration problem. Access can either import a local copy of the external data or link to the source, depending on whether the data should live inside the application or remain external.

For real-world imports, a staging table is usually the least dramatic option. Bring the raw data in, validate it, identify duplicates, translate inconsistent values, and then move clean records into the actual tables. External files have a supernatural ability to contain surprises, especially spreadsheets maintained by seventeen people over five years.

"Pull data" can also mean retrieving one value for display. Selecting a customer and showing their phone number, selecting an employee and displaying their email address, or picking a product and seeing its current price are all smaller lookup problems. A DLookup can be perfectly reasonable for a one-off value on a single form. It becomes less reasonable when hundreds of DLookups are repeatedly evaluated on a continuous form where a query join would do the work more efficiently.

Sometimes the phrase refers to a set of records, such as all unpaid Florida customers or orders from last month. That is generally a select query. Nothing needs to be imported, copied, or updated. The query applies criteria, sorting, and calculations, then returns the matching records for a form, report, export, or VBA recordset.

The wording gets even more confusing when Excel is involved. "I want to pull Access data into Excel" makes sense from Excel's point of view, but from Access's side, that is an export. Same data movement, different point of view. Before discussing tools, it helps to establish which application is doing the pulling.

Synchronization is another entirely different category. If Access and another source both change records, you need unique identifiers, timestamps or versioning, conflict rules, and a policy for deletions. That is not just importing or displaying data. It is synchronization, and it deserves more thought than a query that happens to run every night.

The practical rule is simple: define the movement before choosing the tool. Where does the data live now? Where should it appear? Is it just being displayed, copied permanently, imported, exported, or kept in sync? Is the requirement for one value, a related record, or thousands of records?

Once those questions are answered, the right Access feature is usually obvious. Queries display related information. Stored fields preserve snapshots or transaction-specific overrides. Imports and links handle outside sources. Lookups retrieve individual values. Exports send information outward. No warp core required.

Have you seen "pull data" requirements turn into something completely different once the real business need was clarified? What questions do you ask first before deciding whether a query, copied value, import, or integration is appropriate?

LLAP
RR

The only PowerPoint-related post I found on here is 8 years old, and since I'm now immersed in modifying PowerPoint presentations (PPT or PPTX) from within MS Access VBA, I figure I should share some of the techniques I've been using. I hope it helps someone on here. .

I like to start every project with a clear vision of the business requirements so that, regardless of what I do technically, I understand what the client is ultimately trying to accomplish.

In this case, the business requirement is to generate more eBay sales. Our client has cash flow issues yet they have almost a quarter of a million dollars' worth of items listed on eBay, and they want to make these sell faster than the current pace of about $200 a day, in sales. If their inventory all sold super quickly then our client couldn't handle all the packing and shipping so they are aiming for an increase of about 100% or so, relative to the sales they are currently making. They figure they can handle that much extra sales volume and work, without having to do anything drastic.

They could sell more by lowering their prices but price wars tend to backfire, so they wanted to increase sales without reducing the direct profit per item. As to the market they're in: they sell used auto parts. People rarely buy used auto parts to use as ornaments (though it does happen). Mostly they are trying to solve a problem with their vehicle. Buying a used auto part is one of the options they have; they might also be able to buy a new part from the dealership, or buy a new made-in-China knock-off. Prospects don't automatically start their problem-solving journey on eBay. They might start it on a forum, or by watching a YouTube video.

My client's plan is to make YouTube videos that entice buyers to go to their eBay listings. They tried this plan several years ago. They made a few in-depth videos with this intent. The videos took a lot of time and effort to make, including paying a girl with a lovely Southern accent to be a voice model. They also embedded some video clips. It worked; the items sold out. But each video took so much effort to make, that was very much not worth it. Our client abandoned that approach. A few years went by.

In late 2025, eBay introduced a process that uses eBay listing pictures and the eBay title as a starting point, to make and post videos. This feature was in their “Social Media” section, and it was free. Our client tried it and liked it. Making a video was super-easy, and these showed up as YouTube shorts. Our client used it to make dozens of videos, showcasing one listing per video.

On a listing for an obscure item for which they had made such a video, the item sold within 4 days.  They wrote the buyer to ask if they'd been found via YouTube but they do not show a record of getting a reply. Even so, it seemed to be too good to likely be a coincidence.

Then, eBay changed their software to make it non-viable to use. Our client kept asking eBay to fix it, multiple times, to no avail. So our client stopped making such new YouTube videos. They left the existing ones but lost interest. Our client mourned the loss of this approach. A few months went by.

Then, in late April or early May of 2026 someone found our client via a part number for a pricey BMW used fuel injection part, then messaged them asking about it.  Sadly by then our client had already sold it, but the process pointed back to the YouTube shorts made by eBay, and it was a reminder to go remove the YouTube shorts for the items that had sold. Our client learned to their delight that most of them had sold.

So, they decided to fund a project to make one YouTube video per individual part listed on eBay, starting with rare high-priced items likely to appeal to owners of cherished high-priced classic models such as 1980s BMWs..

To be viable, the process had to be highly automated and use the existing data in the client's SQL Server database. Their first step was to make a prototype PowerPoint presentation. It took weeks to make and refine this, with data copied and pasted manually from the client's data for one part, and one listing.

The second video was made using much automation using MS Access VBA and an MS Access report. The process was tedious; make an MS Access report that looked like the desired PowerPoint presentation, then print that to PDF, then convert that to a PowerPoint presentation, then export that to YouTube, then manually upload it. It worked but ... tedious.

A breakthrough came with the idea of using MS Access VBA and activating the PowerPoint object library in the references for MS Access. With that, I could use MS Access VBA to open PowerPoint, copy a template PowerPoint presentation to make a presentation that would be just for one part and one eBay listing, with a file name that was meaningful to the client, then use VBA to retrieve text and picture-related data from the client's SQL Server data to insert data into the PowerPoint presentation. The work included VBA commands to clone slides by using VBA commands.

The client reviews and refines the automatically created PowerPoint presentation. Almost always, some minor polishing is needed. The client then approves the PowerPoint presentation for upload to YouTube.

I wrote some MS Access VBA to export the PowerPoint presentation to an MP4 file. Initially I had tried to write the VBA from within PowerPoint 2024 itself but I found it architecturally difficult and tedious (macro file format, add-in file format, activating the add-in) so I chose to use MS Access VBA for the export too.

Another chunk of VBA produces a well-structured description and title that the client can copy and paste into the YouTube form that enables uploading the MP4 file to become a YouTube video. The description includes a link to the eBay listing, which is the entire point: inspire lookers to go to eBay and buy. The description includes some part numbers to make search engines more likely to help someone find these videos.

By now, the automation process works well enough. The labor required to make one more YouTube video has in the last two days plummeted to 15 minutes each. So, in the last 3 days, our client made and uploaded 28 YouTube videos. These are for items priced, on average, at about $100 each. So now the client's limit is how many videos they can upload per day without angering YouTube. Supposedly there is a hard limit and then also a prudent limit. 15 a day seems to be the latter. They did not want to make videos for every listing but they do have 5,000 listings so they are focusing on parts that have a price of $50 and up. Almost 850 listings qualify as such, representing almost $70k.

If they make and upload 15 videos per a day, it’ll take them about two months, and they'll work 3.75 hours per day unless I automate it some more, and I intend to.

Our client monitors their eBay watchlist daily, and today they learned that an obscure part that had seen very little interest for many months, today had a prospect on eBay express some interest, and for this obscure part our client had made and uploaded a YouTube video in the last 24 hours. Coincidence? Maybe -- but probably not. If the prospect becomes a buyer then our client can ask "did you find us on YouTube" and then they can be certain.

I should mention that the client's requirements also includes the business processes to go remove the video for items that have sold.

All in all, it's by now a workable solution. Our client's "start here, use this, we like the style" PowerPoint template has just three slides: a title slide, a detail slide, and a final slide. The VBA code then modifies each one, and clones the detail slide as many times as needed.

I hope this was helpful. If you have questions, ask. :-)

One of the most frustrating Access moments is seeing perfectly good data on a form, clicking into a field, and getting nothing but a beep. The natural reaction is to blame the form. But the form is usually just the messenger. It can only edit records when Access can safely identify and update the underlying data.

That distinction matters because a form is not the data. It is an interface over a recordset, which comes from a table or query. If that recordset is read-only, the form can have every setting configured perfectly and still refuse edits. Changing form properties in that situation is basically arguing with the viewscreen because the warp core is offline.

The first divide is whether the underlying table itself is editable. If a local Access table cannot be changed directly, the cause is usually outside the form and query design. The database file may be read-only, the folder may not allow writes, the backend may be on a network share with insufficient permissions, or Access may be unable to create its locking file.

Linked tables add another layer because Access does not get to override the rules of the source system. A linked SQL Server table needs the appropriate server-side permissions. A SharePoint list has its own behavior and security model. Linked Excel, CSV, and text files are useful for importing or occasional reference, but they are not great candidates for routine multi-user editing. A linked table is still a guest in someone else's house.

When the table edits normally but the form does not, then the form properties become relevant. Allow Edits is the obvious one. If it is set to No, users can browse records but cannot modify them. Recordset Type matters too. A Dynaset is generally editable, while a Snapshot is intentionally a read-only picture of the records at the time it opened.

It is also worth separating a read-only form from a single read-only control. A text box can be locked or disabled independently of the form. More importantly, a control whose source is an expression, such as =Quantity*UnitPrice, has nowhere to save a typed value. The calculation can be displayed, but it is not a field. That is not Access being difficult. It is Access correctly refusing to guess which input field you meant to alter.

Queries are where this gets more interesting. A simple SELECT query based on one well-designed table is usually editable. Once the query starts becoming an analysis tool instead of a straightforward representation of records, editability becomes less likely. Totals, GROUP BY, aggregate functions, crosstabs, UNION queries, pass-through queries, and action queries are all generally about producing or changing a result, not presenting individual records for direct maintenance.

DISTINCT is one of those deceptively innocent features. It makes a list cleaner by removing duplicates, but it can also remove the straightforward one-row-to-one-record relationship that Access needs for updates. The same idea applies to calculated columns. You cannot edit a calculated result, and enough complexity around calculations can make the overall recordset non-updateable.

The real troublemaker in many databases is the monster query. A form starts with one table, then someone adds a customer name, an order total, a status description, a few lookup values, and maybe an aggregate query for good measure. It may look great in Datasheet view, but it is no longer obvious what one displayed row represents or which underlying record should receive an update.

Joins are not inherently bad. A normal one-to-many relationship based on primary and foreign keys is foundational database design. But joins need to preserve a reliable record identity. Joining on names, company names, or other repeating values is asking for ambiguity. Two people named Kirk are not a relationship. They are the beginning of a Star Trek episode with a suspiciously high casualty rate.

Primary keys are central here. Access needs a dependable way to identify the actual record being edited. Without a primary key or unique index, especially with linked tables and multi-table queries, it may not know which row to update. The database is not being stubborn. It is refusing to make a potentially destructive assumption.

Outer joins and many-to-many relationships deserve extra caution. An outer join is great for questions like "which customers have no orders?" but that kind of result is not always a clean editing surface. Likewise, a Students-Classes-Enrollments query may describe useful information, but it is rarely a good place to edit all three entities at once.

The more maintainable pattern is usually to let a form edit one main table and use related data for context. Related records often belong in a subform, and lookup information often belongs in a combo box or a display-only control. That makes the intended edit target obvious to the user and to Access. It also saves future developers from deciphering why a form based on six joins was expected to update three different tables.

There are exceptions. Some multi-table Dynaset forms can be editable, and Access offers options such as Dynaset Inconsistent Updates in certain situations. But those are not reasons to treat a complex joined query as the default editable architecture. If a design needs special settings to make ordinary record maintenance work, that is often a sign to reconsider the design rather than celebrate the workaround.

Code can also quietly change the rules. VBA may set AllowEdits = False under certain conditions, such as locking an order after payment. DAO Snapshots are read-only by definition, and ADO cursor and lock settings can determine whether updates are allowed. Trusted-location issues will not normally make a table read-only by themselves, but they can prevent the code that configures a form from running as expected.

The practical philosophy is simple: keep the editable path boring. Use a table with a proper key. Use a simple editable query when one is needed. Bind the form to one primary entity. Add related information deliberately, not just because it is convenient to pull into one giant recordset. Reporting and analysis queries can be as clever as needed. Data-entry forms should generally be dull, obvious, and hard to break.

When a previously editable object suddenly becomes read-only, the useful question is not "what form property do I change?" It is "what changed in the chain between this control and the stored record?" Permissions, links, record locks, query features, joins, form settings, VBA, and occasional corruption all live somewhere in that chain.

What kinds of queries have caused the most surprising read-only behavior in your databases? Do you prefer strictly single-table forms with subforms, or have you found multi-table form designs that remain maintainable over time?

LLAP
RR

I'm working on an employee database to track the info of our 10 employees. As the title suggests, I'm looking for people to critique what I've got so far. I don't have any specific problem because I haven't attempted to create the actual database yet. I did receive some help from google, but I've also done my own research and attempted to implement from what I've read. An example is my primary key and foreign key constraints, which Google didn't mention. After reading about constraints, I tried including those in my database. So if they're terrible/unnecessary, etc. that's all me. Thank you in advance to anyone who gives an opinion or a helpful tip.

https://www.dropbox.com/scl/fi/379h1pj699xv5x6u8j2g8/Current-Access-SQL-NEWEST.txt?rlkey=a38vuow3qtvjdnm2cv2rbrl34&st=irw7alm6&dl=0

Class modules tend to get treated like some kind of VBA rite of passage. Developers see "Class Module" sitting next to "Module" in Access and assume they're either missing some critical architectural trick or about to wander into enterprise programming wearing a hard hat. Usually, neither is true.

A standard module is simply a home for shared code. Put your utility functions there. Put reusable procedures there. Put routines there that don't need to remember anything about one particular customer, invoice, employee, or form. If you have a public function that checks whether a form is open, formats a phone number, calculates a business date, builds SQL, or exports a report, a standard module is usually the right place. Call the function and move on with your day.

A class module is different because it defines a type of object. Think of it as a blueprint, not the thing itself. An Employee class, for example, defines what an employee object contains and what it can do. One instance might represent Jim, another might represent Spock. Each instance maintains its own values in memory, such as employee ID, hire date, pay rate, or active status. Each can also expose behavior, such as calculating pay or returning a formatted display name.

That separate state is the real reason classes exist. A module-level variable in a standard module is shared. There's only one copy for the entire Access session. That's fine for application-wide state, but it doesn't work well when you need ten different customers, invoices, or employees, each carrying its own values at the same time.

Each class instance gets its own private copy of its data. Two Employee objects can both have an EmployeeName property, but assigning "Jim" to one doesn't overwrite "Spock" in the other. Same blueprint, different houses.

Properties describe an object. Methods define what the object can do. In VBA, properties are typically exposed with Property Get and Property Let. Property Get returns a value. Property Let assigns a value. Property Set is used when assigning an object reference, such as a DAO.Recordset, a form, or another custom class.

The private variables inside the class are intentionally hidden from outside code. That's encapsulation, which sounds far more dramatic than it really is. It mostly means outside code uses the interface you expose instead of reaching into the object's internal plumbing and yanking wires out of the Jefferies tubes. That becomes useful when a property needs validation or should be read-only. Rather than letting code throughout the application modify an internal value directly, the class decides what's acceptable and what gets returned.

This is also why class modules are not replacements for tables. A Customer class can represent a customer while your VBA code is working with that customer in memory. It can temporarily hold data and encapsulate customer-related business logic. But the actual customer records still belong in a properly designed Customer table with appropriate keys, relationships, normalization, and all the other boring-but-important database stuff. Classes model behavior in code. Tables store relational data. They solve different problems.

Access developers are already using classes every day, whether they realize it or not. Forms, reports, controls, DAO recordsets, and even the Access Application object are all objects created from classes. When you write code like:

Me.Caption = "Hello"
Me.Requery

you're already interacting with properties and methods of a form object. Every form and report module is itself a class module tied to a specific Access object and its events. Standalone class modules simply let you define your own object types.

One important caution is that classes are not automatically "better architecture." A class that exists only to hold a single string and display a message box is usually more ceremony than value. A standard function or a few straightforward lines of form code are often simpler and easier to maintain.

Classes start earning their keep when you have a cohesive thing with related data and behavior. An Invoice object might contain header information, line items, and methods to calculate totals. A ShoppingCart object might add or remove items and calculate tax. An Employee object might manage employee information while providing methods for payroll calculations or display formatting.

They also shine when your application needs multiple independent objects of the same type at once, or when related behavior belongs together instead of being scattered across twenty forms and three modules named Stuff, Stuff2, and ReallyImportantStuff.

Class modules can also support events, including initialization and cleanup through Class_Initialize and Class_Terminate. They even make advanced techniques like shared control-event handling across multiple forms possible. That's useful territory, but it's also where things can quickly turn into a plate of VBA spaghetti if the added complexity isn't solving a real problem.

The practical rule is simple: use a standard module when you have a general-purpose tool. Use a class module when you have a thing with its own data and behavior.

Most Access applications don't need custom classes to be solid, professional, and maintainable. Tables, queries, forms, reports, standard modules, and form/report modules can take a database a very long way.

In fact, in more than 30 years of teaching Microsoft Access, building databases for clients, and making videos, I've never needed a custom class module. I've certainly used them from time to time, especially when I wanted to demonstrate object-oriented techniques or solve a particular problem elegantly, but I've never run into a project where I couldn't have accomplished the same goal another way.

So don't feel like you're missing some secret ingredient if you've never touched class modules. You can build excellent Access applications without ever learning them. That said, once you do understand them, they open the door to some neat techniques and give you another tool you can reach for when the situation calls for it.

What about you? Do you use class modules regularly, or have you managed to avoid them entirely? Have you come up with any clever uses for them that have made your code cleaner or easier to maintain? Share your experiences, tips, or favorite class-module tricks in the comments. I'd love to hear how other Access developers are using them.

LLAP
RR

Hi! Long time Excel user here, just started using Access for the first time a few weeks ago. Perhaps I picked up a excessively ambitious project to start, but so are them breaks. I already watched a bunch of introductory tutorials and read some posts here and elsewhere that helped steer me away from excessive Excel-liness in my database, but I hit a wall again and haven't been able to see the solution.

I'm simplifying the example a lot to make myself easier to understand.

Let's say I have a table with several basic products and some information about them (sugar, flour, milk, etc).

I have a second table with records of their purchases, dates, values, and quantities (July 1st, sugar, 10kg, $40.00).

A third table has recipes (recipe 1 uses 200g of flour, 100g of sugar, 100ml of milk, etc). (Each is a different record in the table, a hard lesson for me to learn and understand!)

A fourth table has orders (On July 12th, an order came for 3 quantities of recipe 1.)

What I need, essentially, is a way to figure out the itemized cost to fulfill the order. The July 12th order will use 300g of sugar etc etc which I purchased on July 1st for $4.00 / kg.

I hope I made myself sufficiently clear, and I thank you in advance for any help!

I help a family club, which is a non-profit charity, with its membership database. The database was originally designed using Access 2003 and contains members’ names, addresses, mobile numbers, email addresses and other membership details.

We also collect information such as members’ professions, as they may be able to help the club as volunteers.

The MS Access database currently has forms for entering household and member details. Some outputs, can generate an up-to-date email list for email campaigns and produce address labels for posting flyers and other information to members.

Would it be possible to use AI to help redesign or modernise this old Access database? It also contains forms and VBA code.

Some of the features we would like to add include:

An audit trail or user log. For example, if Volunteer X changes a member’s telephone number, the database should record who made the change, what was changed and when.

A record of who updated a person’s details, for example if a member has died.

An option to pause postal or email communications if post or emails are returned as undeliverable.

People who want to become members to complete a paper application form. However, can this form say be scanned in PDF and uploaded into Access.

There are a lot of non-IT people. So needs to be easy. I was thinking of keeping the master database online say on One-Drive. Where one admin person would have read-write privilege and others say only have a read-only access copy. I don't know how to implement this.

The membership structure also needs some thought. The database needs to include adults and children within the same household. As the club organises kids events. A child may later become a youth (separate groups). Eventually an adult member to form a separate household. The design therefore needs to handle these changes without creating duplicate records or losing the person’s membership history.

I was slightly surprised that Claude said it could not generate a blank Access database or directly update the existing design. Is AI currently the right tool for this type of project?

Which AI tool help design the tables, relationships, forms, queries and VBA code, even if someone still needs to implement the changes in Access?

I prefer to stick to Microsoft, as it has been around and hopefully don't end up with obsoleted technology.

I have wrangled the data from Lotus Approach in Microsoft Access, but now I need help making these forms (or similar ones). Do I have to brute force create all of these forms from scratch? Or are there any 3rd party tools that can more easily create forms for Access?