AccessDev.NET

Microsoft Access Developer Network

Welcome to the Access Developer Network. If you are looking for someone to help you with your Microsoft Access database issues, troubleshoot problems, or even build a database for you, then you have come to the right place. For complete information on the Access Developer Network, please click here for details and instructions for posting.

Want to see Testimonials from other clients that have found Access help here in the Developer Network? Click here.

Developer Students: if you're interested in picking up some consulting work, subscribe to this forum and receive an email update whenever new posts are added. You will also be notified by email if any conversations you are involved in receive new comments.

Consultation required Upload Images Link
Jim Baldner
2 days ago

Hello developers,

I have a time sensitive project, Im looking for some help with. Specifically a form to assign tickets to invoices and reconcile same. Another to assign services to tickets in a granular sense. I have a few requirements for these forms that require a higher level of knowledge than I process. I will no doubt have other requests in the near future as well. Im certainly willing to reimburse you for your time. Read More...

Adam Schwanz
40 hours ago

Hello Jim, you can email me at aschwanz@hotmail.com to talk more about what you're trying to achieve and costs.

Project Upload Images Link
Shreyans Ghia
3 days ago

Hello
I have built a Purchase Database based on what i have learnt from Richard's various videos. It seems to work quite ok. However I would like have some professional help for the following:
1. Fix some minor aspects of my database which I am just not able to figure out. Read More...

Adam Schwanz
3 days ago

We'll have Richard or Alex move it to the AccessDev.NET

I'll shoot you an e-mail to discuss your needs and pricing.

Property Management Database Upload Images Link
Stephanie Brandow
7 days ago

I am in need of a consultant to build a property management database to be used by my company. We manage around 500 properties with both long and short term rentals. We are located in Florida and having someone close would be a plus. I already have the specs in mind.

Adam Schwanz
7 days ago

Im nowhere near florida but could built it all remotely. If you want to email me at aschwanz@hotmail.com to discuss needs and cost

Richard Rost
6 days ago

Hi Stephanie. I actually AM in Florida - not too far from you, in fact. About an hour drive south in Fort Myers. However I don't do custom consulting work anymore. Adam is a great developer, however, and I'm sure he'll take care of you.

I've been thinking about putting together a property management database template. I think it would be something that would sell well. However, my plate is VERY full for the next couple of months, so if you need it in a hurry, definitely work with Adam.

Show 1 More Reply...

Absence Tracking Upload Images Link
Eric Wallace
15 days ago

I am working on an HR database application. We track time in 3 categories: PTO, sick and leave. Sick time and leave time are based on the calendar year. PTO however, is tracked by service date. Currently my design includes an employee table with relationship to the absence table that contains fields for employee, occurrence date, category and amount of time. I have a single form set up for entering occurrences. My goal is to set up a single form to display an individual employees occurrences with balances remaining. I am trying to build a table using a query to produce the appropriate start date for tracking PTO. Meaning, if today is 9/8/2021 and they reset 11/20/2021, the query needs to set the PTOreset date to 11/20/2020. I have tried DateAdd with my best guesses at an IIf statement in the query but, it either changes all years or leaves the fields blank. Any help or suggestions for another way are welcome. If I can get that piece right I think Ive learned enough to setup the queries for the end result form with totals using criteria but I just cant figure out the date problem. Any suggestions are welcome. Read More...

Alex Hedley
15 days ago

Can you share what you've tried code wise?

Adam Schwanz
15 days ago

What are you trying to have happen? How does service date affect the PTO reset date? I'm assuming you store a "start date" in the employee table? What's stopping you from using that or is there some other strange calculation that you can't just use an if statement to pick the current year if todays date is before the start date or current year+1 (next year) if todays date is after the start date?

Show 6 More Replies...

Eric Wallace
15 days ago

Yes, I have a start date in the employee table and that is the date I am trying to manipulate. PTO balances reset to their starting amount on their start date and I am working towards taking the start date and changing it to the proper PTO reset date. In the example today being 9/8/2021 and their start date was 11/20/1997, I want the query to take 11/20/1997 and change it to 11/20/2020 as that was the last reset of PTO. I have tried several things like formatting the start date to current year as [ServiceDate] in a new field and then tried something like this: PTOResetDate: IIf([ServiceDate]>=Date(), DateAdd([ServiceDate], "yyyy"-1). I had planned to run this as a make table query to then use the PTO reset date as criteria on the single form with calculations to keep running totals of use/balances remaining. I am probably overcomplicating this but, hopefully the elaboration makes sense...

Adam Schwanz
15 days ago

Thats almost certainly not the cleanest way to do it, but it works

Adam Schwanz
15 days ago

That's their last reset date, which I think is what you're wanting?

Eric Wallace
14 days ago

Thanks Adam! That looks to be exactly what I was trying to get to.

New Database Help Upload Images Link
Ray Marotta
18 days ago

Hi, Im trying to build a fairly elaborate database from scratch that I currently have in Azure DevOps and Smartsheets. We have a lot of external users, which is why we use Smartsheets. I would like to find someone that is knowledgeable in online databases (which I know are complicated) to answer my initial questions to see if Access can do exactly what I need it to do. Thank you!

Adam Schwanz
18 days ago

What is your initial question? SQL server can probably do it.

Ray Marotta
18 days ago

Hi Adam, thanks for your response!

Several questions:
1. Can I make it so anyone vis web link can add data to my tables via a link to a form entry? From what I read, SharePoint is not the way to go for public data entry/reports.
2. Can I make reports accessible to the public vis web link?
3. I am all about learning, trying, filing and doing the dirty work, but is there someone that help me with the initial framework so I don't waste time setting up something the wring way initially?
4. Is Power Bi best for dashboards?

Thats the big ones. Thanks Adam

Show 8 More Replies...

Adam Schwanz
17 days ago

I am not a SQL server expert I've just played with it enough for my own needs. Just to verify though, you're not looking for a remote user to use access to enter data, you want them to do it through a web interface? I think this sounds like an ASP thing.

Let me try to send out the bat signal here to the boss. HEY RICK, WHAT DO YOU THINK?

Ray Marotta
16 days ago

Hi Adam, i'm not looking for someone that is completely outside of my company to be able to add data to the database. In Smartsheets, anyone can enter data with just a link. Here is an example: [LINK REMOVED]
But they do not have access to the database. What im trying to do is have people be able to enter data through forms without being a user and also see reports without being a user. I hope that makes sense.

Richard Rost
16 days ago

Hi Ray. Yes, Adam is correct. Everything you need to do can be accomplished with a combination of Access, Hosted SQL Server, and ASP for the web interface. Sharepoint is not the way to go unless you've got a small team of trusted users. You can make reports public, of course. The page you're reading and interacting with right now is built on the ASP with SQL Server. I practice what I preach, and I've got courses that show most of it. As far as PowerBI goes, I'm not in a position to answer that question as I don't consider myself an expert in it... yet. However there isn't a dashboard yet that I haven't been able to construct using Access or Excel.

Richard Rost
16 days ago

Good bat signal, BTW. I check in a couple of times a day to APPROVE posts, but I don't always have the time to answer them. But that got my attention. I stopped recording a video for that! LOL

And yes, Ray, my site doesn't allow external links. You can post screen shots if you want using the link at the top of the thread.

Ray Marotta
16 days ago

This is great! Thank you to you both! I will definitely need help on the Hosted SQL Server and ASP. I'm assuming that's pretty difficult. Can you refer me to the courses that cover that? Also, is there a student that may have more experience thank other that could help me? I appreciate both of you.

Richard Rost
16 days ago

I already linked you to the relevant courses. I would finish the beginner and expert courses you've already purchased so you have a solid foundation of Access first, then move up to the Hosted SQL Server seminar. That will get your database online and show you the basics of ASP. Then if you want more ASP, you can go from there. As far as the students go... I'll leave that up to them to respond. :)

Adam Schwanz
16 days ago

I'm usually quick to jump on these, but not this one. I'm confident enough to bounce ideas off of maybe if you want to post here or email me directly (aschwanz@hotmail.com) but I'm not confident enough with ASP to tell you what to do. I've been wanting to get more into ASP for a while now and been putting it off, maybe a good excuse to look through it again.

So I may be able to help some, but not promising anything ;)

If another dev is more comfortable with ASP and wants to help him feel free to post.

Alex Hedley
16 days ago

There's also the ASP Forum to ask questions. Happy to help there, when I've got time.

Vendor Database Upload Images Link
Jim Baldner
18 days ago

Hello folks, I'm looking for sum "some" assistance with the following, pictures to follow.

I have a main form "VendorActive" with three seperate subforms each displayed as continuos, "subTicket", subTicketDetail", and "subService". The three subs are independent of each other. I have specifically chosen not to nest the subforms in a hierarchy within the main form. the subs are linked to the main. Read More...

Jim Baldner
18 days ago

Show 6 More Replies...

Jim Baldner
18 days ago

Richard Rost
18 days ago

So you're saying that you want to start with a COMPLETE list of services over to the right, and you want to pick one and have it added to the details subform on the left which means it's then assigned to that ticket? Then once it's assigned it disappears from the list on the right? Do I understand you correctly?

Jim Baldner
18 days ago

Thx for your reply Richard,

Absolutely, one other tidbit is that currently, the service list is filtered by vendorActiveID, ensuring the list is only populated with services associated with each particular vendor.

Richard Rost
18 days ago

Since you're a Gold Member, I'll put together a simplified version of this as a TechHelp video, since I haven't done anything like this before. However, if you want one of my Developers to actually build it into YOUR database, then I'll leave that up to them. :)

Jim Baldner
17 days ago

Voila - Filtered the "Services" form using NOT with the IN Condition...
Case closed... :)

Richard Rost
16 days ago

There you go. See, doesn't it feel great to figure out the solution yourself?

MS Access Sharing Forms Upload Images Link
Kirsten Allen
20 days ago

I have built a db with 12 tables with relationship mapping. I have built several associated forms & queries.

Ideally, I would like for my team members to only have access to the forms & queries without the tables. We already have a sharepoint site. I am registered for the MS Access Sharepoint seminar, according to the seminar I need to migrate the tables to sharepoint, but want to make sure I am on the right track before I actually do this migration. Read More...

Scott Axton
19 days ago

Kirsten -
Disclaimer:  I have not ever used Sharepoint.  I don't know the ins and outs of it.

That said:
Microsoft announced quite some time ago that the were depreciating Access and Sharepoint for Microsoft 365 / Access 2019.
Sharepoint Roadmap Article

For that reason I quit going down that branch of my learning path.

So....
Can you describe your organization and it's needs?  What are your requirements based on ?

For instance, if you are all in the same building on the same LAN,  I would suggest differing alternatives than if you are in multiple buildings or even different cities wanting to access your database via web browser over the internet.  (The latter is way beyond my skill set at the moment by the way)

How many people are currently accessing the db.  What does the potential user needs look like in the next 5 years?

Scott Axton
19 days ago

Last but not least. If you were wanting to hire a developer to help you make this move. This is the correct spot to place this question.

If you were asking questions prior to doing the work yourself, I will ask Richard or Alex to help us move this post to the appropriate forum.

Show 5 More Replies...

Alex Hedley
19 days ago

@Scott Access still works with SharePoint, it's "Access-based web apps and Access web databases" that aren't supported anymore. They are quite different.

Scott Axton
19 days ago

Well I learned something today. Thanks Alex.
Can you help me understand the difference then? Or point me in the right direction.

Alex Hedley
19 days ago

MS were looking to make a new web app for Access, but with macro security and no vba in browser it didn't quite work out, not much up take either
They have started porting Excel macros/vba to Typescript so if it works well maybe they'll look back at it again, but I doubt it

Richard Rost
18 days ago

Alex: thanks for the correction

Scott: yeah, Access Web Apps were horrible. I played with them for a bit and immediately hated them.

Kirsten: don't worry. Access and SharePoint will continue to work just fine together for the foreseeable future. And yes, you can bring your data back down from SharePoint and put it back in Access if you want. It's not as easy as moving UP to SharePoint, but it's still doable. I would keep copies of your old Access tables around just in case. It's easier to import the data into existing tables than to recreate the tables from scratch again.

Kirsten Allen
16 days ago

Hi everyone - Thanks so much for the inputs and advice.

To answer Scott's question above - I am planning to do the migration work myself as much as possible.... however, I would be willing to pay for a short consulting session or two to ensure I'm on the right track.  I really need this to work for sharing with the team (particularly the forms & queries), but I also really don't need to muck this up.

Richard - Thank you so much for the additional inputs.  I will definitely keep a backup copy of the original tables.  It's good to know that we could bring back the data into Access if need be.... but that importing the data is better than re-creating.  Awesome tip.  I'll keep watching the remaining videos in the Sharepoint seminar, as some of my question might already be answered there.

Database design help Upload Images Link
Paul Sikkila
37 days ago

Im looking for help setting up an Access database for house construction inspection checklists. Each checklist would contain a list of inspection points with a pass/fail/NA/note entry for each item on a list. There could be 100 or more categories (framing, roofing, trim, etc.), therefore many checklists (tables), resulting in MANY one-to-many relationships. At least thats the way I envision it. I dont want to start too far into this without making sure the database is laid out correctly. Im not sure if Ive provided enough info here but would gladly discuss cost of consultant work for help with database design.

Adam Schwanz
37 days ago

If you want to e-mail me at aschwanz@hotmail.com to discuss costs and what you need.

Thanks

Nested Subforms Upload Images Link
David Cooke
46 days ago

Apologies if this is a double post as I wanted to post here first.

Followed the video for nesting one to many subforms, however I have 2 many to many tables (I believe) that I cannot for the life me get to work. I can get the bottom half to work but then when I add them in the subforms half the subforms don't change when I change the top record. 3 lookup tables that match two junction tables (2 table to one junction and 2 tables to the other junction. Looking for live assistance. thanks Read More...

Alex Hedley
46 days ago

LINK

Richard Rost
46 days ago

Hi David. I don't allow links to external sites for various reasons. Please use the Upload Images link at the top of the thread. Thanks.

Show 2 More Replies...

Automation Of Reports Upload Images Link
Jeremy Guenthner
49 days ago

I am a trucking company with a database that I had started and have been using for 3 years. I would like to have a report that pulls from multiple query's and run a end of year Report. I have started but don't really have the time to learn and rework the database at the same time as running my business. If any advance students or those that do this on a daily bases I would be happy to pay for the service.

Adam Schwanz
49 days ago

If you want to e-mail me at aschwanz@hotmail.com to discuss what you need and costs, I'd be willing to work on it

Thanks
Adam

Show Older Comments...
View in Table Format