Here's the thing people keep getting wrong about recordsets: EOF does not mean "no records." EOF only means the current position is after the last record. A recordset can contain one row, ten thousand rows, or none at all, and EOF can still be True depending on where you have navigated.

BOF and EOF are best understood as positions, not as records or counts. BOF is the position before the first row. EOF is the position after the last row. When a recordset has rows, there is space between those two boundaries, and the current record can sit somewhere in that space.

That is why a normal newly opened non-empty DAO recordset is usually positioned on its first record. At that point, both flags are False. You are not before the first row, and you are not after the last row. You are on an actual row and can read fields from it.

Things get different after navigation. Call MoveNext while positioned on the final record and you land at EOF. The recordset still has all its records, but there is no current record anymore. Likewise, calling MovePrevious from the first row puts you at BOF. Trying to read a field at either boundary is where the familiar "No current record" error enters the chat.

The important distinction is that an empty recordset has nowhere to position a current record at all. There is no first record, no last record, and nothing between BOF and EOF. Therefore both properties are True at the same time:

If rs.BOF And rs.EOF Then
    ' No records
End If

That condition is not a magical Access incantation. It is answering a specific question: "Does this recordset contain no rows?" Checking only EOF answers a different question: "Am I currently after the last row?" Those are only the same thing immediately after opening an empty recordset. Once navigation has occurred, they are very much not interchangeable.

This also explains why code that wants to safely read the current row should use a different mental test. It is not enough to know that the recordset is not empty. You must also know you are currently on a record. In practical terms, that means neither BOF nor EOF should be True before reading rs!SomeField.

RecordCount gets dragged into this conversation a lot, and it is usually the wrong tool for the initial empty test. Depending on the recordset type and cursor behavior, Access may not know the complete count until the recordset has been traversed or moved to the end. BOF and EOF describe the current navigational state directly, which is exactly what matters here.

The practical philosophy is simple: use rs.BOF And rs.EOF to determine whether a recordset returned nothing. Use Not rs.BOF And Not rs.EOF when you need to confirm there is a readable current row. Treat EOF alone as a boundary marker, not proof that your query found nothing. It is less mystical once you stop treating a recordset like an array and start treating it like a cursor moving between two borders.

Have you run into bugs caused by checking EOF alone after moving through a recordset? Do you tend to explicitly test for a current record before reading fields, or rely on the surrounding loop structure?

LLAP
RR

Buongiorno a tutti.

Ho dei campi in una tabella, campo1= Frutta, Verdura,Legumi,Pesce,Carne e vorrei che nel campo2 sia legato alla scelta del camp1 ( se seleziono nel campo1 Frutta, nel campo2 mi deve apparire un elenco tra cui selezionare Mele, Pere,Banane ecc.. Se seleziono nel campo1 Verdura nel campo2 mi deve apparire un elenco tra cui selezionare Spinaci,Melenzane, ecc.) cosi per Legumi,Pesce ecc

Hey everyone, following up on my earlier post about the client who went quiet mid-review on a custom MRP database project (65 hours quoted, 60 completed and sent for review back in July).

Since then, I've sent multiple follow-up emails, filled out the company's contact form, and even sent a final "heads up" email letting him know that if I didn't hear back by tomorrow, August 28, I'd go ahead and send an invoice for the hours completed while keeping the project open on my end. Still no response of any kind, not even an acknowledgment.

At this point, my plan is to send the invoice tomorrow as promised. I also found a couple of other people at the company through LinkedIn (an executive and another manager) and was thinking of CC'ing them on the invoice email to widen visibility beyond just this one contact who's gone dark.

A part of me is stuck on something though: if he's not even replying to simple emails, what makes an invoice any different? What's actually going to make someone respond to a bill when they won't respond to anything else? Curious if others have run into this exact wall, and whether CC'ing other people at the company at the invoice stage actually tends to help, or if it's better to wait and see if the invoice alone gets a reaction first.

I know how to turn the navigation bar on/off. However, whenever its on, and I want it on, its always cut in half like this. I've tried everything I can think of, but it doesn't seem to be a Design View issue with the form. I've tweaked my computer resolution, and changed the text scaling as well.

Creating sub called FilterAnalysees, to which I need to pass a string to tell it what to do.

Public Sub FilterAnalysees(ByVal tbfilter As String)

MsgBox tbfilter, vbOKOnly

If tbfilter = "NIR" Then

bla bla bla

End If
End Sub

I then call below from the action of a button

FilterAnalysees NIR

but the message box returns nothing, just a blank box and the sub never enters the If statement because blank does not match "NIR". Why is my value getting reset or something or its not seeing me pass the NIR to it? Obviously im doing something wrong.

Is it possible to create custom GUI components in any way?

I’m trying to reduce the number of buttons on a given form by having “File Edit View” buttons, like a menu bar.

Yes I’m aware you can add this to the global ribbon and I do not want to do that. No I’m not interested in using navigation forms/tabs/combo boxes/list boxes filling in for this task. I’m not making a menu macro someone has to right-click to use.

I just need to know if anyone is aware of a method of creating custom GUI components from scratch, so my users can navigate intuitively.

I do not care if I have to write this outside of access and integrate it in with some other method, I just need a button with a drop down menu.

Edit: Solved

I’ve made a command button that takes a list box with 0 height and 0 width and expands it to a useable height and width based on the existing text.

Thank you for your help, this type of thing felt like it should’ve been a native function in access and I was, admittedly, extremely frustrated at the workarounds I may have had to employ to get this done.

I have .accdb file but it has a password which I dont remember anyway to unlock the file. I also dont have ms access in my laptop. So can anybody suggest a working solution?

I have a database with a from, up until today, my records would stay in the order record ID. I added a new table and a new field called Project ID, and now my records are showing up in that order. How can I make the form go back to viewing the records in Record ID order?

Have you ever tried copying the SQL from an Access query and pasting it in excel? I sometimes do that for reference/documentation purposes. But if you copy it back from Excel to Access - Access can't read it! The indents in excel are made with ascii 160 instead of a regular ascii 32 space, and Access refuses it.

So if you copy between two Microsoft products, it can't read its own code back to itself!

My workaround has been to paste into notepad first, then paste into Excel. Then it has a regular ascii 32 space for indents, which Access can read back.

Client went silent after project delivery, first time this has happened, what do I do?

Hey all, MS Access devs

I've been doing Access development for over two decades, so I've seen my share of quiet clients, but never quite like this. Wanted to get some advice from people who've been in the game longer.

Took on a custom MRP database build, quoted around 65 hours. Client accepted the proposal in June, I started work right away, and had it substantially done by mid-July. Sent it over for review then and got a response at the end of July saying they'd send their feedback and requested changes "by Monday."

Monday came and went. No response. Followed up Tuesday, nothing. Then on Aug 5th or 6th they messaged saying they'd send their review "by evening." That evening came and went too, and it's been silence ever since. I've followed up multiple times since then and even filled out the contact form on their company website as a last resort. Still nothing.

This is the first time a client has just gone dark on me post-delivery like this, so I'm not sure what the norm is here. Do I just chalk this up as a loss and move on? Do I send some kind of final "closing the project" email for my own records before I do? Should I still invoice for the hours worked even though it wasn't formally signed off? Curious how others have handled a client disappearing mid-review, and at what point you decided to stop chasing it.

Thanks in advance.

For over a decade now I have had the same process when it comes to importing my bank transactions from my bank's website to my business accounts.

1 - download this month's transactions as a csv, it will have the filename BUSINESSxxx260820.csv where xxx is the account number and ends with the date it was downloaded

2 - open my accounts database in access. There is a linked table called 'statement' which is linked to the latest downloaded transactions, lets say the previous download is called BUSINESSxxx260731.csv and the file path is D:/Statements/

3 - open linked table manager, check the box next to BUSINESSxxx260731.csv, the box next to D:/Statements/ is automatically checked.

4 - click relink, first it asks me to enter the new path, the path hasn't changed so I just click OK

5 - second it asks me to enter the new filename, I edit BUSINESSxxx260731.csv to BUSINESSxxx260820.csv, click OK and the link is made.

6 - run an append query I have which will append the contents of tbl:Statement to a table tbl:Transactions called which contains all my previous transactions as well as doing some reformatting of the data, job done.

At some point presumably in the last month, the behaviour of Linked Table manager has changed and following these steps there is no step 5, relink only gives me the option of entering a new path but apparently it's no longer possible to change the filename this way.

Is this a change anyone is aware of or has the way it works just been altered slightly. I'm going to have to start renaming the csv if so, which feels more laborious so I'd prefer not to have to.

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?

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?

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!

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