Access 2016: Updating a value in a form based on input

Question

I, am admittedly, not well versed with SQL, VBA, or Access in general, aside from what I learned in a college course I failed to complete nearly 15 years ago.

I've built a database for my employer (I'm a tradesman, who was injured on the job, and learning this in order to be able to continue working and being productive with the company) and have gotten many wrinkles ironed out, and learned a lot on the way.

Now, I'm trying to streamline things, and make processes better. I've learned to update row sources based on the value of other fields in a form, so that my combo boxes only show relevant options, based on other fields.

Now, much of the information I put in is repeated, frequently. The example being, majority of Jobsites (Listed in the form as "Location") will have multiple objects per site, that can't be helped.

In the interest of streamlining the data entry (as the company is in multiple cities, and I'll have to do this in other cities as well), I'd like to make the "Assignee" Field update when you chose a location.

There is a table called "Locations" where the options for the location combo here are stored. Each location also has a "foreman" field, linked as the primary key for a "Users" table. All of these values are bound as their numerical primary key index, and just set to display their names for the sake of readability.

What I'm trying to do, is upon updating (selecting) the location in the form combo box, automatically update the record "Assignee" value to whatever Foreman is assigned to that jobsite. Even if I can only streamline a 6 second part of the data entry, thats hours over the course of thousands of assets in the company over time.

"Foreman" is also the Primary Key index from the Users table (so Number values).

I don't even know where to being to look to learn how to accomplish this. And unfortunately during covid, there isn't any annex courses or post secondary courses in community college available to attend in my area.

I've watched a large number of tutorial videos, but they just repeat the same information, and don't go into specifics concerning this.

I don't know if what I want to do is the result of a SQL Update, or from VBA Coding. I haven't really much experience with either.

Any help or direction to learning resources is greatly appreciated.

-A desperate tradesman in way over his head.

Answer 1

First, I want to let you know that one of the finest Access developers I know started out working on the shop floor, moved into  supervisor role, and went on to learn Access as part of that desk job. Years later, he ended up founding his own development company! So there's precedent for what you are doing.

That said, the secret here is probably going to be to get a better picutre of ALL of the tables involved and the relationships between them. It sounds to me like there could be a flaw here in one or more of the tables.

"What I'm trying to do, is upon updating (selecting) the location in the form combo box, automatically update the record "Assignee" value to whatever Foreman is assigned to that jobsite. "

If that means what I THINK it means, you should NOT even have an Assignee value in addition to the Location value in that table. Why, because you already have that relationship identified in the Location table. You should NOT duplicate that again in the other table.

But, to be sure, we should see a screenshot of the relationships in this accdb to make sure our assumptions match. We can offer suggestions better after we have a better understanding of the overall table design.

Answer 2

Thank you for taking the time to reply to me.

My aim to keep an entire users table seperate from locations, is that users can fill several roles in this database. And some foreman cover multiple jobsites. By making them their own table, I could make them a row source for the drop down in location to select a foreman. I'm trying to restrict manual typing and editing as little as possible, as the expectation will be that other users aside from myself will eventually use this database, and there's a lot of room for error with so many users.

I won't go into depth as to why we're trying to accomplish tool tracking this way, but they won't spend the money for professional development, or for professional third party software (the program we were looking at was quoted at nearly 6k a year)

So, Here I am, way over my head, struggling to learn SQL and Access on my own.

Answer 3

I don't see "Assignee" anywhere.

That said, I do see Region in both the User table and the Location table. Is a User assigned to more than one Region?

Answer 4

First question is whether a Location will have one and only one foreman? If the Foreman is unique to a location then foreman should NOT be in your Assets table. You can display the Foreman via a link through the location table.

If you want to default to the Foreman but allow the ability to select a different person as Foreman, that's a different matter. Or if you want to freeze the Foreman at the time the asset record is created that's also a different story.

So let us know why you need to include the Foreman with the asset instead of the Location and we can help further.

As an aside, I would not leave the default names of the autonumber fields as ID. I always name my PKs as tablenameID and give the same name to the corresponding foreign key. This makes it easy to identify the source of the key fields and the relationships.