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.

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.

Absence Tracking Upload Images Link
Eric Wallace
8 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
8 days ago

Can you share what you've tried code wise?

Adam Schwanz
8 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
8 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
8 days ago

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

Adam Schwanz
8 days ago

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

Eric Wallace
7 days ago

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

New Database Help Upload Images Link
Ray Marotta
11 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
11 days ago

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

Ray Marotta
11 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
10 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
9 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
9 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
9 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
9 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
9 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
9 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
9 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
11 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
11 days ago

Show 6 More Replies...

Jim Baldner
11 days ago

Richard Rost
11 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
11 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
11 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
10 days ago

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

Richard Rost
9 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
13 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
12 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
12 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
12 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
12 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
12 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
11 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
9 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
30 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
30 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
39 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
39 days ago

LINK

Richard Rost
39 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
42 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
42 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

Acdb with ActiveX Controls Upload Images Link
Titus Hoover
42 days ago

Hello
Where do I go for help to get my database working on my new PC? I use my db to communicate with a PLC via an Activex Control Library. All the ActiveX Controls work fine on Excel But not on Access. Someone suggested that I might have to manually edit the registry. This happened when I switched to a newer PC. Read More...

Richard Rost
42 days ago

Hi Titus. I've moved your post to my Access Developer Network. I don't personally offer consulting and troubleshooting services myself, but maybe one of my other expert Access students will be able to help you.

Richard Rost
42 days ago

Very strange that the controls will work in Excel but not Access however. Personally I tell people to avoid 3rd party controls like the plague, but as we've seen with Covid, people don't try to avoid the plague that hard either these days. LOL

Show 3 More Replies...

Titus Hoover
42 days ago

There is a PortOpen control that opens a handle to the PLC and that works fine but as soon as I run an AreaRead then Access shuts down.

Richard Rost
42 days ago

I haven't done any work with PLCs since the 90s. LOL. If the controls work with Excel but not Access, try running down my Troubleshooter. Maybe you've got a corrupt ACCDB file or something like that.

Titus Hoover
37 days ago

I finally got the db working again. I had to rewrite a few lines.  Can someone tell me the difference in the code below?
Public p As New FP_CONNECT
Public plcDataArrayReturned as variant
Public Function connecttoplc() As Boolean
Dim plcDataArray(1) As Long
Dim strError As String
Dim plcResult As Integer
     plcResult = p.PortOpen(0, 1)
    If plcResult <> 0 Then GoTo ErrorHandler
        plcDataArrayReturned = p.ReadBits(0, "R10", 1, strError)              '  This worked on my old PC

        plcDataArrayReturned = p.ReadBits(0, "R" & CStr(10), 1, strError)  '  Now I have to convert the number 10 into a string

        ' Why wouldn't the compiler take "R10" as a string only?

        If strError <> "" Then GoTo ErrorHandler
        t = plcDataArrayReturned(0)
     If t = False Then GoTo notheattreating
   ' Other Code
notheattreating:
'
    Exit Function
ErrorHandler:
'
End Function

Online Database Upload Images Link
Blanca Malpartida-Girard
2 months ago

HI,

Can I share an Access Database with some people on-line.
(Need to know before proceeding to build my data in MS Access.

Thanks,
Blanca Malpartida-Girard

Adam Schwanz
2 months ago

This should help you. Access Online

Update 1 record from query Upload Images Link
Cita Reid
2 months ago

Good afternoon, I have HR database that I use to track employees throughout my company. The employee can be assigned to multiple directorates (offices, boards) throughout my company; however, they can only be appointed to the specific directorate once. I have 5 tables (ParentOrg, Directorate, Personnel, Appointment, Action). Each table has an autonumber ID field and I have created a composite key (SSN & DirectorateID) to ensure employees are only appointed once to any directorate. The trouble I am having is creating a relationship from the Appointment table to the Action table. Although an employee can only be appointed once to a specific directorate, they can have multiple actions (promotions, training, awards, etc). I have created an Appointment Code for each appointment (DirectorateCode-LastName, FI) through a query, but I am having difficulty getting this query to update the individual record in the Appointment table. This is the foreign key that will be used to link the Appointment table and Action table (1-many relationship). I'm sure it is something simple I am missing. I'm not well versed in VBA or SQL, but I am joining this forum as Platinum member so I can learn as much as I can. I appreciate your assistance.

Richard Rost
2 months ago

Hi Cita, and welcome. It's difficult to understand exactly what you're trying to do because we're not familiar with your table and field names. Sounds like you've got the right setup for your employees and directorates. As far as appointments and actions go, those should be simple many-to-many relationships with a junction table for each. Are you familiar with how to set that up? If not,

Show Older Comments...
View in Table Format