Results 1 to 7 of 7
  1. #1
    CzarBomba is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2016
    Posts
    4

    Cool Introduction/Loaded Access Questions

    First of all, I'm new to Access so bear with me a little... I started as the sole data entry person and have been using Access on that basis until recently. It's a long story so all I'm going to say is that this database is a frankenstein's monster and plagued with some logical issues from the very beginning. Basically someone had a good idea but didn't fully understand how to bring that idea into fruition and can't seem to understand why they're not getting the results they want. From a data analysis point of view this a is complete and utter nightmare. Over time I've managed to deal with some of the issues (more like create workarounds or manual fixes to reports that are a waste of my time) eventually I'm going to create a new database but that will take some time and I want to make sure I 100% know what I'm doing.

    I guess the primary function of the database is to record production activity (its a manufacturer) but honestly it's hard to say. I also use it to create reports that explain/break down profit/loss margins on specific jobs, and come up with an average time frame it takes for a job to be completed (to use for giving customers due dates that aren't unreasonable). I also created a section for capturing info related to machine issues in order to steer maintenance in the right direction from time to time.



    Issue #1 I need to log activity from other users. Now I did put in a module that logs when people open/close a form, who's computer did it and the time... It works beautifully but what I really need is an ability to capture any errors that come up? Is this possible? Every time I'm gone for a day I come back and I'm told something vague like "the program doesn't work!". I ask what happened, what did the error message say, they say, "I don't know it just won't let me do anything!" It's not helpful at all. I'd love to ban certain people from using it at all but unfortunately my need for the paperwork to not back up is more important.

    Issue #2 Can I create a macro that will zap out rows with null values? This is driving me nuts. The office also has to fill out production sheets but they do it differently. They actually enter the information into Excel, email it to me, I save their stuff to a folder, and then run a macro on Access that grabs the files from the folder, uploads, appends to the appropriate table, and opens up a report that I can print. It works great. Except for null rows getting uploaded. This problem has been going on since I started. I tested a few theories out and it looks like what they're doing is just opening up the file (I have them using the same file over and over because someone kept screwing up the header) and typing over whatever they typed the day before. Sometimes they end up with extra lines so they highlight them and hit the delete key. For some reason Access thinks there's still data in there. I told them to delete the actual rows but they're not doing it and after four months I gave up on telling them to.

    Issue #2 People logged in to .LACCDB except that they are not even in the database at the moment. Causes locking error. Weird!!! Can I kick them out?

    Issue #3 Separate logins (complete with username/password prompt) for different people. Possible?

    Issue #4 Best way to create an area for someone to view/print reports but not have the ability to do anything but that. Such as a completely separate area where they cannot even see anything else but what I want them to see. Linked database maybe?

    50% of my issues are lack of knowledge. The other 50% are people. Please help me increase my Access knowledge so I can circumvent the people problems

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what I really need is an ability capture any errors that come up?
    use error handling
    Can I create a macro that will zap out rows with null values
    couple of options. You haven't detailed how you are loading the data but if using something like transferspreadsheet, have a query run after loading to delete null rows. Otherwise my preferred method is to link to the spreadsheet then use an append query to add the data to a table - query can have criteria to exclude rows
    People logged in to .LACCDB except that they are not even in the database at the moment. Causes locking error. Weird!!! Can I kick them out?
    common subject - there is a way, saw it in a thread recently
    Separate logins (complete with username/password prompt) for different people. Possible?
    yes, common subject - again google for a solution that suits you
    Best way to create an area for someone to view/print reports but not have the ability to do anything but that. Such as a completely separate area where they cannot even see anything else but what I want them to see. Linked database maybe?
    don't know what you mean by area. reference to linked database concerns me - database should be linked and implies your second issue #2 would be solved by having a split db (and all users have their own front end

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    1. Probably need error handler code. This code can also have instructions to save error info to a table. http://allenbrowne.com/ser-23a.html

    2a. Run DELETE sql action

    2b. Maybe. The LACCDB can be opened in a text editor. If have experimented with deleting info from this file but not very successful. Have them open db and close properly. If you know no one is use db, try simply deleting the LACCDB.

    3. Login process is common topic, search forum.

    4. With a login process, the user and their permissions would be known. This info can be used to manage what is made available to users (such as disable buttons). However, be aware that holding shift key while opening db will bypass db option settings and autoexec code and everything will be available to user.

    Is this a split design?

    Here is a good tutorial site http://www.rogersaccesslibrary.com/
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    CzarBomba is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2016
    Posts
    4
    Quote Originally Posted by Ajax View Post
    couple of options. You haven't detailed how you are loading the data but if using something like transferspreadsheet, have a query run after loading to delete null rows. Otherwise my preferred method is to link to the spreadsheet then use an append query to add the data to a table - query can have criteria to exclude rows
    I used to go to load their sheets one by one (external date, import excel spreadsheet, append to correct table- basically just the basic way) but it was so time-consuming what with so many sheets I created at Macro to do the work for me.

    Didnt think about a delete query... So a few minutes ago I added a delete query to the macro with criteria "is null" to the fields. Worked perfectly.

    Quote Originally Posted by Ajax View Post
    yes, common subject - again google for a solution that suits you
    Tried Google all week... Information overload I guess, I just can't seem to resolve that problem or better yet figure out what's causing it.

    Quote Originally Posted by Ajax View Post
    don't know what you mean by area. reference to linked database concerns me - database should be linked and implies your second issue #2 would be solved by having a split db (and all users have their own front end
    By area I mean... See I'm hesitant to call it a form. It's not technically a form because it's not a place to enter information... (the hypothetical form) It's just a page with buttons for them to press that view specific reports and that's all. But I suppose you could say, it's a form linked to all the reports and *nothing else*. This what I meant about having to circumvent people problems. The original admin used to go in to the database to view reports/queries for our production meetings and end up editing information in the tables because he saw something that didn't jive and assumed it was wrong (it was not) thus screwing up the data. I fixed that problem by creating a password, but I don't want to be bothered every time someone wants a report made (and I don't trust them with getting in the DB either)

    I actually want a split DB for a variety of reasons. (The above is one of them) but unfortunately I can't right now.

  5. #5
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I just googled 'access login form' and had plenty of hits - here's one http://www.iaccessworld.com/create-l...orm-ms-access/

    unless you split your db you are likely to run into many problems - particularly corruption from which you can only recover by reinstating a backup

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    A form object is still a form object even if it is not bound to data. I have a 'main menu' form that is just a collection of buttons.

    Lots of threads in forum about login and managing split db.
    https://www.accessforums.net/access/...gin-23585.html
    https://www.accessforums.net/code-re...end-33331.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    CzarBomba is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2016
    Posts
    4
    Thank for the assistance everybody. I'm getting steered in the right direction now. Hopefully I can get this database split

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Stop the introduction of records
    By azhar2006 in forum Forms
    Replies: 27
    Last Post: 02-14-2014, 03:42 PM
  2. Replies: 3
    Last Post: 11-16-2012, 10:21 AM
  3. How to check if any form is loaded
    By Kivan in forum Programming
    Replies: 3
    Last Post: 09-03-2012, 08:50 AM
  4. Good introduction to programming in Visual Basic?
    By sshepard in forum Programming
    Replies: 1
    Last Post: 09-22-2011, 11:07 AM
  5. Introduction and Questions
    By sblanky in forum Access
    Replies: 2
    Last Post: 01-19-2011, 08:12 PM

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