Time to get serious

[CraigBaker]

Hello all.

I'm Craig. I'm in Adelaide, Australia so I might be in here at different times to most people. We find that most international events are at awkward times for us.

I inherited a partially implemented Access system about six months ago, and I'm starting to get on top of things. I've been googling all over the place, and I'm now pretty sure that the best information I get is from this site. I want to read in detail many of the excellent posts I've seen.

I suspect I'll be busy reading until I get a bit more knowledge, but I might join the odd conversation. Already, my reading has shown me that the site is supposedly more about "hobbyist" users, or "power users". That is not the impression I have gotten, and now that I'm looking in more detail, I still think this looks like a top notch site. Modesty is a highly under rated trait, so I'll just put that as another tick for the site.

I have 20 or so years experience in software development, with minor exposure to Access, and an average to higher than average knowledge of SQL and relational database systems. The main challenge so far is putting it all together in the front of my mind, while also coming to grips with the existing system. It was written by a manager (power user?) who left the company a month or two after they started using it, and I'm discovering that it was never really tested. Overall, it is quite well written, but there are parts that just don't work at all, as you can imagine for something that had virtually no testing. I've never worked at this company before, so I'm also trying to understand the culture.

It's been very enjoyable so far, but I'm impatient by nature, so I want to make better progress!

I'm sure this site will help me surge ahead, even if I don't trouble you with direct questions.

Thanks in advance,
Craig

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[The_Doc_Man]

Hello, Craig, and welcome to the site. We actually have a couple of ozzies in the mix here, so you aren't as alone as you think. I'm constantly behind the scenes here myself because I'm in a time zone normally +6 from Greenwich, in New Orleans Louisiana USA. As long as you recognize that there might be a little time lag between posting and getting an answer, you'll be fine.

 

[theDBguy]

Hi. Welcome to AWF!

 

[NauticalGent]

Welcome aboard!

 

[jdraw]

Hi Craig. Welcome to AWF.The site has a broad range of users and experiences. I'm sure you'll get answers/advice if you pose any questions clearly. The players are all volunteers and will assist where possible.

 

[Pat Hartman]

Welcome. If you can deal with the time lag, you should get the help you need. Since you are experienced in another platform let me give you my experienced developer lecture.

Access is a Rapid Application Development (RAD) tool. That means that it does a lot of stuff for you behind the scenes. I recommend that you try to understand the "Access way" rather than trying to impose your will on Access. You will be far less frustrated and far more successful more quickly and end up with a lot less work. It's hard for experienced developers to not just write code to solve problems but my suggestion is - to get Access to do something:
1. look for a control or form property setting
2. Use an action query (always faster than VBA loops)
3. Find an appropriate VBA function. Look for the list of functions by category. It is easier to work with. All the "date" functions will be together and you don't have to try to figure out what the name might be.
4. Write code. The vast majority of my code ends up being validation code. But there is always other code to tweek the interface or to run batch functions.

I too came to Access with more than 20 years as a developer and the hardest thing for me to get my head around was the event model. Forms and Reports are objects and they have properties and methods (events). They can be bound (have a specified source of data) or unbound (not be connected to a table or query). Binding a form to a querydef or table or SQL string allows Access to handle the data. It handles fetching and updating and populating all the bound controls on the form/report. This is the normal "Access way". There are reasons to use unbound forms but they require that YOU write all the code to handle the fetching/updating/control filling etc. so they are not for the faint of heart or newcomers. Forms and Reports have event procedures where you can attach code or macros. I strongly suggest that you use VBA rather than macros and if the original developer used macros, I would convert the macros to VBA. There is a tool on the ribbon to do this for you. These event procedures are "hooks" that the MS Access team gave us so that we have the ability to interact with the form/report at certain points in time.

In my opinion, the most important event of all is the form's BeforeUpdate event. It is the flapper at the bottom of a funnel. If the flapper is open, the record gets saved (assuming it doesn't violate any rules applied at the table design level). If the flapper is closed, the record does not get saved. Access takes it as a personal mission to save all data. This decisions was probably made because the product needs to support unsophisticated users who don't understand how databases work. So, with Access, YOUR job is to add validation code to the form's BeforeUpdate event (or possibly individual control BeforeUpdate events) to ensure that bad data doesn't get saved. Hence the title "Bad data is bad for business)

For example, with forms we have these form level events that fire when you open a form:
1. Open (runs once)
2. Load (fetch the bound data) (runs once)
3. Resize (make the form size adjust) - optional (runs any time the form is resized manually)
4. Activate (set focus to the form) (runs when the form gets the focus from some other place in the application)
5. Current (set focus on the first displayed record) (runs each time a record changes. So if you scroll to the next record or a new, empty record, the Current event runs and that record becomes the focus of ALL the forms code)

So knowing what triggers an event should give you a clue as to what types of code you would put there.
1. Open, you can check the user's security and lock or unlock form controls to allow or prevent editing, adding, deleting, viewing.
2. Load, If you are using your own scroll buttons rather than the built in ones, this lets you populate the 1 of x value.
3. Never found a use for this.
4. I think I've used this once
5. Since a specific record is being displayed, you can use this to alter the lock status. for example, if a record is "closed", you can lock it.

This is a picture of an app I built so I could create a training video regarding form and control level events. This picture is showing the logged events in the background form and in the foreground form we have a main form with a subform. As you can see the events are listed in inverse order so the last event that runs is on the top. This makes it easier to follow the logic if you are doing multiple tests. Also note that there are four additional events listed and the form name is that of the subform with a pink background. So for forms with subforms, the subforms load and get populated first. I can't say why the subform loads first but the original team had a reason. It probably makes the flow behind the scenes simpler.

There are three videos plus the sample database here:

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

If you have the time, watch at least one of the videos so you get a sense of how the app works. Then you can play with the forms that are used as examples. I wouldn't mess with the form that displays the events. That is intended to stay in the background and display events in real time as you cause them to fire by doing things in the app.

I've posted over a dozen sample databases that help you to understand various concepts. Just look in the database samples section.

If you want us to help with the clean up, we can start with the schema if you have questions with that and then move on to specifics.