Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Bobrosss is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2021
    Posts
    17

    Please help

    So, I think you should know right off, that I am not very well versed in access. I have tried making databases in the past and I get just so far and I get stuck, lose interest and delete the program I was building. I'm not too good with the correct wording of specific tasks in access, so I will do my best.
    I built a program called "central hub". The purpose of this access program is that we have a lot of programs that are pre-built and there are several copies and it's hard to dig through and find the correct things. Even if you know where everything is, there are several databases that we open, some even at the same times. I thought by making one database, I could incorporate 2 new ones that my business needs and drag all the previous built programs (as buttons) to one spot and then give each employee that uses it, a single handy icon.


    What my goal here is, I want to open the program and have a pre-filled log that will incorporate information from 2 separate forms that gets filled out at the end of their shift. I want the forms to populate this "central hub" so it is read over by the operator coming in for relief.
    In one form, I want my engineer to fill out daily accomplishments and night time (24 hour operations) expectations. In the other form, I want to have the operators fill out their own type of form. Once each person has filled out their required form, I want their forms to populate the landing pages (main form ("central hub")) respective fields. I have tried to design queries and even dlookup strings, but somewhere I am losing how to get from point a to point b.
    Each form has a table. Each table has a field that is represented in their own forms. I tried to make sure that I named fields in such a way that they don't get mixed up while combining the info from both forms.

    Any help would be great at getting me pointed in the right direction. I can give more descriptions if needed, but they will be limited to my understanding if access.
    I know there are relationships, queries and parent/child forms, but how they all work is limited on my end.
    Thanks in advance for anything you can offer

  2. #2
    Bobrosss is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2021
    Posts
    17
    Also, I should mention that the "central hub" form would need to pull the most current entered data to be relevant for shift relief

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    All I can say is it’s doable but what is the best way depends on what you have.

    First off - are your apps split? I.e. each user has their own local copy of the front end which contains every thing except tables, and the back ends all located on the server any contain only tables.

    Next, you can merge the back ends together - but are some tables duplicated at least in name? If so the this will need to be addressed

    The same goes for the front end- do you have same name forms/reports/queries an d subs/functions in a standard module?

  4. #4
    Bobrosss is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2021
    Posts
    17
    I was able to build a report to pull in a specific field in a table, but when I do that, it puts everything in that column, in theory own row of a page in the report. I want 3 or 4 fields from the most current saved report on each form to be displayed on 1 page.

  5. #5
    Bobrosss is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2021
    Posts
    17
    Ajax, thanks for the reply, I will do my best to answer this.
    So, there are currently 3 databases, each with their own front and back ends. When a person logs in, there is a module (I think) that pulls the getuser or global info to automatically get who is filling in the databases, based on who logged into windows. There are 2 versions of access running these databases, so we can view what is being added at the time they are being added from another pc on the same network.
    The current databases work fine as their own databases and technically I would prefer to not join them at this time. I have made a control link that opens the old databases from the new "central hub" format.
    So my thought was that it should be simple to just make a new text box on the landing page of "central hub", and then link the necessary text boxes from other created forms and poof, the magic happens, but it doesn't 😉.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you have defined relationships I suggest you post a pic of that.
    Or consider that 1 db (front end) can link to several back ends which may be what you're thinking you want. However, unless one db would be too large if all the tables were in one back end there's no sense in not having them all together. I suspect that you've broken things out too much and probably have too much replication of objects (tables, forms, etc.) based on your comments about having to open several db's to find something. One form per table isn't really a red flag - unless those tables are basically the same (e.g. tblJanuarySales, tblFebruarySales and so on). That would be wrong.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Bobrosss is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2021
    Posts
    17
    Basically, I have 2 databases that run 24/7. An operator looks at what is called the "panel board database". The assistant uses "lab database". The operator has a version of the lab database open to make adjustments to a distillation process. These databases have info dating back as far as the late 80's, of which probably is that way due to a lack of knowledge on purging some of these older records. I don't really want to change those databases, but I wanted to incorporate a evening new log and a operator relief log to the computer side of things. I thought I could just make a separate database to store the new logs and put links to the old DBs on the main page of the new DB. Keep in mind that I only have knowledge of a tinkerer who has never had any training of access. I have muddled through a few excel documents that had formulas and calculations, but that's about it.
    As for the relationship thing, I have tried a few times to make relationships and that is probably where I am lost. I want to link 2 forms, one that doesn't have a table (the "central hub"), and I have thought about making a... replicated table that has both the evening news fields and the operator relief log fields and then joining them by relationship. Doing so won't help with making the info display on the landing page though, right? I still need some macro or something to pull that info over onStart or something?!

  8. #8
    Bobrosss is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2021
    Posts
    17
    Tomorrow, I can try to take pictures to give a better understanding of what I have and what I want

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sure, post your pics and we'll see. Maybe what you really need is to consult with a developer who knows what questions to ask so that something can be created that fits your needs, because for the most part we help others solve design and functional issues. Your disadvantage might be that you don't know how to begin to arrive at the finish line because you don't know the possibilities of Access. Pretty much anything you can think of can be done - if the design is right - and then some.

    Some tips in case you ask for design advice: When it comes to what the db is supposed to support, I suggest you start at the 30,000 ft overview and come down from there. One thing that adds clarity is if you use a non-database term that isn't in everyone's vocabulary, explain it then stick with it. Don't call something a "central hub" in one sentence and then call it a "landing page" later (just using that as an example, not saying it wasn't warranted in your post). We're used to knowing things by their common db/Access terms so it might help if you Google some loosely defined phrase and see what that brings up. To continue with that example, you might find that what you want is commonly known as a switchboard. Then when you write switchboard, we know exactly what you mean. Try to avoid business jargon as well. You might think that everyone knows what you know about your business, but trust me when I say that's not going to be the case.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to Micron's advice, I would suggest that you tell us about the business in plain English -- what is the primary business?
    Then how do the different databases/applications fit and/or support that main business. Readers need some context -they/we don't know you or your business nor the environment nor the purpose of the seemingly related databases. I agree with the 30,000 ft level until some issues are identified- then there may be options. I'm sure you'll get more focused responses as the details evolve.

  11. #11
    Bobrosss is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2021
    Posts
    17
    Sorry, work has been hectic. It's a distillation facility. Assistant pulls samples and enters the same into a table type database and the operator makes adjustments off of those results. Then the operator uses possibly a split form database and takes a snap shot of the process (temperatures, vacuums, levels, etc...). This database displays the last 6 entries and the new entry to the left.
    I don't want to mess with those. I only want a program that they can open, that has all the links to the other databases , but to have them all in one display that has 2 sections to read when the database opens (evening news and operator to operator relief). I already made tables and forms for the news and the relief log, I just couldn't understand how to make them viewable when the program opened and how to make them grab the newest record to display.
    As I mentioned before, I assumed it was something like dlookup, or "onStartup" or something. After tinkering a little, I'm almost thinking that a query or report would work, but again, the disconnect I have is where to make them pull a tailored report to the "central hub".
    I am familiar with how to make a specific page load in a database, so I have that going for me hahaha 😆

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Seems to me that as per post 3 all you need to do in a new db is link to the tables that contain the records that you need which are in the existing databases. If there is to be supporting data that you want to enter into this new db while leaving the rest alone, that is doable - just do it in new tables. Hopefully that does not mean duplication of data. To show any particular form when you open you can simply set it as the startup form in options. Perhaps that would be what we call a switchboard form (I would not use the built in one) or some type of dashboard. If you want a simple form that just shows one record, it's really no different in how you accomplish that beyond applying some sort of filter. If you want a report instead, I believe the only option is to use an AutoExec macro that calls code (sure, you can use just the macro but macro support here beyond the simplest operation seems to be scarce - I for one don't use them). Easy enough to find code samples if, as I've already noted, you Google phrases. Maybe "ms access open form on startup" or something similar for that particular piece of the puzzle.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    BobRoss,

    Thanks for the description. You mentioned a picture in post #8 --?
    As has been said, these databases or more specifically the tables in those databases can be linked to your new database. You can use data from the various tables to populate needed forms and dashboard/hub. You will need to know the sources (database/tables/fields) for your central hub and any related procedures that are spawned from it. Probably time to "map out" what your central hub will contain. I suggest you focus on WHAT that hub is composed of before jumping too quickly as to HOW it could be implemented. May be easiest to mock up reports/forms etc that are of interest, then identify the related sources of the info and any processes/processing involved. Then move on to options for HOW.
    Good luck.

  14. #14
    Bobrosss is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2021
    Posts
    17
    Central hub https://imgur.com/gallery/OwwmTB2
    In this link, you will see 3 pictures. Top one is the rough sketch of what I need. There are buttons at the top. I can make functioning buttons, so that's not an issue for me. With my lack of access knowledge, I'm not sure what it is that I am exactly needing. I can pay a developer, but I wanted to give it a good effort before giving up and going that route. When I say that I don't know what I mean, I am referring to the fact that I know how versatile access is, and I'm not sure if this loading page of the central hub is a query, a report, or a form. I don't want the page altered ever, I just want to have the 2 fields filled out and saved. Then, when the central hub is opened, I want the info from the last saved form from both fields (evening news and operator log) to just be there. Poof..
    So with access, I am aware that you lock people out of specific areas and prevent altering of records/forms. Another situation where I can tinker until I figure that part out, not concerned about it. Like I figured out that you can change a field in the forms properties to "data entry" or something to that effect and now the form is blank when I open the operator log, instead of having my test entries.
    Last edited by Bobrosss; 10-09-2021 at 07:21 PM. Reason: Post was not needed, so I want to reuse it

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well, I have out of country visitors so not sure how much I'm going to be able to chime in until Wednesday. It's our Thanksgiving weekend.
    There is a link at the very top about how to attach files. Best that you post your pics here, methinks.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums